Skip to content

Instantly share code, notes, and snippets.

@tanaikech
Last active June 20, 2024 12:42
Show Gist options
  • Save tanaikech/3030603299d1e302821611c834420258 to your computer and use it in GitHub Desktop.
Save tanaikech/3030603299d1e302821611c834420258 to your computer and use it in GitHub Desktop.
Replacing Multiple Values in Google Spreadsheet with Low Process Cost using Google Apps Script

Replacing Multiple Values in Google Spreadsheet with Low Process Cost using Google Apps Script

This is a sample script for replacing the multiple values with various values in Google Spreadsheet with the low process cost using Google Apps Script. In this script, the batchUpdate method of Sheets API is used. So the request can be done by one API call. When Spreadsheet services is used for this situation, the values are retrieved from the sheet and replaced the retrieved values, and then, the updated values are put to the sheet. Or the TextFinder is used in a loop. In this case, the process cost is higher than that using Sheets API. By using the bathUpdate method of Sheets API, the process cost is reduced.

Sample script

Before you use this script, please enable Sheets API at Advanced Google services. Ref

const replaceConditions = {
  a1: "updated_a1",
  b2: "updated_b2",
  c3: "updated_c3",
  d4: "updated_d4",
  e5: "updated_e5",
  d6: "updated_d6",
  c7: "updated_c7",
  b8: "updated_b8",
  a9: "updated_a9",
};

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheetId = ss.getSheetByName("Sheet1").getSheetId();
const requests = Object.entries(replaceConditions).map(([k, v]) => ({
  findReplace: {
    find: k.toString(),
    replacement: v.toString(),
    matchEntireCell: true,
    sheetId: sheetId,
  },
}));
Sheets.Spreadsheets.batchUpdate({ requests: requests }, ss.getId());
  • In this script, when sheetId: sheetId is replaced with allSheets: true, replaceConditions are used for all sheets in the Google Spreadsheet.

  • This method can be also used for Google Docs API and Google Slides API.

References

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment