Google电子表格脚本跨电子表格共享（不是库） [英] Google Spreadsheet Scripts shared across spreadsheets (not libraries)

问题描述

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

什么我想要：
一堆电子表格都包含一个主脚本。每次脚本更新时，它们都会更新为使用最新脚本。

我拥有的：
15个电子表格，它们都有原始脚本的副本。原来的脚本已经改变了，现在看来我必须去编辑每个复制电子表格中存在的名为 Copy of myScriptName 的每个脚本。

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

我怎样才能分享脚本并管理它在任何单独的电子表格本身之外？考虑到所有寻找相同答案的人，我必须在这里错过一些东西。我只是看不出如何让它成为一个图书馆来解决我的使用案例。

谢谢！

我不知道看看这会有什么帮助，但根据评论的请求，这里是脚本：

 函数createRollupTable（）{
 
 //返回如果：
 //只有传入的帐户代码参数没有季度信息
 //如果传递的帐户代码参数的长度为空
 if（arguments.length< = 1 ||！arguments [0] || arguments [0] .length< = 0）{
 return; 
} 
 
 var rollupTable = new Array（）; 
 var fullListAccountCodes = arguments [0]; 
 
 //输出的第一列是所有季度的完整帐户代码列表
 rollupTable [0] = fullListAccountCodes; 
 
 // Array保持每个账户代码的YTD总数
 var annualAccountCostOutput = new Array（fullListAccountCodes.length）; 
 
 //遍历在
中传入的所有宿舍（var i = 1; i< arguments.length; i ++）{
 
 // // This数组应设置为可用帐户代码的总长度
 var quarterlyRollupCostOutput = new Array（fullListAccountCodes.length）; 
 var quarterlyBreakdown = arguments [i]; 
 var quarterIndexCounter = 0; 
 var quarterTotalCost = 0; 
 
 //遍历所有帐户代码
（var j = 0; j< fullListAccountCodes.length&& quarterIndexCounter< quarterlyBreakdown.length; j ++）{
 
 //找到与本季度当前帐户代码相匹配的
 if（fullListAccountCodes [j] == quarterlyBreakdown [quarterIndexCounter] [0]）{
 
 //设置基于完整列表的输出索引，因此它们对齐
 quarterlyRollupCostOutput [j] = quarterlyBreakdown [quarterIndexCounter] [1]; 
 
 //将此费用添加到本季度的运行总计
 quarterTotalCost + = quarterlyBreakdown [quarterIndexCounter] [1]; 
 
 //为该账户的年度汇总添加总金额代码
 if（annualAccountCostOutput [j]）{
 annualAccountCostOutput [j] + = quarterlyBreakdown [quarterIndexCounter] [1 ]。 
} else {
 annualAccountCostOutput [j] = quarterlyBreakdown [quarterIndexCounter] [1]; 
} 
 
 //递增计数器，以便在季度
 quarterIndexCounter ++中搜索下一个帐户代码; 
 
} 
} 
 
 rollupTable [i] = quarterlyRollupCostOutput; 
 
 //在结果中为空格添加一个空行
 rollupTable [i] .push（）; 
 
 //添加季度总成本
 rollupTable [i] .push（quarterTotalCost）; 
 
} 
 
 //为空格添加一个空行
 rollupTable [0] .push（）; 
 
 // Google电子表格强制您使用非空白空格填充，没有右对齐选项可用
 var spaces =; 
 var numSpaces = 66; （var i = 0; i 。 
 
 //为总计
添加一行rollupTable [0] .push（spaces +Totals：）; 
 
 //添加YTD列
 rollupTable.push（yearlyAccountCostOutput）; 
 
返回汇总表; 
} 
  

解决方案

要求只是将主脚本中的内容完全复制到电子表格副本内的所有脚本，以便替换其代码并跳过引用库的需要，但我会提供我的印象图书馆设置的工作原理......

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

当您制作一个电子表格的副本时，它已经有一个带有库引用的脚本，它会停留与新的副本。因此，在创建一个要复制的电子表格模板后，您不必创建任何新脚本。

这一个电子表格模板应该有一个对主脚本的库引用。主人不需要在工作表内，你不应该复制原始/主脚本。

所以，我们有： 1个主脚本， 1电子表格模板，其中有一个脚本引用了主人，然后尽可能多地模板副本。

现在，当您设置图书馆

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

这种设置仍然不能解决向每个模板副本需要引用的主脚本添加全新功能的问题。也许有人可以评论或提供一个单独的答案。

[Update：3/15/2015]
因此，在Chrome网上应用店发布附加组件，附加一次，并让它出现在我认为是OP最初需要的所有表格/文档/表单中。推出新版本会更新所有使用它的Google文档。

https：//开发人员.google.com / apps-script / add-ons /

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.

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.

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.

Thanks!

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.

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.

[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屋！