LoginSignup
4
1
記事投稿キャンペーン 「エンジニア×非エンジニアのコミュニケーション」
@gas-suke

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

Last updated at Posted at 2023-10-25

はじめに

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

シリーズの対象者

  • そもそもGASってなんだかわからない方
  • GASを学びたいけど何から始めればいいかわからない方
  • GASはわかり始めたけど、もっと活用ができないかと模索している方
  • とにかくGoogleが好き！ という方

前回記事

スプレッドシートで遊ぶ

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

事前準備

スプレッドシートのデータを取得しなければ始まらないので、取得元となるスプレッドシートを作りましょう。私は以下のようなスプレッドシートを作成しました。サンプルなので、この通りである必要はありませんが、これを前提にプログラムは書かせてもらいます。

今回やること

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

プログラム開始

GASエディタを開く

今回は取得元のスプレッドシートが決まっているため、このスプレッドシートにバインドするGASにプログラムを記述していきましょう。こちらを参考にGASエディタを起動してください。では、どんどんいきますよ、ついてきてください。

STEP.1　スプレッドシート内のすべてのデータを取得する

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をもとに[必要数]>[在庫数]となっている行を評価してみましょう。

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	お知らせ	実行完了

何気なくコードの上部に書きましたが、スプレッドシートを扱う場合は、セル位置を定数化しておくと便利です。これにより例えば列が追加、削除された場合に、定数だけを変更すれば、処理側を変更する必要がなくなります。ぜひ、参考にしてみてください。

const COL = {
  NUM:1
  , PRODUCT_NAME:2
  , REQUIRED_QUANTITY:3
  , INVENTORY_CNT:4
  , MAIL:5
  , MAIL_TIMESTAMP:6
}

STEP.3　特定した行が[発注メール]欄が空であれば[発注先メールアドレス]にメールを送信する

それでは上記で特定した[必要数]>[在庫数]となっている行の場合は、[発注先メールアドレス]に設定されたアドレスにメールを送信してみましょう。
※[発注先メールアドレス]には試しにご自身のメールアドレスなどを設定してみてください。

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]}`
      )
// ここまで
    }
  }
}

こちらを実行すると以下のようなメールが送信されたかと思います。

ここでも何気なくコードに書きましたが、文字列を囲う符号は以下の3つがあります。

  1. ' シングルクォート
  2. " ダブルクォート
  3. ` バッククォート

どれを使用しても最終的には同じことはできますが、バッククォートは文字列の中で変数を扱ったり改行する場合に非常に便利です。

// シングルクォートでの書き方
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]}`
)

ただし、バッククォートの場合はバッククォート内の文字がそのまま反映されるため、プログラム上のインデントを合わせるためのスペースにも注意が必要です。例えば、インデントを合わせるために

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　メールを送信した後に[発注メール]欄に送信日時を記入する

それでは最後にメール送信後にその記録を[発注メール]欄に記入しましょう。

【完成版プログラム】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);
}

上記プログラムを実行すると[発注メール]の箇所に送信日時が記録されます。

本プログラムのポイントは以下のように最後に一括で貼り付けを行うことです。
wSheet.getRange(2, COL.MAIL_TIMESTAMP, wWriteLst.length, 1).setValues(wWriteLst);
for文でループさせながら、セルに書き込むことも間違いではありません。

都度セルに書き込む場合
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と組み合わせるなど、少しずつ業務活用に足をかけ始めました。次回も引き続き、スプレッドシートで遊んでみたいと思います。
記事を読んで、「良いな」や「今後に期待できる！」と感じて頂けたらいいねやフォロー、コメントいただけると幸いです。それではまた次回をお楽しみに！

ブログでより詳しく解説しています！

以下画像をクリックしてブログにアクセス！！

4
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
4
1