When integrating data from a Google Sheet in Flowics Graphics you usually do it with the Google Sheet Data Provider but that means that every time you update the data you have to do a manual synchronization in the Flowics Remote.
This article will explain an alternative to this workflow so that with the help of the JSON Push Connector you can synchronize the data directly from the Google Sheet.
Generating the graphic Package with the JSON Push connector
The first thing you need to do is define the JSON format with which you are going to work. The following example is made with a very simple JSON with only two values since you will be controlling a Scorebug and will only get the Home and Away scoring from the Google Sheet.
The JSON is as follows:
{ "Home": 1, "Away": 2 }Once you have the JSON format ready, you have to follow the next steps:
Create a new graphics package.
Add the JSON Push Connector
You are going to see a screen that says "Complete the Dataset settings", there you have to paste the JSON format that you had previously prepared.
And then click on "Generate from example". This will automatically generate the schema to be used by the connector.
When creating the connector you will receive the Push instructions that you will need later to send the data from the Google Sheet.
Learn more on how to add a JSON Push Data Connector here.
Generating the Google Sheet with the necessary Scripting to send data to the JSON Push Connector
The next step will be to create the Google Sheet with the data that you are going to send to the JSON Push and also add the scripting so that this data automatically reaches our graphic.
The steps are as follows:
Create a new Google Sheet.
Then give the format to the sheet and assign the data.
Now you are going to add the Scripting. For that, what you need to do is click on the "Extensions" option in the menu above and select the "Apps Script" option.
Once in the code editor, paste the following code. It has two functions: one for the home score and one for the away score. Take into account that some details must be customized.
//********************************************************************************* function ScoreUPHome() { const apiUrl = "Here you need to insert the URL that you get from the Push Instructions"; var val1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Name of your Sheet").getRange('B2').getValue(); var newval1 = val1 + 1;SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Name of your Sheet" ).getRange('B2').setValue(newval1); var val2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Name of your Sheet" ).getRange('B6').getValue(); //Make a POST request with a JSON payload. var data = { 'Home': newval1, 'Away': val2, }; var options = { 'method' : 'post', 'contentType': 'application/json', // Convert the JavaScript object to a JSON string. 'payload' : JSON.stringify(data) }; UrlFetchApp.fetch(apiUrl, options); } //********************************************************************************* function ScoreUPAway() { const apiUrl = "Here we need to insert the URL that we get from the Push Instructions" ; var val1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Name of your Sheet" ).getRange('B6').getValue(); var newval1 = val1 + 1;SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Name of your Sheet" ).getRange('B6').setValue(newval1); var val2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Name of your Sheet" ).getRange('B2').getValue(); //Make a POST request with a JSON payload. var data = { 'Home': val2, 'Away': newval1, }; var options = { 'method' : 'post', 'contentType': 'application/json', // Convert the JavaScript object to a JSON string. 'payload' : JSON.stringify(data) }; UrlFetchApp.fetch(apiUrl, options); }
Understanding the code
API Url: You are registering the variable that will contain the URL of the endpoint that we take from the JSON Push. Basically, it is to know where to push the data.
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Name of your Sheet" ).getRange('B6').getValue(): This function allows you to read the value assigned to a specific cell from the Sheet that you declare.
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Name of your Sheet" ).getRange('B6').setValue(): This function allows you to set a value to a specific cell from the Sheet that you declare.
Data: In this variable, you are going to register the JSON that you are going to send to the connector. In this case, you are reading the values from the cells of the Google Sheet.
Options: In this variable, you are defining the options used by the URLFetchApp command, here is the definition of each one:
contentType: String the content type (defaults to 'application/x-www-form-urlencoded').
method: String the HTTP method for the request: get, delete, patch, post, or put. The default is get.
payload: String the payload (that is, the POST body) for the request. Certain HTTP methods (for example, GET) do not accept a payload. It can be a string, a byte array, a blob, or a JavaScript object.
fetch(url, params): Makes a request to fetch a URL using optional advanced parameters. This works over HTTP as well as HTTPS.
5. Once the functions have been successfully completed you have to save the script and execute it.
You will be asked for authorization you need to:
Click on Review permissions
Select the account you are using
Click on Advanced
Go to Thenameofyourscript (Unsafe)
Click on Allow
6. Now you need to add a couple of buttons to assign the functions you just created to them. To add the buttons, back to the Sheet and click on the Insert menu and then Drawing.
7. Select the Bevel type from Shapes to create a button type.
8. Put a text on top of it and then save and close.
9. Follow the same process to add a second button.
10. Once you have the two buttons, right click on the button, and from the Menu select the Assign Script option.
11. Assign the corresponding script to each button. For that, you need to write the name of the function.
Connecting the data in the Flowics Graphics
Once you have received the data in your JSON Push connector, you just have to create the graphic and do the binding to the corresponding data.
For that you need to:
From your Graphics Package, add a couple of texts to connect them to the JSON Push data provider.
Bind the texts.
Your graphic will look like the image below:
3. Publish the graphic package and proceed to test the script from the Google Sheet. Every time you click the button, a goal must be added to the corresponding team.
