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
Create Sheet
Insert New Row
Append New Row
Append New Column
Update Spreadsheet Row Values
Update Spreadsheet Column Values
Clear Spreadsheet Values
Copy Sheet
Delete Sheet
Delete A Row
Delete A Column
Delete Multiple Rows
Delete Multiple Columns
Spreadsheet Management
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.
Create a new project (or select an existing one)
Click on Create service account.
Under Service account details, type a name, ID, and description for the service account, then click Create.
Under Service account permissions, select the IAM roles as 'Project Owner' to grant to the service account, then click Continue.
Optional: Under Grant users access to this service account, add the users or groups that are allowed to use and manage the service account.
After service account is created, open the service account click on "Edit" then click "Add Key" under "Keys", then click "Create New Key".
Make sure the key type is set to JSON and click Create.
Click Close > Save.
Then click on "Domain-Wide Delegation" and then tick "Enable G Suite Domain-wide Delegation".
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
Open your project in the API Console. Click on ENABLE APIS AND SERVICES
In the list of APIs, search and click Google Sheets API.
Click on ENABLE to enable Google Sheets API
Step 3: Assign OAUTH Scopes for Google Sheets API
Go to Admin console https://admin.google.com/. From the Admin console, go to Home > Security > API controls.
Under Domain wide delegation, click Manage Domain Wide Delegation.
On the Manage domain wide delegation page, click Add new.
Enter the client ID of the service account or OAuth2 client ID of the app.
Under the OAuth Scope, add each scope that the application can access.
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
Usecases
Now that you've successfully installed the Google Sheets orchestration app, please have a look at the sample use case below to show how the app can be used efficiently.
