CODEWITHSUNDEEP
functionloopsgoogle-apps-scriptgoogle-sheetsreset
deeresther
deeresther

Reputation: 37

Looping a reset function in Google Apps Script

I'm trying to loop the reset function so I can apply it across 30+ different sheets in the same spreadsheet.

I've found an answer in the past but I can't manage to modify it to work.

function reset() {
  var keys = ['my spreadsheet id']; // list of spreadsheet ids
  for (var i = 0; i < keys.length; i++) {
    var ss = SpreadsheetApp.openById(keys[i]);
    var sheet = ss.getSheetByName('sheet name'); // i tried writing multiple sheet names here already but it doesn't work, hence trying to make the loop
    sheet.getRange('C3').setValue('X');
  }
}

The loop that I am talking about has been mentioned here: One function for multiple sheets in Google Sheets

I don't need to get all the sheets. 2 of the sheets should be excluded. And I need help applying it to the function I have which is posted above.

Upvotes: 1

Views: 522

Answers (2)

CMB
CMB

Reputation: 5163

Solution:

You can use getSheets() to get all sheets in a spreadsheet, and filter() to exclude defined sheets:

function reset() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var exclude = ['Sheet1','Sheet2'];
  sheets.filter(s => exclude.indexOf(s.getName()) == -1).forEach(s => s.getRange('C3').setValue('Z'));
}

Reference:

getSheets()

Upvotes: 2

Marios
Marios

Reputation: 27380

Create an array of sheets to be excluded and make sure they are not included when setting the value:

function reset() {
  const key = 'my spreadsheet id';
  const sheetsToExclude = ["Sheet2","testSheet"]; // add the sheet names you want to exclude
  const sheets = SpreadsheetApp.openById(key).getSheets();
  sheets.forEach(sheet => {
       if(!sheetsToExclude.includes(sheet.getName())){    
           sheet.getRange('C3').setValue('X')
  }});
}

Upvotes: 1

Related Questions