How to handle Google SpreadSheet with Magic xpi using REST Google API
1/ Connect to Google Developer Console (https://console.cloud.google.com) with your gmail account
2/ Create a new project « SpreadSheet »
![](https://france.magicsoftware.com/blog/wp-content/uploads/2024/09/GoogleSheets2-1024x278.png)
3/ Choose APIs & services => Library and choose Sheets and enable it
![](https://france.magicsoftware.com/blog/wp-content/uploads/2024/09/GoogleSheets4-1-1024x420.png)
![](https://france.magicsoftware.com/blog/wp-content/uploads/2024/09/GoogleSheets5-1024x392.png)
4/ Choose credentials Define an OAuth client ID with type Desktop Application
![](https://france.magicsoftware.com/blog/wp-content/uploads/2024/09/GoogleSheets5_-1024x366.png)
You need to set the scope to :
https://www.googleapis.com/auth/spreadsheets and https://www.googleapis.com/auth/drive
![](https://france.magicsoftware.com/blog/wp-content/uploads/2024/09/GoogleSheets5__-1024x445.png)
![](https://france.magicsoftware.com/blog/wp-content/uploads/2024/09/GoogleSheets6-1024x311.png)
Keep the client Id and ClientSecret and the call Back URL (http://localhost)
5/ Check with Postman that you can interact with a Google Sheet.
Fill all the relevant informations to get the Access Token and execute a Put operation
![](https://france.magicsoftware.com/blog/wp-content/uploads/2024/09/GoogleSheets8.png)
![](https://france.magicsoftware.com/blog/wp-content/uploads/2024/09/GoogleSheets9-1024x628.png)
6/ Define a REST Client resource in a magic xpi project
![](https://france.magicsoftware.com/blog/wp-content/uploads/2024/09/GoogleSheets10-1-1024x603.png)
Call Back URL must be in our case : http://localhost
Fill the Client id and Client Secret.
Set the Authorization URL to : https://accounts.google.com/o/oauth2/auth
Set the Access Token URL to : https://oauth2.googleapis.com/token
Set the scope to : https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/drive
Click on Access Token to acquire the Token
![](https://france.magicsoftware.com/blog/wp-content/uploads/2024/09/GoogleSheets11.png)
Sign with you gmail account then you will be redirected to a page and choose go to Spreadsheet at the bottom
![](https://france.magicsoftware.com/blog/wp-content/uploads/2024/09/GoogleSheets12.png)
And confirm the access
![](https://france.magicsoftware.com/blog/wp-content/uploads/2024/09/GoogleSheets13-1024x669.png)
![](https://france.magicsoftware.com/blog/wp-content/uploads/2024/09/GoogleSheets14-1024x223.png)
![](https://france.magicsoftware.com/blog/wp-content/uploads/2024/09/GoogleSheets15-1024x689.png)
Define Paths that you want to interact with the API (use {} to use parameter)
![](https://france.magicsoftware.com/blog/wp-content/uploads/2024/09/GoogleSheets17-1024x577.png)
You can use Google Workspace explorer ( Sheet API) to test and to get the different paths (https://developers.google.com/workspace/)
![](https://france.magicsoftware.com/blog/wp-content/uploads/2024/09/GoogleSheets16-1024x616.png)
Google Sheet API specification can be found at https://sheets.googleapis.com/$discovery/rest?version=v4
7/ To get a Google SpreadSheet, Drag and Drop a REST Client connector
Configure the connector to GET a SpreadSheet
![](https://france.magicsoftware.com/blog/wp-content/uploads/2024/09/GoogleSheets18-1024x596.png)
Set {endpath} parameter to 1KSrbWrBezXI437ZgdJAozE8tssdj6OtRnJyimsBwRAw/values/A2%3AB (<sheetid>/values/<range>)
![](https://france.magicsoftware.com/blog/wp-content/uploads/2024/09/GoogleSheets19-1024x440.png)
After flow execution, you should receive the result in a a json format
![](https://france.magicsoftware.com/blog/wp-content/uploads/2024/09/GoogleSheets20-1024x525.png)
8/ To send data to a Google SpreadSheet
Build a json request in the format below (use a datamapper for this step)
{« values »: [[« ALEM », »JOHN »],[« DAVID », »ISNER »],[« MAHER », »METHENY »],[« ROSS », »HARRIS »]]}
Configure the REST Client connector for PUT operation and set {upload} parameter to 1KSrbWrBezXI437ZgdJAozE8tssdj6OtRnJyimsBwRAw/values/A2%3AB (<sheetid>/values/<range>)
Pass the Blob Json request to DataBlob
![](https://france.magicsoftware.com/blog/wp-content/uploads/2024/09/GoogleSheets22-1024x552.png)
You should receive this type of JSON response
![](https://france.magicsoftware.com/blog/wp-content/uploads/2024/09/GoogleSheets21-1024x768.png)
![](https://france.magicsoftware.com/blog/wp-content/uploads/2024/09/GoogleSheets23.png)
9/ To clear data, do a POST with this parameter in the URL
https://sheets.googleapis.com/v4/spreadsheets/<SpreadSheetId>/values/<Range>:clear
with empty json {}
example fill the {endpath} parameter with : 1KSrbWrBezXI437ZgdJAozE8tssdj6OtRnJyimsBwRAw/values/A4%3AB:clear
and leave the DataBlob empty
![](https://france.magicsoftware.com/blog/wp-content/uploads/2024/09/GoogleSheets25.png)
![](https://france.magicsoftware.com/blog/wp-content/uploads/2024/09/GoogleSheets24-1024x596.png)
10/ Relevant links