GoogleAppsScriptでよく使うコードまとめ

基本処理

ショートカット

ショートカット	説明
Ctrl+R	選択している関数を実行
Ctrl+Enter	ログ表示

※基本的なテキストエディタのショートカットは使用できます
　[Ctrl+Z]:元に戻す、[Ctrl+F]:検索、[Ctrl+S]:保存など

ログ出力

Logger.log(data);
Logger.log | Google Developers

Logger.log('Hello World.');

日付フォーマット

Utilities.formatDate(date, timeZone, format);
Utilities.formatDate | Google Developers

var string = Utilities.formatDate(new Date(), "JST", "yyyy/MM/dd HH:mm:ss");

スリープ(遅延処理)

Utilities.sleep(milliseconds);
Utilities.sleep | Google Developers

Utilities.sleep(1000);

メール送信

MailApp.sendEmail(recipient, subject, body);
MailApp.sendEmail | Google Developers

MailApp.sendEmail('hoge@gmail.com', '件名', '本文');

※自分自身のGMailに送信すると受信トレイには届かないため、注意(すべてのメールにはある)

プロパティ

Class PropertiesService | Google Developers

// ドキュメントプロパティ
var prop = PropertiesService.getDocumentProperties();
// スクリプトプロパティ
var prop = PropertiesService.getScriptProperties();
// ユーザプロパティ
var prop = PropertiesService.getUserProperties();

スクリプトプロパティ読込み

var prop = PropertiesService.getScriptProperties();
var value = prop.getProperty('key');

スクリプトプロパティ書込み

var prop = PropertiesService.getScriptProperties();
prop.setProperty('key', 'value');

キャッシュ

Class CacheService  |  Apps Script  |  Google Developers

// ドキュメントキャッシュ
var cache = CacheService.getDocumentCache();
// スクリプトキャッシュ
var cache = CacheService.getScriptCache();
// ユーザキャッシュ
var cache = CacheService.getUserCache();

キャッシュ読込み

var cache = CacheService.getScriptCache();
var value = cache.get('key');

キャッシュ書込み

var cache = CacheService.getScriptCache();
cache.put('key', 'value', 21600);

※キャッシュの最大保持時間は、6時間(21600秒)です。

ドキュメント

GASの実行ログを出力

// 既存のドキュメントを削除して、GASの実行ログをドキュメントに出力する
function log() {
  var docid = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
  var doc = DocumentApp.openById(docid);
  doc.getBody().clear();
  doc.getBody().appendParagraph(Logger.getLog());
}

※Logger.logで長すぎて表示しきれない文字列(8192文字より多い文字列)は、Logger.getLog()から取得できない

GASの長過ぎる文字列の出力

// 既存のドキュメントを削除して、指定文字列をドキュメントに出力する
var text = '長過ぎる文字列...';
var docid = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
var doc = DocumentApp.openById(docid);
doc.getBody().clear();
doc.getBody().appendParagraph(text);

※Googleドキュメントの文字数制限の半角文字で1,020,000文字まで出力できる

スプレッドシート

基本

// 関連付けられたスプレッドシートにアクセス
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

// 別スプレッドシートにアクセス
// https://docs.google.com/spreadsheets/d/{id}/edit
var spreadsheet = SpreadsheetApp.openById('{id}');

// シート取得
var sheet = spreadsheet.getSheetByName('シート1');

// 列数と行数取得
var row = sheet.getLastRow();
var col = sheet.getLastColumn();

独自メニュー追加

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  var menu = ui.createMenu('追加メニュー');
  menu.addItem('アイテム1', 'onClickItem1');
  menu.addItem('アイテム2', 'onClickItem2');
  menu.addToUi();
}
function onClickItem1() {
  Browser.msgBox('アイテム1がクリックされました。');
}
function onClickItem2() {
  SpreadsheetApp.getUi().alert('アイテム2がクリックされました。');
}

メッセージボックス

Browser.msgBox(prompt);
Class Browser | Google Developers
alert
Dialogs and Sidebars in G Suite Documents | Google Developers

Browser.msgBox('Hello World');
SpreadsheetApp.getUi().alert('Hello World');

※スプレッドシート上にメッセージボックスを表示する。
※メニューから呼び出された関数で実行する。
※カスタム関数では、権限がなくメッセージボックスを表示しない。
※6分以内にダイアログを閉じないとタイムアウトエラーとなる。
※他と共通で利用可能なUIを使用したほうが良い。

シートの読み書き

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

// 読込み
var values = row && sheet.getRange(1, 1, row, col).getValues() || [];

// データ編集
// ...
//values[1][0] = 'xxx';         // 2行目1列目の値を書換える
//values.push([1, 2, 3 ,4, 5]); // 最終行に追加
//values.splice(2, 1);          // 3行目を削除

// 書込み + データ消去
// 行数が減少する場合、消去が必要
values.length && sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
(row-values.length > 0) && sheet.deleteRows(values.length+1, row-values.length);

