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文字より多い文字列)は、出力できない

スプレッドシート

基本

  // 関連付けられたスプレッドシートにアクセス
  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[i].length; c<mcol; c++) {
      values[r].push('');
    }
  }
}

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

補足

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

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

参考

doGet, doPost

doGet

function doGet() {
  return HtmlService.createHtmlOutputFromFile('index');
}
<!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);
}

補足

パラメータの場合、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();
<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>
<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>

補足

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

参考

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

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

HTMLファイル取得

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

アクセストークン取得(OAuth2.0)

function fetchAccessToken() {
  var client_id     = 'クライアントID';
  var client_secret = 'シークレットID';
  var redirect_uri  = 'リダイレクトURI';
  var refresh_token = 'リフレッシュトークン';

  var res = UrlFetchApp.fetch("https://www.googleapis.com/oauth2/v4/token", {
    "method" : "POST",
    payload : {
      "refresh_token" : refresh_token,
      "client_id" : client_id,
      "client_secret" : client_secret,
      "redirect_uri" : redirect_uri,
      "grant_type" : "refresh_token"
    },
    muteHttpExceptions : true
  });

  return JSON.parse(res.getContentText());
}
// fetchAccessToken().access_token;

参考

関連記事

コメントを書く