Google API with impersonation

Questions
1

I was just following the docs to setup a new service account (since the OAuth token is not valid more than 7 days)

Now the service account connects just fine - but does not have write access to the spreadsheet. I assume I could just add the email of the service account to the spreadsheet to have write access. But shouldn’t it also be possible to impersonate the owner of the service account (= owner of the spreadsheet)?

I was trying it like this

Screen Shot 2021-10-30 at 00.15.35
Screen Shot 2021-10-30 at 00.15.35965×599 37.6 KB

but that gives an error

Private key validation failed: 401 - {“error”:“unauthorized_client”,“error_description”:“Client is unauthorized to retrieve access tokens using this method, or client not authorized for any of the scopes requested.”}

I bet it’s just a configuration issue - but what am I missing?

2

Hi @tcurdt, as per Google’s docs, this would require a Google Workspace account:

If you have a Google Workspace account, an administrator of the organization can authorize an application to access user data on behalf of users in the Google Workspace domain.

If you are using a Google Workspace account and are an administrator in your organization, you would need to follow the steps outlined in Google’s documentation to enable this feature:

To delegate domain-wide authority to a service account, a super administrator of the Google Workspace domain must complete the following steps:

  1. From your Google Workspace domain’s Admin console,go to Main menu menu > Security > API Controls.
  2. In the Domain wide delegation pane, select Manage Domain Wide Delegation.
  3. Click Add new.
  4. In the Client ID field, enter the service account’s Client ID. You can find your service account’s client ID in the Service accounts page.
  5. In the OAuth scopes (comma-delimited) field, enter the list of scopes that your application should be granted access to. For example, if your application needs domain-wide full access to the Google Drive API and the Google Calendar API, enter: https://www.googleapis.com/auth/drive, https://www.googleapis.com/auth/calendar.
  6. 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.

Hope this clarifies and helps! Give me a shout if you have followed these steps and are still having trouble.

4

I don’t have a Google Workspace account - just a regular account. That’s also why I couldn’t use an “internal” app with OAuth.

I am a little baffled. I am pretty sure I had this working before.

So as a non Google Workspace user there is no other way to write to a Google Spreadsheet but to add the service account’s email to the spreadsheet?

Adding the service account as editor works - although it’s not exactly a fantastic solution.