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

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

## Spreadsheet
勤務時間を管理しているSpreadsheetはこんな感じ。
![screeenshot.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/218398/ffa5961d-1079-e7bb-0fa4-9284098e6cf5.png)

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

## SpreadSheetのデータをGmailに送る
### Spreadsheetの基本的な使い方
`GAS`、`Google Apps Script`を使うコードの記入は`ツールタブ` → `スクリプト エディタ`をクリックする。
![screeenshot.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/218398/eebfebfa-2054-ce39-fc30-80b53350ee7a.png)

ファイル名の変更は`コード.gs`の横にある`▼`を押すと`Rename`が表示されるので、それをクリックし、設定したいファイル名にする。
![screeenshot.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/218398/83e62c48-0549-938f-9acf-2b018111ecfd.png)

`console.log()`を使用したログの表示は、`表示タブ`→`ログ`から。
実行ボタンを押した後に`console.log()`で指定した値を確認することができる。
一々Gmailとかの出力結果を待たなくていいので、覚えておくと便利。
![screeenshot.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/218398/b9d6f3db-8ed1-1a67-beee-58d4837fa369.png)
[【初心者向けGAS】ログを表示するconsole.logの使い方とテンプレート文字列](https://tonari-it.com/gas-logger-log/)

### SpreadsheetからGmailを送信
タイトルを`勤務時間管理`、ファイル名を`SendGmail`に変更し、[初心者でも簡単！Google Apps ScriptでGmailを操作してメールを送る方法](https://tonari-it.com/gas-gmail-sendemail/)を参考に、以下のコードを実装した。

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

コードの記入が終わったら、虫みたいなマークの左隣にある`▶`のマーク、`実行ボタン`を押す。
![screeenshot.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/218398/c01cae02-0d7a-cb42-9638-61058dba8c61.png)

すると、このコードが自分のGoogleデータへのアクセス許可が求められる。
![screeenshot.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/218398/1f222a37-3778-764c-ffed-8071d4549427.png)

`許可を確認`を押し、Googleアカウントでログインすると、`このアプリは確認されていません`と表示される。
左下にある`詳細`を押すと、下の画像のようになるので、`勤務時間管理（安全ではないページ）に移動`を押す。
![screeenshot.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/218398/554deb07-e08d-ca81-55e9-ed6498272617.png)

そうすると、Gmailの様々な権限をこのコードに与えて良いかを許可するかどうかを聞かれるため、`許可`を押す。
![screeenshot.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/218398/0286cc75-2a59-3b7d-f8d5-e5c56f42b84a.png)

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

![screeenshot.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/218398/8565f34a-02ac-dd6e-e885-5e1e9532dc27.png)

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

### Spreadsheetにあるセルの値を取得する
ここからが本番。

まずは新しいファイルを作成する。
手順は`ファイルタブ` → `New` → `スクリプト ファイル`。
自分はファイル名を`GetSpreadsheet`にした。
![screeenshot.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/218398/2bcc55b3-f73f-a7dd-983f-db8023c9a361.png)

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

- [【初心者向けのGAS】スプレッドシートの最後の列（最終列）を取得する方法 | よこのじ.work](https://yokonoji.work/gas-get-last-column)
- [GASの使い方（forEach文） | リディアミックス株式会社](https://lediamix.com/journal/cording/gas-foreach/)

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

```js
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);`の中身を確認する。
次の画像のようなログが出力されるはず。

![screeenshot.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/218398/d044aa3c-5823-49de-8f9f-d97cfba3875a.png)


#### セルの取得
セルに挿入されたデータを取得する場合、セルの場所を指定する必要がある。
`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ファイルの関数を使う方法](https://qiita.com/shirakiya/items/5e51e352faef6f7db9be)


最後の作業。
`SendGmail.gs`のファイルを開いている状態で`▶`の実行ボタン`を押して`Gmail`にSpreadsheetで勤務時間を管理している人のデータを送信。
次の画像のようなメールが送信されていればおしまい。
![screeenshot.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/218398/a79113ee-c2bb-de03-a79a-97267e6514ec.png)


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

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

## 参考記事
- [オートフィルで数字や日付などの連続データを入力する｜スプレッドシートの使い方](https://spread-sheets.com/useful/data/autofill/)
- [【初心者向けGAS】ログを表示するconsole.logの使い方とテンプレート文字列](https://tonari-it.com/gas-logger-log/)
- [初心者でも簡単！Google Apps ScriptでGmailを操作してメールを送る方法](https://tonari-it.com/gas-gmail-sendemail/)
- [【初心者向けのGAS】スプレッドシートの最後の列（最終列）を取得する方法 | よこのじ.work](https://yokonoji.work/gas-get-last-column)
- [GASの使い方（forEach文） | リディアミックス株式会社](https://lediamix.com/journal/cording/gas-foreach/)
- [Google Apps Scriptで、同一プロジェクト内で分割された.gsファイルの関数を使う方法](https://qiita.com/shirakiya/items/5e51e352faef6f7db9be)