Importing and Exporting Google Spreadsheets with Treasure Data

Spreadsheets: Business People’s Data Warehouse?

Let’s admit it. Spreadsheets are not going away.

Despite various business intelligence vendors’ campaigns against Excel and similar programs, spreadsheets continue to be the most popular business intelligence tool. In fact, they are more than a BI tool: Because they are so accessible for business people (compared to IT-controlled, actual databases), they end up becoming the source of truth for various business metrics.

Google Spreadsheets are especially convenient in this regard because collaboration and revision tracking are built in. Excel pros may bemoan the lack of keyboard shortcuts, but sharing a Google Spreadsheet is far more manageable than passing around an Excel file over email. We might go as far as saying that Google Spreadsheets are the cloud data warehouse for business people.

So, if your business KPIs rely on Google Spreadsheets, you are not alone. Many of Treasure Data’s early customers specifically asked for the feature to output query results into Google Spreadsheet because they wanted to share query results with business people who aren’t familiar with SQL.

But what about the other direction? Can Treasure Data import data from Google Spreadsheets?

Enriching Big Data with (Small) Spreadsheet Data

Importing data from Google Spreadsheets into Treasure is not as silly as you might think. Here are some real use cases from our customer prospects:

1. Google Analytics/Adwords/AdSense integration: Because Google Spreadsheet is deeply integrated with the rest of Google’s ecosystem, it ends up becoming the data hub for Google-generated data, especially Adwords and Analytics.
2. Taking snapshots of business metrics: The marketing team uses Google Spreadsheets as their business data warehouse, and the data engineering team is asked to store daily snapshots of these spreadsheets and prepare them for trend analysis.

Once Google Spreadsheet data is available inside Treasure Data, it can be joined and correlated with higher-volume product usage data coming from web/mobile/server and Internet-enabled devices.

Google AppScript + Treasure Data API

How do we go about doing this? Google Spreadsheet supports scripting via AppScript, a JavaScript implementation with additional APIs specific to Google Apps. Because Treasure Data has a simple API for importing data, we can write a simple AppScript script and schedule them to run inside a Google Spreadsheet.

The script looks as follows:

1. To use the script, replace ‘YOUR_API_KEY’ with your Treasure Data API Key. GoogleSheet2TD is the function that you want to call.
2. This script copies the data in the currently active sheet, assuming that the data is laid out as follows:
   1. Row 1 is the header, starting on A1.
   2. Subsequent rows are the body data, until it finds the row where the first column is empty.
3. By default, the name of the spreadsheet is used as the database name (normalized to be a valid Treasure Data database name). To specify a different database name, update CONSTANTS.databaseName.
4. By default, the name of the sheet is used as the table name (normalized to be a valid Treasure Data table name). To specify a different table name, update CONSTANTS.tableName.

Seeing is believing, so here’s a quick screencast of what it looks like.

What’s Next?

Let us know how you use your data in Google Spreadsheet. The above script is obviously an early prototype, and we’d love to get your feedback to help us evolve!