【0からGASを学ぶ】GASを用いてスプレッドシートのデータを取得し、条件に応じてメールを送信してみよう！

# はじめに
本シリーズでは、GASの始め方や便利な使い方、ビジネス活用まで幅広く解説します。シリーズをひと通り読んでいただければ、あなたもきっとGASマスターになれるはずです。

# シリーズの対象者
* そもそもGASってなんだか**わからない**方
* GASを学びたいけど**何から始めればいいか**わからない方
* GASはわかり始めたけど、**もっと活用ができないか**と模索している方
* とにかく**Googleが好き！** という方

# 前回記事
https://qiita.com/gas-suke/items/ab83c04944ae2e109673

# スプレッドシートで遊ぶ
では早速始めていきましょう。【0からGASを学ぶ】シリーズの第5回は「GASを用いてスプレッドシートのデータを取得し、条件に応じてメールを送信してみよう！」です。これまでにお伝えした`Googleスプレッドシート`および`Gmail`を操作するクラスを使用すれば、そこまで複雑ではないので早速やってみましょう。

## 事前準備
スプレッドシートのデータを取得しなければ始まらないので、**取得元となるスプレッドシート**を作りましょう。私は以下のようなスプレッドシートを作成しました。サンプルなので、この通りである必要はありませんが、これを前提にプログラムは書かせてもらいます。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3575702/2796cca7-963b-8dcb-ced7-e6b968d21ece.png" width="600">

## 今回やること
1. スプレッドシート内のすべてのデータを取得する。
1. 1行ごとにデータを処理して、[必要数]>[在庫数]の行を特定する。
1. 特定した行が[発注メール]欄が空であれば[発注先メールアドレス]にメールを送信する。
1. メールを送信した後に[発注メール]欄に送信日時を記入する。

## プログラム開始
### GASエディタを開く
今回は取得元のスプレッドシートが決まっているため、このスプレッドシートにバインドするGASにプログラムを記述していきましょう。<a href="https://qiita.com/gas-suke/items/5cf7bd4c6c3f15a25838#%E3%82%B3%E3%83%B3%E3%83%86%E3%83%8A%E3%83%90%E3%82%A4%E3%83%B3%E3%83%89%E5%9E%8B" target="_blank">こちら</a>を参考にGASエディタを起動してください。では、どんどんいきますよ、ついてきてください。

### STEP.1　スプレッドシート内のすべてのデータを取得する
```javascript:Qiita005.gs
function Qiita005_SpreadSheet() {
  // コンテナバインド型なので、getActiveSpreadsheetにより自分自身を取得する
  const wSpread = SpreadsheetApp.getActiveSpreadsheet();
  // 以下の記述でも可能（単なるエイリアスの違いのみ）
  // const wSpread = SpreadsheetApp.getActive()

  // スプレッドシート内の1番目のシートを取得する
  const wSheet = wSpread.getSheets()[0];

  // シート内の全データを取得する。
  // getDataRangeはデータが存在する範囲のみを取得してくれるため非常に便利
  let wVals = wSheet.getDataRange().getValues();
  console.log(wVals);
}
```
```:実行結果
13:57:05	お知らせ	実行開始
13:57:06	情報	[ [ 'No', '商品名', '必要数', '在庫数', '発注先メールアドレス', '発注メール' ],
                      [ 1, '商品サンプル１', 1000, 30, '*******01@gmail.com', '' ],
                      [ 2, '商品サンプル２', 2000, 3000, '*******02@gmail.com', '' ],
                      [ 3, '商品サンプル３', 3000, 2999, '*******03@gmail.com', '' ] ]
13:57:07	お知らせ	実行完了
```
無事にすべてのデータを取得できていることが確認できます。

### STEP.2　1行ごとにデータを処理して、[必要数]>[在庫数]の行を特定する
それでは上記で取得した`wVals`をもとに[必要数]>[在庫数]となっている行を評価してみましょう。
```javascript:Qiita005.gs
// ここから追加コード
const COL = {
  NUM:1
  , PRODUCT_NAME:2
  , REQUIRED_QUANTITY:3
  , INVENTORY_CNT:4
  , MAIL:5
  , MAIL_TIMESTAMP:6
}
// ここまで

function Qiita005_SpreadSheet() {
  // コンテナバインド型なので、getActiveSpreadsheetにより自分自身を取得する
  const wSpread = SpreadsheetApp.getActiveSpreadsheet();
  // 以下の記述でも可能（単なるエイリアスの違いのみ）
  // const wSpread = SpreadsheetApp.getActive()

  // スプレッドシート内の1番目のシートを取得する
  const wSheet = wSpread.getSheets()[0];

  // シート内の全データを取得する。
  // getDataRangeはデータが存在する範囲のみを取得してくれるため非常に便利
  let wVals = wSheet.getDataRange().getValues();

// ここから追加コード  
  // 1行目（index:0）はヘッダー列なので、2行目からデータを走査
  for (let rIdx=1; rIdx<wVals.length; rIdx++) {
    // [必要数]>[在庫数]の行を特定する
    if (wVals[rIdx][COL.REQUIRED_QUANTITY-1]>wVals[rIdx][COL.INVENTORY_CNT-1]) {
      console.log(rIdx+1+"行目は[必要数]>[在庫数]");
    }
  }
// ここまで
}

```
```:実行結果
16:11:31	お知らせ	実行開始
16:11:32	情報	2行目は[必要数]>[在庫数]
16:11:32	情報	4行目は[必要数]>[在庫数]
16:11:32	お知らせ	実行完了
```

