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


![image](https://qiita-image-store.s3.amazonaws.com/0/30079/463e42b8-3372-64e2-48a1-ff3243feca7c.png)

逆引きで、[Google Apps Script](https://developers.google.com/apps-script/)を使って、Spreadsheetを操作する方法を記します。

# 基本編

## Apps ScriptをDriveから開く
* Apps Scriptを開きたいときは、Spreadsheetのメニュー内の、Tool=>Script Editor...で開きます。

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

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

![image](https://qiita-image-store.s3.amazonaws.com/0/30079/1fb50f84-7b58-8b7b-9428-dabe916be04d.png)

## APIの公開

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

![image](https://qiita-image-store.s3.amazonaws.com/0/30079/ad0c5b6e-ad4a-9364-18a3-33ce4ef7a97a.png)

# Driveフォルダ編

https://developers.google.com/apps-script/reference/drive/drive-app

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

```js
var files = DriveApp.getFolderById(folderId).getFiles();
while (files.hasNext()) {
	var file = files.next();
	Logger.log(file.getName());
}
```
# スプレッドシート(ワークシート)・シート編

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

[DriveApps](https://developers.google.com/apps-script/reference/drive/file)の方から行う。

```
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](https://developers.google.com/apps-script/guides/properties)というのがあって、スクリプトで使う設定値を格納することができます。
設定はメニューの ファイル -> プロジェクトのプロパティ から出来ます。 

![image](https://qiita-image-store.s3.amazonaws.com/0/30079/5660cfe4-a157-fd51-9b9d-6e319788a754.png)

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

```
PropertiesService.getScriptProperties().
```

# UI編

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

詳しくは[ここ](https://developers.google.com/apps-script/guides/menus#clickable_images_and_drawings_in_google_sheets)を見ていただければと思いますが、以下のようにAppsScriptを実行できるボタンを設置できます。

![image](https://qiita-image-store.s3.amazonaws.com/0/30079/4281213e-5edc-c43b-fbc7-9bf0470f721f.png)

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

```
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);
}
```

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

![image](https://qiita-image-store.s3.amazonaws.com/0/30079/165560cc-c70e-b0ec-7ec7-3d5351258e57.png)

## カスタムメニューを作成

## ダイアログを出す

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

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

![image](https://qiita-image-store.s3.amazonaws.com/0/30079/0418ff60-57bf-baa2-a182-6e8eb47a3bbe.png)

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

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

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

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

![image](https://qiita-image-store.s3.amazonaws.com/0/30079/f9ffede9-b82c-3124-5f52-a8df50cdaea5.png)

```
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);
}
```

![image](https://qiita-image-store.s3.amazonaws.com/0/30079/cb866a21-c8d0-b734-2045-171aed91820d.png)


# イベント編

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

```
function onOpen() {
}
```

その他の[トリガー](https://developers.google.com/apps-script/guides/triggers/)として以下があります。なお、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を設定します。
時計っぽいアイコンを選択します。

![image](https://qiita-image-store.s3.amazonaws.com/0/30079/cd0f6796-f6ca-a0d6-8a4c-747999027067.png)

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

![image](https://qiita-image-store.s3.amazonaws.com/0/30079/bbb7889f-d450-c90d-6a5c-88b27727de51.png)

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

![image](https://qiita-image-store.s3.amazonaws.com/0/30079/a050ce42-3e1a-041f-4bda-e1cd58c4ccf2.png)

# エクスポート編

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

[Convert Google Documents and Spreadsheets with Apps Script](https://ctrlq.org/code/20009-convert-google-documents)に書いてあります。

なおexport出来るタイプは[ここ](https://developers.google.com/drive/v3/web/manage-downloads#downloading_google_documents)によると、以下のようです

![image](https://qiita-image-store.s3.amazonaws.com/0/30079/df1cdf66-988f-29be-a006-55ac58e8c2a4.png)

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

https://gist.github.com/ixhd/3660885

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

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

https://sites.google.com/site/scriptsexamples/custom-methods/sheetconverter

# 外部連携

## Slack連携

[Google Apps Scriptを使って、スプレッドシートの内容をSlackに通知する](http://www.tam-tam.co.jp/tipsnote/javascript/post8499.html)を参考にするとできます。

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

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

## 外部からのデータ取得

今回は、天気を取得し、それをslackになげてみます。[【Google Apps Script】天気予報をWeb APIで取得する方法](http://tonari-it.com/gas-api-weather/)を参考にしました。

出力フォーマットは「東京の今日の天気は曇り。最高気温は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);
}
```

できました。
![image](https://qiita-image-store.s3.amazonaws.com/0/30079/babcec2d-c1ad-fd96-29bc-528487b4d155.png)

## メール送信
wapa5pow@example.comがtoになって、メールが送られます。

```
var email = 'wapa5pow@example.com';
GmailApp.sendEmail(email, 'subject', 'body');
```

Fromを変えたい場合は[別のアドレスからメールを送信する](http://www.ka-net.org/blog/?p=4441)にやり方がかいてあります。

# デバッグ編

## ログを出す

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

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

![image](https://qiita-image-store.s3.amazonaws.com/0/30079/387ea39a-eec8-d08f-9704-b3c99068bede.png)


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

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

![image](https://qiita-image-store.s3.amazonaws.com/0/30079/25ea6e7f-4f0b-a652-8d21-500f86bf4337.png)

![image](https://qiita-image-store.s3.amazonaws.com/0/30079/9b1691b1-0ee2-9d73-b76e-18824ee3c81c.png)

# 開発編

## ローカルで開発する

* 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を入れればいい。

![image](https://qiita-image-store.s3.amazonaws.com/0/30079/13e8b60e-0847-54c2-acb8-5e74c0cc51c3.png)

## 外部ライブラリ

* [Notable Script Libraries](https://developers.google.com/apps-script/notable-script-libraries#fusiontablesservice)

### [2D Array Library](https://sites.google.com/site/scriptsexamples/custom-methods/2d-arrays-library)

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

### [underscoreGS](https://sites.google.com/site/scriptsexamples/custom-methods/underscoregs)

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

### [BetterLog](https://github.com/peterherrmann/BetterLog)

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

https://sites.google.com/site/scriptsexamples/custom-methods/betterlog

# 参考

* [App Scriptリファレンス](https://developers.google.com/apps-script/)