GoogleスプレッドシートでKPIをSlackにも投稿できちゃうGoogle Apps Scriptの逆引きリファレンス

逆引きで、Google Apps Scriptを使って、Spreadsheetを操作する方法を記します。

基本編

Apps ScriptをDriveから開く

• Apps Scriptを開きたいときは、Spreadsheetのメニュー内の、Tool=>Script Editor...で開きます。

スクリプトが必要なスコープの確認

ファイル => プロジェクトのプロパティ

APIの公開

ただし、必要に応じてGoogle Cloud Platform側のAPIをEnableにする必要がある。

Driveフォルダ編

指定のフォルダ以下のファイルをすべて列挙する

var files = DriveApp.getFolderById(folderId).getFiles();
while (files.hasNext()) {
    var file = files.next();
    Logger.log(file.getName());
}

スプレッドシート(ワークシート)・シート編

スプレッドシートをコピーする

DriveAppsの方から行う。

var file = DriveApp.getFileById(fromId);
var folder = DriveApp.getFolderById(toFolderId);
file.makeCopy(name, folder);

IDからSpreadsheetを取得する

SpreadsheetのURLのhttps://docs.google.com/spreadsheets/d/abc1234567/にあるIDを使ってSpreadsheetを開けます。

var ss = SpreadsheetApp.openById("abc1234567");

シート名からSheetを取得する

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Sheet1');

シートをActive状態にする

sheet.activate()

Activeなシートを削除する

sheet.deleteActiveSheet()

新しいシートを足す

以下では新規シートがあるかどうか確認して、なければ作成してます

  var directionsSheet = spreadsheet.getSheetByName(sheetName);
  if (directionsSheet) {
    directionsSheet.clear();
    directionsSheet.activate();
  } else {
    directionsSheet =
        spreadsheet.insertSheet(sheetName, spreadsheet.getNumSheets());
  }

シートを複製する

var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.duplicateActiveSheet();

異なったスプレッドシートにシートをコピーする

var fromSheet = ss.getSheetByName('Sheet1');
var toSS = SpreadsheetApp.openById(fromId);
fromSheet.copyTo(toSS)

スプレッドシートの名前を変更する

var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.setName('TestName')

シートの名前を変更する

var sheet = ss.getSheetByName('Sheet1');
sheet.setName('TestSheetName')

スプレッドシート内のシート名をすべて取得する

ss.getSheets()

セル編

データの取得

以下のようにすればdataに配列でセルの内容が取得できます。

var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();

特定のセルのデータを取得

var value = sheet.getRange("A4").getValue();

データの追加

シートのデータがある最後に追加したいときは以下のようにします。

var sheet = SpreadsheetApp.getActiveSheet();
sheet.appendRow(['Cotton Sweatshirt XL', 'css004']);

データの更新

function setSellValue() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  var cell = sheet.getRange("B2");
  cell.setValue(100);
}

セルに計算式を設定

function setFormulaToCell() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  var cell = sheet.getRange("B13");
  cell.setFormula("=SUM(B2:B12)");
}

固定行・列を設定

# 行の設定: rowsは行数
setFrozenRows(rows) 

# 列の設定: columnsは列数
setFrozenColumns(columns)

セルのフォーマットを整える

以下な感じでセルのフォーマットをいろいろ整えれます

directionsSheet.getRange('A1:C1').merge().setBackground('#ddddee');
directionsSheet.getRange('A1:2').setFontWeight('bold');
directionsSheet.setColumnWidth(1, 500); directionsSheet.getRange('B2:C').setVerticalAlignment('top');
directionsSheet.getRange('C2:C').setNumberFormat('0.00');

列を追加する

以下だとB列とC列の間に1行足す

sheet.insertColumnAfter(2)

行を追加する

sheet.insertRowAfter(2)

行をコピーする

fromRowはコピー元の行数を、fromRowはコピー先の行数を表す。
Rangeは、例えば、getRangeの引数に、16:16とすると、16行すべてを選択することができる。

相対的な値のフォーミュラも相対的にコピーできるので便利。

sheet.getRange(fromRow + ':' + fromRow).copyTo(ss.getRange(toRow + ':' + toRow));

シート内のすべての値を2D配列で取得する

sheet.getDataRange().getValues();

シート内の特定の値を2D配列で取得する

sheet.getSheetValues(2, 1, 42, 2)

プロパティ編

Properties Serviceというのがあって、スクリプトで使う設定値を格納することができます。
設定はメニューの ファイル -> プロジェクトのプロパティ から出来ます。

• Script Properties: スクリプト全体にかかわる設定
• User Properties: ユーザに応じた設定
• Document Properties: ソースURLなどのドキュメントの情報

PropertiesService.getScriptProperties().

UI編

スプレッドシートにボタンを設置する

詳しくはここを見ていただければと思いますが、以下のようにAppsScriptを実行できるボタンを設置できます。

セルのデータの入力を特定の範囲にしぼりたいとき

function validateMySpreadsheet() {
  // Set a rule for the cell B4 to be a number between 1 and 100.
  var cell = SpreadsheetApp.getActive().getRange('D4');
  var rule = SpreadsheetApp.newDataValidation()
     .requireNumberBetween(1, 100)
     .setAllowInvalid(false)
     .setHelpText('Number must be between 1 and 100.')
     .build();
  cell.setDataValidation(rule);
}

