GoogleAppScript初心者がGoogleスプレッドシートで作業を自動化した時のメモ

対象者

• javascriptで繰り返し処理したり条件分岐したりな処理を書いたことがある人
  （javascriptについて全然詳しく書いてないのでjavascript完全初心者さん向きではないです）
• GoogleAppScript使ったことないけど、スプレッドシートでなんかエクセルのVBAっぽい自動化処理したい人
• すげー雑事すぎることは機械がやれよ！と考えてるオートメーション大賛成なズボラな人
• なんかイマドキっぽいことして俺イケてる感を味わいたい人（ただし味わえるか否かは人によるので保証できかねます）

経緯

毎日ツイッタ上の特定のURLがつぶやかれた数を集計してシート更新したい！ということになった。
→数とる分にはAPI叩けばできるということはわかっていた。
→てことはスクリプト書けば自動化できるんじゃね。
→ついでに更新した後slackに通知流してくれると嬉しいな♪
→楽したいから作るかーーー
という感じで作ることにしました。

もともと管理系はスプレッドシートが主流な文化の弊社なので、必然的にGoogle App Scriptで処理書くことになりました。

• 公式ドキュメント：
  Apps Script | Google Developers

使ったことなかったですけど面白そうなのでやってみました。その結果無事動かすことができたので、作る際に自分用にメモっといた内容をちょっと読みやすくして公開することにしました。

まずはじめに

作るとと決まればまずやりたいこと・やることを書き出し。
ソースに落とし込むので、できるだけ具体的にブレイクダウンします。

1. 指定の時刻になったら起動

• 日付を確認し、日付を記載した行のうち、記入する列を特定
• 繰り返し処理（収集したいURLが複数あったので）
  1. URL指定してAPIからデータ取ってくる
  • URLに対応するセルにデータ記入
• 集計した時刻を記載
• slackの通知を流す

ちなみに使う表のイメージはこんな感じでやることにしました。
上に日付、横にURL一覧というシンプルな表です。

GAS使ったことないとはいえ、基本はJavaScript。かつググれば情報は出て来ます。
それより大事なのは、どういうふうに動かして目的を達成するかのシナリオを描くこと。これができればあとはどうにでもなると思ってまず書き出しました。

処理実装

書き出したらそれぞれ具体的に実現させていきます。
ここでは具体的にどうすればいいのか調べたものに絞って解説していきます。

指定の時刻になったら起動

スクリプトエディタでトリガーを設定できるので、シンプルにこいつを使うことにしました。

参考：Google FormのGoogle Apps Scriptトリガー登録 - Qiita

※この記事で「メニューにトリガー選択できるものがない！」と書いてあり、時計マーク押すことを記載されてますが、メニューにも実はあって、「編集 > 現在のプロジェクトのトリガー」で見ることができます。場所が変わった模様です。

トリガーはスクリプトエディタのGUIで設定できますし、自分でスクリプト書いて指定することもできます。今回は単純にお昼頃のどっかの時間帯で動いてくれたらいいと思ったので画面上から設定しました。

ちなみに細かく設定したい場合はこれとかが参考になりそう↓
Google Apps Scriptの日毎のトリガーで時間をもっと細かく設定する - Qiita

日付判定、記載列の決定

日付の探し方

日付の判定は単純にDateオブジェクトで今日の日付を出して日付列の内容と一致するものを探すことにしました。
GASには日付操作に便利なformatDate()というやつがあるのでこいつを使います。

参考：Google apps scriptで日付を表示してみよう！

ちなみに手順４番目の集計した時刻の取得もこれでやりました。

記載列の決定

GASというか、スプレッドシートでの処理を書く際にちょと最初頭がこんがらかったのがシートの範囲指定やセル指定の方法です。
上記掲載した画像の表の形から、実際にこの目的を達成するにあたって「日付の記載された列を全部取ってきて、今日の日付と値が同じものを指定すれば行けそうかなー」ということを考えると思います。
「特定の範囲に含まれている値を全部取ってくる」ということをする場合に便利なGASの関数としてgetValues()があります。

getValuesは便利なんですけど、一つめんどくさい点としては２次元配列で値を返してくるということです。
そもそもスプレッドシートのセル指定は行・列の２つの値で指定して特定するのでまあ当たり前といえば当たり前ですが
getValues()で取得した２次元配列のままだとちょっと値の捜索に難があるので、取得した２次元配列をならす処理が必要です。
探してみたらやり方ありました。

