スプレッドシートをGoogle Action Script で操作する

Apr 5, 2019

Google Action Script でスプレッドシートを操作するときに知っておくと良さそうなことをまとめてみた。

初級編

シート名の先頭に data/ をつけておく
- どのシートがGASで更新するか、スプレッドシートを見た時にわかるように
- GASで更新するシートは、基本GASだけがデータを変更する様にして、手入力するシートは分けた方が良い
余計なことはせず、自分自身のファイルを参照する
- SpreadsheetApp.getActive() を使う
GASの実行時間は6分以内じゃないとダメ

セルに書き込む

const sheet = SpreadsheetApp.getActive().getSheetByName('data/hoge');
sheet.getRange(1, 1).setValue('Hello World!');

シート内のデータを消す

const sheet = SpreadsheetApp.getActive().getSheetByName('data/hoge');
sheet.getDataRange().clearContent();

シート内のデータをすべて読み出す

values という変数に2次元配列でデータを取得する

const sheet = SpreadsheetApp.getActive().getSheetByName('data/hoge');
var values = sheet.getDataRange().getValues();

定期実行する

GUIからトリガーを使い、実行したい関数を指定して設定します。いつ実行されたかわかるように、データを取得するシートの一番上に new Date() の値を書き込みしておくと良い。

const sheet = SpreadsheetApp.getActive().getSheetByName('data/hoge');
sheet.getRange(1, 1, 1, 2).setValues([['更新日', new Date()]]);

中級編

ファイル整理

一つのファイルに色々書くと散らかるので、一つのシートを変更するロジックを一つのスクリプトファイルに書く。

パフォーマンス改善

GASの実行時間は6分以内じゃないとダメなので、外部のHTTPアクセス回数、Google API の呼び出し回数を減らす。 for ループでちまちま sheet.getRange(i, j).setValue(v) とかダメ。

function append(records) {
  const sheet = SpreadsheetApp.getActive().getSheetByName('data/hoge');
  var data = sheet.getDataRange().getValues();

  // 配列結合してまとめて書き込み
  var values = data.concat(records);
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
};

HTTPリクエスト

UrlFetchApp.fetch() を使う。

function post(url, payload) {
  const options = {
    'method': 'post',
    'contentType': 'application/json',
    'headers': {
      'Authorization':'Bearer hoge',
      'Accept': 'application/json'
    },
    muteHttpExceptions: true,
    'payload': JSON.stringify(payload)
  };

  return UrlFetchApp.fetch(url, options)
}

上級編

コード管理とデプロイ

Git でコード管理、claspを使いデプロイするのが良さそう。

シークレット情報の取り扱い

GAS 上で、PropertiesServiceクラスを使うと定数データを良い感じに使える。 config/secret.gs を .gitignore しておき、 config/secret.gs.tmp でテンプレートをgit にpush しておけば何とかなる。

git から除外しても clasp push の対象になります。

function initialize() {
  var properties = PropertiesService.getScriptProperties();
  properties.setProperties({
    'token': 'hoge'
  });
};

テスト書く

GASはNode.js じゃ無いので、 require や exports できません。邪魔にならないように module.exports します。

下記は、擬似コード

var Model = function() {};

Model.prototype.fetch = function(url, options) {
  UrlFetchApp.fetch(url, options);
};

if (typeof exports !== 'undefined') { module.exports = Model; }

テストはAVA 使おう。スタブするなら Sinonも。

import test  from 'ava';
import sinon from 'sinon';

var Model = require('../../src/model.gs');

test('Model#fetch', t => {
  var model = new Model();
  sinon.stub(model, 'fetch').returns('OK');

  t.is(model.fetch(), 'OK');
});

知らないとハマる仕様

日付型（シリアル値）

スプレッドシートのシリアル値は「1900/1/0」からの経過日数
UNIX時間は「1970/1/1」からの経過秒数

// スプレッドシート日付型のシリアル値への変換
function toSerialDate(timestamp) {
  const COEFFICIENT = 24 * 60 * 60 * 1000; //日数とミリ秒を変換する係数
  const DATES_OFFSET = 70 * 365 + 17 + 1 + 1; //「1900/1/0」～「1970/1/1」 (日数)
  const MILLIS_DIFFERENCE = 9 * 60 * 60 * 1000; //UTCとJSTの時差 (ミリ秒)

  return (timestamp + MILLIS_DIFFERENCE) / COEFFICIENT + DATES_OFFSET;
}

API等で、ISO 8601形式で日付が返却される場合は、 Date#parse() で日付型にキャストしてください。

toSerialDate(Date.parse("2020-01-23T01:14:39+00:00"))
// return 43853.42684027778

初級編