範囲外の入力が来たときは、警告がでます。

カスタムメニューを作成

ダイアログを出す

以下でダイアログが出せます。

function showMessageBox() {
  Browser.msgBox('ダイアログ!');
}

入力可能なダイアログを表示する

function showInputMessageBox() {
  var selectedRow = Browser.inputBox('数値を入れてください', Browser.Buttons.OK_CANCEL);
  if (selectedRow == 'cancel') {
    return;
  }
  Browser.msgBox(selectedRow);
}

カスタムメニューを表示する

こんな感じのができます。

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Generate step-by-step...', functionName: 'generateStepByStep_'}
  ];
  spreadsheet.addMenu('Directions', menuItems);
}

グラフ編

グラフを作成する

Sheet1にのA1:B15にデータがあるとすると、以下のコードで棒グラフが作成できます。

function newChart() {
  // Generate a chart representing the data in the range of A1:B15.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');

  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.BAR)
     .addRange(sheet.getRange('A1:B15'))
     .setPosition(5, 5, 0, 0)
     .build();

  sheet.insertChart(chart);
}

イベント編

Spreadsheetオープン時に実行

onOpenはスプレッドシートが開いたときに実行されるので、何か初期化などしたいときは、実行するといいでしょう。

function onOpen() {
}

その他のトリガーとして以下があります。なお、SpreadsheetではdoGetとdoPostはありません。

• onEdit: スプレッドシートの値を変えたとき
• onInstall: アドオンがインストールされたとき
• doGet: Web AppにHTTP GETリクエストをしたとき
• doPost: Web AppにHTTP POSTリクエストをしたとき

定期ジョブ編

cron設定

まず、日時が設定できる関数を設定します。

function setDate() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('CurrentDate');
  var cell = sheet.getRange("A1");
  var date = new Date();
  cell.setValue(date);
}

つぎにその関数に対して、cronを設定します。
時計っぽいアイコンを選択します。

Add a new triggerを設定します。今回は毎分実行されるように設定しました。

結果、1分ごとに更新される時計ができました。

エクスポート編

CSVとしてエクスポートする

Convert Google Documents and Spreadsheets with Apps Scriptに書いてあります。

なおexport出来るタイプはここによると、以下のようです

PDFとしてエクスポートする

HTMLとしてエクスポートする

外部ライブラリを使うとできます。これでスプレッドシートをHTMLに変換してメールで送信ということができます。

外部連携

Slack連携

Google Apps Scriptを使って、スプレッドシートの内容をSlackに通知するを参考にするとできます。

Botのようにしたい場合は以下らへんを見るとよさそうです。

• http://qiita.com/soundTricker/items/43267609a870fc9c7453
• http://tech.camph.net/slack-bot-with-gas/

外部からのデータ取得

今回は、天気を取得し、それをslackになげてみます。【Google Apps Script】天気予報をWeb APIで取得する方法を参考にしました。

出力フォーマットは「東京の今日の天気は曇り。最高気温は23度でしょう」でいきます。

function weatherForecast() {
  var response = UrlFetchApp.fetch("http://weather.livedoor.com/forecast/webservice/json/v1?city=130010"); //URL+cityID
  var json = JSON.parse(response.getContentText());
  var message = json.location.city + "の" + json.forecasts[0].dateLabel + 'の天気は' + json.forecasts[0].telop + '。最高気温は' + json.forecasts[0].temperature.max.celsius + '度でしょう';
  slack(message);
}

できました。

メール送信

[e-mail]がtoになって、メールが送られます。

var email = '[e-mail]';
GmailApp.sendEmail(email, 'subject', 'body');

Fromを変えたい場合は別のアドレスからメールを送信するにやり方がかいてあります。

デバッグ編

ログを出す

Logger.log('Product name: ' + data[i][0]);
Logger.log('Product number: ' + data[i][1]);

Script Editor内で、View(表示) => Logs(ログ) を選ぶとログが見えます。
また、View(表示) => 実行トランスクリプト を選ぶと実際にどの関数がよばれたかなどの実行ログが出ます。

ブレークポイントを設定してデバッグ実行する

関数ごとにデバッグができます。オブジェクトの値を見ることしかできないっぽいですが、それでも便利そうです。
ステップ実行などもできます。
コンソールみたいなっていて、ブレークポイント上で、実行できると便利そうですがいまはできません。

開発編

ローカルで開発する

• node-google-apps-script
  • http://qiita.com/zaki-yama/items/9a301542137febd8876c
  • http://googledevjp.blogspot.jp/2016/01/apps-script.html

IntelliJでコード補完する

設定の、Languages & Frameworks > JavaScript > Libraries から、Download... でgoogle-apps-scriptを入れればいい。

外部ライブラリ

• Notable Script Libraries

2D Array Library

2D配列を扱いやすくしてくれます。転置も楽にしてくれるので便利です。

underscoreGS

mapやeachなどが使えるようになります。

BetterLog

BetterLogはスクリプト実行ログをスプレッドに出せるようになります。
これでRubyからApps Scriptを実行してもログがたまっていくようになります。

参考

• App Scriptリファレンス