How to clear same multiple columns on different sheets in worksheet in google sheets-CodePudding

I created a Google sheet workbook with 4 sheets. Each sheet will need the following columns to be cleared each Friday (columns C3:S40 and column U3:U40). Here is what I came up with but it doesn't work all the way through. So I was wondering if anyone can help me to simplify the script. Thank you in advance!

function ClearCells()
var sheet = SpreadsheetApp.getActive().getSheetbyName ();
sheet.getRange(C3:S40).clearContent();
sheet.getRange(U3:U40).clearContent();}

CodePudding user response：

Try this:

function ClearRangesofSelectedSheets() {
  const shts = ["Sheet1","Sheet2","Sheet3"];
  const ss = SpreadsheetApp.getActive();
  shts.forEach(name => {
    const sheet = ss.getSheetByName(name);
    sheet.getRange("C3:S40").clearContent();
    sheet.getRange("U3:U40").clearContent();
  });
}

CodePudding user response：

I believe your goal is as follows.

You want to simplify your showing script.
As the additional function, you want to use your script for the specific 4 sheets in a Google Spreadsheet.

Modification points:

It seems that your function of function ClearCells() is not enclosed by {}.
getSheetbyName () of SpreadsheetApp.getActive().getSheetbyName () is required to be getSheetByName("Sheet name").
When getRange method of Class Sheet is used, C3:S40 and U3:U40 of sheet.getRange(C3:S40).clearContent() and sheet.getRange(U3:U40).clearContent() are required to be given as the string value as follows.
```
  sheet.getRange("C3:S40").clearContent();
  sheet.getRange("U3:U40").clearContent();
```
In your script, sheet.getRange("C3:S40").clearContent() and sheet.getRange("U3:U40").clearContent() can be modified by one call as follows.
```
  sheet.getRangeList(["C3:S40", "U3:U40"]).clearContent();
```
In order to use your script to the specific 4 sheets, it is required to give the sheets.

When these points are reflected in your script, how about the following modification?

Modified script:

function ClearCells() {
  var sheetNames = ["Sheet1", "Sheet2",,,]; // Please set the sheet names you want to use.

  var sheet = SpreadsheetApp.getActive();
  sheetNames.forEach(sheetName =>
    sheet.getSheetByName(sheetName).getRangeList(["C3:S40", "U3:U40"]).clearContent()
  );
}

Or, in order to simplify the script, you can also use the following script. When you use this script, please enable Sheets API at Advanced Google services.

function ClearCells() {
  var sheetNames = ["Sheet1", "Sheet2",,,]; // Please set the sheet names you want to use.

  var ss = SpreadsheetApp.getActive();
  Sheets.Spreadsheets.Values.batchClear({ ranges: sheetNames.flatMap(s => ["C3:S40", "U3:U40"].map(r => `'${s}'!${r}`)) }, ss.getId());
}

I think that when Sheets API is used, the process cost might be able to be reduced a little. Ref

Modification points:

Modified script:

References: