google-apps-script在一个电子表格中有多个脚本 [英] google-apps-script have multiple scripts in one spreadsheet
本文介绍了google-apps-script在一个电子表格中有多个脚本的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我为Google Spreadsheet应用程序开发了几个通用脚本函数。
目前我将每项功能保存在笔记本电脑上的单个文件中。为了将它们包括在电子表格中，我只知道一种方法。
将各个函数文件合并为一个文件。
然后将合并文件中的数据复制并粘贴到
电子表格的脚本编辑器中，替换以前的内容。
如果有是一种方式（例如使用项目），其中
可以将单个函数文件保存一次，然后我可以
仅在新脚本中根据需要引用这些函数。
我不知道是否有这种可能性。我只能在电子表格内容的树列表中看到一个名为code的节点
。
感谢任何帮助。
谢谢。-
任何包含一个或多个脚本项目的电子表格文件都可以被引用作为
图书馆。 script_project组成库定义。
-
电子表格文件可以包含一个或多个script_projects。
-
脚本函数的接口位于电子表格文件的工具菜单
下。此菜单包含三个与
相关的条目：- 脚本库（我还没有探索过此项）
- 脚本管理器
a。此条目提供了在每个项目中找到的所有项目和函数
的列表视图。该列表按照函数名，然后
project_name排序。第三列是服务，我目前只有
找到电子表格的值。我还没有探索其他类型的
文件，网站和服务知道还有什么可以出现。
b。在这个显示的底部是一个菜单，你可以在列表条目中执行什么操作：运行，编辑，新建，关闭。
- 运行：执行突出显示的脚本。
- 编辑：打开所选项目/功能上的脚本编辑器。
这与工具菜单上的第三个（3）相同（请参见下文）。
- 新建。在无标题（空）项目上打开脚本编辑器。这个
与在没有以前保存的脚本时从工具
菜单中选择脚本编辑器选项相同。
- 关闭：这将关闭脚本管理器窗口。这与
按下右上角的X按钮相同。
脚本编辑器。
这使您可以访问用于操作的函数：
a。 LIBRARY - 具有定义版本号
b的脚本PROJECT。 PROJECT - 一个或多个FILE的集合。
c。 FILE - 一个或多个JavaScript功能的集合。
d。 FUNCTION - 一组JavaScript语句，可以通过电子表格界面或其他
JavaScript FUNCTION引用
（调用）。当从新电子表格访问脚本编辑器时，它会创建
a new（untitled）PROJECT。新项目包含一个FILE。初始文件的名称
是代码。这是
下的初始树条目，编辑器屏幕左栏中的untitled project根目录。
代码文件的内容列在右栏text_area中。
text_area用一个空的FUNCTION定义进行初始化，该定义具有
名称myFunction。
text_area提供了正常的标准文本编辑功能，您可以在其他脚本编辑器中找到
（复制，粘贴，剪切;叠加/插入键入文本
选择替换（粘贴）和删除
text_area显示文件的完整内容这可以是
一个或多个函数定义
编辑器的主要功能在文件菜单下找到以
a高级别管理项目和文件
文件菜单包含以下项目：
1。 New - 这可以让你在电子表格中插入一个新的PROJECT，
或者一个新的FILE，它还允许你
创建其他类型的文档，但是我没有探索这些
options。
2.打开 - 此子菜单为您提供电子表格中包含
的PROJECTS列表，并允许您切换项目，或
以访问一个附加文件项目。
打开PROJECT时，树结构会列出项目中包含的所有
文件。文件
的名称也列在text_area上方的选项卡中。您可以在文件之间切换
，方法是单击树条目或
选项卡名称。
3.修订记录 - 为您提供
项目的保存内容列表。您可以使用此选项退回更改。
4.重命名 - 此子菜单允许您更改PROJECT中FILE
的名称或PROJECT的名称。重命名的对象
由在
中选择的条目决定，即左列中的树。
5.删除 - 此子菜单允许您删除
中的FILE项目或整个PROJECT项目。删除的对象是
，由左列树形结构
中的高亮条目确定。
6.制作副本 - 在树结构中插入当前选定文件
的副本，并将访问权切换到复制的
文件的内容。如果树状结构
中的当前条目是PROJECT条目，则不适用于PROJECT级别的这个和其他子功能
将被禁用
（变灰）。
7.保存 - 将选定文件的当前内容标记为
还原点，并保存其内容，以便下次编辑器操作此项目时可以访问它
。
您可以在多个文件中保存未保存的更改。此选项
仅保存对当前活动文件的更改。
8.全部保存 - 这将保存所有未保存的
更改文件的内容。编辑器在该文件的选项卡中的
FILE名称前面放置一个红色星号。每个文件的
更改都被保存。 红星被删除。
9.管理版本 - 该子菜单允许您为项目分配新版本
编号。它真的不允许你
来管理当前的版本定义。它会提示
作为描述（创建新版本的原因）。
通过定义一个版本号码，您可以将PROJECT作为一个库使用
。
10.项目属性 - 此子菜单提供对与PROJECT对象关联的一组属性
的访问。我没有
探索过使用用户属性和项目属性
标签。我期望他们关心在电子表格访问会话中保存Key-value
对（作为全局变量）。
但我会在稍后探讨它们。
INFO选项卡上的重要属性（对我来说）是
Project Key属性。这是您必须将
复制/粘贴到另一个电子表格文件中的值，才能访问
此PROJECT中包含的函数而不必
将实际函数定义复制到引用
电子表格文件。
11.构建用户界面 - 我还没有探索过这个子菜单。
现在，一旦为项目分配了一个版本编号，该项目
就可以用作另一电子表格文件中的参考（即LIBRARY）。
这是你如何做到的：
1.转到包含PROJECT（ieLIBRARY）
的电子表格文件，其中包含要引用$ b的PROJECT $ b 2.访问工具菜单以访问项目。使用
脚本管理器或脚本编辑器选项。如果您的
电子表格只有一个PRJOECT，那么脚本编辑器将
带您到该项目。否则，使用脚本管理器，为
选择PROJECT。 （如果您打开脚本编辑器，并且电子表格
文件具有多个PROJECT，它会提示您选择要访问的
PROJECT
3.转到文件/项目属性子菜单中，然后
选择（高亮）Project Key的属性值，并将
复制到您的剪辑板（即CTRL-c）。然后关闭这个
电子表格文件
4.打开包含需要
的脚本的电子表格文件，以引用另一个PROJECT（LIBRARY）中的函数
5.访问PROJECT包含函数调用
函数的函数
6.在编辑器中，进入Resourses / Manger Libraries子菜单
您将收到一个面板标题为INCLUDED LIBRARIES
7.要添加新库引用，请将复制的Project Key
值粘贴到查找库字段中，然后按SELECT
按钮。
8.如果您没有发生错误，您将设置库
的列表进行更新，以将您的PROJECT添加到列表中。
PROJECT（库）列表中的每行包含5个条目。这里
是它们如何使用的。
1.标题 - 这包含另一个
电子表格文件中的PROJECT的名称。这是与Prject Key相关联的
名称。您无法在此引用文件中更改此值
。
2.版本 - 这显示了您允许通过此定义访问
的PROJECT资源的哪个版本。
下拉列表显示哪些版本可用
以及您在创建版本时输入的注释。
3. Identifer - 向您显示您在FUNCTION定义中使用
的限定符，以唯一标识
您正在调用的函数。即使在初始定义了
之后，您也可以更改此值。这个标识符类似于在XML文档中找到的命名空间限定符
。如果您更改
限定符值，则必须对引用此
标识符的函数内的所有代码进行类似更改，否则执行时会抛出一个错误
time（to the最终用户）遇到未定义的标识符
。
4.开发模式 - 此属性影响当前电子表格中
脚本的执行。它告诉spreadhseet
引擎不保存目标
（引用）库的编译映像。这会减慢脚本执行
的速度，因为电子表格引擎每次执行脚本时都会编译
脚本，并且每次执行时还会
重新编译LIBRARY的内容。在使用库脚本调试问题
时，您应该使用开发模式来使用
。
5.删除 - 单击时从包含的库的
列表中删除库定义。
关闭
管理者访问面板时，库定义更改会自动保存。在面板的底部有两个按钮。
保存 - 允许您保存更改并继续创建库
的定义。取消 - 导致管理员功能退出。自上次保存后，任何
变化都将被丢弃。
在脚本中调用库函数类似于调用
浏览器对象的msgBox方法。例如，为
调用包含Browser.msgBox（'...'）
子句的msgBox函数。
要为您分配LETS的标识符的
在库（项目）中调用doSomething函数，您需要
编码子句LETS。 doSomthing（....）。
发现这些事实后，我得出以下
结论：
1.电子表格的目的文件应该只保存
LIBRARY函数定义。
2.如果您打算使用PROJECT作为库，那么您可能
将希望在电子表格文件中包含多个项目，
包含LIBRARY。主PROJECT应该保存构成LIBRARY的
函数定义。应该定义第二个项目
来保存测试功能。只有LIBRARY
项目应该有一个版本号。
3.电子表格数据通常不会提供给参考库的
脚本。它们可以使
可用，但是如果没有将库电子表格文件作为
单独的应用程序打开，它们可用。
4.我不知道（因为我没有测试过），但我认为
库（电子表格）
文件的PROJECT PROPERTIES和USER PROPERTIES是在调用库函数时无法访问它们。
我可以看到两者都允许和禁止访问的意见。
出于安全原因，我猜测他们不可用。
（我可能是错的！ - 通常我是）。
5.我喜欢使用FILES的想法。实际上，我打算为每个函数使用一个文件
。从面向对象编程的角度来看，
可能需要为每个对象的
定义使用单独的文件。您必须认识到（此时）JavaScript语言
不支持对象定义。使用FILES作为容器
似乎是创建FILE构造的原因。
6. FILE对象还允许您限制视图范围并将
更改为FILE中的函数。通过这种方式，您不能破坏不在文件中的其他
函数。
7.当脚本运行
引用一个或多个库时，我发现对执行时间有重大影响。我的猜测是，随着时间的推移，执行
的性能可以得到提高，使用JIT和缓存
工具。
8.从最终用户的角度来看，当脚本执行时，最终用户会看到时钟类型的
光标图标。
考虑到执行引用库函数的
代码所花的时间，这非常重要。
9.对于脚本执行性能的原因，您可能会预计
将不得不使库函数成为
电子表格文件的实际部分。展望这种可能性，我会
在我的电子表格脚本
中创建虚拟（骨架）函数，这些函数没有做任何事情，只是将输入参数传递给具有适当名称空间标识符的
库函数。然后
当我必须在库函数中复制时，我只需要
就可以修改虚拟函数。我会把这些框架函数
放到一个单独的文件中。我会为每个库
引用定义使用一个文件。
I have developed several general purpose script functions, for Google Spreadsheet apps. Currently I keep each function in an individual file on my lap-top. In order to include them into a spreadsheet, I only know of one way.
Merge the individual function-files into one file. Then copy and paste the data in the merged file into the spreadsheet's script editor, replacing the previous content.
It would be nice if there was a way (e.g. using a "project") where the individual function files could be saved once and then I could just reference those functions as needed in a new script.
I don't know if there is such a possibility. I only see one node called "code" in the tree list of the spreadsheet content.
Any help is appreciated.
解决方案
Thanks Serge. I looked into "Libraries" here is what I found.
Any spreadsheet file which contains one or more Script Projects can be referenced as a Library. The script_project makes up the library definition.
A spreadsheet file can contain one or more script_projects.
The interface to script functions is found under the "tools" menu in the spreadsheet file. This menu contains three (3) entries related to scripts:
- Script Gallery (I have not yet explored this entry)
- Script Manger
a. This entry provides a list view of all the projects and functions
found in each "project". The list is sorted by function_name, then
project_name. The third column is "service" which I currently only
find a value of "spreadsheet". I have not explored other types of
documents, "sites" and "services" to know what else can appear.
b. At the bottom of this display is a menu of what actions you can
perform on the list entries: Run, Edit, New, Close.
- Run: executes the highlighted script. - Edit: opens the "Script Editor" on the selected project/function. This is the same the third (3) on the "tools" menu (see below). - New. opens the "Script Editor" on an untitled (empty) projects. This the same action that selecting "Script Editor" option from the "tools" menu when there are no previously saved scripts. - Close: this closes the Script Manager window. This is the same as pressing the "X" button in the top right corner. Script Editor. This provides you access to functions for manipulating: a. LIBRARY - a script PROJECT with a defined version number b. PROJECT - a collection of one or more FILEs. c. FILE - a collection of one or more JavaScript FUNCTIONs. d. FUNCTION - A set of JavaScript statements that can be referenced (called) by either a Spreadsheet interface or another JavaScript FUNCTION.
When the Script Editor is accessed from a new spreadsheet, it creates a new (untitled) PROJECT. The new project contains one FILE. The name of the initial file is "Code". This is the initial "tree" entry under the "untitled project" root in the left column of the editor's screen. The contents of the "Code" file is listed in the right column "text_area". The text_area is initialized with an empty FUNCTION definition which has the name "myFunction". The text_area provides normal standard text editing functions that you find in other script editors (copy, paste, cut; overlay/insert typing text selection replace(with paste) and delete. The text_area shows the complete contents of the file. This may be one or more "function" definitions. The major functions of the editor in managing PROJECTs and FILEs at a "higher" level are found under the "File" menu. The "File" menu contains the following entries: 1. New - This allows you to insert a new PROJECT into the spreadsheet, or a new FILE in the current PROJECT. It also allows you to create other types of documents. But I have not explored these options. 2. Open - This sub-menu provides you with a list of PROJECTS contained within the spreadsheet and allows you to switch projects, or to access an additional file under a project. When a PROJECT is opened, The tree structure lists all of the files contained in the project. The names of the files are also listed in "tab" above the text_area. You can switch between files by either clicking on the tree entry or the "tab" name. 3. Revision History - provides you with a list of saved contents of the project. You can back-off changes using this option. 4. Rename - This sub-menu allows you to change the name of a FILE within a PROJECT, or the name of the PROJECT. The object that is renamed is determined by the entry selected in the "tree" in the left column. 5. Delete - This sub-menu allows you to delete either a FILE within a PROJECT or the entire PROJECT. The object deleted is determined by the high-lighted entry in the tree structure in the left column. 6. Make A Copy - inserts a duplicate copy of the current file selected in the tree structure, and switches access to the copied file's contents. If the current entry in the tree structure is the PROJECT entry, then this and other sub-functions which do not apply at the PROJECT level are inactivated (grayed out). 7. Save - Marks the current contents of the selected file as a restore point, and saves its contents so that it is accessible the next time the editor manipulates this project. You can have unsaved changes in multiple files. This option only saves changes to the currently active file. 8. Save All - This saves the contents of all files that have unsaved changes. The editor places a "red star" in front of the FILE's name in the "tab" for that file. As each file's changes are saved. the "red star" is removed. 9. Manage Versions - This sub-menu allows you to assign a NEW version number to a project. It really does not allow you to manage the current version definitions. It prompts you for a description (a reason for creating a new version). By defining a "version" number you make the PROJECT usable as a library. 10. Project Properties - This sub-menu provides access to a set of attributes that are associated with the PROJECT object. I have not explored the use of "User Properties" and "Project Properties" tabs. I expect that they are concerned with saving Key-value pairs (as global variables) across spreadsheet access sessions. But I will explore them later. The important attribute on the "INFO" tab (for me) is the "Project Key" attribute. This is the value that you must copy/paste into another spreadsheet file, in order to access the functions contained in this PROJECT without having to copy the actual function definitions into referencing spreadsheet file. 11. Build a user interface - I have not explored this sub-menu. Now once you have assigned a "version" number to a project, that project can be used as a "reference" (i.e. LIBRARY) in another spreadsheet file. Here is how you do this: 1. Go to the spreadsheet file that contains the PROJECT (i.e.LIBRARY) that contains the PROJECT you want to "reference" 2. Access the "tools" menu to gain access to the project. Use either the "Script Manager", or the "Script Editor" options. If your spreadsheet only has one PRJOECT, then the "Script Editor" will take you to that PROJECT. Otherwise, use the Script Manger, to select the PROJECT. (If you open the Script Editor, and the spreadsheet file has more than one PROJECT, it will prompt you to select which PROJECT that you want to access. 3. Go to the "File/Project Properties" sub-menu within the editor and select (high-light) the Project Key's attribute's value. and copy it to your "clip board" (i.e. CTRL-c). You can then close this spreadsheet file. 4. Open the spreadsheet file that contains the scripts that needs to reference the functions from the other PROJECT(LIBRARY). 5. Access the PROJECT containing the functions that are to call the external functions from the LIBRARY. 6. In the editor go to the "Resourses/Manger Libraries" sub-menu. You will receive a panel with the title "INCLUDED LIBRARIES". 7. To add the new library reference, paste the copied Project Key value into the "Find a Library" field, and press the "SELECT" button. 8. If you have not made an error, you will set the list of libraries updated to include your PROJECT added to the list. Each row in the PROJECT (library) list contains 5 entries. Here is how they are used. 1. Title - This contains the name of the PROJECT from the other spreadsheet file. This is the name that is associated with the "Prject Key". You can not change this value, within this referencing file. 2. Version - This shows which version of the PROJECT resources that you are allowed to access by this definition. The drop-down list shows you which versions are available and the comment you entered when you created the version. 3. Identifer - This shows you the qualifier that you are to use within your FUNCTION definitions to uniquely identify which function you are calling. You are allowed to change this value even after you have initially defined it. This identifier is similar to a "namespace" qualifier that is found in an XML document. If you change the qualifier value you must make a similar change to all code within your functions that reference this identifier, otherwise an error is thrown at execution time (to the end user) that an undefined identifier has been encountered. 4. Development Mode - This attribute impacts the execution of the scripts in the current spreadsheet. It tells the spreadhseet engine NOT TO SAVE the compiled image of the target (referenced) library. This will slow down the execution of your script since the spreadsheet engine will compile your script every time you execute it, and it will also recompile the contents of the LIBRARY each time. You should only use "Development mode" when you are debugging a problem with a library script. 5. Remove - when clicked removes the "Library definition" from the list of included libraries. Library definition changes are automatically saved when you close the mangers access panel. At the bottom of the panel are two buttons. "SAVE" - allows you to save changes and continue to make Library definitions. CANCEL - causes the manger function to exit. Any changes since the last SAVE are discarded. To call a Library function in your script is similar to calling the "msgBox" method of the "Browser" object. For example to call the msgBox function you include "Browser.msgBox('...')" clause. To call the "doSomething" function in a library (project) for which you assigned the "identifier" of "LETS", you would code the clause "LETS.doSomthing(....)". After discovering these "facts", I have come to the following conclusions: 1. The purpose of the spreadsheet file should be to only hold the LIBRARY function definitions. 2. If you are going to use a PROJECT as a LIBRARY, then you probably will want to have multiple PROJECTs in the spreadsheet file that contains the LIBRARY. The primary PROJECT should hold the function definitions that make up the LIBRARY. A second project should be defined that holds testing functions. Only the LIBRARY project should be given a version number. 3. The spreadsheet data in general will not be available to the scripts that references the LIBRARY. They can be made available but not without opening the libraries spreadsheet file as a separate application. 4. I don't know (since I have not tested this), but I assume that the PROJECT PROPERTIES and USER PROPERTIES of the library (spreadsheet) file are NOT accessible to the library functions when they are called. I can see both, views which would allow and prohibit this access. For security reasons I would guess that they would not be available. (I could be wrong! - and generally I am). 5. I like the idea of using FILES. In fact, I plan to use one file for each function. From a Object Oriented Programming perspective, you will probably want to use a separate file for each Object's definition. You must realize (at this time) the JavaScript language does not support "Object Definitions". The use of FILES as a container seems to be the reason for creating the FILE construct. 6. The FILE object also allows you to limit the scope of view and change to functions within the FILE. In this way, you cannot corrupt other functions which are not in the file. 7. I found a significant impact on execution time when a script is run that references one or more Libraries. My guess is that execution performance can be improved over time, with the use of JIT and caching facilities. 8. From an end-user's perspective, it would be nice is a "clock" type cursor icon would appear to the end user when a script is executing. This is very important considering the time it takes to execute code that references library functions. 9. For script execution performance reasons, you may anticipate that you will have to make the library functions an actual part of your spreadsheet file. Looking ahead to this possibility, I would create dummy (skeleton) functions within my spreadsheet scripts which do nothing more that pass the input parameters to the library functions with the proper namespace identifier. Then when I have to copy in the library functions I only have to change the dummy functions. I would put these skeleton functions into a separate file. I would use one file for each library reference definition.
这篇关于google-apps-script在一个电子表格中有多个脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
