Integrations

Google Sheets

28min

PROCESIO x Google Sheets
This documentation is outdated. For the up-to-date version, visit: https://docs.procesio.com/integrations/oauth2-google-sheets﻿
﻿
💡 Imagine automating your work processes and workflows with PROCESIO and having all the data and information automatically transferred and updated in your Google Sheets. This can save you a lot of ⏰ and effort, and make your life a whole lot easier!

📊 For example, let's say you're running a small business and you need to keep track of your inventory. You can use PROCESIO to create an automated workflow that updates your inventory every time a new order is placed. And by integrating PROCESIO with Google Sheets, you can have all that data automatically transferred and updated in a spreadsheet that you can access from anywhere, at any time.

📈 But that's not all! You can also use Google Sheets to visualize and analyze the data collected by PROCESIO. For example, you can create charts and graphs to track sales trends or use conditional formatting to highlight low inventory levels.

🤖 And the best part? You don't need to be a tech whiz to integrate PROCESIO with Google Sheets. The process is straightforward and easy to follow, and you can find lots of resources online to guide you.

💻 What are you waiting for? Put your thinking hats on and let's see the configurations we need to tackle.
Prerequisites:
A PROCESIO account and a Google account
﻿Postman﻿
Google Sheets API  - Project & Credentials Creation
To POST in Google Sheets, we'll need an OAuth Access Token, API keys only work for GET requests.
First, we are going to create a new project in the Google Cloud Console for API Services: 
﻿https://console.cloud.google.com/apis/dashboard﻿
﻿
Make sure to select the newly created project from the project selector in the top left corner.
We now need to enable Google Sheets API. Here’s where you can enable it from.
﻿
Once Google API is Enabled, click the Manage button.
﻿
Proceed with creating a Credential by clicking the Create Credentials button in the top right corner.
﻿
Select Google Sheets API from the Select an API drop-down and then hit Next.
﻿
Click Add or Remove Scopes to select the scopes for this credential.
﻿
Filter by API: Google Sheets API, select the scopes from the screenshot and press Update.
﻿
You can only Filter by Google Sheets API, if you enabled it.
Hit the Save and Continue button to go further.
﻿
Fill in the OAuth Client ID details as below.
Application type ➜ Web
 application
Name ➜ Give a name to the credential
 (PROCESIO will be ok for now)
Authorized redirect URIs
URIs 1 ➜ https://procesio.com/
URIs 2 ➜ https://procesio.app/
Don't forget to click Create after you fill in the details.
﻿
Hit Done and you're done with the credential part for now.
﻿
Obtaining the OAuth Authentication Token
To generate an OAUTH token, we'll need 3 things:
﻿client_id - part of the Google Sheets API Credential we just created.
﻿client_secret - also part of the Google Sheets API Credential we just created.
﻿code - secret code valid for 60 seconds after obtaining it.
Finding client_id and client_secret﻿
From within Google API Console navigate to the credential tab, and click the credential you've just created. 
Make sure you have the right project selected.
﻿
Now copy the values for Client ID and Client secret and save them for later.
﻿
Preparing the request
Now that we have the client_id and client_secret ,we only need to get the code so that we can use it to receive the OAuth token. The code has a 60 second validity so before obtaining it, we'll prepare a request in Postman so that we can just Obtain the code & Send the request straight away.
Open Postman and configure a POST request with x-www-form-urlencoded body type.
POST: https://accounts.google.com/o/oauth2/token
Set the following key-value pairs
grant_type ➜ authorization_code
﻿code ➜ here you need to add the code we obtain in the next step﻿
﻿client_id ➜ Value of Client ID that you saved.
﻿client_secret ➜ Value of Client secret that you saved.
redirect_uri ➜ https://procesio.com/
access_type ➜ offline
﻿
Getting the code﻿
The request is ready to be sent and only needs the code. To obtain the code you need to:
Build an URL as per below:
﻿https://accounts.google.com/o/oauth2/auth?access_type=offline&prompt=consent&client_id=YOURCLIENTID&response_type=code&scope=https://www.googleapis.com/auth/spreadsheets&redirect_uri=https://procesio.com/﻿
In the green highlighted section you will insert the value of Client ID just like in the request. 
Copy the URL & paste it into a browser. This should prompt you to select a corresponding account to which resources will become accessible via OAuth.
Make sure to sellect the right account.
﻿
Once you logged in, press the Allow button
﻿
After allowing, you’ll be redirected to a screen that should look something like below. This new URL (on which you were redirected) contains the code to be used in POSTMAN for obtaining the Access Token as well as the Refresh Token. (We have about 60 s until the code expires)﻿
﻿
Complete your Postman request by adding the code and hit Send.
﻿
﻿
You have now obtained the access token and the refresh token. Make sure to save them 💪
The access token expires in one hour so a refresh mechanism needs to be put in place.
More on how to refresh the token here.
Using PROCESIO with Google Sheets (Append Demo)
Build a basic process with Start ➜ Call API ➜ Stop
Create a Rest API Credential with the base URL: https://sheets.googleapis.com/v4/spreadsheets
We are going to skip testing the credential.
﻿
Create some variables in your process
Name
Type
Default value
Single value
Set as
Token
﻿String﻿
Bearer access_token﻿
✔
-
SheetsAPIStatus
﻿Integer﻿
﻿
✔
Output
SheetsAPIBodyOutput
﻿Json﻿
﻿
✔
Output
﻿
﻿
Configure the Call API Action as follows:
Verb ➜ POST
MethodName ➜ /spreadsheets/{SheetID}/values/A1:append
Query Params:
Key
Value
valueInputOption
USER_ENTERED
Headers:
Key
Value
Authorization
Token
Body:
JSON

{
    "range": "A1",
    "majorDimension": "ROWS",
    "values": [
        [
            "Sample Value"
        ]
    ]
}

{
    "range": "A1",
    "majorDimension": "ROWS",
    "values": [
        [
            "Sample Value"
        ]
    ]
}
﻿
﻿
Run the Process & Check your spreadsheet.
More details on the Google Sheets API can be found here.
Stay tuned as we are working on preconfigured Call API Actions with Google Sheets.