:::note info
何気なくコードの上部に書きましたが、スプレッドシートを扱う場合は、**セル位置を定数化**しておくと便利です。これにより例えば列が**追加**、**削除**された場合に、**定数だけを変更すれば、処理側を変更する必要がなくなり**ます。ぜひ、参考にしてみてください。
```javascript
const COL = {
  NUM:1
  , PRODUCT_NAME:2
  , REQUIRED_QUANTITY:3
  , INVENTORY_CNT:4
  , MAIL:5
  , MAIL_TIMESTAMP:6
}
```
:::

### STEP.3　特定した行が[発注メール]欄が空であれば[発注先メールアドレス]にメールを送信する
それでは上記で特定した[必要数]>[在庫数]となっている行の場合は、[発注先メールアドレス]に設定されたアドレスにメールを送信してみましょう。
**※[発注先メールアドレス]には試しにご自身のメールアドレスなどを設定してみてください。**
```javascript:Qiita005.gs
const COL = {
  NUM:1
  , PRODUCT_NAME:2
  , REQUIRED_QUANTITY:3
  , INVENTORY_CNT:4
  , MAIL:5
  , MAIL_TIMESTAMP:6
}

function Qiita005_SpreadSheet() {
  // コンテナバインド型なので、getActiveSpreadsheetにより自分自身を取得する
  const wSpread = SpreadsheetApp.getActiveSpreadsheet();
  // 以下の記述でも可能（単なるエイリアスの違いのみ）
  // const wSpread = SpreadsheetApp.getActive()

  // スプレッドシート内の1番目のシートを取得する
  const wSheet = wSpread.getSheets()[0];

  // シート内の全データを取得する。
  // getDataRangeはデータが存在する範囲のみを取得してくれるため非常に便利
  let wVals = wSheet.getDataRange().getValues();
  
  // 1行目（index:0）はヘッダー列なので、2行目からデータを走査
  for (let rIdx=1; rIdx<wVals.length; rIdx++) {
    // [必要数]>[在庫数]の行を特定する
    if (wVals[rIdx][COL.REQUIRED_QUANTITY-1]>wVals[rIdx][COL.INVENTORY_CNT-1]
// ここから追加コード
        && wVals[rIdx][COL.MAIL_TIMESTAMP-1]=='') {
      GmailApp.sendEmail(
        wVals[rIdx][COL.MAIL-1]
        , `【自動送信メール】発注依頼`
        , `${wVals[rIdx][COL.PRODUCT_NAME-1]}の在庫がなくなりました。
必要数：${wVals[rIdx][COL.REQUIRED_QUANTITY-1]}
在庫数：${wVals[rIdx][COL.INVENTORY_CNT-1]}`
      )
// ここまで
    }
  }
}
```
こちらを実行すると以下のようなメールが送信されたかと思います。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3575702/cf13485d-80f5-ac24-bda3-d23b6ad6a66e.jpeg" width="400">

:::note info
ここでも何気なくコードに書きましたが、文字列を囲う符号は以下の3つがあります。
1. **'** シングルクォート
1. **"** ダブルクォート
1. **`** バッククォート

どれを使用しても最終的には同じことはできますが、バッククォートは**文字列の中で変数を扱ったり改行する場合**に非常に便利です。
```javascript
// シングルクォートでの書き方
GmailApp.sendEmail(
  wVals[rIdx][COL.MAIL-1]
  , '【自動送信メール】発注依頼'
  , wVals[rIdx][COL.PRODUCT_NAME-1]+'の在庫がなくなりました。\n'
  +'必要数：'+wVals[rIdx][COL.REQUIRED_QUANTITY-1]+'\n'
  +'在庫数：'+wVals[rIdx][COL.INVENTORY_CNT-1]
)

// バッククォートでの書き方
GmailApp.sendEmail(
  wVals[rIdx][COL.MAIL-1]
  , `【自動送信メール】発注依頼`
  , `${wVals[rIdx][COL.PRODUCT_NAME-1]}の在庫がなくなりました。
必要数：${wVals[rIdx][COL.REQUIRED_QUANTITY-1]}
在庫数：${wVals[rIdx][COL.INVENTORY_CNT-1]}`
)
```
ただし、バッククォートの場合は**バッククォート内の文字がそのまま反映される**ため、プログラム上の**インデントを合わせるためのスペース**にも注意が必要です。例えば、インデントを合わせるために
```javascript
GmailApp.sendEmail(
  wVals[rIdx][COL.MAIL-1]
  , `【自動送信メール】発注依頼`
  , `${wVals[rIdx][COL.PRODUCT_NAME-1]}の在庫がなくなりました。
     必要数：${wVals[rIdx][COL.REQUIRED_QUANTITY-1]}
     在庫数：${wVals[rIdx][COL.INVENTORY_CNT-1]}`
)
```
とした場合は、**"必要数："** および **"在庫数："** 前のスペースもスペースとして出力されます。
:::

### STEP.4　メールを送信した後に[発注メール]欄に送信日時を記入する
それでは最後にメール送信後にその記録を[発注メール]欄に記入しましょう。
```javascript:【完成版プログラム】Qiita005.gs
const COL = {
  NUM:1
  , PRODUCT_NAME:2
  , REQUIRED_QUANTITY:3
  , INVENTORY_CNT:4
  , MAIL:5
  , MAIL_TIMESTAMP:6
}

