This is a sample script for replacing values in the cells on Google Spreadsheet using Google Apps Script.
The sample situation is as follows.
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.