Is there a function to exclude certain sheets from my workbook from the script

user_12269148 0
Viewed 281

I'm trying to write a code to clear certain data cells ("F2:M95") on a time-based trigger once a week. Everything works as I want it to except I noticed it's clearing the data from those particular cells on every sheet in my workbook.

This is the function I currently have:

function LunchEmailClear() {
  var app = SpreadsheetApp;
  var activeSheet = app.getActive()
  activeSheet.getRange("F2:M95").clear();
}

What can I add so that it will only apply to certain sheets? (i.e. "Morning Emails, Lunch Emails")

PS. Also sorry if I sound a bit dumbfounded, I just started messing around with scripts last week and hardly have any clue as to what I'm doing. I appreciate any help I can get! :)

google-apps-script google-sheets javascript
2 Answers

Explanation:

We can make use of the JavaScript Array.indexOf() method to achieve this.

In the script below I've an array of sheets we want to include and defined it as var include. We can then compare this array with the sheet names using a for loop running an if statement. This checks the sheet names against our array of inclusions using Array.indexOf().

Example:

function emailClear() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

  //list sheets to include here
  var include = ['Morning Emails', 'Lunch Emails'];

  for (var i = 0; i < sheets.length; i++) {
    if (include.indexOf(sheets[i].getName()) > -1) {
      sheets[i].getRange("F2:M95").clear();
    }
  }
}

References:

user_114273750

This should works properly:

function LunchEmailClear() {
  //Add the list of sheets to clean
  var sheets = ['Morning Emails', 'Lunch Emails'] 
  var app = SpreadsheetApp.getActive();

  for (var i=0; i < sheets.length; i++){
    app.getSheetByName(sheets[i]).getRange("F2:M95").clear();
  }
}
user_86021970
Related