我的 Google Apps 脚本如何由共享工作表的其他人运行? [英] How can my Google Apps Script be run by others the Sheet is shared with?

问题描述

我有一个适合我的 Google Apps 脚本.与其共享工作表并尝试使用它的其他人会收到消息:

I've got a Google Apps Script that works fine for me. Others who the sheet is shared with that try to use it get the message:

您无权执行该操作.请问楼主此项目授予您访问权限.

You do not have access to perform that action. Please ask the owner of this item to grant access to you.

该脚本用于使用新数据行更新现有工作表.它是使用添加到 UI 的菜单项触发的，并执行以下操作:

The script is used to update an existing sheet with new rows of data. It's triggered using a menu item added to the UI and does the following:

• 在 GDrive 文件夹中找到一个 .xlsx 文件(此文件由网络服务导出并由我们手动放入 GDrive 文件夹，我们无法控制文件的内容或其格式)
• 使用 UrlFetchApp 将文件从 .xlsx 转换为 .gsheet (uploadType=media&convert=true)
• 使用 UrlFetchApp 更改生成的 .gsheet 的文件名和文件夹位置.文件夹位置现在与 .xlsx 文件的位置匹配(当使用 UrlFetchApp 转换文件时，生成的文件似乎放置在 GDrive 的根目录中)
• 使用 DriveApp 获取 .gsheet，然后将其打开以供脚本访问
• 将 .gsheet 中的所有数据存储到二维数组中，使用列标题作为行中每个元素的键
• 将目标工作表中现有数据的所有 ID 值(唯一)存储到一维数组中
• 比较两个数组并从二维数组中删除任何包含与一维数组中的一个匹配的 ID 的行，只在二维数组中留下新数据
• 循环遍历二维数组的每一行，然后遍历目标工作表的每一列，将二维数组行中的数据添加到目标工作表中数据底部的新行，使用列标题的值作为每个元素的键

我知道代码没有问题，因为它对我来说很好，所以它一定是权限问题，但我不知道怎么做.工作表与他们共享，存储文件的 GDrive 文件夹与他们共享.当我第一次自己运行脚本时，我必须授予脚本访问 GDrive 文件夹的权限，这显然已经完成了.

I know there's no problem with the code because it works fine for me so it must be a problem with permissions, but I can't figure out how. The sheet is shared with them and the GDrive folder that the files are stored in is shared with them. When I first ran the script myself, I had to grant permissions for the script to access the GDrive folder, which has obviously now been done.

我是我们小组的开发人员，而不是数据的用户，但是 atm 我必须运行脚本来更新用户每次需要做的数据，而不是他们自己做，这是...烦人.

I'm the developer of our group and not the user of the data but atm I'm having to run the script to update the data for the users every time it needs doing instead of them just doing it themselves, which is... annoying.

非常感谢您在尝试找出问题所在的任何帮助.

Any help in trying to figure out where the problem is here greatly appreciated.

再次阅读此文件时，我发现当文件被转换时，首先将其保存到 GDrive 根目录，这就是我必须更改文件夹的原因.作为根，它不与该文件的用户共享.这可能是原因吗?如果是这样，我该如何解决这个问题?我可以在转换时指定结果文件应该保存到哪个文件夹吗?

Reading through this again its occurred to me that when the file is converted, at first its saved to the GDrive root which is why I then have to change the folder. Being the root, it's not shared with the users of this file. Could this be the reason? If so, how can I get around this? Can I specify at convert time which folder the resulting file should be saved to?

推荐答案

要解决这个问题，您需要将脚本部署为 Web 应用程序(想法来自@MarioR 答案，但设置不同):

To fix this, you need to deploy the script as a web app (the idea comes from @MarioR answer, but the settings are different):

• 使用所有者帐户，打开工作表，然后打开脚本编辑器
• 点击Publish，然后Deploy as web app
• 对于 Execute the app as，选择 访问网络应用程序的用户，对于 谁有权访问应用程序，选择 任何人

• With the owner account, open the sheet, then script editor
• Click on Publish and then Deploy as web app
• For Execute the app as, choose User accessing the web app and for Who has access to the app, choose Anyone

用户第一次尝试使用该脚本时，他们必须允许该脚本(如果他们得到一个此应用未验证，他们应该点击高级code> 在底部，然后 Go to <script name> (unsafe)).在此之后，他们可能需要刷新工作表才能运行脚本.

The first time the users try to use the script, they will have to allow the script (if they get a This app isn't verified, they should click on Advanced at the bottom and then Go to <script name> (unsafe)). After this, they may need to refresh the sheet to be able to run the script.

如果所有者将工作表共享给新用户，则在将脚本部署为 Web 应用程序后，新用户可能需要等待 15 分钟(左右)才能被允许授权脚本(同时该用户将继续收到红色警告).

If the owner shares the sheet to a new user, after the script has been deploy as a web app, the new user may need to wait 15 minutes (or so) before being allowed to authorize the script (meanwhile this user will continue to get the red warning).

只有所有者可以部署为网络应用程序，其他尝试这样做的用户不会收到消息错误，只是一个弹出 Fetching Data 永远卡住！)要更改所有者:使用所有者帐户打开工作表，单击 Share > Advanced > 单击触发准所有者"旁边的下拉菜单的箭头" > 设为所有者

Only the owner can deploy as a web app, the other users who try do to it won't get a message error, just a popup Fetching Data that remains stuck forever!) To change the owner: open the sheet with the owner account, click on Share > Advanced > Click on the arrow that triggers the dropdown next to the "owner-to-be" > Set as owner

这篇关于我的 Google Apps 脚本如何由共享工作表的其他人运行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！