GASを使ってSpreadsheetを使いこなす!! セルの値を取得し社員の勤務時間をGmailに送ろう!!

やりたいこと

A社では社員の勤務時間をSpreadsheetで管理している。
毎日確認するのも面倒なので、毎日Gmailに管理してる社員の勤務時間が送られるようにしたい。

Spreadsheet

勤務時間を管理しているSpreadsheetはこんな感じ。

同じ値のデータのコピーはCtrlキーを押しながら引きたいところまでドラッグすると連番にならずコピーできる。(自分も初めて知った)
オートフィルで数字や日付などの連続データを入力する｜スプレッドシートの使い方

SpreadSheetのデータをGmailに送る

Spreadsheetの基本的な使い方

GAS、Google Apps Scriptを使うコードの記入はツールタブ → スクリプト エディタをクリックする。

ファイル名の変更はコード.gsの横にある▼を押すとRenameが表示されるので、それをクリックし、設定したいファイル名にする。

console.log()を使用したログの表示は、表示タブ→ログから。
実行ボタンを押した後にconsole.log()で指定した値を確認することができる。
一々Gmailとかの出力結果を待たなくていいので、覚えておくと便利。

【初心者向けGAS】ログを表示するconsole.logの使い方とテンプレート文字列

SpreadsheetからGmailを送信

タイトルを勤務時間管理、ファイル名をSendGmailに変更し、初心者でも簡単！Google Apps ScriptでGmailを操作してメールを送る方法を参考に、以下のコードを実装した。

function SendGmail() {
  const address = '[e-mail]'
  //送信先のメールアドレス
  
  const subject = 'Spreadsheetからのメール送信';
  // 送信するメールの件名
 
  const body = `Spreadsheetからの情報をお送りします。`;
  // 送信するメールの本文
  
  const options = {name: 'Spreadsheet'};
  // ccやbccといったオプションを指定できる。
  // nameは送信者名のオプションを設定している。
  
  GmailApp.sendEmail(address, subject, body, options);
}

コードの記入が終わったら、虫みたいなマークの左隣にある▶のマーク、実行ボタンを押す。

すると、このコードが自分のGoogleデータへのアクセス許可が求められる。

許可を確認を押し、Googleアカウントでログインすると、このアプリは確認されていませんと表示される。
左下にある詳細を押すと、下の画像のようになるので、勤務時間管理（安全ではないページ）に移動を押す。

そうすると、Gmailの様々な権限をこのコードに与えて良いかを許可するかどうかを聞かれるため、許可を押す。

