From Your Data Warehouse, to Google Sheets
- Published on
Among data practitioners and vendors, a common theme is to bemoan the widespread use of spreadsheets. We fully disagree: working with spreadsheets is popular because they’re extremely effective.
For your stakeholders that work best in spreadsheets, Slight lets you support their workflows directly, maintaining a clear record of the source, and without brittle manual steps. We’ve taken a common workflow and made it both dead simple and safe.
We’ve built an integration with Google Sheets that lets stakeholders create a spreadsheet from a Slight data appSidenote: An app in Slight is what we create from a parameterized query. with a click, automatically recording the source of the data, the time of the run, and what inputs were used. Further, users can change the inputs and re-run the app right from within the sheet, without having to go back to Slight.
Creating a Sheet from Slight: An Example Workflow
We won’t go through how to create an app in this post: we’ll assume you have one alreadySidenote: If you want to create one, see either our short doc or our long tutorial on creating apps. — in typical usage, the user creating the spreadsheet and the app creator won’t be the same.
In short: run an app in Slight → click on “Create Google Sheet” → you get your spreadsheet.
We can also re-run the app directly from within the sheet. With our extension (“Slight App Runner” to navigate there directly) installed and signed into, edit the values in the “Variables and Input” tab and choose one of the running options from the extension menu. We’ll go through these steps in more detail below.
If you prefer, we have a video showing our integration in action (with a different app than the one in this post), including how to get set up:
Running the App
We’ll use a public app, on 311 calls in San Francisco. We’ll change the dates to run from the start of 2009 to the start of 2010, and use five categories. We’ll get something that looks like the image below. We’ve highlighted the “Create Google Sheet” button in orange:
Creating the Spreadsheet
After you click on the “Create Google Sheet” button, you’ll get a confirmation dialog, which also gives you the opportunity to give the spreadsheet a name. This creates the Google Spreadsheet for us, and navigates us to itSidenote: Some browsers block this navigation, but you can still get there by following the link in the success message, or by navigating to your Google docs catalog..
We get three initial tabs (or sheets). The first is the data tab, which will look very similar to the data in the table shown after running the app in Slight (the data is the exact same). The second is the variables tab, which we’ll see and discuss further below. The variables tab is the tab we’ll edit for re-running the app. Finally we have the “App and Run Info” tab, which shows information about the app used to generate the spreadsheet, and records all runs of the app:
Running and Re-Running Our App
As mentioned, Slight’s extension allows us to run and re-run our app from within the Google Sheet. For install and setup details, see our docs, or again our demo video showing the integration in action.
Let’s get the top 10 categories instead of the top 5. To do this, we edit the Value column (column B) for row 2 in the Variables tab, as row 2 is the row for
num_top_categories. This gives us two main options: either we’ll update the original Data tab, or we can create a new tab for the new run. Those are the first and second options in our add-on menu as we can see from the screenshot below. The third option, “Run, update data sheet, and create copy” will also overwrite the data tab, but then will create a snapshot of that tab, so any future overwrites won’t lose that data.
The benefit of overwriting the Data tab is that if we have additional sheets and cells that build on this data — maybe a pivot table, a chart, a data filter, etc. — they can automatically update to the latest data. This depends on your workflow in Google Sheets of course.
As well as giving you the new data, we also add a row to the “App and Run Info” tab, with the time of your run, and what parameter values were used. Now you get tracking of each run without brittle manual steps added on top.
A common workflow in companies today begins with a stakeholder asking a question to data teams, or making a request for some data, resulting in data in a spreadsheet. How does this typical work compare to our solution?
First, the relevant data team finds time to answer the question. Maybe they build a whole dashboard for it. Or they decide it’s not worth creating a heavyweight dashboard, so they just run a query, export the data, and send the data over (and store the query somewhere… or nowhere at all).
The business user puts the resulting file into a spreadsheet to view, analyze, interrogate and/or explore it. The file either was exported from the dashboard (with the classic “export to CSV” option), or was just sent to them directly.Sidenote: E.g. through Slack, email, Dropdox, OneDrive etc.
The user needs the data again, maybe a slightly different cut of it. Trouble begins. In the dashboard version, hopefully the dashboard supports this different cut, else it’ll need another run through with the data team. In the “lightweight” version, they get back in touch with the data team, who ideally have the query on hand; that team edits it as appropriate, and sends a new file (within some reasonable time frame).
The business user takes the new cut and repeats the process of putting it into a spreadsheet. Hopefully nothing goes wrong during these manual steps.
Ideally all steps and queries are recorded so that if something odd pops out, it can all be traced back and sorted. But with files floating around, queries either buried in BI tools or stored somewhere completely different, and very few people having a ton of time on their hands to do extracurricular activies, what is the likelihood this can all be reproduced?
Reconciling data, chasing old queries and logic, remembering which version of a file you uploaded where and when: all great stuff we love to do when we’re busy…Sidenote: 🥴 🥴 🥴. This is not even getting into other teams who don’t use the same data source (BI or files) getting totally disparate results.
What Slight Offers
Compared to the above, Slight solves multiple major pains for spreadsheet users. Firstly, there are no more manual steps needed, and the users get the data exactly as we intended, where and when they need it: no exporting files, no CSVs floating around (with parsing issues to boot), no friction.
Secondly, the data doesn’t get disconnected from the source, so you don’t have to go searching (through email, or your chat archive) for the origin of some spreadsheet showing an issue: where is the query buried that created this, which set of parameters generated this, when did we get sent this file? And this isn’t just for the initial spreadsheet: all the relevant information is already stored in the spreadsheet for each run.
Finally, we know that our spreadsheets users are looking at the same data and query as anyone else using this app: everyone is in sync. A pure spreadsheet interaction isn’t the sole focus of Slight. Our integration makes a lot of spreadsheet workflows significantly easier and with much better repeatability, but the big advantage of our platform is that the Google Sheets integration is just one of the interfaces from your company’s apps: with Slight, regardless of where people run an app — from Python or R, within the website, a graph in Slight, through the API, any of our interfaces, even external clients — they’ll all be accessing the warehouse through the same query, with the same governance. This avoids the mess of people comparing contradictory numbers because they use different queries etc, but then also helps because no one needs to track down how exactly some query/metric is defined/used by other teams at the company.
If any of this sounds up your alley, if you’ve got a bunch of productive spreadsheet users you’d like to support as best you can, getting data exactly where they need it, we’d be delighted to hear from you. Reach out at email@example.com or start your free trial, whichever you prefer.
- Related Articles
What differentiates us, what tradeoffs does our product make? We think our tradeoffs strike the right balance for many data workflows, but this depends on your needs and current painpoints.
Slight's API is up and running. Now anyone who knows SQL can create APIs for their parametrised queries with no extra effort.