Skip to content

Instantly share code, notes, and snippets.

@tanaikech
Created May 30, 2022
Embed
What would you like to do?
Replacing Values in Cells on Google Spreadsheet using Google Apps Script

Replacing Values in Cells on Google Spreadsheet using Google Apps Script

This is a sample script for replacing values in the cells on Google Spreadsheet using Google Apps Script.

Sample situation

The sample situation is as follows.

Sample script

This sample script used Sheets API. So, please enable Sheets API at Advanced Google services.

function myFunction() {
  const obj = {
    sample1: "ab",
    sample2: "cd",
    sample3: "ef",
    sample4: "gh",
    sample5: "ij",
    sample6: "kl",
    sample7: "mn",
    sample8: "op",
    sample9: "qr",
  };
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetId = ss.getSheetByName("Sheet1").getSheetId(); // Please set the sheet name you want to use this script.

  const requests = Object.entries(obj).map(([k, v]) => ({
    findReplace: {
      find: `(${k})`,
      matchCase: true,
      sheetId,
      replacement: `[${v}]`,
    },
  }));
  requests.push({
    findReplace: { find: ",", matchCase: true, sheetId, replacement: "" },
  });
  Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
}
  • In this script, one sheet is used. Also, you can use this script to all sheets in a Google Spreadsheet.

Reference

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