跨电子表格(而非库)共享的 Google 电子表格脚本 [英] Google Spreadsheet Scripts shared across spreadsheets (not libraries)

问题描述

我已经对这个问题进行了大量搜索，我认为问题是所有答案都会导致需要您创建一个库的解决方案.然后，我认为将该库添加到电子表格的唯一方法是为该电子表格创建一个新脚本并将其包含在内.

I have done a ton of searching for this problem and I think the issue is all the answers result in a solution that requires you to create a library. Then, the only way I see to add that library to a spreadsheet is to create a new script for that spreadsheet and include it.

我想要的:一堆电子表格，都包含一个主脚本.每次更新脚本时，都会更新为使用最新的脚本.

What I want: A bunch of spreadsheets that all include one master script. Each time the script is updated, they all update to use the latest script.

我有什么:15 个电子表格，全部都有原始脚本的副本.原始脚本已更改，现在看来我必须编辑每个复制的电子表格中存在的名为 Copy of myScriptName 的每个脚本.

What I have: 15 spreadsheets that all have copies of the original script. The original script has changed and now it would appear I have to go edit each script called Copy of myScriptName that exists within each copied spreadsheet.

我做了什么:我创建了第一个电子表格，并从我在脚本编辑器中创建的项目中编写了脚本.工作完美.然后，我为公司的每个部门制作了该电子表格的 14 份副本以供使用.

What I did: I created the first spreadsheet and wrote the script from within a project I created in its script editor. Worked perfect. I then made 14 copies of that spreadsheet for each division of a company to use.

我如何才能在任何单个电子表格本身之外共享脚本并对其进行管理?考虑到所有寻找相同答案的人，我必须在这里遗漏一些东西.我只是不明白把它变成一个库如何解决我的用例.

How can I just share the script and manage it outside of any of the individual spreadsheets themselves? I have to be missing something here considering all the people looking for this same answer. I just don't see how making it a library solves my use case.

谢谢！

我不知道这有什么帮助，但根据评论的要求，这里是脚本:

I don't see what this will help, but per the comment's request here is the script:

function createRollupTable() {

  //Return if:
  //   There is only the account code parameters passed in with no quarterly info
  //   If the length of the account code parameters passed is empty
  if(arguments.length <= 1 || !arguments[0] || arguments[0].length <= 0) {
    return "";
  }

  var rollupTable = new Array();
  var fullListAccountCodes = arguments[0];

  //The first column of output is the full list of account codes for all the quarters
  rollupTable[0] = fullListAccountCodes;

  //Array to keep the YTD total for each account code
  var yearlyAccountCostOutput = new Array(fullListAccountCodes.length);

  //Iterate over all the quarters that were passed in
  for(var i=1;i<arguments.length;i++) {

    //This array should be set to the total length of the available account codes
    var quarterlyRollupCostOutput = new Array(fullListAccountCodes.length);
    var quarterlyBreakdown = arguments[i];
    var quarterIndexCounter = 0;
    var quarterTotalCost = 0;

    //Iterate over all the account codes
    for(var j=0;j<fullListAccountCodes.length && quarterIndexCounter<quarterlyBreakdown.length;j++) {

      //Find the one that matches the current account code for this quarter
      if(fullListAccountCodes[j] == quarterlyBreakdown[quarterIndexCounter][0]) {

        //Set the index of the output based on the full list so they align
        quarterlyRollupCostOutput[j] = quarterlyBreakdown[quarterIndexCounter][1];

        //Add this cost to the running total for the quarter
        quarterTotalCost += quarterlyBreakdown[quarterIndexCounter][1];

        //Add the total amount for the yearly rollup for that account code
        if(yearlyAccountCostOutput[j]) {
          yearlyAccountCostOutput[j] += quarterlyBreakdown[quarterIndexCounter][1];
        } else {
          yearlyAccountCostOutput[j] = quarterlyBreakdown[quarterIndexCounter][1];
        }

        //Increment the counter so we search for the next account code in the quarter
        quarterIndexCounter++;

      }
    }

    rollupTable[i] = quarterlyRollupCostOutput;

    //Add a blank row in the results for spacing
    rollupTable[i].push("");

    //Add the quarterly total cost
    rollupTable[i].push(quarterTotalCost);

  }

  //Add a blank row for spacing
  rollupTable[0].push("");

  //Google spreadsheet forces you to pad with non breaking spaces, no right align option available
  var spaces = "";
  var numSpaces = 66;
  for(var i=0;i<numSpaces;i++){spaces+=String.fromCharCode(160);};

  //Add a row for the Totals
  rollupTable[0].push(spaces + "Totals:");

  //Add the YTD column
  rollupTable.push(yearlyAccountCostOutput);

  return rollupTable;
}

