App Engine OAuth2.0 authorized cron job to analyze

Question

I'm creating a Google App Engine project which is going to automatically trigger a function each 5 minutes to analyze a Google sheet.

OAuth authorization

The to-be-analyzed sheet is a G Suite sheet, public to only company members. So I need OAuth2 to authorize the access. How do I do this?

I think I need a Service Account client ID, because as this is going to run automatically in the server, there cannot be a OAuth2 flow, right? Who is going to click the buttons if the function is ran in the server?

I need some directions.

Thanks

https://developers.google.com/identity/protocols/OAuth2ServiceAccount

Answer 1

Yes, it is an OAuth2 flow, but not one involving a manual user operation - the actions are performed by the servers, automatically, based on pre-configured information. From the very document you mentioned:

The Google OAuth 2.0 system supports server-to-server interactions such as those between a web application and a Google service. For this scenario you need a service account, which is an account that belongs to your application instead of to an individual end user. Your application calls Google APIs on behalf of the service account, so users aren't directly involved. This scenario is sometimes called "two-legged OAuth," or "2LO." (The related term "three-legged OAuth" refers to scenarios in which your application calls Google APIs on behalf of end users, and in which user consent is sometimes required.)

Basically you need:

• on the GAE side to locate an existing (or create a new) service account for your app (in the cloud project's IAM & Admin Service Accounts page). One service account is automatically created when your app is created.

• on the G Suite side to allow access by Delegating domain-wide authority to the service account:

To delegate domain-wide authority to a service account, first enable domain-wide delegation for an existing service account in the Service accounts page or create a new service account with domain-wide delegation enabled.

Then, an administrator of the G Suite domain must complete the following steps:

1. Go to your G Suite domain’s Admin console.
2. Select Security from the list of controls. If you don't see Security listed, select More controls from the gray bar at the bottom of the page, then select Security from the list of controls. If you can't see the controls, make sure you're signed in as an administrator for the domain.
3. Select Show more and then Advanced settings from the list of options.
4. Select Manage API client access in the Authentication section.
5. In the Client Name field enter the service account's Client ID. You can find your service account's client ID in the Service accounts page.
6. In the One or More API Scopes field enter the list of scopes that your application should be granted access to. For example, if your application needs domain-wide access to the Google Drive API and the Google Calendar API, enter: https://www.googleapis.com/auth/drive, https://www.googleapis.com/auth/calendar.
7. Click Authorize.

Your application now has the authority to make API calls as users in your domain (to "impersonate" users). When you prepare to make authorized API calls, you specify the user to impersonate.

Answer 2

Finally I solved it this way:

• Setup a new App Engine service account (I don't know if a "new" one was neccesary really)
• Take note of that new service account email
• Share the Sheet with that service account mail (I've not yet tested it without this step)
• Download its service secrets as a JSON.

• Use this code(inspired in (1) and (2))

  class analysisHandler(Handler):
      def get(self):
  
          credentials = ServiceAccountCredentials.from_json_keyfile_name('service-secrets.json',
                             ["https://www.googleapis.com/auth/spreadsheets"])
  
          http = httplib2.Http()
  
           #if credentials are still valid
          if not credentials.invalid:
              logging.info("Valid credentials, entering main function")
              http = credentials.authorize(http)
              main(http)
          else:
              credentials.refresh(http)
              main(http)
  

Then in main():

sheetService = discovery.build('sheets', 'v4', http=authorized_http)
logging.info("Reading Google Sheet")
result = sheetService.spreadsheets().values().get(spreadsheetId=spreadsheet_id, range=range_name).execute(http=authorized_http)

urlfetch.set_default_fetch_deadline(45)
logging.info("Printing in Google Sheet")
sheetService.spreadsheets().values().append(spreadsheetId=spreadsheet_id, range="Log", body=body, valueInputOption="USER_ENTERED").execute(http=authorized_http)

Where the authorized_http parameter is the one built before with credentials.authorize()

I think this can be improved however.

(1) How to use "Service account" authorization (rather than user based access refresh tokens)

(2) Creating and sharing Google Sheet spreadsheets using Python