Applying a google sheets macro to multiple google sheet files

Question

Column "A" has a the list of a hundred different google sheets or IDs I have the apps script for a macro I created that needs to be ran on the "tab 2" of all 100 google sheets

Is it possible to create a script that goes through each and every one of those files and applies the macro? If not, is there a quick and easy way for me to access the macro in each google sheet?

Here is the Macro code as requested - changed some info.

function Combined () {

  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('AW:AW').activate();
  spreadsheet.getActiveSheet().showColumns(47, 1);
  spreadsheet.getRange('AV2').activate();
  spreadsheet.getCurrentCell().setFormula('=ifs(H2="Brossard", "111",H2="Calgary", "222",H2="Edmonton", "333",H2="Gatineau", "444",H2="Halifax", "555",H2="London", "666",H2="Montreal", "777",H2="Oakville", "888",H2="Ottawa", "999",H2="Quebec", "111",H2="Regina", "222",H2="Saint John", "333",H2="Saskatoon", "444",H2="St. John\'s", "555",H2="Surrey", "666",H2="Toronto Downtown", "777",H2="Truro", "888",H2="Vancouver", "999",H2="Vaughan", "111",H2="Victoria", "222",H2="Waterloo", "333",H2="Windsor", "444",H2="Winnipeg", "555",H2="Waterloo", "666",H2="Windsor", "777",H2="Winnipeg", "888")');
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('AV2:AV1000'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  spreadsheet.getRange('AV:AV').activate();
  spreadsheet.getActiveSheet().hideColumns(spreadsheet.getActiveRange().getColumn(), spreadsheet.getActiveRange().getNumColumns());
  spreadsheet.getRange('AW:AW').activate();
  
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('B:B').activate();
  var allProtections = spreadsheet.getActiveSheet().getProtections(SpreadsheetApp.ProtectionType.RANGE);
  var matchingProtections = allProtections.filter(function(existingProtection) {
  return existingProtection.getRange().getA1Notation() == 'B:B';
  });
  var protection = matchingProtections[0];
  protection.addEditors(['[email protected]', '[email protected]']);
  allProtections = spreadsheet.getActiveSheet().getProtections(SpreadsheetApp.ProtectionType.RANGE);
  matchingProtections = allProtections.filter(function(existingProtection) {
  return existingProtection.getRange().getA1Notation() == 'B:B';
  });
  protection = matchingProtections[0];
  allProtections = spreadsheet.getActiveSheet().getProtections(SpreadsheetApp.ProtectionType.RANGE);
  matchingProtections = allProtections.filter(function(existingProtection) {
  return existingProtection.getRange().getA1Notation() == 'A:A';
  });
  protection = matchingProtections[0];
  protection.addEditors(['[email protected]', '[email protected]']);
  allProtections = spreadsheet.getActiveSheet().getProtections(SpreadsheetApp.ProtectionType.RANGE);
  matchingProtections = allProtections.filter(function(existingProtection) {
  return existingProtection.getRange().getA1Notation() == 'A:A';
  });
  protection = matchingProtections[0];
  spreadsheet.getRange('AW:AW').activate();
  allProtections = spreadsheet.getActiveSheet().getProtections(SpreadsheetApp.ProtectionType.RANGE);
  matchingProtections = allProtections.filter(function(existingProtection) {
  return existingProtection.getRange().getA1Notation() == 'AV:AV';
  });
  protection = matchingProtections[0];
  protection.addEditors(['[email protected]', '[email protected]']);
  allProtections = spreadsheet.getActiveSheet().getProtections(SpreadsheetApp.ProtectionType.RANGE);
  matchingProtections = allProtections.filter(function(existingProtection) {
  return existingProtection.getRange().getA1Notation() == 'AV:AV';
  });
  protection = matchingProtections[0];
  spreadsheet.getRange('C1:AW1').activate();
  allProtections = spreadsheet.getActiveSheet().getProtections(SpreadsheetApp.ProtectionType.RANGE);
  matchingProtections = allProtections.filter(function(existingProtection) {
  return existingProtection.getRange().getA1Notation() == 'C1:AV1';
  });
  protection = matchingProtections[0];
  protection.addEditors(['[email protected]', '[email protected]']);
  allProtections = spreadsheet.getActiveSheet().getProtections(SpreadsheetApp.ProtectionType.RANGE);
  matchingProtections = allProtections.filter(function(existingProtection) {
  return existingProtection.getRange().getA1Notation() == 'C1:AV1';
  });
  protection = matchingProtections[0];
}

Oh the assumption I make here is that you are a programmer or at least you desirie to be a programmer. If that's not true, then I apologize and I'll just move on to another question. — Cooper
The problem is that macros while being easy to create really suck as scripts. And turning them into working scripts is kind of a nuisance but It leads to a much more useable script. I suspect that if you try to automate this script to run with 100 spreadsheets you'll ultimately get to the point where you will have to rewrite it any way because you will probably hit the time out. — Cooper

Cooper Cooper · Accepted Answer · 2020-06-26T17:17:54

Here's a simple start:

function Combined () {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  sh.showColumns(47,1);
  sh.getRange(2,48).setFormula('=ifs(H2="Brossard", "111",H2="Calgary", "222",H2="Edmonton", "333",H2="Gatineau", "444",H2="Halifax", "555",H2="London", "666",H2="Montreal", "777",H2="Oakville", "888",H2="Ottawa", "999",H2="Quebec", "111",H2="Regina", "222",H2="Saint John", "333",H2="Saskatoon", "444",H2="St. John\'s", "555",H2="Surrey", "666",H2="Toronto Downtown", "777",H2="Truro", "888",H2="Vancouver", "999",H2="Vaughan", "111",H2="Victoria", "222",H2="Waterloo", "333",H2="Windsor", "444",H2="Winnipeg", "555",H2="Waterloo", "666",H2="Windsor", "777",H2="Winnipeg", "888")');

Applying a google sheets macro to multiple google sheet files

1 Answers