【Google Apps Script】その2　スプレッドシートのデータと連携する

この記事は[Google Apps Scriptを実例交えて基礎からざっくり学ぶ Advent Calendar 2017](https://qiita.com/advent-calendar/2017/gas_beginner)　2日目の記事です。

本アドベントカレンダーは@rt_pの個人プロジェクトですが、筆者は[Ateam Brides Inc. Advent Calendar 2017](https://qiita.com/advent-calendar/2017/a-t-brides)にも参加しています。そちらでも出張版記事を書いているので、覗いていただけると嬉しいです。

# はじめに

今回はGASを使い、スプレッドシートにデータをセットしたり、スプレッドシートからデータを取ってくる方法を紹介します。

![setValues.PNG](https://qiita-image-store.s3.amazonaws.com/0/199678/48c3edb8-2026-4b4c-be19-12958e02c3e9.png)

# 1．スプレッドシートにデータをセット

[1日目のHello, world!記事](https://qiita.com/rf_p/items/0535ec495df8f9d2b894)の復習になります。
まずスプレッドシートを新規作成しスクリプトエディタを開いてください。
やり方がわからない場合は[1日目のHello, world!記事](https://qiita.com/rf_p/items/0535ec495df8f9d2b894)をご参照ください。

```javascript:set_data.gs
function myFunction() {
  SpreadsheetApp.getActiveSheet().getRange('A1').setValue('カレーライス');
}
```

ちなみに`getRange()`でセル番地を指定していますが、下記コードでも全く同じ結果が得られます。

```javascript:set_data2.gs
function myFunction() {
  SpreadsheetApp.getActiveSheet().getRange(1, 1).setValue('カレーライス');
}
```

> getRange(row, column) 

https://developers.google.com/apps-script/reference/spreadsheet/sheet#getRange(Integer,Integer)

第1引数が行（上から何番目の行）、第2引数が列（左から何番目の列）を示しています。

Excel、スプレッドシート的な参照方法だと前者`('A1')`の指定方法が分かりやすく、GASの中で変数として行や列を指定する際は後者`(1, 1)`の指定方法が分かりやすそうですね。

![setValue.PNG](https://qiita-image-store.s3.amazonaws.com/0/199678/c8507b0a-4d28-fd15-164d-da6aad804bae.png)

# 2．スプレッドシートからデータを取得

今後は逆に、スプレッドシートに入力されているデータをGAS側で扱えるように取得してみましょう。
まずはスプレッドシートのA1セルに適当な値を入力します。

![getValue1.PNG](https://qiita-image-store.s3.amazonaws.com/0/199678/83e9e6d7-804c-ffae-b6d4-c0ee77c7fc6a.png)

次にGASで以下コードを入力し実行します。

```javascript:get_data.gs
function myFunction() {
  var value = SpreadsheetApp.getActiveSheet().getRange('A1').getValue();
  Logger.log(value);
}
```

>  getValue()

https://developers.google.com/apps-script/reference/spreadsheet/range#getvalue

実行しても何も起きないように見えますが、`Logger.log()`でログに吐き出しているのでそちらを確認してみましょう。
Ctrl+Enterでログを表示します。

![getValue2.PNG](https://qiita-image-store.s3.amazonaws.com/0/199678/36299226-0d46-e658-7fd7-ca8369fa6c96.png)

# 3．複数セルを対象としたセット

今後は、複数のセルに値を入れてみましょう。
1行目に、1, 2, 3, …, 10
2行目に、11, 12, 13, …, 20
…
10行目に、91, 92, 93, …, 100
とデータを入れたい時にどうすれば良いでしょうか？
（まぁこの程度の操作であれば、スプレッドシート上でドラッグ操作した方が早いかもしれないですが:sweat_smile:）

勘の良い方であれば、for文を駆使すればイケそうだと気付きそうですね。

```javascript:set_values.gs
function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  for (var i = 0; i < 10; i++) {
    for (var j = 0; j < 10; j++) {
      var row = i + 1;
      var col = j + 1;
      var value = i * 10 + j + 1;
      sheet.getRange(row, col).setValue(value);
    }
  }
}
```

![setValues.PNG](https://qiita-image-store.s3.amazonaws.com/0/199678/48c3edb8-2026-4b4c-be19-12958e02c3e9.png)

イイ感じに動きましたね！
ただ、ここで問題が1個あります。
実は**`getRange()`やら`setValue()`等を実行する度に、GASからスプレッドシートのAPI呼び出しが発生します。**
当然その分処理に時間はかかるので、できることなら1回でバッと取ってきて1回でサクッとセットしたいですね。

その場合は`getRange()`で複数セルを範囲として取得し、`setValues()`で二次元配列を渡すことで一度にセットができます。

```javascript:set_values2.gs
function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = [];
  for (var i = 0; i < 10; i++) {
    values[i] = [];
    for (var j = 0; j < 10; j++) {
      var value = i * 10 + j + 1;
      values[i][j] = value;
    }
  }
  Logger.log(values);
  sheet.getRange('A1:J10').setValues(values);
}
```

> setValues(values)

https://developers.google.com/apps-script/reference/spreadsheet/range#setvaluesvalues

どんな配列をセットしているのか分かるようにログに吐いています。
Ctrl+Enterで確認してみてください。

ちなみにログだと分かりづらいかもしれないので、セットしている配列はどんなものかは以下をご確認ください。

```javascript:set_values_sample.gs
  var values = [
    [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
    [11, 12, 13, 14, 15, 16, 17, 18, 19, 20],
    [21, 22, 23, 24, 25, 26, 27, 28, 29, 30],
    [31, 32, 33, 34, 35, 36, 37, 38, 39, 40],
    [41, 42, 43, 44, 45, 46, 47, 48, 49, 50],
    [51, 52, 53, 54, 55, 56, 57, 58, 59, 60],
    [61, 62, 63, 64, 65, 66, 67, 68, 69, 70],
    [71, 72, 73, 74, 75, 76, 77, 78, 79, 80],
    [81, 82, 83, 84, 85, 86, 87, 88, 89, 90],
    [91, 92, 93, 94, 95, 96, 97, 98, 99, 100]
  ];
```

ちなみに以下コードでも同じ結果が得られます。

```javascript:set_values2.gs
function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = [];
  for (var i = 0; i < 10; i++) {
    values[i] = [];
    for (var j = 0; j < 10; j++) {
      var value = i * 10 + j + 1;
      values[i][j] = value;
    }
  }
  Logger.log(values);
  sheet.getRange(1, 1, 10, 10).setValues(values); // この行が違う
}
```

`getRange()`の指定方法が変わりました。

> getRange(row, column, numRows, numColumns)

https://developers.google.com/apps-script/reference/spreadsheet/sheet#getRange(Integer,Integer,Integer,Integer)

第1引数が行番号、第2引数が列番号、第3引数が行数、第4引数が列数になります。
`getRange(1, 1, 10, 10)`だと1, 1つまりA1から開始し、縦に10行、横に10列選択しています。

例えばC4:D9をこの指定方法で選択するなら`getRange(4, 3, 6, 2)`となります。
→C4セルは上から**4**行目、左から**3**番目、C4からD9は縦に**6**セル横に**2**セルの為

```javascript:set_values_example.gs
function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(4, 3, 6, 2).setValue(1);
}
```

![example.PNG](https://qiita-image-store.s3.amazonaws.com/0/199678/a14353dd-0e20-2d3a-a738-9462eb987d05.png)

しれっとやっていますが、複数セルを範囲指定して`setValues()`ではなく`setValue()`を実行すると、範囲内全てのセルに同じ値をセットできます。

# 4．複数セルを対象とした取得

ここまで来るとほぼ分かるかもしれないですが、複数のセルから値を取るには
`setValue()`に対する`setValues()`のように、`getValue()`に対する**`getValues()`**を使います。

![getValues1.PNG](https://qiita-image-store.s3.amazonaws.com/0/199678/c3796e7b-296e-a6a0-dc63-95b68e5722c3.png)

```javascript:get_values.gs
function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getRange('A1:B3').getValues();
  Logger.log(values);
}
```

実行したらCtrl+Enterでログを確認。

![getValues2.PNG](https://qiita-image-store.s3.amazonaws.com/0/199678/4c46b027-25f2-7c2e-a61e-2cb52fa8f33a.png)

無事にデータを取得できました。

# 5．数式を取得する

さて、先程使ったサンプルの「カレーライス、ラーメン、チャーハン」の合計金額をSUM関数で集計してGASで取得してみると、どういう結果が返ってくるでしょうか？

![getFormula1.PNG](https://qiita-image-store.s3.amazonaws.com/0/199678/73fff66b-7c74-06cb-dd04-99b62c9a7b41.png)

![getFormula2.PNG](https://qiita-image-store.s3.amazonaws.com/0/199678/10f33db0-50c7-0ff5-c49e-0fd3db51672c.png)

1800.0という結果が返ってきました。
つまり、`getValue()`は数式ではなく計算結果を返します。

では、`=sum('B1:B3')`という数式を文字列として取得したい場合はどうすれば良いのでしょうか？

GASでは`getFormula()`を使います。

```javascript:get_formula.gs
function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = sheet.getRange('B4').getFormula();
  Logger.log(formula);
}
```

>  getFormula()

https://developers.google.com/apps-script/reference/spreadsheet/range#getformula

実行してみると、`=sum('B1:B3')`が取得できていることが確認できます。

![getFormula3.PNG](https://qiita-image-store.s3.amazonaws.com/0/199678/6c400608-bc8d-b071-572e-e784b2645bc6.png)

`getFormulas()`、`setFormula()`、`setFormulas()`等もありますが、考え方はvalue系と同じなので割愛します。

これでスプレッドシートの範囲を指定して、値をセット／取得することができるようになりました。
本日は以上です。

# おわりに

`getRange()`、`getValue()`、`setValue()`の概念を理解できれば、今後紹介するGASによるスプレッドシート操作もすんなり理解できるはずです。

本アドベントカレンダーは、1日目～9日目あたりは基礎編でGASに関する基本的な操作を紹介していきます。
10日目以降は応用編ということで、実際に作ったアプリケーションとそれに関する技術の紹介になります。お楽しみに！

2018/01/15追記：
getActive()になっている箇所をgetActiveSheet()に修正しました。
@ocknamoさん、ご指摘ありがとうございました。

# 明日

[【Google Apps Script】その3　外部スプレッドシートをDBとして利用する](https://qiita.com/rf_p/items/47b3154e12c5107b9015)
となります。
お楽しみに！

前の記事
[【Google Apps Script】その1　Hello, world!](https://qiita.com/rf_p/items/0535ec495df8f9d2b894)
次の記事
[【Google Apps Script】その3　外部スプレッドシートをDBとして利用する](https://qiita.com/rf_p/items/47b3154e12c5107b9015)