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”
3/ Choose APIs & services => Library and choose Sheets and enable it
4/ Choose credentials Define an OAuth client ID with type Desktop Application
You need to set the scope to :
https://www.googleapis.com/auth/spreadsheets and https://www.googleapis.com/auth/drive
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
6/ Define a REST Client resource in a magic xpi project
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
Sign with you gmail account then you will be redirected to a page and choose go to Spreadsheet at the bottom
And confirm the access
Define Paths that you want to interact with the API (use {} to use parameter)
You can use Google Workspace explorer ( Sheet API) to test and to get the different paths (https://developers.google.com/workspace/)
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
Set {endpath} parameter to 1KSrbWrBezXI437ZgdJAozE8tssdj6OtRnJyimsBwRAw/values/A2%3AB (<sheetid>/values/<range>)
After flow execution, you should receive the result in a a json format
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
You should receive this type of JSON response
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
10/ Relevant links