Braze: how to use Connected Content to read from Google Spreadsheet (Part 2)

4 min readJan 2, 2021

Part 2: Learn 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

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

Tab

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

Sheet Range

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

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/1RO_56gNbP8gXG6bUIaidP1xPEIq7AGodf9Io_COnKoU/values/users!a:b?majorDimension=COLUMNS&key={{key}}

(I’m 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 my 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!

We’ll deepdive into other advanced use cases in following stories!