Skip to main content

How to collect the output of a visualization in a Google Sheet

Flourish Team
Updated

Currently, our External write module has been added to our premium Calculator template. Get in touch to let us know which other templates you would like us to add it to.

In this article

Some Flourish templates aren't as much focused on data visualization as they are focused on interactive content. Examples of this are our Quiz, Calculator or Draw the line templates, where it might be of interest to collect responses in order to keep a record for internal analysis or to visualize the output. Our External write module makes this possible. 

While the module can write to any public storage endpoint, this help doc focuses on writing to Google Sheets, also providing an integration.

Why Google Sheets?

Google Sheets offer a way to write data to an external source without authentication but with a reasonable level of protection. The writer doesn't directly write to the sheet but posts data to a web app that triggers the Google Sheet's own Apps Script which has read/write access to the sheet it runs on. Google Apps Script runs on Google servers and is similar to VBA in Excel being able to manipulate the respective spreadsheet it works on.

Using a Google Sheets solution has the additional benefit of using a popular tool which fits into other Flourish workflows like the Live CSV functionality. For example, a Flourish user can store data from a Calculator visualization in a Google Sheet which feeds another visualization via Live CSV.

Setup steps

1

To send data from a Flourish template to a Google Sheet, you will first need to ensure that the template has the External Write module installed. You can do this by locating the External Write settings in the settings panel.

2

Creating a Google sheet

First, create a new Google Sheet and give it a name. There's no need to change any Share settings unless connecting the sheet with the Live CSV functionality (more info in this help doc).

3

Adding the Google Apps Script

Go to Extensions and hit Apps Script, which will open the Google Apps Script IDE (Integrated Development Environment). The Google Apps Script IDE is a web-based environment where you can write, edit, and manage your Google Apps Script projects. It provides a user interface and tools for writing and debugging scripts, as well as managing project files and dependencies.

  1. Give it a name – Flourish External Write in this example – delete everything in the Code.gs file.
  2. Copy the contents of the following Apps Script code and paste it into the Code.gs file.
  3. This code will receive the data externalWrite sends off, validate it and add it to a sheet called "responses" by default - which you can change at the top of the Apps Script code:

4

Deploying the Google Apps Script

Next, deploy the app as a New deployment:

  1. Select the type Web app:

  2. Name the deployment. Google calls this a HEAD deployment, which you can use as a sensible name. Also set the Who has access setting to Anyone:
  3. Step through the authorization process for the Apps Script, so the script can read and write to your Google Sheet.

  4. Eventually, the New deployment screen will show the web app's URL that can be copied:

4

Entering your visualization settings

In your Flourish visualization:
  1. Enable External write in the settings
  2. Paste in the web app's URL into the Storage endpoint setting:

  3. externalWrite will now be able to write data from the template to the assigned Google Sheet.
5

Safety

Your Google Sheet is now a web app exposing a public endpoint clients can write to programmatically. In order to safeguard against random data sends the Google Apps Script always checks for the sender's hostname to be part of a list of host names Flourish typically uses.
A further safety measure is to allow your sheet to only receive data from published visualisations with the unique chart ID your visualization is assigned.
In order to add this:
  2. Go to its public URL and take note of its chart ID in the URL.
  3. Go to your Google Sheet, click Add/Edit chart ID and add the chart ID.

    TIP: If you don't see the Flourish menu appear in your Google Sheets menu bar, try refreshing the page.

  4. You can also remove your chart ID with the menu field below or check if you have a chart ID set / what chart ID you have set in the Info menu field above.

Related articles