Googleスプレッドシートで管理シート作成時に活用したこと

GoogleAppsScriptでscriptを初めてまともに組んだので、今後に再活用できるようにまとめました。

classオブジェクトを準備

シートの行ごとにclassを用意する。例えばシートが以下を想定すると

id	名前	年齢	作成日
1	Taro	21	2022/07/01
2	Jiro	20	2022/08/01

書き方は色々あるけど今回は以下のような書き方を採用。

class UserInfo {
  constructor(record) {
    [this.id, this.name, this.age, this.createDate] = record
  }
}

定義場所は試した限りだと以下のように、特定の関数内でも問題なかった...(スコープ外かと思ったけど)

// トリガー設定している関数
function main() {
	const user = getUserInfo()
	console.log("user name: " + user.name)	// user name: Taro
}

function getUserInfo() {
  class UserInfo {
    constructor(record) {
      [this.id, this.name, this.age, this.createDate] = record
    }
  }
  
  return UserInfo("1", "Taro", "21", "2022/07/01")
}

Google Apps Scriptはグローバル領域があるようなので今回は以下のように定義。

class UserInfo {
  constructor(record) {
  	[this.id, this.name, this.age, this.createDate] = record
  }
}

// トリガー設定している関数
function main() {
	const user = UserInfo("1", "Taro", "21", "2022/07/01")
	console.log("user name: " + user.name)	// user name: Taro
}

スプレッドシートの値を取得してclassオブジェクトの配列として保持する

セルを指定して1セル分を取得することなどもできるが、今回は指定した1シート全ての値を取得する。

const values = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート名").getDataRange().getValues()

あとは上で作成したclassオブジェクトのインスタンスを生成して配列に追加していけばOK

var result = []
for (let i = 0; i < values.length; i++) {
	if (i == 0) {
		// 先頭はシートのラベルなのでもし保持する必要がない場合は0番目は保持しないようにする
		continue
	}

	const info = new UserInfo(values[i])
	result.push(info)
}

ついでにGASでのfor文の代わりとしてforEachがある。

values.forEach(function(item){
	result.push(item)
})

またはindexありのforEachもあるが...

values.forEach(function(item, index, array){
	result.push(item)
});

https://auto-worker.com/blog/?p=5715#
forEachは非推奨かつループスキップができない様子。

さらにforEachは配列オブジェクトのメソッドなので、breakやcontinueといった処理中断やスキップができません。そのため、新しくGASのスクリプトを書く際はforEachではなく、for…ofがオススメです。

indexを使わないのであればfor ofが一番良さげ?

for (item of values) {
	result.push(item)
}

配列を並び替える

作成日(createDate)の古い順にする場合は

// valuesはconstではなくvarで宣言している前提
values.sort((a, b) => (a.createDate > b.createDate ? 1 : -1))

新しい順にする場合はa.createDate < b.createDateにすればいけるはず。

スプレッドシート側で作成日の入力セルを日付に設定した場合はどうなる

データの入力規則 > 日付 を設定するとカレンダー形式で日付を選択できるようになるが、その場合にGAS側で取得する値はStringではなくDate型になる。

Date型をString型に変換して扱う

formatDateはDate型の値, タイムゾーン, 日付変換のフォーマットの順で引数に設定する。デフォルトのタイムゾーンがAsia/Tokyo。

const info = UserInfo()	// 初期化は省略(createDateはDate型)
const strDate = Utilities.formatDate(info.createDate, "Asia/Tokyo", "yyyy/MM/dd")

Date型の加算減算

1日前にする場合は以下で

const info = UserInfo()	// 初期化は省略(createDateはDate型)
var date = new Date(info.createDate)
date.setDate(date.getDate() - 1)

1日後にする場合は-1を+1に変えればOK

date.setDate(date.getDate() + 1)

GASのイベントの種類を把握する

現在規定されているイベントのタイプは以下の2つです。

1. Simple Triggers
2. Installable Triggers

とあるように編集時にデフォルトでトリガーになるのはSimple TriggersのonEdit関数など。
今回はInstallable Triggersで自分でトリガーを編集時に設定した。理由はシートに書き込みなどをするときの権限不足を解消するのが楽だったから。（後述）

Installable Triggers、つまり自分で独自の関数を定義してトリガーを設定するパターンで知っておくべきこととしては、トリガーに設定した関数の第一引数にはトリガーオブジェクトが入ってくる。

function callEdit(e) {
	// eからシートの情報が入ってる（引数名は任意）
}

GASで権限を追加する

書き込み操作などをする際に権限が不足していてエラーになる。その場合はappsscript.jsonのマニフェストに権限を追記する必要がある。マニフェストはデフォルトだと非表示なので以下のように表示設定をする。
https://blog-and-destroy.com/42443

追加したい権限をoauthScopes内に追加していく。

"oauthScopes": [
	"https://www.googleapis.com/auth/spreadsheets"
],

追加して保存するだけでは反映されておらず、トリガーではなく一度scriptを実行すると権限許可のダイアログが表示されるのでそこで許可する必要がある。※分かりにくいけど

スプレッドシートのセルに書き込む

上の権限が確か必要になったはずなので注意。
rangeRowは書き込む行番号、rangeColumnは書き込む列番号(A列が1)で、getRange("A3")のような固定でも良い。

