GASでSpreadSheetを読み書きする。

前提

GoogleAppsScript(GAS) で 「Hello World」する方法

本記事について

GASを使っていろいろやってみようという記事。

SpreadSheetから関数を呼び出す。

まずはSpreadSheetを開いた状態からGASを呼び出す方法を学ぶ。

メニューを追加してダイアログを表示

getUi ドキュメント
ドキュメントを参考に上部メニューを拡張する。

以下のように記述

コード.gs

const onOpen = (e) => {
  SpreadsheetApp.getUi()
      .createMenu('My Menu') // 上部メニューにMy Menuを追加
      .addItem('Hello GAS', 'helloGAS') // Hello GASを押したらhelloGAS関数を呼ぶ。
      .addToUi();
}

const helloGAS = () => {
  Browser.msgBox("hello GAS!"); // ダイアログを表示
}

その上で、ブラウザを更新すると以下のようにメニューが表示されるようになる。
(SpreadSheetの読み込みは時間かかる。)

そしてボタンを押すと、許可が求められる。
(画像はエディタ側で読んだ時のものなので見た目は異なってると思われる)

許可をおしてGoogleでログインすると以下のような画面が表示される。

その上で実行すると、以下のようにダイアログが表示されるようになる。

ミスの修正などでスクリプトを更新した場合にはSpreadSheetの画面を更新して再度スクリプトを読み込む必要がある。

これで読み書きの準備完了。

ブラウザに広告ブロックツールを入れてる場合に、読み込みがめっちゃ遅くなることがあるのでこのページでは無効にすると良い。

演習

同じMyMenuに新しいメニューアイテムを1つ追加してみよう。
呼ぶ関数はhelloGAS2とか適当に。

SpreadSheetに書かれた文字を読む。

以下のようにデータを作成した。

そして、シート名をmonstersとする。

これをGASから取得する。

コード.gs

const onOpen = (e) => {
  SpreadsheetApp.getUi()
      .createMenu('My Menu')
      .addItem('Hello GAS', 'helloGAS')
      .addItem('Read Monster', 'readMonster') // <- 追加
      .addToUi();
}
...
  
const readMonster = () => {
  const spreadSheet = SpreadsheetApp.getActive(); // 今開いているスプレッドシートファイルの中の
  const monstersSheet = spreadSheet.getSheetByName("monsters"); // monstersというシートの
  const range = monstersSheet.getRange(2, 1); // 2行目、1列目の範囲を指定
  const value = range.getValue(); // そこの値を取得
  Browser.msgBox(value);
}

getRange

結果は以下

複数行読み込む場合はgetDataRangeやgetValues等を使うが二次元配列を使うため今回は省略

SpreadSheetに書き込む

新しいモンスターを追加してみる。

以下のように記述

コード.gs

const onOpen = (e) => {
  SpreadsheetApp.getUi()
      .createMenu('My Menu')
      .addItem('Hello GAS', 'helloGAS')
      .addItem('Read Monster', 'readMonster')
      .addItem('Append Monster', 'appendMonster') // 追加
      .addToUi();
}

...

const appendMonster = () => {
  const spreadSheet = SpreadsheetApp.getActive();
  const monstersSheet = spreadSheet.getSheetByName("monsters");
  monstersSheet.appendRow(["ウルフ", 12, 14]);
}

appendRow

このように行末に追加される。

まとめ

ドキュメントにいろいろサンプルコードが乗ってるので試しやすい。