使用应用程序脚本API从另一个Google工作表访问Google工作表 [英] Accessing google sheet from another google sheet using app script api

问题描述

这是一个Google表格和应用脚本问题.我想要做的是有两个工作表，其中一个我称为客户工作表，另一个我称为服务器工作表.这些工作表中的每一个都有一个绑定的应用程序脚本.

服务器工作表的绑定应用程序脚本将作为Web应用程序部署，以作为用户访问Web应用程序"执行.并设置为拥有Google帐户的任何人"都可以访问.

客户表将与多个不同的用户作为查看者共享，以便他们可以制作自己的副本.

每个用户都可以修改其客户工作表实例.然后，每个客户工作表中的绑定脚本应用程序将调用与服务器电子表格绑定的Web应用程序，并向其中传递一些数据.该Web应用程序将访问其电子表格，进行一些处理，然后将结果返回到调用脚本，该脚本将使用结果修改其客户端工作表.

此处的目的是向用户隐藏服务器工作表和脚本的逻辑.执行为用户访问网络应用程序"的原因是:因为如果服务器应用程序脚本以我"的身份执行，则使用用户的api限制.那么如果正在进行大量处理，这些限制可能会很快被超过.

当我尝试从绑定到Google工作表的应用程序脚本调用网络应用程序时遇到授权问题(响应401).另外，如果以用户身份执行Web应用程序脚本，我不确定该Web应用程序脚本是否能够访问它的电子表格.

是否可以使用Google表格和容器绑定脚本来执行此操作?还是有更好的方法来创建此设置?

解决方案

没有办法解决以我的身份执行"

如果您使用用户访问网络应用"，那么通过扩展，他们将需要访问服务器"服务器.床单.这是因为该脚本代表用户 .因此，如果用户无权访问工作表，则脚本也不会.

作为服务器"，确实没有办法让服务器来完成工作"任务.如果您想限制呼叫者的访问权限.

最小示例

服务器"脚本

  function doGet(){return ContentService.createTextOutput("hello world！");} 

部署为以我的身份执行" 和具有Google帐户的任何人" .

客户"脚本

  function myFunction(){让webappURL ='[YOUR_DEPLOYMENT_URL]'让响应= UrlFetchApp.fetch(webappURL)Logger.log(response.getContentText())} 

哪个给出其输出 hello world！

配额

检查配额页面:

如果您只需要访问数据，则可以将电子表格数据发布为.csv文件，然后您的客户可以通过以下方式进行调用:

  function getData(){let sheetURL ='[LINK_GENERATED_BY_PUBLISH_DIALOG]'让响应= UrlFetchApp.fetch(sheetURL)Logger.log(response.getContentText())} 

如果服务器工作表具有以下值:

响应将是:

参考文献

• 制作Google文档，表格和幻灯片&公开表格
• 配额
• 将您的脚本部署为网络应用

This is a google sheets and app scripts question. What I'd like to do is to have two sheets, one of them I'll call the client sheet, and the other I'll call the server sheet. Each of these sheets will have a bound app script.

The bound app script of the server sheet will be deployed as a web app to execute as "User accessing the web app" and set to be accessible to "Anyone who has a Google account".

The client sheet will be shared with multiple different users as viewer, so that they can make their own copies.

Each user can modify their instance of the client sheet. The bound script app in each client sheet will then call the web app which is bound to the server spreadsheet passing it some data. The web app will access it's spreadsheet, do some processing and return the results to the calling script which will modify it's client sheet with the results.

The intent here is to hide the logic of the server sheet and script from the users. The reason for executing as "User accessing the web app" is so that the user's api limits are used, since if the server app script were executed as "me" then those limits could be exceeded quickly if there is lots of processing going on.

I ran into authorization issues (response 401) when trying to call a web app from an app script bound to a google sheet. Also I'm not sure that the web app script will be able to access it's spreadsheet if it is being executed as the user.

Is there a way to do this using google sheets and container bound scripts? Or is there a better way to create this setup?

解决方案

There is no way around "Execute as Me"

If you use "User accessing the web app" then by extension they will need to have access to the "server" sheet. This is because the script is acting on behalf of the user. So if the user doesn't have access to the sheet, then the script will not either.

As a "server" there really is no way around having the server do the "work" if you want to limit the callers access.

Minimal example

"Server" script

function doGet() {
  return ContentService.createTextOutput("hello world!");
}

Deployed as "Execute as me" and "Anyone with Google account".

"Client" script

function myFunction() {
  let webappURL = '[YOUR_DEPLOYMENT_URL]'
  let response = UrlFetchApp.fetch(webappURL)
  Logger.log(response.getContentText())
}

Which gives as its output hello world!

Quotas

Inspecting the quotas page at:

https://developers.google.com/apps-script/guides/services/quotas

It does not seem that there is a limit for your Web App to receive calls and read spreadsheets. There is a limit to the simultaneous executions though, which is 30, though some have reported it being just shy of this number.

Another option: Publish data to the web

If you only need access to the data then you can publish your spreadsheet data as a .csv file which then your clients can call with:

function getData() {
  let sheetURL = '[LINK_GENERATED_BY_PUBLISH_DIALOG]'
  let response = UrlFetchApp.fetch(sheetURL)
  Logger.log(response.getContentText())
}

If the server sheet has these values:

The response will be:

References

• Make Google Docs, Sheets, Slides & Forms public
• Quotas
• deploy your script as a web app

这篇关于使用应用程序脚本API从另一个Google工作表访问Google工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！