const sheet = SpreadsheetApp.openById("シートID").getSheetByName("シート名")
const range = sheet.getRange(rangeRow, rangeColumn)
range.setValue("書き込む値")

スプレッドシートの指定した範囲に書き込む

セル単位ではなく、一気に指定範囲内を書き込む場合はその範囲をgetRangeで指定して、setValuesで設定すれば良い。

const range = sheet.getRange(startRow, startColumn, endRow, endColumn)
range.setValues(values)	// valuesは指定した1行分のセル数を1要素として持つ配列

注意点としてはendRowの設定値について。今想定しているシートは以下のようになっており、書き込みたいのは先頭行のラベルを除く行とすると、

id	名前	年齢	作成日
1	Taro	21	2022/07/01
2	Jiro	20	2022/08/01

const range = sheet.getRange(2, 1, 2, 4)

となる。第三引数のendRowがつい先頭から3行目までなので3にしたくなるが、1行目を除いて3行目まで書き込み場合は(3 -1) = 2を設定する必要がある（試した限りそうだった）。getRangeはA1:D3のようにも設定できるが今回は動的に設定したかったので数値で設定した。

編集されたセルの位置を取得する

動的に色々と対応するには編集されたセルの位置を把握したい時がある。上で説明した

Installable Triggers、つまり自分で独自の関数を定義してトリガーを設定するパターンで知っておくべきこととしては、トリガーに設定した関数の第一引数にはトリガーオブジェクトが入ってくる。

の件で、編集時トリガー関数の第一引数にそれらの情報が入ってくるので、event変数からrangeを取り出すことができる。

// トリガー関数
function editTrigger(event) {
  const editColumnStart = event.range.columnStart
  const editColumnEnd = event.range.columnEnd
}

シートの値がある最後の行または列番号を取得する

値を更新する場合などに、どこまで更新するかを設定する際に現在のシートの値がある範囲を把握したい時がある。該当シートのインスタンスから取り出せる。

const sheet = SpreadsheetApp.openById("シートID").getSheetByName("シート名")
const lastRow = sheet.getLastRow()
const lastColumn = sheet.getLastColumn()

例えばシートの中で、特定の列に限定して値がある最後の行番号を取得するにはそのセル(1つだけでOK)が含まれる範囲をrow, columnに設定すれば良い。

const lastRow = sheet.getRange(row, column).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow()

処理開始中にトースト表示する

処理中であることをユーザーに明示するために今回は簡易的にトースト表示することにした。トリガー関数の最初と最後でトースト表示することでそれっぽくなる。toastの第三引数は表示秒数で、0秒の場合は表示しっぱなしとなり、トーストを再表示すると前に表示していたトーストは非表示になる様子。

function editTrigger(event) {
  // process start
  SpreadsheetApp.getActiveSpreadsheet().toast("しばらくお待ちださい", "更新中", 0)
  
  // .. 実際の処理
  
  // process finish
  spreadSheetApp.toast("更新に成功しました", "更新完了", 2)
}

途中でException発生して処理が中断した場合は最後のトースト表示に達しないので最初のトーストが表示され続ける。とりあえずの対策としてはExceptionを拾うためにtry&catchでcatchしたら別トースト表示とかにすると良さげ。

セルに動的に選択肢(プルダウン)を設定する

あるシートの値を取得して、その値を選択肢としてセルに表示させたい時がある。別シートの値の取得方法は前述したようにすればOK

const list = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート名").getDataRange().getValues()

あとはareaをセル固定でいいのであれば"A2"のように設定してセルと作成したルールをsetDataValidationで紐づければOK

const rule = SpreadsheetApp.newDataValidation().requireValueInList(list).build()
const cell = spreadSheetApp.getSheetByName("シート名").getRange(area)
cell.setDataValidation(rule)

HTMLでカスタムダイアログを作成する

今回は使わなかったが以下を参考にすれば作成できる。トースト表示の代わりに今後はカスタムダイアログでユーザー操作を制御していく予定。
https://tonari-it.com/gas-html-show-dialog/

Git連携

今回は環境の理由でしていないが、後ほどGit連携を以下を参考にする予定。
https://kikaionchi.com/2022/02/24/gas-github-push/

参考

https://tonari-it.com/gas-class-spreadsheet-record/
https://xn--t8j3bz04sl3w.xyz/google-apps-script/getrange/3487/
https://hirachin.com/post-3321/
https://lediamix.com/journal/cording/gas-foreach/
https://www.javadrive.jp/javascript/array/index10.html
https://mebee.info/2022/03/01/post-57962/
https://blog.8basetech.com/google-apps-script/gas-formatdate/
https://www.relief.jp/docs/google-spreadsheet-gas-get-next-date.html
https://zw-kakeru.com/tips/gas-trigger-first-param/
https://moripro.net/gas-get-specified-lastcol-lastrow/
https://walking-elephant.blogspot.com/2021/05/updated-values-in-sheet.html
https://groups.google.com/g/google-apps-api-japan/c/xdSfTjEsRzQ
https://moripro.net/gas-setdatavalidation/
https://qiita.com/ume3003/items/dc6e7978a4c82c1bbbe8