Salesforceとスプレッドシートを連携してレポートをGASで自動整形できるようにしてみた

こんにちは。GMOアドマーケティングの @chi1kuwa です。
私は事業統括本部の事務スタッフとして、受注管理システムの入力および集計の業務や
GASやRPAを使用した業務効率化に取り組んでいます。
業務の中で直接開発に関わることはないのですが、
エンジニアのリソースを多く割かずに全社業務の自動化を進めていくことを
ミッションとしたRPAプロジェクトにも参加しています。

#はじめに
みなさんの会社ではSalesforceを使っていますでしょうか？

弊社では販売管理にSalesforceを使っています。
Salesforceのレポートの機能は誰でも簡単に設定が出来て非常に便利なのですが、
分析用にデータを整形する点ではExcelやスプレッドシートの方が使いやすい仕様になっていると思います。
セールスチームから、レポートをスプレッドシート上で見れるようにし、指定の表記に整形、
それを自動で更新していくようにしたいという要望があったので、
この機会にGASを使用してレポートの集計、整形の作業を自動化してみました。

本記事では、非エンジニアでも簡単にできる
Salesforceのレポートをスプレッドシートに自動で取り込む方法と、
その後GASを使って自動で整形する方法をご紹介します。

#手順
１．スプレッドシートを用意
２．Salesforceとスプレッドシートを連携し、レポートの内容をスプレッドシートにインポート
３．GASを使ってレポートを整形
４．定期実行の設定

#Salesforceとスプレッドシートの連携
レポートを連携するためのスプレッドシートを用意。
スプレッドシートでSalesforceのデータをインポート・更新・削除するために、
まずはアドオンを設定します。

ツールバー上の[アドオン]＞[アドオンを取得]をクリック
右上の検索バーで[Data connector for Salesforce]を検索。
（もしくは こちらからインストールを選択して追加でもOK！）
アドオンの横の追加アイコン[＋]をクリック。

アドオンのダウンロードが完了したら、
[アドオン]＞[Data connector for Salesforce]＞[Click to enable the add-on]＞[続行]＞[許可]をクリック。

プルダウンメニューから、ログインしたいSalesforceの環境を選択し、[承認]をクリック。
あとはSalesforceのユーザー名とパスワードを入力してログイン。

ここまでで、Salesforceとスプレッドシートの連携は完了しました！
とても簡単なので非エンジニアでも迷うことなく設定が出来ます。

#レポートの内容をスプレッドシートにインポート
[アドオン]＞[Data connector for Salesforce]＞[Open]をクリック。
右側にオプションを選択する欄が出てくるので、何を行うかを選択。

　・[Reports]: 既存の Salesforceレポートをスプレッドシートに取り込む。
　　┗既存のシートと新しいシートのどちらにレポートを出力するのかを選べる！
　・[Import]: Google のクエリビルダーまたは SOQL を使用して Salesforce からデータをインポートする。
　　┗ソースオブジェクト、フィールド、フィルタを5個まで追加できる！
　・[Update]：Salesforceのレコードに対して登録・更新を行う
　・[Delete]：Salesforceのレコードに対して削除を行う
　・[Refresh]：スプレッドシート上のデータを最新の状態にする

今回はSalesforceで作成済のレポートを元にをインポートしたいので[Reports]をクリック。
対象のレポートを選択し、[GET DATA]をクリック。

そうすると、作成しておいたレポートの通りにスプレッドシートにデータがエクスポートできます！
クエリを自分で書いてもデータをエクスポートできますが、
事前に必要なレポートをSalesforceで設定しておくと便利ですね。

※このアドオンを追加すると、スプレッドシート上で加えた変更をSalesforceアカウントで更新・削除することも可能になります！誤って修正をしてしまわないよう注意してください※
#スプレッドシートへの定期的な出力の設定
メニュー画面で「Refresh」を選択し、「Auto Refresh」を選択します。

・「Auto Refresh」※自動出力
自動で更新するシートを選択し、4,8,24時間ごとのいずれかに設定可能

・「Manual Refresh」※手動出力
Refreshボタンをクリックすると、すぐに更新が完了する

#GASを使ってレポートを整形
ここからはGASを使ってレポートを整形する方法を書いていきます。
※GASの基本的な書き方は省略させていただきます。
最終的にレポートを整形したものを日毎にスプレッドシートで保存したかったので、
まず新規スプレッドシートを作成し、そこにエクスポートしたレポートのデータをコピーします。
###指定のGoogleドライブフォルダに新規スプレッドシートを作成

