Overview

Perform operations on Google Sheets via the Workflow Automator


Description

Orchestration apps let you automate repeatable tasks and actions that span across a diverse set of systems and applications using workflows. The list of actions supported for this app include:

Sheet Management

  1. Create Sheet

  2. Insert New Row

  3. Append New Row 

  4. Append New Column

  5. Update Spreadsheet Row Values

  6. Update Spreadsheet Column Values

  7. Clear Spreadsheet Values

  8. Copy Sheet

  9. Delete Sheet

  10. Delete A Row

  11. Delete A Column

  12. Delete Multiple Rows

  13. Delete Multiple Columns


Spreadsheet Management

  1. Get Spreadsheet


Prerequisites

To install and authenticate the app you need to provide the following input:  



1. Client Email 

2. Private Key

3. Private Key Id

4. Email Id (The Gsuite account email address)

Step 1: Create a Service Account

Set up a Service Account project in the Google API Console.

  1. Create a new project (or select an existing one)

  1. Click on Create service account.

  1. Under Service account details, type a name, ID, and description for the service account, then click Create.

  1. Under Service account permissions, select the IAM roles as 'Project Owner' to grant to the service account, then click Continue.

  1. Optional: Under Grant users access to this service account, add the users or groups that are allowed to use and manage the service account.

  1. After service account is created, open the service account click on "Edit" then click "Add Key" under "Keys", then click "Create New Key".

  1. Make sure the key type is set to JSON and click Create.

  1. Click Close > Save.

  1. Then click on "Domain-Wide Delegation" and then tick "Enable G Suite Domain-wide Delegation".  


  1. Save the downloaded JSON key.

Your new public/private key pair is generated and downloaded to your machine and it serves as the only copy of the private key. You are responsible for storing it securely. If you lose this key pair, you will need to generate a new one.

Note: The Client Email, Private Key and Private Key Id are obtained from Service account JSON file downloaded.

Step 2: Enable Google Sheets API

  1. Open your project in the API Console. Click on ENABLE APIS AND SERVICES

  1. In the list of APIs, search and click Google Sheets API.

  1. Click on ENABLE to enable Google Sheets API


Step 3: Assign OAUTH Scopes for Google Sheets API

  1. Go to Admin consolhttps://admin.google.com/. From the Admin console, go to Home > Security > API controls.


  1. Under Domain wide delegation, click Manage Domain Wide Delegation.


  1. On the Manage domain wide delegation page, click Add new.


  1. Enter the client ID of the service account or OAuth2 client ID of the app.

  2. Under the OAuth Scope, add each scope that the application can access.

  3. Click Authorize


Provide the following OAuth scopes using the above steps

1. https://www.googleapis.com/auth/drive

2. https://www.googleapis.com/auth/drive.file

3. https://www.googleapis.com/auth/spreadsheets

4. https://www.googleapis.com/auth/drive.readonly

5. https://www.googleapis.com/auth/spreadsheets.readonly