Guide: Using Google Sheets through Zapier
Zapier lets you connect to hundreds of apps and services and send data right into Databox (more about our Zapier integration).
Let’s assume that we run a business selling books and book collections to customers. We use a simple Google Sheets to keep track of all the transactions.
And now we want to transform this sample Google Sheet into an amazing, easy-to-consume dashboard with access to essential data and insights on Databox. In this example, we’ll show you how.
For each unique metric you want to pull into Databox, you will have to create a new zap. However, once the first one is built you can duplicate zaps in your Zapier account and make minimal changes to access the new metric.
We have a short video tutorial to show you how to connect Zapier with Databox:
And here is a video on how to handle Google Sheets and Zapier:
Here’s part two: setting up a Zap to get all transaction dimensions into Databox:
If you prefer text and pictures, follow the directions below.
1. Preparing your spreadsheet
To use Google Sheets with Zapier, you need to set up your spreadsheet in a specific way. If you don’t set up your spreadsheet this way, your connection could be compromised.
Zapier’s documentation outlines the way you'll need to format your Google Sheet in order to successfully build the connection. Here are the highlights:
- The first row must have titles for any column headers you want to be able to see in your Zap.
- The second row must have content (data) for any columns you want to be able to see in your Zap
- Copy all of the rows that contain data to another sheet in the same workbook. That way, you can easily add them back when the time comes. For our example, we named our header + dummy data sheet "Databox" and our sheet storing the additional rows "Copy of Databox." Now delete all rows in the sheet named "Databox" except the 1st and 2nd row on the sheet.
- You must have a timestamp row (date + optional: time). You can make sure this is formatted correctly by entering a date and time in your Google Sheet, then going to Format -> Number -> Date Time. Make sure every Date + Time is unique (can't have the exact same data + time in one sheet).
- When you copy and paste data from another program into a spreadsheet, Google Sheets saves the original formatting that will sometimes cause an issue during this integration. To combat this, either type your metrics in directly, or first copy them to an unformatted program then bring them over to your Google Sheet.
- Editing your spreadsheet while a Zap is already live is not recommended. So if you plan on editing your spreadsheet, then turn your Zap off first!
Here is our example worksheet (view it here):
To successfully run and improve my business as an entrepreneur, I need to know the following metrics at any given time:
- Total Revenue (Revenue)
What is the total revenue for my business?
- Revenue by Referrer (Revenue / Referrer)
How much revenue has each referrer brought to my business?
- Revenue by Customer (Revenue / Customer)
How much revenue has each customer brought to my business?
- Revenue by Product (Revenue / Product)
How much revenue has each product brought to my business?
- Revenue by Country (Revenue / Country)
How much revenue has each country brought business?
We will be creating two zaps: one for the single metric (i.e, Revenue), and one for all of the metrics that reference the "Revenue" metric (i.e., Revenue / Referrer, Revenue / Customer, Revenue / Product, Revenue / Country).
2. Extract Data Using Zapier
The first step is to extract this data from Google Sheets and push it to Databox.
We’ll use Zapier for that.
Zapier is a tool for primarily non-technical users to connect their web apps. A connection between two apps is called a Zap. A Zap is made up of a trigger and actions. Whenever the trigger happens in one app, Zapier will automatically perform the actions in another app, in order. In our example, we’ll use Google Sheets as trigger and Databox as an action. Whenever data in the Google Sheet changes, Zapier will make sure these changes are reflected in Databox as well.
In the "Explore" section of Zapier, make sure that "Google Sheets" and "Databox" are both checked. In the "Connected Accounts" section of Zapier, make sure "Google Sheets" and "Databox" are both connected properly (here's how to do it).
We’ll need two Zaps for our example. Both have the same trigger (the revenue column), but different configurations for the Databox action.
Make sure you follow the following steps precisely (if it's not done exactly right, it will not properly connect):
Now we can move on to the Action portion.
3. Setting Up Databox Action
As we mentioned before, Databox will be the Action app. Find your Databox connection and select it. Use “Push Custom Data” as an action on "Select Databox Action" screen.
The next screen is the most important step, because we’ll select which columns should be pushed over to Databox.
- Metric Name: Select “Use Custom Value” from the dropdown list.
- Custom Value for Metric Name ID: Type in any unique name you want for this metric. For our example, we'll name it "Revenue”.
- Value: Select the appropriate column from your Google Sheet (click on icon on the right to load Google Sheet data). In our example, this is the "Revenue [USD]" column. Keep in mind you can select only one column for value!
- Title: To pull in the natural metric (just one column) leave this field empty. We will explain other use cases for this field in our second zap.
- Date / Timestamp: Select the “timestamp” column from your Google Sheet.
- Every Row Has Unique Data: If you have several rows with the same date, you have to choose "Yes each row is unique". This way every row will be pulled, no matter of its date.
Click “Continue” and save this Zap. You can name it anything you'd like. For our example, we’ll name it “Total Revenue.” This Zap is now complete and can be left enabled.
As mentioned before, we’ll need two zaps for this example (one for the natural Revenue value, and one for grouping the rest of the transaction dimensions). Let's get started on our second zap.
Second Zap – Transaction Dimensions
To simplify this second zap, duplicate the first one in your Zapier dashboard. Nothing will change with the trigger app, so click through until the "Edit Template" step in your action app (title of the page is "Set up Databox Custom"). The only thing we'll need to change is the name (e.g. "Revenue by") and the "Title" field. This is where you'll want to push all of the columns grouped by revenue.
To do this in one Zap, we’ll need to use a special character – the pipe
| – to separate these values. We’ll select the “customer”, “email”, “country”, “product” and “referrer” columns from our Google Sheet, all separated with the pipe characters (the different values are selected via the dropdown list on the right).
It should look like this:
Click “Continue” and save this Zap. For our example, we named it “Transaction.” Now turn both Zaps on.
Great! We are almost done.
The last thing we need to do is to trigger some changes in Google Sheets so that the data is pushed into Databox. We can do that by adding the rows we copied out of our initial spreadsheet back in (so we'll be copying the data from the "Copy of Databox" sheet to our trigger "Databox" sheet). Copy all rows with data into the main "Databox" sheet, including the first row with data. In the sheet, the second and third row will be duplicated, but don't worry, the data in second row is just for Zapier test purposes and it won't interfere with your data in Databox.
In your Zapier dashboard, click the drop down next to both zaps and choose "Run." This will trigger Zapier to read the new rows in Google Sheets and send it (action) to Databox.
4. Visualize Data in Databox
Now we have our data in Databox. All you need to do is go in the designer, choose your Zapier connection as the data source, and view the metrics that were pushed from your Google Sheet. From our example, we would have 7 metrics showing up in Databox:
Revenue by (this metric is empty. It's only displayed to be able to select one of the dimensions below)
˪ Revenue / Referrer
˪ Revenue / Customer
˪ Revenue / Product
˪ Revenue / Country
˪ Revenue / Email
If you need help with setting up your Databoard, here is a video showing how we did it.
Here’s our final result:
- My Zap stopped working - Check that there aren't any blank rows in your Google Sheet document (Zapier recognizes blank rows as end of the spreadsheet). If you are deleting or renaming existing rows/columns or resorting the sheet, first turn off the zap and turn it back on after the changes are made.
- Databox isn't showing right data - When trying to push large Google Sheets documents with several hundred rows into Databox, it can happen, that during the trigger not all rows are pushed, so data may be missing. This is because of the limitations in Zapier. To fix this, try to push smaller batches of data at a time (100 rows).
- Always start your Spreadsheet with the first row - Never leave the first row in your Spreadsheet blank, or the pushed data will not be displayed correctly.
- Making changes to your zap - If you want to make changes to the existing Zap, duplicate it (delete the old one) and configure its settings. Again remove all rows in your sheet, except the first two and turn on the Zap. Paste all rows back and run the Zap again, so that the data is then again pushed into Databox.
When encountering problems you can also check your zaps task history to check, if the data was pushed and if so, where it got stuck. For this go into Zapier and Task History
For more information about Zapier visit the Zapier Help Page.