时间戳

首页 > 解决方案 > 将谷歌表格宏应用于多个谷歌表格文件

google-apps-script - 将谷歌表格宏应用于多个谷歌表格文件

问题描述

列“A”有一百个不同的谷歌表或 ID 的列表我有我创建的宏的应用程序脚本，需要在所有 100 个谷歌表的“选项卡 2”上运行

是否可以创建一个脚本来遍历每个文件并应用宏？如果没有，是否有一种快速简便的方法可以让我访问每个 Google 工作表中的宏？

这是所要求的宏代码 - 更改了一些信息。

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(['abc@abc.com', 'abc@abc.com']);
  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(['abc@abc.com', 'abc@abc.com']);
  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(['abc@abc.com', 'abc@abc.com']);
  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(['abc@abc.com', 'abc@abc.com']);
  allProtections = spreadsheet.getActiveSheet().getProtections(SpreadsheetApp.ProtectionType.RANGE);
  matchingProtections = allProtections.filter(function(existingProtection) {
  return existingProtection.getRange().getA1Notation() == 'C1:AV1';
  });
  protection = matchingProtections[0];
}

标签： google-apps-scriptgoogle-sheetsgoogle-sheets-formulagoogle-sheets-apigoogle-sheets-macros

解决方案

这是一个简单的开始：

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")');

推荐阅读