🔁 Import a data from Google Sheet

🔰 Example: https://creator.celestory.io/project/8x5_uqKpY

✨ Export: https://api.celestory.io/play/ZTfV7kb5ch

In a 💨 Data Import, the data catalog is defined by a list of Menus pages allowing you to navigate between your different products or services.



Updating is defined by the fact that cell values from a Google Sheet (Google excel) are directly imported in real time by the App through continuous connectivity.

🔹 To learn more about the creation of Menu:

http://help.celestory.io/fr/articles/4204688-creer-un-menu-et-ses-pages

🔸 To find out how to create this continuous connectivity, go to the JS Import Cell block subgraph in the left side menu of the modules.

⬛ Launch JS Block

This block allows to launch a code written in Javascript. Just copy/paste the code available below:

*

// spreadsheet

const apiKey = celestoryPoints.get('apiKey', '');

const spreadsheetId = celestoryPoints.get('id', '');

const spreadsheetCell = celestoryPoints.get('cell', '');

const spreadsheetSheet = celestoryPoints.get('sheet', '');

// load google apis to access google drive

await loadExternalScript('gapi', 'https://apis.google.com/js/api.js

try {

const cell = await new Promise((resolve, reject) => {

window.gapi.load('client:auth2', async () => {

try {

// Authenticate to google apis

await gapi.client.init({

apiKey,

discoveryDocs: '[https://sheets.googleapis.com/$discovery/rest?version=v4](https://sheets.googleapis.com/%24discovery/rest?version=v4)'

});

// Get spreadsheet range (single cell)

const spreadsheetRange = await gapi.client.sheets.spreadsheets.values.get({

spreadsheetId,

range: ${spreadsheetSheet}!${spreadsheetCell}, // Get the given cell in the given sheet

});

// Get results

if (spreadsheetRange && spreadsheetRange.result && spreadsheetRange.result.values && spreadsheetRange.result.values.length === 1) {

// Spreadsheet & cell was found

resolve(spreadsheetRange.result.values0][0

} else {

// Spreadsheet or cell was not found

reject(new Error(Cannot read cell ${spreadsheetCell} in sheet ${spreadsheetName} in spreadsheet ${spreadsheetId}));

}

} catch (e) {

// An error occured

reject(e);

}

});

});

celestoryPoints.set('value', cell);

} catch (e) {

celestoryPoints.set('error', e.toString());

}

*

or simply copy/paste a Javascript block called Import cell to import another data from another cell.



You can import this data from any spreadsheet of any Google Account.



You only have to fill in the following fields in the JS block editing window:

◾ apiKey:

It is the Google account that is the owner of the Spreadsheet that must click on that link

Leave Quickstart as the default, then click Next:



Finally copy your API Key and paste it into the block.



Then you give the corresponding apiKey.

◾ id:

This is the id of the spreadsheet. In this example, its url is indicated on the link 2 below:

So the id of link 2 is:

1rd5oiw7rVUNVS2dKSV8sy_jemSyBjMR3eTEIzngYo6o

◾ cell:

This is the name of the cell you want connect to be able to modify it afterwards and

import it automatically into the app.

Ex: B2

It will be necessary to create as many JS "Cell Import" blocks as there are cells to connect.

◾ sheet:

This is the leaflet (the tab at the bottom of the Spreadsheet) on which is the cell to be

import. Be careful to write your name correctly.

The ⬛ JS block also consists of two outputs and two value points:

🔹 First the default output taken if the code works.

🔹 If the code doesn't work, the stream is sent back to catch.

🔹 Finally the error point will indicate the nature of the error in English.

That's why this value point is directly linked to the unknown in the Alert block that follows, in order to give an immediate detail in case of switching to the "catch" output.

🔹 Finally, of course the value point is the value extracted from the cell targeted by the JS block.

This is why it is connected to the incoming value of a block 🟦 Assignment, in order to associate this value with that of a variable that the application will remember.

Updated on: 29/03/2021