google-apps-script - 复制互连的（通过 IMPORTRANGE）电子表格并通过 Google Apps 脚本更改 IMPORTRANGE 公式

问题描述

如果您不关心背景并立即想查看编码问题，请跳过本段。我对编码很陌生，这是我在这里发布的第一个问题，但我希望我能说得通。我彻底搜索了网络，但我找不到我可能不那么特别的问题的答案。一些背景知识：我是一名教练，我有谷歌电子表格，里面有我给客户的培训计划。因为我喜欢编码和谷歌电子表格，所以我编程的统计数据和功能发展得很快，所以我不得不将电子表格及其多达 7 个培训日标签/表格拆分到不同的电子表格中，所以我节省了我的客户或者他们的智能手机一些工作记忆。到目前为止，一切都很好。

我编写了一个应用程序脚本，该脚本复制了主/设置表以及培训日电子表格（#1 到 #7），并自定义了保存它们的文件夹以及带有客户昵称/名字的副本名称。

今天我还成功地替换了我的主/设置电子表格的日历表中的导入范围（背景：所以我可以看到我的客户在哪一天训练），所以我刚刚复制的主电子表格的副本不会链接到培训日 #1、#2、... 模板，但培训日 #1、#2 ... 模板的副本。我不知道这是多么容易理解（英语也不是我的第一语言）所以我尝试改写它：当然，将所有模板电子表格相互连接的 IMPORTRANGE 公式在复制时不会更新，但仍然参考模板电子表格。

但是，我问自己为什么要对所有内容（实际的单元格范围和内容）进行硬编码，以及是否不可能始终将所有 importrange 函数（例如模板＃1）的 id 部分替换为工作表的 id（其中我已经存储在一个数组中）。你明白我的意思吗，你能帮我解决这个问题吗？这是我到目前为止已经编码的内容。

感谢任何阅读此问题并试图提供帮助的人！PS：如果有人想要更正或简化我当前的代码，我也很高兴，但我很高兴它到目前为止有效，如果有人能推动我朝着正确的方向前进，我会不知所措。PPS：我倾向于使用 GAS 版本的查找和替换所有 importrange 函数的原因是，我会在我的电子表格模板架构中保持一些灵活性，并且将来我可以不假思索地编辑模板关于 GAS 代码中的硬编码范围，我可能会破坏。

Tl; Dr：是否有一种简单的方法可以通过 GAS 使用查找和替换来在复制电子表格后更改 importrange 公式的 id 部分？

//function that creates a new menu in the spreadsheet just right of the "help" tab
function onOpen() {

SpreadsheetApp.getUi()
              .createMenu('Scripts')
              .addItem('Create Duplicates', 'createDuplicates')
              .addItem('Create Links in Master Spreadsheet', 'createLinksInMasterSheet')
              .addToUi();
  
}
const parentFolder = DriveApp.getFolderById("ID OF PARENT FOLDER");
const nameOfMasterSheet = "TrnngPln";
const currentVersion = "4.0";

