Google Sheets (spreadsheet)で日付単位などの集計処理をやっていて、最近覚えた小ネタです。
🔗 日時を連続で出力する
縦にずらーっとデータを出したい（セルのオートフィルはめんどくさい）ときは SEQUENCE を使いますが、日付のときは少しだけ癖があります。
値の1はどうやら「1日」になるようなので、例えば10日分を表示したければこのようにします。
=ARRAYFORMULA("2022-01-01" + SEQUENCE(10,1,0))
これだけだと数字で表示されてしまうので、表示形式を日付にしましょう。
| そのまま | 表示形式を日付に |
|---|---|
 |
 |
10分ごとの時刻を表示したければ、1日（24時間）を10分まで割ればOKです。
=ARRAYFORMULA("2022-01-01 10:00:00" + SEQUENCE(100,1,0)/24/6)
同じく表示形式を日時にするのを忘れずに。
🔗 参照する別シートの名前をタイトル行から取得する
シートごとにデータを記録していて、それを集計するようなとき、他のシートを参照するには =シート1!A1 といった形式で参照します。
集計テーブルで、このシート名もセルの値から取りたいようなときは `INDIRECT が便利です。
=INDIRECT(A1&"!A1")
オートフィルやARRAYFORMULAなどと組み合わせると、いい感じになります。
🔗 1000万セルしか使えない
行数ではなく、セル数に1000万の上限があります。これはワークブック内の全シートが対象です。
1000万セルを超えるような操作をすると、このようなエラーが出ます。
GASから実行するとこのような例外が発生します。
Exception: This action would increase the number of cells in the workbook above the limit of 10000000 cells.
1000万というと多いような気もしますが、標準のA-Zで26列だと、38.5万行にしかなりません。僕の場合は、1分ごとの集計データを記録していたのですが、それが10シートあり、1分毎に260セル増えていったので、3.85万行くらいつまり1ヶ月弱で上限に達してしまいました。
ちなみにこれでも2022年に増えた結果らしいです。
参考: Google Workspace Updates: Google Sheets doubles cell limit
対策としては、とりあえず不要な列を消すのは効果的です。データが5列しかないなら、F列から右を消せば良いのです。これで5倍は入ります。
🔗 GASからデータを読み書きするには、全権限を与えるしかない
これはTIPSというよりはただの不満ですが...
Sheets標準の関数だけではちょっと辛いロジックを書くときは、GASを使うことが多いと思います。その際、シートのデータを読み書きするには
function myFunction() {
const sheet = SpreadsheetApp.getActiveSheet();
sheet.appendRow(["test"]);
}
と言った感じのコードを書きます。
これを実行しようとすると、スクリプトにSheetsを読み書きする権限を求められるのですが、最近は信頼されていない発行者の危険なアプリだと強い警告が出るようになりました。求められる権限も「Google スプレッドシートのすべてのスプレッドシートの参照、編集、作成、削除」と非常に強いものです。別にそんな権限いらなくて、このシートのデータだけ読み書きできれば十分なんですが...
紐付けられたシートだけの読み書き権限を付与できないか調べたりGoogleサポートに問い合わせたりしたのですが、結論としてはできません。ドライブに関する権限が以下の5種類しか存在せず、どうこねくり回しても特定のスプレッドシートだけに限定した権限を付与することはできないようです。
<スコープ>
https://www.googleapis.com/auth/drive :
Google ドライブのすべてのファイルを表示、編集、削除したり、Google ドライブにファイルを作成したりできます
https://www.googleapis.com/auth/drive.file :
このアプリで操作する Google ドライブ上の特定のファイルのみを表示、編集、作成、削除します
https://www.googleapis.com/auth/drive.readonly :
Google ドライブのすべてのファイルを表示およびダウンロードできます
https://www.googleapis.com/auth/spreadsheets :
Google スプレッドシートのすべてのスプレッドシートの参照、編集、作成、削除
https://www.googleapis.com/auth/spreadsheets.readonly :
Google スプレッドシートのすべてのスプレッドシートの参照
なお、権限を付与しないだけなら、権限要求ダイアログ出るかどうかは「スクリプト内に SpreadsheetApp という文字列が存在するか」で判定しているようなので、SpreadsheetApp.getActiveSpreadsheet() を globalThis['Spreadsheet' + 'App']['getActiveSpreadsheet']() のように書き換えれば権限を要求されなくなります。実行するとエラーになるので何の意味もありませんが。
最小限の権限を用意して欲しいというのをGoogle WorkspaceのFeature Ideasに投稿はしてみたので、反応があると良いな。
🔗 GASを書くときに、なるべく権限を不要にする
上述のとおり、GASからシートの内容を読み書きすると強大な権限が要求され、あまり気軽に使いたくありません。そこで、簡単なスクリプトでは権限を要求されないようにすると幸せになれます。
まず基本として、スクリプトに書いた関数はシートから呼び出せます。この形式では権限はいらないので、関数呼び出しの形で完結させることにします。
function mySum(a, b) {
return a + b;
}
さらに、
- 引数にレンジを渡せば、配列や二次元配列で受け取れる。
- 別シートのレンジも配列で受け取れる。
- 結果を配列で返せば複数行、二次元配列で返せば複数列複数行に出力される。
- 1行3列に返したいなら
return [[1,2,3]]のようにする。
- 1行3列に返したいなら
ということを踏まえれば、割と色々な事ができます。
/** 引数で渡されたレンジ内の各セルを2倍にしたものを返す */
function myDouble(range) {
return range.map(line => line.map(cell => cell * 2))
}
グローバル変数のようなものはないので、欲しくなったら適当なセルを仲介してやるか、マスタデータの類なら漢らしくスクリプト内にベタ書きするのも手です。このような工夫により、標準関数だけで頑張るのと権限を求める複雑なスクリプトの中間でバランスを取ることができます。
もちろん、より複雑な処理をしたいときや外部APIとして公開したいときなどはこうはいかないので、そういうときは諦めて強大な権限を付与するしかありません。