参考：【JavaScript】2次元配列を1次元配列に変換する、ならす方法【Google Apps Script】 – oki2a24

上記の二つを踏まえて書いた処理がこんな感じです。

  // 現在のスプレッドシートを取得
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // 現在のシートを取得
  var sheet = spreadsheet.getSheetByName("presentSheet");;
  // 日付に応じて検索する範囲設定
  var values = Array.prototype.concat.apply([],sheet.getRange("A1:K1").getValues());
  var today = Utilities.formatDate(todayObj,'Asia/Tokyo','yyyyMMdd');
  // 現在日付と一致するセルの列を探す
  var colIndex = 0;
  for (var i = 0; i < values.length; i++) {
    var day = Utilities.formatDate(values[i],'Asia/Tokyo','yyyyMMdd');
    if (today === day) {
      colIndex = i;
      break;
    }
  }

APIからデータ取ってくる

ツイート数のカウントに関しては数とってきてくれる外部APIがあるのでそれを使えばいいのですが、GASからAPI叩くにはどうしたらいいんだ？と思ったらちょうど参考になる記事がありました。

参考：Google Apps ScriptでREST APIを使って郵便番号住所変換スプレッドシート関数を作る

UrlFetchApp.fetch()で叩いて帰ってきたレスポンスをJSON.parse()してあげれば一発でした。

slack通知

これに関してはincoming webhookがあるよ〜と社内のハイパーエンジニアさんが教えてくれました。

参考：Incoming Webhooks | Slack

連携の仕方は上記のドキュメントに書いてあるやつが一番参考になりました。あと社内で動いている他のSlack連携しているスプレッドシートのGASを参考にしたりして動かしました。

function slackBot() {
  var postUrl = 'incomming webhook連携設定した時に発行されるURL';
  var username = '　ユーザー名';  // 通知時に表示されるユーザー名
  var icon = ':hatching_chick:';  // 通知時に表示されるアイコン
  var message = 'メッセージ　\n https://docs.google.com/spreadsheets/hogehoge';
  
  var data =
  {
     channel: "#channel-name",
     username : username,
     icon_emoji: icon,
     text : message
  };

  var payload = JSON.stringify(data);

  var options =
  {
    method : "post",
    contentType : "application/json",
    payload : payload
  };

  UrlFetchApp.fetch(postUrl, options);

}

Slack表示させるアイコンとかも指定できるので楽しいです↓

ちなみに日本語でわかりやすいのが見たい人はここらへんとか参考になりそうです。
SlackのIncoming Webhooksを使い倒す - Qiita
SlackのWebhook URL取得手順 - Qiita

あと、Slack通知に関しては土日まで通知は流す必要はないかなと思い、土日以外動くように指定しました。

var todayObj = new Date();
var sunday = 0;
var saturday = 6;
if (todayObj.getDay() === sunday || todayObj.getDay() === saturday) {
    return;
}
// slack通知
slackBot();

参考：曜日判定を行うGASスクリプト:Googleスプレッドシートの使い方

終わりに

基本的に自分の書いた制作メモをベースに記事を書いたので、ほとんどソースコードは書きませんでした。（ソース目的でこの記事読んでる方にとっては見当違いと思われたかもしれません）
ググれば出てくるものに関しては私がここで記載せずともその記事に詳しく書いてあるので、そっちの詳しめな解説をみた方が確実と思ったからです。
あくまで「初めてこういうことやろうとしてるんだけどどうすればいいんだろう」という時にどうやって試行錯誤していくかの参考になればなーという思いが基本で記事を書きました。
何かの参考になれば幸いです。

おまけ：さらに掘り下げるのに参考になりそうなもの

入門系

• Excel VBAプログラマーのためのGoogle Apps Script入門 - ＠IT
• Google Apps Script 入門 - Qiita
• 【Google Apps Script入門】セルの取得・変更をする | UX MILK

実際にこんなことしてみよう系

• Google Docs SpreadSheetにAPIを利用してデータを追加してみよう(without zend gdata) | RakSul Tech Blog
• スプレットシートでSlack連携 - Qiita
• SlackとGASを使って会社のお弁当発注を自動化した話 - Qiita
• Trello API と GoogleAppsScript（GAS）を連携！環境準備からスプレッドシートへの書き込みまで楽々！ | スタッフブログ | 株式会社クーネルワーク - 新潟