Trying to access Gsheet webapp from Python 3.7 using service account

Question

Config

Python 3.7
google-auth 1.11.0
google-auth-oauthlib 0.4.1
Google Service Account credentials
GSheet shared with ServiceAccount
GSheet exposed as Webapp
Gsheet has a doGet method which reads a sheet and converts to CSV and returns it
Works fine when you hit it directly in the browser
Doesnt work when using python

Can see the call from python hitting the webapp - can see the webapp processing. Getting a response returned to the client which states -

    b'<!DOCTYPE html><html><head><link rel="shortcut icon" 
    href="//ssl.gstatic.com/docs/script/images/favicon.ico"><title>Error</title><style 
    type="text/css">body {background-color: #fff; margin: 0; padding: 0;}.errorMessage {font- 
   family: Arial,sans-serif; font-size: 12pt; font-weight: bold; line-height: 150%; padding-top: 
    25px;}</style></head><body style="margin:20px"><div><img alt="Google Apps Script" 
    src="//ssl.gstatic.com/docs/script/images/logo.png"></div><div style="text- 
   align:center;font-family:monospace;margin:50px auto 0;max-width:600px">We&#39;re sorry, a 
    server error occurred. Please wait a bit and try again.</div></body></html>'

No errors in logs.

Python code doing the work:-

from __future__ import print_function
from google.oauth2 import service_account
from google.auth.transport.urllib3 import AuthorizedHttp

SCOPES = ['https://www.googleapis.com/auth/spreadsheets',
      'https://www.googleapis.com/auth/drive',
      'https://www.googleapis.com/auth/drive.readonly']
credentials = service_account.Credentials.from_service_account_file(
'service_account.json', scopes=SCOPES)


