I have a macro running through a forEach loop that works but always ends on the last sheet in my workbook. How do I get it to finish on a specified sheet?
I tried a few variations of getSheetbyname but I don't really know where to put it.
This is the code:
function ResetPage() {
var sheet = SpreadsheetApp.getActive();
var racenumber = sheet.getRange('B2');
var venue = sheet.getRange('E2');
venue.clearContent();
racenumber.activate();
sheet.getCurrentCell().setValue('1');
}
function doForAllTabs() {
var spreadsheet = SpreadsheetApp.getActive();
var allsheets = spreadsheet.getSheets();
allsheets.forEach(function(workbook) {
if(workbook.getSheetName() !== "Venues") {
workbook.activate();
ResetPage();
}
});
}
CodePudding user response:
You don't need to set each sheet active to change the values. The macro recorder does it but it makes the process slower.
This should do the updates without changing your active cell:
function ResetPage(sheet) {
var racenumber = sheet.getRange('B2');
var venue = sheet.getRange('E2');
racenumber.setValue(1);
venue.clearContent();
}
function doForAllTabs() {
var spreadsheet = SpreadsheetApp.getActive();
var allsheets = spreadsheet.getSheets();
allsheets.forEach(function(workbook) {
if(workbook.getSheetName() !== "Venues") {
ResetPage(workbook);
}
});
}
CodePudding user response:
function doForAllTabs() {
SpreadsheetApp.getActive().getSheets().forEach(sh => {
if (sh.getName() != "Venues") {
sh.getRange('B2').setValue(1);
sh.getRange('E2').setValue('');
}
});
}