皆さん、こんにちは。
新人研修を終えてもうすぐ1カ月のRです。
今回は社内Salesforceのデータを使用してGoogle Spread Sheet上で情報を管理するシステムを作成した話をします。

作成内容は「ボタンを押すことで案件ごとの社員の作業時間を導出するシートを作成する」です。
下の画像を見るとわかりやすいと思います。

これをするためには以下の3つの手順で実装しました。
・Salesforce上のデータをGoogle Spread Sheet上に落としこむ
・使いやすいようにデータを整理する
・整理したデータで表を作成する

まずSalesforce上のデータをGoogle Spread Sheet上に落とし込むことはSalesforce Connectorという拡張機能を使って持ってきました。
詳しいことは下にリンクを貼りますので気になりましたら是非確認してみてください。
オブジェクト、項目、条件を選択してSOQL文を自動で生成し、データ収集、自動更新してくれる優れものですが、
データ管理がしやすいように表を作成する際に、単純にコピー＆ペーストで作成できる並び替え機能がなかったため最終的にはSOQLコードを直接打っていました。

これを後に行う表作成のコードを作りやすいよう別のシートにまとめ、表作成のコードを作成します。
表作成にはGoogle App Scriptを使用して行いました。
まとめたデータシートを用いて案件名と作業者名を参照して等しいデータの作業時間を追加するコードを作成しました。
こうして作ったコードですが、すぐに問題点が浮上してきました。

遅い

スクリプト起動して終わるまでが遅すぎました。
原因はセルの読み取りをシートを介して行っていることと値の貼り付けをセル単位で行っていたからでした。
というのも、セルやシートの読み取り、書き込みはスプレッドシートAPIを呼び出していて、
例えばセルで案件を参照する→セルで作業者を参照する→セルで作業時間を参照する→セルに作業時間を追加する
というアルゴリズムである問題のコードだと一つのセルに4回API呼び出しを行う非効率極まりないコードになっていました。

これが仮に0.5秒に1つのセルを埋めると仮定すると、作業者を40人、案件数をこの倍の80あるとして
表すべてを埋めるには0.5 × 40 × 80 = 1600秒、26分40秒かかることになります。
これには起動してからコーヒーを淹れに行く古のPCもびっくりののんびりさです。
というよりそもそもGoogle Apps Scriptの実行時間は6分に制限されているためそもそも実行ができません。

当然この方法は没に。
これを解決するにはとにかくページ・セルを参照・書き込み等のスプレッドシートに触れるAPI呼び出し行為を減らすことに限ります。
そのためには「セルを参照する→処理をする→セルに書き込む」を繰り返すのでなく、
セルを一括で参照する→「処理する」→セルに一括で書き込む方法に変更しました。

Google App ScriptでGoogle Spread Sheetの複数セルを一括で書き込むことは配列をセルに書き込むことでできます。
長さ作業者数の一次元配列を案件数分持った二次元配列を作成し、
案件と作業者が等しいデータの作業時間を配列上に追加してシートに張り付ける挙動のコードに変更しました。
その結果30分ぐらいかかる表埋めの実行時間が1秒未満になりました。

ここまで劇的に変わるとはと驚くほどの速さでした。

開発にGoogle Apps Scriptを用いた感じたメリットとして
・環境構築が不要
・ベースの言語がメジャーなJavascriptだからとっつきやすい
・Google Appsとの連携が簡単
・トリガーで定期的にコードが実行できる
が挙げられ、小規模の開発にはもってこいの選択肢になるかなと思いました。

Salesforceよりも別の内容の方が多かったですが、このようなこともやっております。
今回はこのくらいで、また会いましょう！

【参考記事】

Google スプレッドシートで Salesforce データを取得、更新、同期する方法

【GAS】スプレッドシートの読み書きが遅い！を高速化するコツ