指定のGoogleドライブフォルダに新規スプレッドシートを作成

  var file = Drive.Files.insert({
  "title": "ファイル名",
  "mimeType": "application/vnd.google-apps.spreadsheet",
  "parents": [{"id": "ドライブフォルダID"}]
  });
  var ss_new = SpreadsheetApp.openById(file.id); //新規作成したスプレッドシート
  var sheet_copyTo = ss_new.getSheetByName("シート1"); //コピー先のシートを指定

※こちらのスクリプトを動作させるにはDrive APIを有効にする必要があります。
参考サイト：https://qiita.com/tanaike/items/8c82392ef2749892061c

ちなみに、Salesforceの権限の問題なのか、レポートをエクスポートしたシートを指定してコピー＆別シートに貼り付けができなかったので、
新たにコピー用のシートを作成し、IMPORTRANGE関数を使ってレポートのデータを取り込み、そのシートから新規のスプレッドシートにコピーするという形にしています。

###レポートデータをコピーして作成したスプレッドシートに貼り付け

データをコピーして作成したスプレッドシートに貼り付け

  var ss_copyFrom = SpreadsheetApp.openById("コピー元のスプレッドシートID");
  var sheet_copyFrom = ss_copyFrom.getSheetByName("コピー元のシート名");
   
  var copyValue = sheet_copyFrom.getRange("コピー範囲").getValues();
  sheet_copyTo.getRange("貼り付け範囲").setValues(copyValue);

コピーができたらレポートを整形していきます。
###数値の表示形式を変更
Salesforceのレポートのままですと、金額に3桁区切りカンマが付いておらず見にくいので、表示形式を変更し3桁区切りカンマを付けます。

数値の表示形式を変更

  var range = sht.getRange("変更したい範囲");
  range.setNumberFormat("#,##0"); //3桁区切りカンマを付ける

###指定の列を基準に指定範囲を並び替え
指定の列の金額が多い順に並び替えをしたいので降順にしています。

指定の列を基準に指定範囲を並び替え

  var range = sht.getRange("並び替えたい範囲");
  range.sort([{column: 1, ascending: false}]); //1列目を基準に降順で並び替え
  //「false」を「true」に変えると昇順になります

###行の背景色を変更
下記のスクリプトは特定の列（2列目）にAという文字が入っていたらその行の背景色を黄色、BまたはCという文字が入っていたら背景色をオレンジにするという設定です。

特定の列を基準に行の背景色を変更

  var lastRow = sht.getLastRow(); //最終行を取得
  for(let i = 2; i <= lastRow; i++) { //2列目から繰り返し実行
    var phase = sht.getRange(i, 2).getValue();
    if(phase == "A"){
      sht.getRange(i,1,1,9).setBackground("#ffff00"); //1列目から9列目までの背景色を変更
    }
    else if(phase == "B" || phase == "C"){
      sht.getRange(i,1,1,9).setBackground("#f4b084");
    }
  }

下記のようなSalesforceのレポートを整形した表ができあがりました。

#定期実行の設定
レポートを加工するGASが書けたら、これを毎日指定の時間に実行したいので、
トリガーを設定します。
GASのトリガーでは、日付ベースのタイマーは「午前9～10時」のような
大まかな設定しかできないので、
Scriptサービスの機能を使ってスクリプト内にトリガーを記載しました。
▼「myFunction」を午前10時に実行したい場合

指定の時間にスクリプトを実行

function setTrigger(){
  var setTime = new Date();
  setTime.setHours(10);
  setTime.setMinutes(00); 
  ScriptApp.newTrigger("myFunction").timeBased().at(setTime).create();
}

そして、GASのトリガーの設定から「setTrigger」を「時間主導型」「日付ベースのタイマー」で
上記の指定時間より早い時間に実行されるように設定します。

また、これとセットで作成されたトリガーを削除するスクリプトも入れないといけません。
このトリガーを削除するスクリプトが「myFunction」を実行した際に動作するように
「myFunction」内に「delTrigger();」の記載をしておきます。

実行済のトリガーを削除

function delTrigger() {
  var triggers = ScriptApp.getProjectTriggers();
  for(var i=0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction() == "myFunction") {
      ScriptApp.deleteTrigger(triggers[i]);
    }
  }
}

---

以上、Salesforceとスプレッドシートの連携とレポートの自動整形、いかがでしたでしょうか？

明日は、@mSpring さんによる「Google ColaboratoryのTPUランタイムでKerastunerを用いてパラメタ探索する方法」です。
引き続き、GMOアドマーケティング Advent Calendar 2020 をお楽しみください！