How to connect data to Google Sheets using Data Explorer

Overview

This guide explains the steps to create automated connections that send data stored in Alli to a Google Sheet. Within your Google Sheet, this data can be used for any data needs such as reporting, pacing, and much more.

There are limits in place by Google on total rows in a workbook which can cause these exports to fail unexpectedly.

See the FAQ question “Why won’t my sheet load any data?” below for more information.

Before Getting Started

Prior to getting started with these instructions, ensure that:

You have a Google Sheet with a tab designated to house the data coming from Alli. The tab name is important and you will want to avoid editing it.
You have a report created with the columns you want available in Google Sheets: https://help.alliplatform.com/reporting/Working-version/how-to-explore-your-data-and-create-create-a-repor
You have granted edit access to your Alli client’s Google Service Account. This guide shows where to find the Google Service Account: https://help.alliplatform.com/central-settings/Working-version/how-to-find-an-alli-client-s-google-service-accoun

Instructions

To begin, navigate to your previously created report by going to Data → Reports.

Next, ensure your report is set up with the columns and the date range you want brought into your Google Sheet. You can click Preview to see what the data looks like. When ready, Save and Publish your report.

With the report ready, navigate to the 3-dot menu → Export to… → Google Sheets

Complete the fields within the pop-up and click Save & Run. This will kick-off the sync and data will be available in your designated tab in a few minutes. You will receive an email once the export is complete.

Verify that the data is showing up as expected. If changes are required, work back through these steps. If changes are no required, you are all set and the data will refresh in your Google Sheet based on the schedule you provided.

Frequently Asked Questions

The guide walks through how to do this using the Explorer Report Builder interface, can I use a Custom SQL report?

Yes. Similarly, once you’ve created your Custom SQL report, you can find the same Google Sheets export option within the 3-dot menu.

Are there any limitations to be aware of?

Within Alli there are no set limits, however there some things to keep in mind. First, the more data you include in your report, the longer it will take to load. Second, Google Sheets does have limitations in that you can not have more than 10M cells active. Additionally, the more data in Google Sheets the more likely it will cause your browser to timeout.

As a best practice, ensure you are only including the columns you need and leverage filters and date ranges to limit the total amount of rows. This will ensure you do not experience any interruptions.

Why do I need to give the Alli client’s Google Service Account edit access to my sheet?

Providing edit access to the Alli client’s Google Service Account allows for Alli to actually write the data to your Google Sheet. Without this access, Alli will not have the ability to put data into your Google Sheet.

I have multiple Google Sheets I want to send to data to, can I use the same Explorer report I created?

At this time, you can only have one Google Sheet export per report. This will likely change in the future as the overall data export feature evolves.

I no longer need the data in my Google Sheet, how can I stop the data from flowing in?

To stop the data from loading to your Google Sheet, navigate to 3-dot menu → Export to… → Google Sheets within your report and click Clear. This will remove your previous configurations and stop the data from loading to your Google Sheet.

Why won’t my sheet load any data?

There can be many causes for this issue, and the best path to resolution will begin with opening an Alli Support request.

One common issue, is hitting the 10M cell limit in place from Google. The way this export works, the sheet being export into is duplicated to preserve the data in case of a failure, which means there should be enough headroom left in the 10M limit for the the sheet to be duplicated. Empty cells are counted as a part of the 10M so removing any empty or unnecessary cells and sheets can help if you appear to be close to this limit.
Currently the export can not be completed to sheets saved as types other than Google Sheet. This includes .xlsx file types that are being viewed in Google Sheets. These can be identified by the green .XLSX tag that will appear to the right of the file’s name.