Last updated: 2021-05-19
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
01. Create Sheet
02. Insert New Row
03. Append New Row
04. Append New Column
05. Update Spreadsheet Row Values
06. Update Spreadsheet Column Values
07. Clear Spreadsheet Values
08. Copy Sheet
09. 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)
2. Click on Create service account.
3. Under Service account details, type a name, ID, and description for the service account, then click Create.
4. Under Service account permissions, select the IAM roles as 'Project Owner' to grant to the service account, then click Continue.
5. Optional: Under Grant users access to this service account, add the users or groups that are allowed to use and manage the service account.
6. After service account is created, open the service account click on "Edit" then click "Add Key" under "Keys", then click "Create New Key".
7. Make sure the key type is set to JSON and click Create.
8. Click Close > Save.
9. Then click on "Domain-Wide Delegation" and then tick "Enable G Suite Domain-wide Delegation".
10. 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
2. In the list of APIs, search and click Google Sheets API.
3. Click on ENABLE to enable Google Sheets API
Step 3: Assign OAUTH Scopes for Google Sheets API
1. Go to Admin consol e https://admin.google.com/. From the Admin console, go to Home > Security > API controls.
2. Under Domain wide delegation, click Manage Domain Wide Delegation.
3. On the Manage domain wide delegation page, click Add new.
4. Enter the client ID of the service account or OAuth2 client ID of the app.
5. Under the OAuth Scope, add each scope that the application can access.
6. Click Authorize
Provide the following OAuth scopes using the above s teps
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.