Clearing Discrete Cell Values on Multiple Sheets using Google Apps Script

submit.md

Clearing Discrete Cell Values on Multiple Sheets using Google Apps Script

This is a sample script for clearing the discrete cell values on multiple sheets using Google Apps Script.

There might be a case where you want to clear the values of the discrete cells in the multiple sheets using Google Apps Script. In this post, I would like to introduce the efficient script for achieving this.

Sample script 1

Please copy and paste the following script to the script editor of the Google Spreadsheet you want to use.

In this sample, only Google Spreadsheet service (SpreadsheetApp) is used.

function myFunction() {
  const excludeSheetNames = ["Sheet1", , ,]; // Please set the sheet names you don't want to clear cells.
  const a1Notations = ["B2:C4", "E2:F4", , ,]; // Please set the a1Notation you want to clear cells.

  SpreadsheetApp.getActiveSpreadsheet()
    .getSheets()
    .forEach((sheet) => {
      if (!excludeSheetNames.includes(sheet.getSheetName())) {
        sheet.getRangeList(a1Notations).clearContent();
      }
    });
}

• When this script is run, the cell values of "B2:C4", "E2:F4" of all sheets except for "Sheet1" in the active Spreadsheet are cleared.
• If you want to use only the specific sheets, please modify if (!excludeSheetNames.includes(sheet.getSheetName())) { to if (excludeSheetNames.includes(sheet.getSheetName())) {. By this, only the cell values of excludeSheetNames are cleared.

Sample script 2

Please copy and paste the following script to the script editor of the Google Spreadsheet you want to use.

In this sample, Sheets API is used. So, please enable Sheets API at Advanced Google services. When Sheets API is used, the process cost can be reduced more than that of the above script. I think that if the number of sheets and the number of ranges are large, this method will be useful.

function myFunction() {
  const excludeSheetNames = ["Sheet1", , ,]; // Please set the sheet names you don't want to clear cells.
  const a1Notations = ["B2:C4", "E2:F4", , ,]; // Please set the a1Notation you want to clear cells.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ranges = ss.getSheets().reduce((ar, sheet) => {
    const sheetName = sheet.getSheetName();
    if (!excludeSheetNames.includes(sheetName)) {
      ar = [...ar, ...a1Notations.map((r) => `'${sheetName}'!${r}`)];
    }
    return ar;
  }, []);
  Sheets.Spreadsheets.Values.batchClear({ ranges }, ss.getId());
}

• When this script is run, the cell values of "B2:C4", "E2:F4" of all sheets except for "Sheet1" in the active Spreadsheet are cleared.
• If you want to use only the specific sheets, please modify if (!excludeSheetNames.includes(sheet.getSheetName())) { to if (excludeSheetNames.includes(sheet.getSheetName())) {. By this, only the cell values of excludeSheetNames are cleared.