CODEWITHSUNDEEP
pythonpython-3.xhttpgoogle-apps-script
Simon Taylor
Simon Taylor

Reputation: 607

Trying to access Gsheet webapp from Python 3.7 using service account

Code is not erroring according to logsPrevious related question

Config

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 Role Permissions to GSheet Project

Service Account Permissions to GSheet enter image description here

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

Upvotes: 2

Views: 490

Answers (1)

alberto vielma
alberto vielma

Reputation: 2342

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 [email protected] 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()

Upvotes: 0

Related Questions