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

GasTips / spreadsheet
Google Apps Script / spreadsheet

Gists

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

 Share!