Google Apps Scriptを使ってみる

Googleの提供するサービスをJavaScriptベースのスクリプト言語で操作するためのプログラム環境「Google Apps Script」を使ってみたのでメモ。

使い方

今回はスプレッドシートの内容を操作してみたいと思います。
新しくスプレッドシートを作成します。

ツール > スクリプト エディタ を選択します。

ブラウザ上でエディタが開かれるので、エディタ上で以下のように記述してみます。

function myFunction() {
  Browser.msgBox('test');
}

入力が完了したらファイル > 保存 を選択します。
以下のようなポップアップが表示されるので、プロジェクト名を入力してOKを選択します。

保存が完了しました。
入力した処理を実行する場合は、先ほど記述した関数「myFunction」を選択して、実行アイコンをクリックします。

初めて実行したときには、以下のようなアラートが表示されます。

続行すると、許可のリクエストが表示されるので、問題なければ許可をクリックしてください。

スプレッドシートに戻ると、ダイアログボックスが表示されたのが確認できました。

スプレッドシートの値取得

スプレッドシートにいくつかデータを入力してみます。

エディタに戻って、内容を以下に書き換えます。

function myFunction() {
  var spread = SpreadsheetApp.getActiveSpreadsheet(); // スプレッドシートの選択
  var value = spread.getSheets()[0].getRange(2, 1).getValue(); // 指定したセルの値取得
  Browser.msgBox(value); // ダイアログボックスでの表示
}

実行してスプレッドシートに戻ると、A2番目の値がダイアログボックスで表示されました。

先ほどはアクティブなスプレッドシートから取得しましたが、スプレッドシートのURLを指定して、そこから値を取得することもできます。
以下のように入力して実行をクリックします。

function myFunction() {
  var spread = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/xxxxx'); // スプレッドシートの選択
  var value = spread.getSheets()[0].getRange('A5').getValue(); // 指定したセルの値取得
  Browser.msgBox(value); // ダイアログボックスでの表示
}

A5の値がダイアログボックスで表示されました。

複数のセルを指定して、まとめて値を取得することもできます。
以下のように入力して実行をクリックします。

function myFunction() {
  var spread = SpreadsheetApp.getActiveSpreadsheet(); // スプレッドシートの選択
  var values = spread.getSheets()[0].getSheetValues(2, 1, 7, 2); // 指定した範囲のセルの値取得
  Logger.log(values); // 取得した値をログに表示
}

今回はダイアログボックスではなくログに表示するようにしました。
ログを確認する場合、エディタの 表示 > ログ を選択します。

指定した範囲の値が配列で入っているのが確認できました。

スプレッドシートへの書き込み

先ほど取得した値を別のセルに書き込んでみます。

function myFunction() {
  var spread = SpreadsheetApp.getActiveSpreadsheet(); // スプレッドシートの選択
  var values = spread.getSheets()[0].getSheetValues(2, 1, 7, 2); // 指定した範囲のセルの値取得
  var startRow = 2; // 書き込みを開始する行
  var startCol = 4; // 書き込みを開始する列
  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {
      spread.getSheets()[0].getRange((startRow + i), (startCol + j)).setValue(values[i][j]); // 指定したセルに値を書き込む
    }
  }
}

6～10行目が書き込みを行う記述で、行と列でそれぞれfor文を回して、各セルに書き込みを行うようにしています。

保存して実行をクリックすると、A2～B8の値をD2に書き込むことができました。

最後の行・列を取得する

シート内で値の入っている最後の行数や列数を取得してみます。
セルの範囲を指定する際に使うと便利です。

まずは最後の行数を取得してみます。

function myFunction() {
  var spread = SpreadsheetApp.getActiveSpreadsheet(); // スプレッドシートの選択
  var lastRow = spread.getSheets()[0].getLastRow(); // 最後の行数を取得
  Browser.msgBox(lastRow); // ダイアログボックスでの表示
}

最後の行数をダイアログボックスで表示できました。

次に最後の列数を取得してみます。

function myFunction() {
  var spread = SpreadsheetApp.getActiveSpreadsheet(); // スプレッドシートの選択
  var lastCol = spread.getSheets()[0].getLastColumn(); // 最後の列数を取得
  Browser.msgBox(lastCol); // ダイアログボックスでの表示
}

最後の列数をダイアログボックスで表示できました。

今回は基本的な使い方を試してみましたが、他にもできることはたくさんあるみたいなので、またそのうち試してみたいと思います。
　

【参考サイト】

• Overview of Google Apps Script | Apps Script | Google Developers
• Extending Google Sheets | Apps Script | Google Developers
• Class SpreadsheetApp | Apps Script | Google Developers
• Class Spreadsheet | Apps Script | Google Developers
• Class Range | Apps Script | Google Developers
• スプレッドシート利用の基本(1/5):初心者のためのGoogle Apps Scriptプログラミング入門