CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Robert
Robert

Reputation: 43

Accessing google sheet from another google sheet using app script api

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?

Upvotes: 1

Views: 648

Answers (1)

iansedano
iansedano

Reputation: 6481

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

enter image description here

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:

enter image description here

The response will be:

enter image description here

References

Upvotes: 0

Related Questions