推荐答案

也许您所要求的只是一种将主脚本中的内容准确复制到电子表格副本中的所有脚本的方法它替换了他们的代码并跳过了引用库的需要，但我会提供我对库设置如何工作的印象......

Maybe what what you're asking for is just a way to copy exactly what's in a master script over to all of your scripts that are inside the spreadsheet copies so that it replaces their code and skips over the need to reference a library, but I'll offer my impression on how the library setup works...

我认为将该库添加到电子表格的唯一方法是创建一个该电子表格的新脚本

the only way I see to add that library to a spreadsheet is to create a new script for that spreadsheet

当您复制已经包含库引用脚本的电子表格时，它会保留在新副本中.因此，在创建了一个要复制的电子表格模板后，您不必再创建任何新脚本.

When you make a copy of a spreadsheet that already has a script with a library reference, it'll stay in with the new copy. So, you shouldn't have to create any new scripts after you've created one spreadsheet template to copy.

那个电子表格模板应该有一个对主脚本的库引用.母版不需要在工作表内，您不应该复制原始/母版脚本.

That one spreadsheet template should have a library reference to the master script. The master doesn't need to be inside a sheet and you shouldn't be copying the original / master script.

所以，我们有:1个主脚本，1 个电子表格模板，其中有一个引用主文件的脚本，然后是 模板副本，如您所愿.

So, we have: 1 master script, 1 spreadsheet template that has a script that references the master, then as many copies of the template as you want.

现在，当您设置库参考时，您可以选择将其连接到开发模式下的电子表格模板.这将允许对主脚本中现有功能的任何更改立即影响模板副本(除非首先需要授权).如果您有这种方式，您可能需要先在主脚本的副本中测试您的更改.另一种选择是关闭开发模式，让模板副本的用户手动更新每个脚本中的库版本(除非有我不知道的自动版本更新系统).

Now when you setup the library reference, you have the option to connect it in the spreadsheet template in dev mode. This will allow any changes to existing functions in the master script to instantly affect the template copies (unless authorization is first needed). If you have it this way, you will might want to test out your changes in a copy of the master script first. The other option is to keep dev mode turned off and have users of the template copies manually update their library version inside each of their scripts (unless there's an automatic version updating system I don't know about).

这个设置仍然没有解决向主脚本添加一个全新函数的问题，但每个模板副本都需要引用该函数.也许有人可以对此发表评论或提供单独的答案.

This setup still doesn't solve the issue of adding a completely new function to the master script that each template copy needs to reference though. Maybe some one can comment or provide a separate answer to that.

[更新:3/15/2015]因此，将附加组件发布到 Chrome 网上应用店可以让您安装附加组件一次，并将其显示在您的所有工作表/文档/表单中，我相信这是 OP 最初需要的.推出新版本会更新所有使用它的 Google 文档.

[Update: 3/15/2015] So publishing an Add-on to the Chrome Web store lets you install an Add-on once and have it appear in all your Sheets / Docs / Forms which I believe was what was originally needed for OP. Pushing new versions out updates all Google docs that use it.

https://developers.google.com/apps-script/add-ons/

这篇关于跨电子表格(而非库)共享的 Google 电子表格脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！