Salesforceからのデータ取得とGoogle Sheetsへの高速書き込みテクニック

Salesforceデータの取得とGoogle Sheetsへの書き込みの効率を改善する方法

Google Apps Scriptを使用して、Salesforceからデータを取得し、そのデータをGoogle Sheetsに書き込む際、パフォーマンスの向上は非常に重要です。
以下の改善点により、処理の高速化とリソースの効率的な利用が可能になります。

---

1. appendRowの代わりにsetValuesを使用する

appendRowは行ごとにデータを追加するため、複数行を追加する場合に逐次実行され、処理コストが高くなりがちです。
その代わりに、**setValues**を使用することで、複数行のデータを一括して書き込むことができ、処理効率が大幅に向上します。

改善前の例:

sheet.appendRow(rowData);

改善後の例:

// 複数行のデータを一括で書き込む
sheet.getRange(sheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);

---

2. 複数行の削除はSheets APIを利用する

大量の行を削除する際、ループで個々にdeleteRowを呼び出すと、処理コストが高くなります。
そのため、Google SheetsのAdvanced Servicesで提供されるSheets APIのbatchUpdateメソッドを利用して一括で削除する方法が効率的です。
なお、削除する行のインデックスは、逆順に処理する必要があります（上から削除すると行番号がずれるため）。

Sheets APIを利用した例:

const sheetId = workers.getSheetId();
const requests = turfIndices.reverse().map(e => ({
    deleteDimension: {
        range: {
            sheetId: sheetId,
            startIndex: e - 1,
            endIndex: e,
            dimension: "ROWS"
        }
    }
}));
Sheets.Spreadsheets.batchUpdate({ requests: requests }, ss.getId());
SpreadsheetApp.flush();

もしSheets APIが使用できない場合は:

turfIndices.reverse().forEach(index => workers.deleteRow(index));

---

3. 関数の統合と最適化

以下は、複数行削除と新しいデータの書き込みを一つの関数に統合した例です。
この方法では、まず指定された条件で行を削除し、次に新規データを一括追加します。

function setUserTurf(employerName, payload) {
  // シート全体のデータを取得
  const allData = workers.getDataRange().getValues();

  // employerNameにマッチする行のインデックスを取得（1行目はヘッダーの場合は考慮）
  const turfIndices = allData.map((row, index) => {
    if (row[3] === employerName) {
      return index + 1;
    }
  }).filter(n => n);

  // マッチする行が存在する場合、削除処理を実施
  if (turfIndices.length) {
    const sheetId = workers.getSheetId();
    const requests = turfIndices.reverse().map(e => ({
      deleteDimension: {
        range: {
          sheetId: sheetId,
          startIndex: e - 1,
          endIndex: e,
          dimension: "ROWS"
        }
      }
    }));
    Sheets.Spreadsheets.batchUpdate({ requests: requests }, ss.getId());
    SpreadsheetApp.flush();
  }

  // payloadから必要なデータを抽出し新規行として設定
  const values = payload.reduce((ar, obj) => {
    // contactIdsに存在していなければ行として追加（contactIdsはグローバル変数または別途定義されたリスト）
    if (!contactIds.includes(obj.Id)) {
      const row = Object.values(obj).slice(1);
      ar.push(row);
    }
    return ar;
  }, []);

  // 追加する行が存在する場合、setValuesで一括追加
  if (values.length > 0) {
    workers.getRange(workers.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
  }
}

---

まとめ

• setValues の活用:
  複数行のデータ書き込みには、appendRow の代わりに setValues を利用し、処理速度を向上させます。

• Sheets APIによる一括削除:
  行の削除は、一行ずつ削除するのではなく、batchUpdate を利用することで効率化が実現できます。
  ※ 削除時はインデックスを逆順に処理する必要があります。

• 関数の統合:
  複数の操作を一つの関数にまとめることで、コードの可読性と保守性が向上します。

これらの方法を採用することで、Google Apps ScriptにおけるSalesforceからのデータ取得とGoogle Sheetsへの書き込みが大幅に効率化され、全体のパフォーマンスが向上します。