function Qiita005_SpreadSheet() {
  // コンテナバインド型なので、getActiveSpreadsheetにより自分自身を取得する
  const wSpread = SpreadsheetApp.getActiveSpreadsheet();
  // 以下の記述でも可能（単なるエイリアスの違いのみ）
  // const wSpread = SpreadsheetApp.getActive()

  // スプレッドシート内の1番目のシートを取得する
  const wSheet = wSpread.getSheets()[0];

  // シート内の全データを取得する。
  // getDataRangeはデータが存在する範囲のみを取得してくれるため非常に便利
  let wVals = wSheet.getDataRange().getValues();
  
  // メール送信記録の書き込み用リストを生成する
  let wWriteLst = new Array();

  // 1行目（index:0）はヘッダー列なので、2行目からデータを走査
  for (let rIdx=1; rIdx<wVals.length; rIdx++) {
    // [必要数]>[在庫数]の行を特定する
    if (wVals[rIdx][COL.REQUIRED_QUANTITY-1]>wVals[rIdx][COL.INVENTORY_CNT-1]
        && wVals[rIdx][COL.MAIL_TIMESTAMP-1]=='') {
      GmailApp.sendEmail(
        wVals[rIdx][COL.MAIL-1]
        , `【自動送信メール】発注依頼`
        , `${wVals[rIdx][COL.PRODUCT_NAME-1]}の在庫がなくなりました。
必要数：${wVals[rIdx][COL.REQUIRED_QUANTITY-1]}
在庫数：${wVals[rIdx][COL.INVENTORY_CNT-1]}`
      )
      // メール送信記録をwWriteTimeに書き込み
      wWriteLst.push([Utilities.formatDate(new Date(), 'JST', 'yyyy-MM-dd HH:mm:ss')]);
    } else {
      // メール送信しない場合は現状維持
      wWriteLst.push([wVals[rIdx][COL.MAIL_TIMESTAMP-1]]);
    }
  }
  // 一括でメール送信記録を貼り付け
  wSheet.getRange(2, COL.MAIL_TIMESTAMP, wWriteLst.length, 1).setValues(wWriteLst);
}
```
上記プログラムを実行すると[発注メール]の箇所に送信日時が記録されます。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3575702/9a4a8523-94b4-1a27-49f1-e0f1fabe6f8d.png" width="600">

:::note info
本プログラムのポイントは以下のように**最後に一括で貼り付け**を行うことです。
`wSheet.getRange(2, COL.MAIL_TIMESTAMP, wWriteLst.length, 1).setValues(wWriteLst);`
for文でループさせながら、セルに書き込むことも間違いではありません。
```javascript:都度セルに書き込む場合
for (let rIdx=1; rIdx<wVals.length; rIdx++) {
  // [必要数]>[在庫数]の行を特定する
  if (wVals[rIdx][COL.REQUIRED_QUANTITY-1]>wVals[rIdx][COL.INVENTORY_CNT-1]
      && wVals[rIdx][COL.MAIL_TIMESTAMP-1]=='') {
…
    // メール送信記録をセルに書き込み
    wSheet.getRange(rIdx, COL.MAIL_TIMESTAMP).setValue(Utilities.formatDate(new Date(), 'JST', 'yyyy-MM-dd HH:mm:ss'));
  } else {
    // メール送信しない場合は現状維持
  }
}
```
しかしながら、この場合は都度セルへのアクセスが発生し、**処理時間は遅く**なります。そこで、配列`wWriteLst`に送信記録をpushしていき、最後に**一括で貼り付け**を行った方が**処理時間も早く**、GASの**制限回数に対しても有意義**と言えます。
:::

# おわりに
お疲れ様でした。
第5回は「GASを用いてスプレッドシートのデータを取得し、条件に応じてメールを送信してみよう！」ということで、これまでとは異なりスプレッドシートのデータ**取得**や**評価**、さらに`Gmail`と組み合わせるなど、少しずつ業務活用に足をかけ始めました。次回も引き続き、スプレッドシートで遊んでみたいと思います。
記事を読んで、「良いな」や「今後に期待できる！」と感じて頂けたら**いいね**や**フォロー**、**コメント**いただけると幸いです。それではまた次回をお楽しみに！

## ブログでより詳しく解説しています！
以下画像をクリックしてブログにアクセス！！
<a href="https://gas-4u.com/gas04/" target="_blank"><img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3575702/2b4f0e4e-3dd6-65bb-39e6-d009b0cf1a79.png" width="600"/></a>