Braze tip #5: how to use Connected Content to read from Google Spreadsheet

published on 07 March 2023

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”

image-r0bzo

Next, copy your Sheet ID, which is the following section of the sheet’s URL

image-0due3

2. Tab

Copy the tab name from where you want to read your data. In this case its “users”

image-wwec2

3. Sheet Range

Here we must input the sheets columns which we’ll want to read. In this case its the “a:b” columns.

image-a0ak6

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:

image-fiutq

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:

image-xij0v

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.

image-ps2zy

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.

image-z7lhs

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:

image-0eroo

The node we want to access data is determined in the first bracket

image-8xd65

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.

image-ni5pv

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.

image-egino

For example, by using that code we would obtain the following Push Notification :

image-g7n87

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!

Read more

Make your website with
Unicorn Platform Badge icon