Features

• Write data to Google spreadsheets using OAuth 2.0 Desktop app client credentials.
• Includes basic refresh token management.
• Uses Windows environment user variables for token storage.
• Uses Windows OCR (Optical Character Recognition) engine to automatically copy authorization code from the web browser. Can be turned on/off.

How it works

Reading public spreadsheets with an API key is really easy as described in Read Google Sheets API with Power Automate for Desktop.
Writing to private spreadsheets requires significantly more logic, this can be archived with OAuth 2.0 and involves at a high level following steps:

1. Obtain OAuth 2.0 credentials from the Google API Console.
2. Obtain an access token from the Google Authorization Server.
3. Examine scopes of access granted by the user.
4. Send the access token to an API.
5. Refresh the access token. [1]

This example uses Invoke Web Service flow actions and OAuth 2.0 tokens to send requests to Google API and Google Sheets API. The token lifetime is 3600 seconds and this means that a Google Sheets API request will fail after an hour. Therefore, when such an error occurs, the access token is updated by using the granted refresh token.

But, also the granted refresh token might no longer work. A refresh token might stop working for one of these reasons:

• The user has revoked your app’s access.
• The refresh token has not been used for six months.
• The user changed passwords and the refresh token contains Gmail scopes.
• The user account has exceeded a maximum number of granted (live) refresh tokens.
• The user belongs to a Google Cloud Platform organization that has session control policies in effect. [1]

A Google Cloud Platform project with an OAuth consent screen configured for an external user type and a publishing status of “Testing” is issued a refresh token expiring in 7 days. [1]

If a refresh token request fails, then the web browser is opened and the user is forced to reauthorize to be able to continue with the flow.

As a bonus, Windows OCR engine can be used to copy authorization code from the web browser to the PAD (UseAuthorizationCodeOCR = True /False). However, this is not 100% reliable, sometimes the engine cannot find the code (for some unknown reason), so this feature is not enabled by default. Instead, the user needs to manually copy the authorization code from the browser to the PAD input dialog. This isn’t a big drawback since authorization codes are less needed, a refresh token should be used if an access token gives an unauthorized response.

How to create OAuth 2.0 Desktop app client credentials and enable Google Sheets API

• Register a Google Cloud Account trial.
• Navigate to Credentials panel in cloud console.
• Select +Create Credentials > OAuth client ID from top menu. Then select option Application Type: Desktop app. By using Desktop app we can avoid the need for a redirect URI (we don’t really need one as we are just going to run this from Power Automate for Desktop).
• Go to your developer console and navigate to OAuth consent screen.
• Under Test users select +Add users and then add your user.
• As the last step, enable Google Sheets API.

Instructions

• Line 1: Set Desktop app Client ID (from previous step)
• Line 2: Set Desktop app Client secret (from previous step)
• Line 3: Set Spreadsheet ID.
  ⠀⠀If you don’t know how to locate this, open your Google spreadsheet and get it from the url:
  ⠀⠀https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=0
• Line 4: (Optional) Replace spreadsheet range Sheet1!A1:D5 with with the sheet and cell values you would like to write to.
• Line 5: (Optional) Replace spreadsheet data values.
  
  

Notes

• ValueInputOption=USER_ENTERED means that cell values will be parsed as if the user typed them into the UI. If you prefer to insert values as-is, then use the value RAW instead.
• Access and refresh tokens are stored in Windows environment user variables. Be sure to not expose these variables to anyone.