Correct setup for accessing a GSheet published as a webapp via a service account

Question

I have a GSheet published as a webapp and I am trying to allow access to it from a service account

• It is set to allow access to Anyone.
• I can access it when i hit the deployed app URL directly.
• I have converted the project to be GCP standard project.
• I created a Service Account attached to the project.
• Service account has editor access.
• I then also granted access to the sheet to the service account.

Trying to access it using the following python code and getting a 401. Following examples here

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']
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/a/<DOMAIN_NAME_REDACTED>/macros/s/AKfycbwv53GaXRCvjQMYizjqI4PsLeNMAcmCtHjRhZE9AlheIp0qE_s/exec')
    print(response)
except BaseException as err_base2:
    print(err_base2)

if __name__ == '__main__':
    main()

I think I am missing something obvious. Any ideas?

Answer 1

• You want to access to Web Apps which was deployed as Execute the app as: Me and Who has access to the app: Anyone using request of python.
• The Google Apps Script project deploying Web Apps has already shared with the service account.

If my understanding is correct, how about this modification? In this case, please modify the scope as follows.

From:

SCOPES = ['https://www.googleapis.com/auth/spreadsheets']

To:

SCOPES = ['https://www.googleapis.com/auth/drive']

and/or

SCOPES = ['https://www.googleapis.com/auth/drive.readonly']

• Even when your script of Web Apps uses the methods of Spreadsheet, it seems that the scope of Drive API is required.

Note:

• In this case, you can retrieve the returned values from Web Apps with print(response._body) in your script.

If this was not the result you want, I apologize.