aggregate

Visualisation powered by the Sheets API - griddysheets.firebaseapp.com

Introduction

Many people will be familar with the Google Trends Screensaver: It’s an eye-catching way of showing trending searches.

It set me thinking about how to build something similar for displaying information from my own feeds. A specific use case in mind was data from the Search Query Performance Report. This data shows AdWords advertisers the queries that are resulting in their ad impressions. This can make a great way of visualising what queries are leading to ads being shown.

I have also been using the Sheets API at work, and wanted to find a reason to play with it outside of work.

Using Sheets as an aggregator

A key reason why the use case I mention is interesting in the context of Sheets is that Sheets can be used as an aggregation platform: Advertisers will often have many accounts. By using Sheets, I can have each account write the Search Query Performance Report to separate sheets in a single document, and then retrieve all the data with a single call to the Sheets API.

Obtaining Search Query Performance Report data

The easiest way to obtain SQPR data is through AdWords Scripts. Some simple scripts allow each account to write the data out to a single spreadsheet.

aggregate

Fetching data via the Sheets API

Having written data from all accounts to a single Sheets file, pulling that data from JavaScript is a single call:

gapi.client.sheets.spreadsheets.get({
  spreadsheetId: spreadsheetId,
  includeGridData: true
}).then(updateSheetData, handleFetchError);

The key here to ensure that all data is retrieved from all sheets is the includeGridData property being set.

Visualisation

To visualise the data, I built on the Web Starter Kit to build an app which works as well on a PC or big screen as it does on a mobile device.

mobile

Details

The project is available on github. Consult the README.md file for details on setting it up. It seems to work on Chrome, other browsers may very - hey I’m not a web developer!

I have published my version at griddysheets.firebaseapp.com