{"id":1341,"date":"2024-09-11T09:09:21","date_gmt":"2024-09-11T08:09:21","guid":{"rendered":"https:\/\/france.magicsoftware.com\/blog\/?p=1341"},"modified":"2025-03-04T17:47:07","modified_gmt":"2025-03-04T16:47:07","slug":"google-spreadsheet-magic-xpi","status":"publish","type":"post","link":"https:\/\/france.magicsoftware.com\/blog\/magic-xpi\/google-spreadsheet-magic-xpi\/","title":{"rendered":"Google SpreadSheet – Magic xpi"},"content":{"rendered":"\n

Goal<\/strong> : How to handle Google SpreadSheet with Magic xpi using REST Google API<\/p>\n\n\n\n

1\/ Connect to Google Developer Console (https:\/\/console.cloud.google.com) with your gmail account<\/p>\n\n\n\n

2\/ Create a new project \u00ab\u00a0SpreadSheet\u00a0\u00bb<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

3\/ Choose APIs & services => Library and choose Sheets and enable it<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n

4\/ Choose credentials Define an OAuth client ID with type Desktop Application <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

You need to set the scope to : <\/p>\n\n\n\n

https:\/\/www.googleapis.com\/auth\/spreadsheets and https:\/\/www.googleapis.com\/auth\/drive<\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n

Keep the client Id and ClientSecret and the call Back URL (http:\/\/localhost)<\/p>\n\n\n\n

5\/ Check with Postman that you can interact with a Google Sheet.<\/p>\n\n\n\n

Fill all the relevant informations to get the Access Token and execute a Put operation<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n

6\/ Define a REST Client resource in a magic xpi project<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Call Back URL must be in our case : http:\/\/localhost<\/strong><\/p>\n\n\n\n

Fill the Client id and Client Secret.<\/p>\n\n\n\n

Set the Authorization URL to : https:\/\/accounts.google.com\/o\/oauth2\/auth<\/p>\n\n\n\n

Set the Access Token URL to : https:\/\/oauth2.googleapis.com\/token<\/p>\n\n\n\n

Set the scope to : https:\/\/www.googleapis.com\/auth\/spreadsheets https:\/\/www.googleapis.com\/auth\/drive<\/strong><\/p>\n\n\n\n

Click on Access Token to acquire the Token<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Sign with you gmail account then you will be redirected to a page and choose go to Spreadsheet at the bottom<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

And confirm the access<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n

Define Paths that you want to interact with the API (use {} to use parameter)<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

You can use Google Workspace explorer ( Sheet API) to test and to get the different paths (https:\/\/developers.google.com\/workspace\/)<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Google Sheet API specification can be found at https:\/\/sheets.googleapis.com\/$discovery\/rest?version=v4<\/p>\n\n\n\n

7\/ To get a Google SpreadSheet, Drag and Drop a REST Client connector<\/p>\n\n\n\n

Configure the connector to GET a SpreadSheet<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Set {endpath} parameter to 1KSrbWrBezXI437ZgdJAozE8tssdj6OtRnJyimsBwRAw\/values\/A2%3AB (<sheetid>\/values\/<range><\/strong>)<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

After flow execution, you should receive the result in a a json format<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

8\/ To send data to a Google SpreadSheet<\/p>\n\n\n\n

Build a json request in the format below (use a datamapper for this step)<\/p>\n\n\n\n

{\u00ab\u00a0values\u00a0\u00bb: [[\u00ab\u00a0ALEM\u00a0\u00bb,\u00a0\u00bbJOHN\u00a0\u00bb],[\u00ab\u00a0DAVID\u00a0\u00bb,\u00a0\u00bbISNER\u00a0\u00bb],[\u00ab\u00a0MAHER\u00a0\u00bb,\u00a0\u00bbMETHENY\u00a0\u00bb],[\u00ab\u00a0ROSS\u00a0\u00bb,\u00a0\u00bbHARRIS\u00a0\u00bb]]}<\/p>\n\n\n\n

Configure the REST Client connector for PUT operation and set {upload} parameter to 1KSrbWrBezXI437ZgdJAozE8tssdj6OtRnJyimsBwRAw\/values\/A2%3AB (<sheetid>\/values\/<range><\/strong>)<\/p>\n\n\n\n

Pass the Blob Json request to DataBlob<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

You should receive this type of JSON response<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n

9\/ To clear data, do a POST<\/strong> with this parameter in the URL<\/p>\n\n\n\n

https:\/\/sheets.googleapis.com\/v4\/spreadsheets\/<SpreadSheetId>\/values\/<Range>:clear<\/strong><\/p>\n\n\n\n

with empty json {}<\/p>\n\n\n\n

example fill the {endpath} parameter with : 1KSrbWrBezXI437ZgdJAozE8tssdj6OtRnJyimsBwRAw\/values\/A4%3AB:clear<\/p>\n\n\n\n

and leave the DataBlob empty<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n

10\/ Relevant links<\/p>\n\n\n\n

https:\/\/sheets.googleapis.com\/$discovery\/rest?version=v4<\/a><\/p>\n\n\n\n

https:\/\/developers.google.com\/workspace<\/a><\/p>\n\n\n\n

<\/p>\n","protected":false},"excerpt":{"rendered":"

Goal : 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 \u00ab\u00a0SpreadSheet\u00a0\u00bb 3\/ Choose APIs & services => Library and choose Sheets and enable it 4\/ Choose credentials Define an OAuth client ID with type Desktop […]<\/p>\n","protected":false},"author":1,"featured_media":1344,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[43,4,44],"tags":[45,14],"class_list":["post-1341","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google","category-magic-xpi","category-sheets","tag-sheets","tag-magic-xpi"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/france.magicsoftware.com\/blog\/wp-content\/uploads\/2024\/09\/GoogleSheets1.png","_links":{"self":[{"href":"https:\/\/france.magicsoftware.com\/blog\/wp-json\/wp\/v2\/posts\/1341","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/france.magicsoftware.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/france.magicsoftware.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/france.magicsoftware.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/france.magicsoftware.com\/blog\/wp-json\/wp\/v2\/comments?post=1341"}],"version-history":[{"count":65,"href":"https:\/\/france.magicsoftware.com\/blog\/wp-json\/wp\/v2\/posts\/1341\/revisions"}],"predecessor-version":[{"id":2888,"href":"https:\/\/france.magicsoftware.com\/blog\/wp-json\/wp\/v2\/posts\/1341\/revisions\/2888"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/france.magicsoftware.com\/blog\/wp-json\/wp\/v2\/media\/1344"}],"wp:attachment":[{"href":"https:\/\/france.magicsoftware.com\/blog\/wp-json\/wp\/v2\/media?parent=1341"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/france.magicsoftware.com\/blog\/wp-json\/wp\/v2\/categories?post=1341"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/france.magicsoftware.com\/blog\/wp-json\/wp\/v2\/tags?post=1341"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}