re:dash Google Spreadsheet Feedback Loop

This post pulls together the another post I previously made about re:dash and mapping. This is a hack to avoid one of the issues you find when using Google Spreadsheets as as an intermediate data source.

In the map mashup post I used a Google Spreadsheet as the data source, however it created a problem where your spreadsheet wouldn’t automatically updated by re:dash refresh. This post shows you how to fix that by importing your spreadsheet data from re:dash and then pulling it back into re:dash. You need 2 re:dash queries and a spreadsheet.

Query 1

This query that gathers the data for use in your Google Spreadsheet, this one is a normal looking re:dash query. And for whatever reason you still need to transform the data in a Spreadsheet before you can visualize the data.

select count(*) as users, isoCode from locationTable

Now you need to create a link that will allow access to the data from your Google Spreadsheet. Follow these instructions on how to create a CSV URL your Google Spreadsheet can access.

Google Spreadsheet

In the spreadsheet you’re using to transform some of your data you can use the IMPORTDATA() function to pull in data from your re:dash query 1 using the URL we just constructed.

=IMPORTDATA(“https://your.redash.io/api/queries/827/results/217051.csv?api_key=9352fd37f2f4ce923932c9c6328829a5be7093ea3d””)

Your transform could be some calculation or pulling in data from other spreadsheets to append into this spreadsheet.

Query 2

Now you can create your second query that queries the Google Spreadsheet you’ve made and pulls in the transformed data. Take a look at the mashup post for instructions on getting your Google Spreadsheet query setup so you can create your visualization.

Your spreadsheet will update as queried by your re:dash Query 2, there are also setting to ensure that it updates on certain intervals. And your Query 1 can refresh normally to provide the spreadsheet with updated data. You’ve now solved the stale data problem when using spreadsheets.

NOTE: This is a hack, I wouldn’t recommend using this for a long term solution. You should be evolving to move away from this.