Learn how to read from Google Spreadsheet API in order to automate your Braze messaging by reading your content directly from a Google Spreadsheet!
How to use the Google Sheets API
We are going to use the following URL in order to read from a spreadsheet.
https://sheets.googleapis.com/v4/spreadsheets/{{sheetID}}/values/{{tab}}!{{sheet_range}}?majorDimension=COLUMNS&key={{key}}
If we look closely, there are 4 variables we must define in order to use this URL
1. SheetID
We need to generate a new Google Spreadsheet, click on this link to do so.
Fill in your data and change the share settings to “Anyone with the link may view”
Next, copy your Sheet ID, which is the following section of the sheet’s URL
2. Tab
Copy the tab name from where you want to read your data. In this case its “users”
3. Sheet Range
Here we must input the sheets columns which we’ll want to read. In this case its the “a:b” columns.
4ñ Key
When we’ve set up the Google API Credentials in the previous section, we’ve copied the API Key’s value.
Using the URL in order to read your spreadhseet’s data
So finally, in this case our URL would be the following:
https://sheets.googleapis.com/v4/spreadsheets/{{sheetID}}/values/{{tab}}!{{sheet_range}}?majorDimension=COLUMNS&key={{key}}
(We're not changing the key’s value in this example for security reasons)
Next just copy this URL into Google Chrome and hit enter. You should obtain a blank page that has your data formated as a JSON:
Awesome! You’ve managed to read a Google Spreadsheet’s data through API!!
How to use this URL in Braze
First set up the Campaign or Canvas in which you’ll be reading from a Google Spreadsheet.
In the message section, set up the local variables “sheetID”, “tab”, “sheet_range” and “key” with the values we’ve looked at before:
In this same message, use Connected Content in order to read from the spreadsheet.
We are going to use the same URL structure as we’ve seen before and we’ll be replacing the corresponding sections with the local variables we’ve just named.
What this code has done is saving the same JSON we’ve seen before into the local variable “data”.
Finally, we can access any value from the spreadsheet by specifying the JSON’s data node and position.
Understanding the JSON
The Google Sheets API groups the in the spreadsheet into nodes, where each Column is contained within a whole node.
Where column A of the spreadsheet is node ‘0’, column B is node ‘1’, column C is node ‘2’ and so on.
In this example:
The node we want to access data is determined in the first bracket
While the second bracket’s value determines the position within the node we want to access.
Where the first line of the spreadsheet is position ‘0’, the second line is position ‘1’, the third line is position ‘2’ and so on.
And that’s it! You’ve managed to dynamically get any spreadsheet’s value into Braze!!!!
This service is dynamic, therefore if you change the spreadsheet values the message sent through Braze will change automatically.
Use case example:
Since we are saving the spreadsheet’s values within local variables, if you want to dynamically read a spreadsheet in a Push Notification title and message, you must use the code we’ve seen in both sections.
For example, by using that code we would obtain the following Push Notification :
Here is the example’s complete code
{% assign sheetID = ‘1RO_56gNbP8gXG6bUIaidP1xPEIq7AGodf9Io_COnKoU’ %}
{% assign tab = ‘users’ %}
{% assign sheet_range = ‘a:b’ %}
{% assign key = ‘xxxxx’ %}
{% connected_content https://sheets.googleapis.com/v4/spreadsheets/{{sheetID}}/values/{{tab}}!{{sheet_range}}?majorDimension=COLUMNS&key={{key}} :cache_max_value 14400 :save data %}
Your surname is: {{ data.values[1][2] }}Awesome! Thats it, you’ve managed to read dynamically your messages from a Google Spreadsheet!