※列数が増加する可能性がある場合、最大列数で取得すると書込み時にエラーしない
　エラー例：「The number of columns in the data does not match the number of columns in the range. The data has 2 but the range has 1.」
　解決策例：var col = Math.max(sheet.getLastColumn(), 2);

シートの末尾に追加する

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName('シート1');
var row = sheet.getLastRow();
var values = [];

// データ追加
//values.push(data);

// シートの末尾に追加
sheet.getRange(row+1, 1, values.length, values[0].length).setValues(values);

シートの先頭に追加する

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName('シート1');
var values = [];

// データ追加
//values.push(data);

// シートの先頭に追加する
// 新規行を追加して、追加行に上書きする
sheet.insertRows(1, values.length);
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);

シートの先頭行を削除する

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

シートを全削除する

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

valuesの列数を最大列数に統一する

function valuesToReshape(values) {
  var mcol = 0;
  for (var r=0; r<values.length; r++) {
    mcol = Math.max(values[r].length, mcol);
  }
  for (var r=0; r<values.length; r++) {
    for (var c=values[r].length; c<mcol; c++) {
      values[r].push('');
    }
  }
}

※setValues時に、列数が統一されていない場合、エラーとなるため、列数を統一する

補足

GoogleAppsScriptからスプレッドシートへのアクセスは低速です。特に、複数回の読込み/書込みが非常に遅くなります。そのため、スプレッドシートへのアクセスは、セル毎のアクセスではなく選択範囲全体のアクセスとしたほうが懸命です。ただし、GoogleAppsScriptには、メモリの上限があります。あまりにも大量のデータを一度に処理するとメモリ上限に達してエラー終了します。

onOpen関数は、スプレッドシートを開いたときに実行します。opOpen関数以外に類似の関数としてonEdit関数、onInstall関数があります。

参考

• シンプルトリガ | Google Developers
• Google Apps Scriptを使った独自メニューの作り方 - Qiita
• Google Apps Scriptのスプレッドシート読み書きを格段に高速化をする方法

doGet, doPost

doGet

function doGet() {
  return HtmlService.createHtmlOutputFromFile('index');
}

index.html<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <h1>Hello HTML!</h1>
  </body>
</html>

doPost

function doPost(e) {
  var name = e.parameter.name;
  var email = e.parameter.email;
  var message = e.parameter.message;
  // ...
}

JSON出力

function doGet() {
  var json = {"text": "Hello World."};

  return ContentService.createTextOutput(JSON.stringify(json))
  .setMimeType(ContentService.MimeType.JSON);
}

• Enum MimeType

補足

パラメータの場合、e.parameter.xxxで受け取る。
JSONの場合、JSON.parse(e.postData.getDataAsString());で受け取る。

doGet, doPostのHTML出力は、「このアプリケーションは、Google ではなく、別のユーザーによって作成されたものです。」の帯が挿入される。
JavaScriptが無効の場合、帯のみが表示されHTML出力の結果が表示されない。
doGet, doPostで戻り値を指定しない場合、「スクリプトが完了しましたが、何も返されませんでした。」のメッセージを出力する。

HTMLファイル出力

テンプレートファイル読込み

var temp = HtmlService.createTemplateFromFile("ファイル名").evaluate().getContent();

テンプレートファイル読込み(Scriptlets)

var tempfile = HtmlService.createTemplateFromFile("test");
tempfile.flag = true;
tempfile.data1 = 'Hello World.';
tempfile.data2 = 2;
tempfile.data3 = 3;
var temp = tempfile.evaluate().getContent();

test.html<div>
<? if (flag) { ?>
  <p>flag=true</p>
<? } else { ?>
  <p>flag=false</p>
<? } ?>
  <p><?= data1 ?></p>
</div>
<button onclick="pow(<?= data2 ?>)">
<button onclick="pow(<?!= data3 ?>)">
<script>
function pow(num){
  alert(num * num);
}
</script>

結果.html<div>
    <p>flag=true</p>
    <p>Hello World.</p>
</div>
<button onclick="pow('2')">
<button onclick="pow(3)">
<script>
function pow(num){
  alert(num * num);
}
</script>

補足

テンプレートファイルは、通常の文字列結合と比べるとかなり低速なため、大規模ファイル作成の際はパフォーマンスを考慮する。

参考

• [GAS] Google Apps Script のHtmlServiceまとめ - dackdive's blog

フェッチ(外部ファイル読込み)

UrlFetchApp.fetch(url, params)
UrlFetchApp.fetch | Google Developers

HTMLファイル取得(GET)

var url = "https://exmple.com/";
var response = UrlFetchApp.fetch(url);
var html = response.getContentText("UTF-8");

HTMLファイル取得(POST)

var url = "https://exmple.com/";
var options = {
  "method" : "POST",
  "payload" : {
    "userid":  "userid",
    "password": "passwd"
  }
};
var response = UrlFetchApp.fetch(url, options);
var html = response.getContentText("UTF-8");

関連記事

• GoogleAppsScriptでよくはまる制約まとめ
• GoogleAppsScriptのスプレッドシート処理を高速化する
• GoogleAppsScriptの1日トリガー実行時間チェッカー