ICTworks

Google Sheets can be a powerful tool for real-time monitoring, evaluation, research, and learning. But how can you optimize your setup to work with data that is streaming in from survey forms or other integrations? How can you turn Google Sheets from a tool designed for static data into one that gracefully handles dynamic, real-time data?

1. Add extra rows at the end of your Google Sheet to accommodate new data that streams in from a tool like SurveyCTO.

• Pro tip: You may have to enter placeholder data into the last row of the spreadsheet if your data will be streaming in. Insert two blank rows; add a column, name it “unused”, and in the second blank row put “end” under that column. This creates a space into which new data can publish.

2. Create dashboards on separate worksheets

• Pro tip: Color code your dashboard tabs, and put them first.

3. Formulas like COUNTA() and AVERAGE() are key to overall summary statistics: add them directly to your dashboards.

• Pro tip: Refer to entire columns — but skipping the first header row — with references like COUNTA(data!A2:A) or AVERAGE(data!G2:G)

4. Pivot tables are key to summarizing data: add them as separate worksheets.

• Pro tip: Highlight your entire data worksheet — not just the rows with data! — before adding a new pivot table. That way, new data will be also be included! (But also hide those pesky extra columns and rows for blank values.)

5. Charts make a dashboard come alive: you can create a chart from a pivot table, then cut and paste it into a dashboard.

• Pro tip: Edit the range for the chart’s input, to cover lots of extra rows and columns (e.g., change data!A1:B7 to data!A1:Z600). That way, the chart will include all of the new data as the pivot table expands!

6. Pull columns of qualitative data into your dashboard using the amazing ARRAYFORMULA() function.

• Pro tip: Use UNIQUE() to filter down to unique cells, SORT() to sort, and FILTER() to filter by some other criteria.

7. Exercise control over who can see your dashboards.

• Pro tip: If somebody needs only temporary, read-only access, set their access to automatically expire so that you don’t have to go back to remove it later!

8. Publish your dashboards to the web (example)

• Pro tip: Selectively publish only those charts or dashboard worksheets that you want to share openly.
• Pro tip: Use the “embed” option to get code you can embed in an existing website. If you’re having display issues, try putting <p> tags around the <iframe> embed code.

9. Integrate your data

• Pro tip: Use a service like Zapier or OpenFn to integrate your data with Salesforce, Gmail, Twilio (for SMS), Android, and a host of other platforms.

Try It Yourself

We’ve shared our presentation and resources from MERL Tech 2016 in this Google Folder. You can also review this blog post by Faizan that includes a sample survey form, a sample data set, and a live dashboard.

By Alexis Ditkowsky, Faizan Diwan, and Christopher Robert of SurveyCTO, based on their MERL Tech session, Google Sheets for Real-time MERL, and the blog post Hacking Google Sheets for real-time dashboards (9 tricks).