Google Apps Script（GAS）に関するTipsを書きためていきます。

実行中の関数を強制停止する

https://script.google.com/home/executions
にアクセスして、対象の実行をキャンセルします。

ログ確認

Logger.log()で出力したログはstackdriverに出力されますが、
https://script.google.com/home/executions
から確認することができます。

スプレッドシートから値を取得する

1〜最大行数まで2〜3列目の値を取得するケースについて書いてあります。
対象データが多い場合は、getValues()で二次元配列として一括で値を取得するのが鉄則です。

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName('シート1');
var lastRownum = sheet.getLastRow();

// for文でrowとcolumn指定しながら逐次値を取得するパターン
// -> getValue()=1API呼び出しなので、API呼び出し回数が多すぎるとコストが大きい
for (var rownum = 1; rownum <= lastRownum; rownum++) {
  var valueRow2 = sheet.getRange(rownum, 2).getValue();
  var valueRow3 = sheet.getRange(rownum, 3).getValue();
}

// rangeを取得して、値をまとめて取得するパターン
// -> range内の値を二次元配列として取得する。値なしでも""が設定されるので、行数・列数は固定。コストは小さい
var values = sheet.getRange(1, 2, lastRownum, 2).getValues();

スプレッドシートに値を設定する

1〜4行目の2〜3列目に値を設定するケースについて書いてあります。

var values = [
  [11, 22],
  [33, 44],
  [55, 66],
  [77, 88],
];

// 1セルずつ設定するケース
for (var i = 0; i < values.length; i++) {
  var row = values[i];
  for (var j = 0; j < row.length; j++) {
    var value = row[j];
    sheet.getRange(i + 1, j + 2).setValue(value);
  }
}

// まとめて設定するケース
sheet.getRange(1, 2, 4, 2).setValues(values);

外部APIを呼び出す

UrlFetchApp.fetch APIを利用します。

function main() {
  var name = 'Taro Yamada';

  var url = getGooglePatentUrl(name);
  var options = {
    'muteHttpExceptions': true // 503エラーなどがおきても、エラー扱いにしない
  };
  var response = UrlFetchApp.fetch(url, options);

  var responseCode = response.getResponseCode();
  var responseBody = response.getContentText();
  var jsonData = JSON.parse(responseBody); // このAPIはapplication/jsonを返すのでjsonパースして利用する

  Logger.log(jsonData.hoge.fuga);
}

// Google Patentの発明者検索APIのurlを返す
function getGooglePatentUrl(name) {
  normalizedName = name.replace(/,/g, '').replace(/ /g, '+');
  query = encodeURI('inventor=' + normalizedName);
  return 'https://patents.google.com/xhr/query?url=' + query;
}

スプレッドシート上のボタンにスクリプトを割り当てる

以下の手順で割り当て可能です。

  1. スクリプトを記載する
  2. 図形描画でボタンをスプレッドシート上に配置する
  3. ボタンの「・・・」メニューから「スクリプトを割り当て」を選択して、事前に作成しておいたスクリプト名を記入する
  4. あとはボタンをクリックすると、スクリプトが実行される
    ※ダブルクリックすると図形編集画面が表示されます

最終行を取得

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

// シートの最大行を取得
var rownum = sheet.getMaxRows();

// シートの中で値が入力されている最大行を取得
var rownum = sheet.getLastRow();

// A列が空白でない最終行を取得（空白じゃない値の数を数えている。途中に空白があるケースでは使えない。でも高速）
var rownum = sheet.getRange('A:A').getValues().filter(String).length;

// A列で空白でない最終行を取得（LastRowから逆にチェックしていく。途中に空白があってもOK。でも低速）
var rownum;
for (var i = sheet.getLastRow(); i > 0; i--) {
  if (!sheet.getRange(i, 1).isBlank()) {
    rownum = i;
    break;
  }
}

V8エンジンを有効にする

2020年2月にGASがV8エンジンをサポートするようになりました。現在では新しくGASのプロジェクトを作成すると、V8エンジンが有効になっているようです。
古いGASのプロジェクトの場合でも、実行タブから有効にするだけで簡単にV8エンジンを利用できます。

const や let のスコープ

古いGASのJavaScriptエンジン（V8エンジンを無効にした状態）

変数宣言に constやletも使えるのですが、スコープがおかしなことになっています。

function myFunction() {
  for (var i = 1; i <= 2; i++) {
    const constVal = i;
    let :letVal = i;
    Logger.log('forブロックの中: constVal: ' + constVal + ', letVal: ' + letVal);
  }
  Logger.log('forブロックの外: constVal: ' + constVal + ', letVal: ' + letVal);
}

この出力結果は以下です。

[19-09-27 10:16:36:725 JST] forブロックの中: constVal: 1, letVal: 1
[19-09-27 10:16:36:726 JST] forブロックの中: constVal: 1, letVal: 2
[19-09-27 10:16:36:727 JST] forブロックの外: constVal: 1, letVal: 2
  • constやletはブロックスコープを期待すると思いますが、forブロックの外側で参照できてしまっています
  • constで宣言したconstValが1ループ目の値のまま変更されていません。まさかそんな動きをするとは思わないのでかなり危険です
  • letで宣言するときは、変数名の前に:をつけないと、ステートメントの前に ; がありません。という謎のエラーになります
  • letをfor文の初期化式に使おうとするとfor-loop 初期化子の後に ; がありませんという謎のエラーになります

こんな状況なので古いGASのJavaScriptエンジンでは、varで統一しておいた方が無難だと思います。

V8エンジン

constやletがブロックスコープになりました。

function myFunction() {
  for (let i = 1; i <= 2; i++) {
    const constVal = i;
    let letVal = i;
    Logger.log(`forブロックの中: constVal: ${constVal}, letVal: ${letVal}`);
  }
  Logger.log(`forブロックの外: constVal: ${constVal}, letVal: ${letVal}`);
}

出力結果は以下です。

[20-06-11 21:35:32:489 JST] forブロックの中: constVal: 1, letVal: 1
[20-06-11 21:35:32:492 JST] forブロックの中: constVal: 2, letVal: 2
[20-06-11 21:35:32:498 JST] ReferenceError: constVal is not defined
    at myFunction(コード:8:60)
  • ちゃんとforブロックの外側で参照すると、constで宣言した変数constValはReferenceError: constVal is not definedというエラーが発生しています
  • constで宣言した変数constValの値がちゃんとループのたびに変わっています

テンプレート文字列を使ってログ出力

V8エンジンだとテンプレート文字列を使えます。
ログ出力する時に変数名と値をセットで出力することが多いと思いますが、以下のようにテンプレート文字列に変数を埋め込んで書くことができます。

const hoge = 'xxxx';
const fuga = 'yyyy';

// 単純な埋め込み
Logger.log(`hoge: ${hoge}, fuga: ${fuga}`);
// hoge: xxxx, fuga: yyyy

// 改行もできる
Logger.log(`hoge: ${hoge}
fuga: ${fuga}`);
// hoge: xxxx
// fuga: yyyy

コードのGIT管理やES6/typescriptでの実装にむけて

GASは基本WEB上のエディタで実装しますが、claspというCLIツールが登場したことで、ローカルで実装してGITにプッシュしつつ、Google Driveにclaspでデプロイというような、普通のWEBアプリの開発スタイルも簡単に実現できるようになりました。また、ES6やtypescriptで記述されたコードを、claspでES5にトランスパイルすることもできるみたいなので、上に書いたようなGAS特有の事情を気にするよりも、ES6なりで書くのが良さそうです。

トラブルシューティング

"起動時間の最大値を超えました"

どうやら、スクリプト実行時間は、こちらを参照すると、Consumerだと1実行あたり最大6分までのようです。G Suite Businessだと30ふんまでです。
ググると、スクリプトプロパティ＋トリガーを使った回避策などが見つかります。

"Exception: ドキュメント（xxxxxxxxxx）にアクセス中に　スプレッドシート のサービスに接続できなくなりました。"

SpreadsheetApp.openByUrl()やSpreadsheetApp.openById()でスプレッドシートではなくExcelを開こうとした場合に発生します。Excelだと気づかずにOpenしようとして、しばらく気づかずに悩みましたww

関連記事
  1. BigQueryのTips
  2. puppeteerでファイルをダウンロード
  3. typescriptプロジェクトにreCAPTCHA v2 & v3を導入
  4. pythonにおける親/兄弟階層のモジュールimportの方法
  5. Vue.js, TypeScript, FirebaseでChrome Extensionを作った