is it possible to clear same ranges of multiple sheets of the same spreadsheet in an easier way?-CodePudding

I'm trying to find a easier way to write the following

function Clearing() {
let monday = SpreadsheetApp.openById("XX").getSheetByName("Monday");
monday.getRange('B2:B').clearContent();
monday.getRange('E2:E').clearContent();
monday.getRange('H2:H').clearContent();
let tuesday = SpreadsheetApp.openById("XX").getSheetByName("Tuesday");
tuesday.getRange('B2:B').clearContent();
tuesday.getRange('E2:E').clearContent();
tuesday.getRange('H2:H').clearContent();

}

I also tried with getRangeList('B2:B','E2:E') without success

CodePudding user response：

Using Version 4 of Sheet API

function clearranges() {
  const ss = SpreadsheetApp.getActive();
  let clrrg = Sheets.newBatchClearValuesRequest();
  clrrg.ranges = ['Sheet0!B2:B','Sheet0!E2:E','Sheet0!H2:H'];//using stantdar A1 Notation
  Sheets.Spreadsheets.Values.batchClear(clrrg,ss.getId());
}

Sheet API Version 4 must be enabled

Values.batchClear

I found it worth reading throught Guides at first and then take a look at samples

CodePudding user response：

If you want to use .getRangeList(), you'll need to pass an array of A1 Notations, (i.e. with brackets) rather than a list of arguments.

Something like this:

function clearing() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetNames = ["Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"];
  sheetNames.forEach(function(sheetName){
    ss.getSheetByName(sheetName).getRangeList(['B2:B','E2:E','H2:H']).clearContent()
  })
}

If you're trying to clear the same set of ranges on all sheets in the spreadsheet, you can use ss.getSheets():

function clearing() {
  SpreadsheetApp.getActiveSpreadsheet().getSheets()
    .forEach(function(sheet){
      sheet.getRangeList(['B2:B','E2:E','H2:H']).clearContent()
    })
}

CodePudding user response：

I guess I should have noticed in the title its from the same Spreadsheet. I changed the name from "Clearing" to "clearing" as its typical convention except for opjects.

function clearing() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var days = ["monday","tuesday","wednesday","thursday","friday","saturday","sunday"];
  var i = 0;
  var sh = null;
  for( i=0; i<days.length; i   ) {
    sh = ss.getSheetByName(days[i]);
    sh.getRange('B2:B').clearContent();
    sh.getRange('E2:E').clearContent();
    sh.getRange('H2:H').clearContent();
  }
}