[SOLVED] Running Macro on Multiple Google Sheets Workbooks

Running Macro on Multiple Google Sheets Workbooks

I have a large directory of workbooks that I need to change the parameters of a named range. I have recorded a Macro that can do this and I'm sure there's a quick way to apply?

I have a Tab in a directory workbook called 'Planner Fixes' & I have a list of Sheet ID's to send the amendment too.

The Macro is as below:

function ElementsEdit() {
 var spreadsheet = SpreadsheetApp.getActive();
 spreadsheet.getRange('A1').activate();
 spreadsheet.setActiveSheet(spreadsheet.getSheetByName('API BootstrapStatic'), true);
 spreadsheet.getRange('A2:BK514').activate();
 spreadsheet.setNamedRange('Elements', spreadsheet.getRange('A2:BK1000'));
 spreadsheet.getActiveSheet().hideSheet();

};

I know that the line var spreadsheet = SpreadsheetApp.getActive(); needs to change to look for the spreadsheet ID and 'open' that?

I'm very new to scripts and have generally only used ones I have found online but I can't seem to find anything to do this at all.

Solution

If you have multiple workbooks, and if you know the id of each of them, try

var spreadsheet = SpreadsheetApp.openById(YOUR_SPREADSHEET_ID);

for each

You can loop through a list of id

const ids = [
  '1T7ixa-JGLgh8oxxxxxxxxxo4UzbivflUlwL9zLGVkgg',
  '16uEeVNEBzBbGVTgKa98yyyyyyyyyyyy6mU-yKIZ0fKM',
  '1d3ZHizzzzzzzzzzzzzzzzzzzLkmM4bFHyDn8RFtAlkk'
]
function myFunction() {
  ids.forEach((id, i) => {
    var spreadsheet = SpreadsheetApp.openById(id)
    // your script here
  })
}