【営業向け】スプレッドシートに記録した行動管理シートをSFの取り込み用フォーマットに整形する

やりたいこと＆その理由

外部の営業のアウトソースを利用する際に、CRMとは別でスプレッドシートを用意して、委託先に共有したスプレッドシートに活動履歴を入力いただき、行動履歴を管理することがあるかと思います。ただ、その場合、以下のようなスプレッドシートで管理するのではないでしょうか。

縦に会社名が並んでおり、横に1回目、2回目っという感じで活動履歴が並んでいるイメージです。

なお、SFに活動履歴を取り込むときには、以下のような形でCSVをアプロードする必要があり、手で作業をしようとすると、地獄のような作業量になります。

これを何とか自動化できないかと思い、GASでスクリプトを作成。

実装方法

１：　対象のスプレッドシートを取得
２：　取得したいシートの名前を指定して取得
３：　2で取得したシートで必要な情報を取得
４：　転記したい情報を別のスプレッドシートの一番最後の行に挿入

完成系

ということで、完成形は以下。

gas.js

function SalesforceImportMacro(){
  const SpreadSheet    = SpreadsheetApp.openById("スプレッドシートのID");
  var caldListSheet    = SpreadSheet.getSheetByName("営業管理シート");
  var caldListForMacro = SpreadSheet.getSheetByName("SF取り込み用シート");
  var ownerId          = "SFの所有者ID";
  var lastRow          = caldListSheet.getLastRow();

//活動履歴が記録されているシートのデータを配列で取得
  var data = caldListSheet.getDataRange().getValues(); 

  for(i = 1; i <= lastRow; i++){//行数の数に応じて繰り返し処理
    var columnNum  = 0; //下のFor分の処理が終わったら、columnNumを0にし、初期化
    for(num = 1; num <= 7; num++){
      var sfId        = data[i][0];
      var eventTitle  = data[i][9 + columnNum];//活動の件名を取得
      if(eventTitle == ""){//仮にeventTitleが空だったら処理を止めて次の処理へ
        break;
      }
      var startTime   = data[i][12 + columnNum];//開始時刻を取得
      var endTime     = data[i][13 + columnNum];//終了時刻を取得
      var description = data[i][15 + columnNum];//活動の詳細を取得

     //appendRowメソッドを使って、最終行に値を挿入する      
     caldListForMacro.appendRow([sfId,startTime,endTime,eventTitle,description,ownerId]);
      columnNum += 8; //8列毎に1回目、2回目、と並んでいるので、+8してあげる
    }
  }

var data = caldListSheet.getDataRange().getValues(); の中身

console.log(data);で表示をしてみると、dataの中身は配列が入っています。後は、繰り返し処理で必要な情報を取得し、appendRowしているということです。

アウトプット

無事に出したい形でアウトプットがされているが、とにかく処理が遅い。

実はもともと、以下のようなコードを書いていたのだが、処理が遅すぎて終わるまでネットフリックスを見ていたぐらいｗ　getValueの量を減らして、かなり早くなったのだが、それでも遅い。

コード.gas

  for(i = 2; i <= lastRow; i++){
    var columnNum  = 0;
    for(num = 1; num <= 7; num++){
      var sfId        = caldListSheet.getRange(i,1).getValue();
      var eventTitle  = caldListSheet.getRange(i,10 + columnNum).getValue();
      if(eventTitle == ""){
        break;
      }
      var startTime   = caldListSheet.getRange(i,13 + columnNum).getValue();
      var endTime     = caldListSheet.getRange(i,14 + columnNum).getValue();
      var description = caldListSheet.getRange(i,16 + columnNum).getValue(); 
      caldListForMacro.appendRow([sfId,startTime,endTime,eventTitle,description,ownerId]);
      columnNum += 8;
    }
  }

appendRowは非常に便利なメソッドなのだが、流石にこれを1,000回以上コールしてしまうと、時間がかかる。もう少し、良いアルゴリズムがあるはずなので、探ってみる。