Webapp execute as user BUT save data to spreadsheet

[Michael O'Shaughnessy]

I have a WebApp that populates an HTML table with data from a Google Spreadsheet that is filtered by the current user's email.  I add a "check box" to each table row.  The user opens the web app url, sees just their data in the table then chooses several rows by clicking the checkbox.  I then have a button the sends the data back to the spreadsheet... and this is where I have a problem.  The sheet is set to "anyone can view" so that the data can be displayed BUT the user cannot "save" to the spreadsheet due to their view only permissions.  If I change the app to execute as me then I get an empty string for the current user.

So I have these questions:

1. Is there a way that a webapp can send data to a spreadsheet as a different user?  If so, how?  Is this where "service accounts" come into play?

2. Is there a way to execute the webapp as me BUT get the current users email?

Any hep, guidance and direction is greatly appreciated!!

Thanks,

Michael

[Alan Wells]

Have your Web App make a POST request to another Web App that you own.  The second Web App will execute as you, and therefore it will have edit access to the Google Sheet.  If the first Web App doesn't already ask for permission to make an external request, then you'd need to add that, and the user will need to accept that permission.  The second Web App needs to be published to execute as you.  Because the first Web App is making the POST request on the server side, the URL to the second Web App is not discoverable by the user.  If you want, you could add a password to the POST payload, as a simple test for whether the request came from your first Web App.  The basic concept is that you need some intermediary to make the setting as you.  You mentioned a service account, but you still need to trigger the code in the service account.  So, it's kinda pointless.  Either way you need to somehow trigger that intermediary step.  Since there's no way around that, then just go direct to the Web App in your account.  If you need to have code run that is independent of a user, for example you want to avoid the situation of what happens when a user leaves the company, and there is code running from their account.  To avoid that bad situation, you can use a service account, which runs independently.  But if that's not your case, then you don't really need it.

[Fernando Falcao]

Hi Michael,

Do your users belong to the same domain to where you publish the app? Can't you publish as the user accessing the web app and then in your code you use session.getefectiveuser in order to get user email ID?

Would it fit to your case?

Fernando

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/dd150868-c8e0-4317-a243-f4cb35bc43efn%40googlegroups.com.

[dimud...@gmail.com]

Service accounts, while applicable, are not the best fit for this use-case. They are better suited to situations where the service account acts as a proxy for multiple users. In your case, the service account is just mirroring one account, your own. So it seems like overkill.

Alan's Web-App to Web-App approach is viable. But it adds another layer of abstraction, where the two Web Apps have to communicate, so now you also have the overhead of managing two GAS Web Apps instead of just one.

Here's a solution that I think is a better fit. Its not without its own pros and cons, but its more succinct. Upfront, its more work to setup, but I feel it will be easier to maintain over time since you get to keep the business logic of your app in one place. Basically, you'll need to generate OAuth2 credentials specific to your account and use the OAuth2 library to generate access tokens. That access token can then be used to make direct calls against the Spreadsheet REST API OR alternatively, the Apps Script API (formerly the Execution API) to invoke a function in the script under your own authority.

Here's the basic sequence of steps involved:

• From the GCP Cloud Console

  • Create a GCP Project
  • Enable the requisite APIs (Google Sheets in this case)
  • Generate OAuth2 credentials (clientId, clientSecret) and set redirect URIs
  • Configure OAuth consent screen (setting the appropriate scopes)

• From GAS project (direct invocation of REST API variant)

  • Add OAuth2 GAS library to project
  • Create an OAuth2 service with the appropriate scopes, redirect uri, callback and property store.
  • Use the service to generate the authorization URL
  • Logged in under your own account, navigate to the authorization URL to authenticate/authorize the service.
  • When successfully authenticated a refresh token will be stored in your property store. Use the service to generate access tokens when making calls to the Google Sheets REST API.

• From GAS project (AppsScript/Execution API variant)

  • In addition to the steps above
    • update your (WebApp) manifest to use only a restricted set of scopes (.../auth/spreadsheets.readonly)
    • copy & rename your manifest file and update it to use the execution api (removing the webapp property) and more permissive scopes(.../auth/spreadsheets)
    • create a new deployment using the api executable manifest (easiest way is from the Try this API sidebar in the reference docs)
    • create global functions that leverage the built-in sheets service as you normally would
    • invoke those functions by calling the Apps Script API using the generated deploymentId

Once all the upfront setup is out of the way all you need to do is call either the Google Sheets REST API or the Apps Script API using UrlFetchApp.fetch() with the access tokens generated by the OAuth2 library. All from the same GAS project.

[Alan Wells]

Great solution!  I like it.  I don't understand what copying and renaming the manifest file does.  Can you expand on that?

[dimud...@gmail.com]

I've gotten into the habit of assigning a separate manifest to my deployments. That way I can uniquely define the application type (Web App Or Executable) and clearly delineate which scopes are available to a given deployment.

 
For the scenario described by the OP, my workflow would go as follows:

- reserve the default manifest (appsscript.json) for the default head deployment

- create a deployment with a custom manifest for a web app

- create a deployment with a custom manifest for the executable api

The WebApp manifest will be restricted to read-only scopes (https://googleapis.com/auth/spreadsheets.readonly)

The Executable manifest (for the App Script API variant) will have more permissive scopes (https://googleapis.com/auth/spreadsheets)

if they share the same manifest, and by proxy the same scopes, then Web App users will be asked to authorize the more permissive scopes, which is what we're trying to avoid in the first place. 

[Michael O'Shaughnessy]

Wow!  Thank you all for the information!

@Allan - Your solution I can "wrap"my head around.  I have read about "doPost" but have never used it.... time to increase my knowledge and skills!!!  So I don't spend hours searching, do you know of any tutorials or examples I can take a look at?  I am a "beginner" with doPost so basic would be great!

@Fernando - Although 90% of the time my solutions are run under the domain of the school district I am working with, there are a number of school districts I deal with that are not "Google Schools" so I can't take advantage of "domain" specific things.

@DimuDesgins - your solution is fantastic!  I agree having everything in one project is great and that is what I was trying to accomplish.  I don't have experience with the OAuth2 library, but like I mentioned to Alan above... time to learn!!!  Do you have any suggestions for good tutorials or examples of your solution?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/028ab0cc-25f7-47b6-ad59-78fd97de8f70n%40googlegroups.com.

[Alan Wells]

doGet() and doPost() are reserved function names.  They are reserved for listening to a GET or POST request made to the published URL of the Web App.

They are "triggers" that get triggered to run by the request being made to the published URL of the Web App.

For the reserved function names doGet(e) and doPost(e) one of the most important things to know is the event object.

The letter "e" stands for "event object."  There is nothing special about the letter "e", you could use any name.

When a POST request is made to the published URL of the Web App, the payload of the request gets passed into "e" 

https://developers.google.com/apps-script/guides/web#request_parameters

You can make a POST or GET request with Apps Script by using UrlFetchApp.fetch(url,options)

where "url" is the published url of the Web App and options is a JSON object with various settings including the payload.

Different languages will have slightly different method names for making an HTTP Request.
In the strategy I suggested, you'd be BOTH making a POST request with one Web App and receiving the request with the second Web App.

[dimud...@gmail.com]

Here's are some good places to start:

https://developers.google.com/apps-script/api/how-tos/execute

https://github.com/googleworkspace/apps-script-oauth2

Also I forget to mention, after you set up the GCP project you have to associate it with the GAS project before moving on to the other steps.

[Marcos Gomes]

I have a similar problem.
I need to see the callbak of a webhook application, but POST does not display the return using console.log or Logger.
As I understand it, this data can only be viewed using GCP.
What I would like:
External Service trigger send data > webApp (Webhook) get data > view callback data > write data in sheet

Since I can't use console.log (), I can't identify the positions of the data received.

Amit Agarwal has examples that work with weebhook. I am trying to use the example to create the solution using GCP, but to no avail.
https://www.labnol.org/code/19871-get-post-requests-google-script

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/daf3d5f0-31c2-41bd-b5ce-308cea1fad68n%40googlegroups.com.