def main():
try:
    authed_http = AuthorizedHttp(credentials)

    response = authed_http.request(
        'GET', "https://script.google.com/macros/s/AKfycbzmr5-g2ZIlsGFL5SDYdCYEKmhyqH_- 
QcAhFeBnfN0_D291kRA/exec")

    print(response._body)
except BaseException as err_base2:
    print(err_base2)

if __name__ == '__main__':
   main()

Is this approach supported. Feel like i'm missing something obvious.

Service Account Role Permissions to GSheet Project

Service Account Permissions to GSheet

Put some logging into the requests but it doesnt tell us anymore. DEBUG:google.auth.transport.urllib3:Making request: POST https://oauth2.googleapis.com/token DEBUG:urllib3.connectionpool:Starting new HTTPS connection (1): oauth2.googleapis.com:443 DEBUG:urllib3.connectionpool:https://oauth2.googleapis.com:443 "POST /token HTTP/1.1" 200 None DEBUG:urllib3.connectionpool:Starting new HTTPS connection (1): script.google.com:443 DEBUG:urllib3.connectionpool:https://script.google.com:443 "GET /macros/s/AKfycbzmr5-g2ZIlsGFL5SDYdCYEKmhyqH_-QcAhFeBnfN0_D291kRA/exec HTTP/1.1" 500 None

Does this answer your question? [Google Apps script: "We're sorry, a server error occurred. Please wait a bit and try again"](https://stackoverflow.com/questions/19553828/google-apps-script-were-sorry-a-server-error-occurred-please-wait-a-bit-and) — Kos, Feb 10 '20 at 20:56
Struggling to find where the error is - wrappped most things with try catch. Added a picture showing the log outputs. Will keep looking. — Simon Taylor, Feb 10 '20 at 21:04
When I tested your shared Spreadsheet and scripts of Google Apps Script and python, I could confirm that no error occurs, and the CSV values are returned. So can I ask you about your situation? 1. About `GSheet shared with ServiceAccount`, did you share both the Spreadsheet and GAS project of container-bound script? 2. Can I ask you about the settings for deploying Web Apps? — Tanaike, Feb 10 '20 at 23:40
Have you considered using the [Sheets API](https://developers.google.com/sheets/api) to get the data in your spreadsheet as in [these examples](https://developers.google.com/sheets/api/guides/values#reading_a_single_range) ? — alberto vielma, Feb 11 '20 at 10:32
@taniake - updated 2 images in the question showing the service account permissions on the project and to the gsheet — Simon Taylor, Feb 11 '20 at 12:36
@albertovielma - yes thanks - i have utilised pyspread in a lambda function to get at the data via the sheets API. I'm not blocked in terms of a way forward but I would like to understand why this isn't working when hitting the webapp URL directly from python when it does when hitting it as a user in the browser. — Simon Taylor, Feb 11 '20 at 12:38
Thank you for replying and adding the information. In your situation, your Web Apps is put to the container-bound script of Google Spreadsheet. If my understanding is correct, in this case, it is required to share not only the Spreadsheet, but also the the container-bound script with the service account. How about this? — Tanaike, Feb 11 '20 at 22:56
How do I do that over and above what I have done already? The service user has access to the sheet and when i look at the code and share that the permissions are the same. it also has access to the project via Editor role. — Simon Taylor, Feb 11 '20 at 23:00
Thank you for replying. I deeply apologize for the inconvenience. When I could replicate your situation, I would like to think of the solution. This is due to my poor skill. I deeply apologize for this. — Tanaike, Feb 11 '20 at 23:31

alberto vielma · Accepted Answer · 2020-02-12T11:09:54.130

I was having the same error you were having:

We're sorry, a server error occurred. Please wait a bit and try again.

Then I used domain-wide delegation, which allows the service account to impersonate any user in your G Suite domain. You have to have a G Suite Account to be able to use domain wide-delegation as the docs say:

If you have a G Suite domain—if you use G Suite, for example—an administrator of the G Suite domain can authorize an application to access user data on behalf of users in the G Suite domain.

So now, why do you need to impersonate an user(a real person)? it's due to the fact a service account is a bot(not a real person) that is used to server-to-server interactions making possible your app calls Google APIs and although the service account has a parameter called client_email, which has a structure like name@project-randomnumber.iam.gserviceaccount.com it's not a real email that belongs to a real person (kind of confusing I know).

If you check the Deploying a script as a web app in the 4th step states:

Under Execute the app as, select whose authorization the app should run with: your account (the developer's) or the account of the user who visits the app (see permissions).

Therefore, you can't use the URL provided by the "Deploy as web app". To implement domain-wide delegation in your code, you can do it like this:

from __future__ import print_function
from google.oauth2 import service_account
from google.auth.transport.urllib3 import AuthorizedHttp

SCOPES = ['https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive']
SERVICE_ACCOUNT_FILE = 'service_account.json'
# The user we want to "impersonate"
USER_EMAIL = "name@domain"

def main():
    try:
        credentials = service_account.Credentials.from_service_account_file(SERVICE_ACCOUNT_FILE, scopes=SCOPES)
        delegated_credentials = credentials.with_subject(USER_EMAIL)
        authed_http = AuthorizedHttp(delegated_credentials)
        response = authed_http.request('GET', "https://script.google.com/macros/s/<your-id>/exec")
        print(response._body)
    except BaseException as err_base2:
        print(err_base2)

if __name__ == '__main__':
   main()

That sounds like a great answer. I wont be able to implement domain wide delegation here BUT I should be able to give it my individual account which is the owner of the gsheet and see success? Do you agree? — Simon Taylor, Feb 12 '20 at 10:45
do you mean like giving to the service account your individual account like "myaccount@gmail.com" to impersonate it? if it's that what you mean, then it will not work because the ```gmail.com``` domain is not a [G Suite domain](https://support.google.com/domains/answer/6069226?hl=en) — alberto vielma, Feb 12 '20 at 10:55
no - sorry i wasnt clear. the service account has been provisioned within my work domain. I have an individual account also within the same domain. Could i use this without turning on domain wide delegation? I will test later on but wanted your view on it. — Simon Taylor, Feb 12 '20 at 10:57
I updated my answer. If your work domain is not a G Suite domain, then you will not be able to use domain-wide delegation. I hope this can help — alberto vielma, Feb 12 '20 at 11:11

Trying to access Gsheet webapp from Python 3.7 using service account

1 Answers1