//function that creates duplicates of master sheet, all training days in a new folder that is named after the clients nickname
function createDuplicates() {
  const clientNickname = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SETUP").getRange('AB2').getValue();
  const currentmonth = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("InfoForAppsScript").getRange('F2').getValue();
  const currentyear = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("InfoForAppsScript").getRange('G2').getValue();
  const folderNewClient = parentFolder.createFolder('TP.'+clientNickname+'.['+currentmonth+'/'+currentyear+']');
  
  const newMasterSpreadSheetName = ''+clientNickname+'.'+nameOfMasterSheet+'['+currentmonth+'/'+currentyear+'.]'+currentVersion+'';
  const setup = DriveApp.getFileById("ID OF SETUP TEMPLATE");
  var newMasterID = setup.makeCopy(newMasterSpreadSheetName,folderNewClient).getId();
  
  const nameOfCopyOfDay1 = '#1.'+clientNickname+'['+currentmonth+'/'+currentyear+']'+currentVersion+'';
  const day1 = DriveApp.getFileById("ID OF TEMPLATE DAY 1");
  var newDay1id = day1.makeCopy(nameOfCopyOfDay1,folderNewClient).getId();
  
  const nameOfCopyOfDay2 = '#2.'+clientNickname+'['+currentmonth+'/'+currentyear+']'+currentVersion+'';
  const day2 = DriveApp.getFileById("ID OF TEMPLATE DAY 2");
  var newDay2id = day2.makeCopy(nameOfCopyOfDay2,folderNewClient).getId();
  
  const nameOfCopyOfDay3 = '#3.'+clientNickname+'['+currentmonth+'/'+currentyear+']'+currentVersion+'';
  const day3 = DriveApp.getFileById("ID OF TEMPLATE DAY 3");
  var newDay3id = day3.makeCopy(nameOfCopyOfDay3,folderNewClient).getId();
  
  const nameOfCopyOfDay4 = '#4.'+clientNickname+'['+currentmonth+'/'+currentyear+']'+currentVersion+'';
  const day4 = DriveApp.getFileById("ID OF TEMPLATE DAY 4");
  var newDay4id = day4.makeCopy(nameOfCopyOfDay4,folderNewClient).getId();
  
  const nameOfCopyOfDay5 = '#5.'+clientNickname+'['+currentmonth+'/'+currentyear+']'+currentVersion+'';
  const day5 = DriveApp.getFileById("ID OF TEMPLATE DAY 5");
  var newDay5id = day5.makeCopy(nameOfCopyOfDay5,folderNewClient).getId();
  
  const nameOfCopyOfDay6 = '#6.'+clientNickname+'['+currentmonth+'/'+currentyear+']'+currentVersion+'';
  const day6 = DriveApp.getFileById("ID OF TEMPLATE DAY 6");
  var newDay6id = day6.makeCopy(nameOfCopyOfDay6,folderNewClient).getId();
  
  const nameOfCopyOfDay7 = '#7.'+clientNickname+'['+currentmonth+'/'+currentyear+']'+currentVersion+'';
  const day7 = DriveApp.getFileById("ID OF TEMPLATE DAY 7");
  var newDay7id = day7.makeCopy(nameOfCopyOfDay7,folderNewClient).getId();
}


//function that sets Links in Calendar tab of master trainingplan spreadsheet
function createLinksInMasterSheet() {
  var searchFor = ['title contains ".TrnngPln"','title contains "#1."','title contains "#2."','title contains "#3."','title contains "#4."','title contains "#5."','title contains "#6."','title contains "#7."'];
  var ss = SpreadsheetApp.getActive();                               //current spreadsheet
  var directParents = DriveApp.getFileById(ss.getId()).getParents(); // folderIterator "targets" parent folder
  var folder = directParents.next();                                 //accesses parent folder
  var names =[];                                                     //array that stores names of the spreadsheets found in folder in the order of the array variable searchFor
  var fileIds=[];                                                    //array that stores the IDs of the spreadsheets found in folder in the order of the array variable searchFor
  for (var i=0; i<searchFor.length; i++) {                           //forLoop that goes through the searchFor array
    var files = folder.searchFiles(searchFor[i]);                    //creates file iterator t
    while (files.hasNext()) {                                        //while loop goes through all the files that match searchFor variable
      var file = files.next();                                       //
      var fileId = file.getId();                                     // To get FileId of the file
      fileIds.push(fileId);                                          //
      var name = file.getName();                                     //To get name of the file
      names.push(name);                                              //
    }                                                                
  }
  for (var i=1; i<fileIds.length;i++) {
    const formula = 'IMPORTRANGE("'+fileIds[i]+'";"#'+i+'!JJ188:JX188")'; //tak
    var rownumber = 265+i;
    Logger.log(rownumber);
    ss.getSheetByName('Cal').getRange('C'+rownumber+'').setFormula(formula);
    Logger.log(formula);
  } 
}

PS：其中大部分不是我的代码，而是我在此板、youtube 或任何地方的某个地方找到的经过编辑的代码，并对其进行了编辑以符合我的目的。

标签： google-apps-scriptgoogle-sheetsimportrange

google-apps-script - 复制互连的（通过 IMPORTRANGE）电子表格并通过 Google Apps 脚本更改 IMPORTRANGE 公式

问题描述

解决方案

推荐阅读