再び▶の実行ボタン`を押して、Gmailに移り、以下のようなメールが届いているかを確認する。

上記のコードで設定した内容が届いていればOK

Spreadsheetにあるセルの値を取得する

ここからが本番。

まずは新しいファイルを作成する。
手順はファイルタブ → New → スクリプト ファイル。
自分はファイル名をGetSpreadsheetにした。

Spreadsheetのセルの値を取得するコードは以下。
参考にしたのは次の2つ。

• 【初心者向けのGAS】スプレッドシートの最後の列（最終列）を取得する方法 | よこのじ.work
• GASの使い方（forEach文） | リディアミックス株式会社

各コードの解説はコメントアウトで。

function GetSpreadsheet() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const cell = sheet.getDataRange().getDisplayValues();
  // getDataRange() = 値が存在しているセルを範囲に指定し取得
  // getDisplayValues() = 取得したセルに入っているデータや値を文字列として取得
  
  const today = new Date();
  var name = [];
  var work_time = "";
  
  const last_column = sheet.getLastColumn();
  // データの入っている最終行を取得
  const member = (last_column - 1) / 2;
  // 勤務時間を取得するメンバーは何人か数える。今回は3が入る
  // A列分 = -1, 2つのセルを結合して名前を表示 = ÷ 2
  for (var i=0; i<member; i++) {
    name.push(cell[0][(i*2)+1]);
    //取得した名前を配列に挿入
  }
  
  cell.forEach(function(data_array){
    // cellの全データを1つずつdata_arrayに格納する
    const cell_date = new Date(data_array[0]);
    // A列に記入されているデータを日付として取得
    if(cell_date.getMonth() == today.getMonth() && cell_date.getDate() == today.getDate()){
      for(var j=1; j<=member; j++) {
        start_time = data_array[(j*2)-1];
        // 出勤時間を取得
        if(start_time != "" && start_time != "休暇"){
          // 何かしらの文字が入っていて、休暇じゃなかったら次の処理を行う
          end_time = data_array[j*2];
          // 退勤時間を取得
          if(start_time.match(/~/)){
            time_text = start_time + " " + end_time;
            // 出勤時間の中に「~」の文字があれば、出勤時間と退勤時間を空白で区切る
          }else{
            time_text = start_time + "~" + end_time;
            // 出勤時間の中に「~」の文字がなければ、出勤時間と退勤時間を「~」で繋ぐ
          }
        }else if(start_time != "" && start_time == "休暇"){
          // 休暇だった時の処理
          time_text = start_time;
        }
        work_time += name[j-1] + "  " + time_text +"\n";
        // 取得した名前と出勤時間、退勤時間をwork_timeに文字列として挿入
      }
      return false;
      // 勤務時間を取得できたらforEachを終了させる
    }
  });
  console.log(work_time);
}

この記事を読んでる人が何月にやってるかわからないけれど、A3~A32,A33まで今月の日付を入力。
それができたら▶の実行ボタンを押して、エラーがないか確認。
エラーもないようだったら、表示タブ→ログで設定したconsole.log(work_time);の中身を確認する。
次の画像のようなログが出力されるはず。

セルの取得

セルに挿入されたデータを取得する場合、セルの場所を指定する必要がある。
4/1というデータを取得する場合、SpreadsheetならA3と一目でわかるが、GASを利用したコードではそうはいかない。
コードでセルを指定する場合は(0,0)という風に行と列を数字で指定する必要がある。
Spreadsheet上のA3にある4/1を取得する際には(2,0)となる。
A3に対応させるとすれば(3,A)という感じ。
これはA1が(0,0)で始まっているため、データを取得するセルを指定する場合には工夫する必要がある。
B1なら(0,1)、A6なら(5,0)となる。

これはもう覚えておくしかないと思う。
言葉にするなら、(行,列)で(行の数字-1, Aを0として0から始まるアルファベット25文字) 。

図のイメージなら、以下のような感じ。
()の数字がコードで指定する数字。

	A(0)	B(1)	C(2)
1(0)	(0,0)	(0,1)	(0,2)
2(1)	(1,0)	(1,1)	(1,2)
3(2)	(2,0)	(2,1)	(2,2)
4(3)	(3,0)	(3,1)	(3,2)

Spreadsheetで取得したデータをGmailに送る

ここまで2つのファイルを作成した。

• SpreadsheetからGmailを送信するファイル = SendGmail.gs
• Spreadsheetのデータを取得するファイル = GetSpreadsheet.gs

ここではその2つを組みわせて、この記事の目標である Spreadsheetで取得したデータをGmailに送る を実行する。

といってもやることは2つ。

1. GetSpreadsheet.gsの最後にあるconsole.log(work_time);をreturn work_time;に変える
2. SendGmail.gsのconst body = "Spreadsheetからの情報をお送りします。";をconst body = GetSpreadsheet();に変える

これだけでおしまい。
GASは同一プロジェクトであれば別ファイルであってもfunctionを呼び出したいところに記入するだけで済む。
これは便利。
Google Apps Scriptで、同一プロジェクト内で分割された.gsファイルの関数を使う方法

最後の作業。
SendGmail.gsのファイルを開いている状態で▶の実行ボタンを押してGmail`にSpreadsheetで勤務時間を管理している人のデータを送信。
次の画像のようなメールが送信されていればおしまい。

今回はGmailにSpreadsheetのデータを送信したけれど、キャリアのメールアドレスだったり、Slackやchatworkに送信することもできる。
GASはコードだけでなく、トリガーなるものを設置すれば毎日自動でデータが送られてくるらしい。
Google Apps Script で毎日決まった時間にスクリプトを実行するトリガー設定

この記事を参考に、色々なSpreadsheetのデータを送信できるようにしてみては?

参考記事

• オートフィルで数字や日付などの連続データを入力する｜スプレッドシートの使い方
• 【初心者向けGAS】ログを表示するconsole.logの使い方とテンプレート文字列
• 初心者でも簡単！Google Apps ScriptでGmailを操作してメールを送る方法
• 【初心者向けのGAS】スプレッドシートの最後の列（最終列）を取得する方法 | よこのじ.work
• GASの使い方（forEach文） | リディアミックス株式会社
• Google Apps Scriptで、同一プロジェクト内で分割された.gsファイルの関数を使う方法