Getting script to move through to the next sheet(TAB)

Question

function tabGrab () {

let ss = SpreadsheetApp.getActiveSpreadsheet();
let sheet = ss.getActiveSheet();
let sheetName = ss.getSheetName();
let allSheets = ss.getSheets();
let sheetCount = ss.getNumSheets();
let thisSheet = ss.getActiveSheet().getIndex() -1;
let skipSheets = ['ESCM Schedule data','Location Data','Costco U Data','FOG Data','FOG Trap Repair Data','Unauth Waste Data','Weekly Hazardous Waste Data Dump',
'SPCC Data Dump','Stormwater BMP Data Dump','Drill Down','Feeder'];

allSheets.forEach(function(sheet){
 Logger.log(sheet); 
})

ss.getActiveSheet().getSheetName()

Logger.log(sheetName);

let i = thisSheet;

let notDone = true;

if (i == (sheetCount -1)) {
  i = 0;
} else {
  i++;
}
let nextName = allSheets[i].getName();

if (skipSheets.indexOf(nextName) === -1){
  let nextSheet = allSheets[i];
ss.setActiveSheet(nextSheet);
  notDone = false;
}
Logger.log(nextName);

I am working on a workbook that rotates through a set of dynamic values (they change daily). the data is a set of locations. (each location is assigned a unique number and name). I kept running into the error, "Could not create sheet name/it already exists". So what I am ideally trying to do is when the dynamic values change it triggers the code to run. 1st the code will run through the workbook and assign the tab based on the location number(a numeric value) in "B2". Then run again this time assigning the locations name (text string) in "C2". Now I have data sheets that I am trying to exclude and I think that is working thus far. Now the code runs and renames the sheet but only does one sheet then activates the next tab and goes no further. I need to creat a loop but so far I have failed.

function pullName() {
  var spreadsheet = SpreadsheetApp.getActive();
  let newName = spreadsheet.getRange('B2').getValues();
  let finalName = spreadsheet.getRange('C2').getValues();
  spreadsheet.getActiveSheet().setName(finalName);
}

function doForAllTabs(){
  var spreadsheet = SpreadsheetApp.getActive();
  var allSheets = spreadsheet.getSheets();
 

  allSheets.forEach(function(sheet){
    if (sheet.getSheetName() !== ['ESCM Schedule data','Location Data','Costco U Data','FOG Data','FOG Trap Repair Data','Unauth Waste Data','Weekly Hazardous Waste Data Dump',
  'SPCC Data Dump','Stormwater BMP Data Dump','Drill Down','Feeder']){
      sheet.activate();
      pullName();
  }
  })
}

user30hu · Answer

I went through your code and this is what I end up with:

function doForAllTabs() {
  const skip = ['ESCM Schedule data', 'Location Data', 'Costco U Data', 'FOG Data', 'FOG Trap Repair Data', 'Unauth Waste Data', 'Weekly Hazardous Waste Data Dump', 'SPCC Data Dump', 'Stormwater BMP Data Dump', 'Drill Down', 'Feeder']
  var ss = SpreadsheetApp.getActive();
  ss.getSheets().filter(sh => !~skip.indexOf(sh.getName())).forEach(s => {
    s.setName(s.getRange("c2").getValue());
  })
}

You seem to be changing the name of all of the sheets to a name that is in the sheet. So I don't know if this will work or not because I don't know what's in the sheets

BTW this whole construct is not going to work:

if (sheet.getSheetName() !== ['ESCM Schedule data','Location Data','Costco U Data','FOG Data','FOG Trap Repair Data','Unauth Waste Data','Weekly Hazardous Waste Data Dump',
  'SPCC Data Dump','Stormwater BMP Data Dump','Drill Down','Feeder']

I replaced it with my filter method. That may be why it wasn't working. If you provide a link to a spreadsheet, I'll go to another account and try it. You should be aware though that you will be exposing your email address to everyone. That's why I use a throwaway account.

Getting script to move through to the next sheet(TAB)

1 Answers