When building a data source on Google Sheets in Tableau Desktop 10mb is the max per Google Sheet. However, what if we could connect to more than one Google Sheet at the same time?
Google Sheets works wonders with Tableau Public because it allows for Tableau Public to read data from Google Sheets once per day. This enables everyone the capability to use an online cloud data source to update their Tableau Desktop dashboards.
Introduction
In this blog, we will discuss connecting to multiple Google Sheets in one connection. If “large sheets removed” is not sufficient, and you’re willing to break apart your sheets into multiple sheets manually or with an engineer, we will find this article helpful. We break apart each element, how it works, and explain how your engineer may begin breaking down the solution.
Tableau currently has no built in feature to allow this to happen, however they do have a feature you can setup to make it automatically connect to Google Sheets! Tableau suggests this isn’t possible and the only way to make it work is to use LESS DATA, but what if you have big data?
We built this blog to help you begin the journey of connecting to many sheets. You will want to demo this as a possible solution to show your engineering team to automatically create these Google Sheets (we are here to help too).
Error explained
If you begin connecting to a Google Sheet in Tableau Desktop >10mb, you will see various popups, depending on your operating system, explaining an error has occurred with Google Sheets.
Unable to complete action error message
Did you recently see this error?
Unable to complete action
The Google Sheets service reported an unrecognized error when processing this request.
This file is too large to be exported.
A good question to start asking, “will my data split into other sheets easily?”
In the example below we are going to speak towards an 80mb Google Sheet that will not work in Tableau.
Tech Workaround explained
The Tableau Desktop Wildcard (automatic) feature will capture Google Workbooks (contains google sheets) and google sheets in a workbook(s). It will “automate” building connections to multiple 10mb workbooks or sheets, by establishing a stack of data that will resemble your end goals. Similar to using Union All in SQL.
Stone Working Analogy
If you’re unfamiliar with union in SQL, think of your data stacking on each other like bricks on a brick wall. Data engineers and brick wall experts have similar jobs. They are solving a problem by stacking side by side or on top of each other.
We will focus on a “workbooks” example, where we will stack bricks of data on each other.
Example; there are four regions in our data, each region is <10mbs, about 80mb total.
Four regions:
- SOUTH 10mb
- NORTH 10mb
- EAST 10 mb
- WEST 50 mb*
A Use Case Explaining the Solution
Step 0; union means the sheets need the same column headers. Thanks for learning about unions!
Step 1; build 8 googlesheets… (new workbooks, not new sheets, this works with sheets however I’m using workbooks for now)
Step2; name each google sheet workbook “Table-Demo_EXAMPLE” etc… and you will have the following.
- Table-Demo_SOUTH
- Table-Demo_NORTH
- Table-Demo_EAST
- Table-Demo_WEST_1
- Table-Demo_WEST_2
- Table-Demo_WEST_3
- Table-Demo_WEST_4
- Table123-Demo_WEST_5
Protip; Table123-Demo_WEST_5 will not be included in this exercise because it’s not named Table-Demo_. Wildcard allows you the ability to filter to the things you need. If you name your Google Sheets “Table-Demo_” our wildcard solution automates connection to that google sheet, there’s no need to connect to the extra google sheet if you’re setting up the solution as explained.
Now that we have an understanding of how a wildcard will work, let’s discuss the end to end.
How to setup >10mb union
To increase size of google sheets greater than 10 megabytes, and increase your overall Google Sheets insights in tableau desktop, you need to get good with the Union wildcard!
Connect to the googlesheet. Tableau desktop made this workflow a one click button on left side of opening screen. Requiring two clicks in total.
Walk through the Google authentication, choose which email with many similar tables for wildcard. This means you need to go and change the names of the Tables you wish to put together.
The renaming part needs to be a part of an automated process, you may want to do, using the Google Sheets API also known as the Google API, we found success automatically creating a new Google Sheet, and automatically naming the sheet similarly, which improved a client engagement during a tableau consulting engagement that had a lot of data engineering consulting to generate the solution. If data is constantly morphing, there may be a need to delete old sheets, we found clearing the sheet and re-populating data was the easiest method for fresh cleans. However lets get focused on the manual process because it’s a similar architecture. We found naming tables differently between tests helped us with testing/troubleshooting, and found Google Sheets had some strange hiccups that are easier to avoid by removing old tests completely.
Discussing Data Structure tips for >10mb Google Sheets
Here’s a good time to start making sure column headers are the same. If not it will continue to make a column, which will lead you down the path of learning how to solve for dynamic parameters due to string values being many to many.
Convert to union…
Very important step, drop down carrot and find the Convert to union click.
This workaround allows you to connect once, to all sheets similarly named (using wild cards) VS connecting to all the different google sheet workbooks. This allows you to remove many data sources and transition into one data source.
The Wildcard Union Screen in Tableau
Tableau offers a feature to union more than one google sheet together, which enables users to quickly build large cloud data storages on Tableau public, or internally.
Below are documents, notes, and community posts from other Tableau authors.
- https://community.tableau.com/s/question/0D54T00000C62YC/is-it-possible-to-union-google-sheets-from-different-workbooksconnections?t=1634788554971
- https://community.tableau.com/s/question/0D54T00000C6d1DSAR/this-file-is-too-large-to-be-exported?_ga=2.151469113.1478915185.1634739545-1826838528.1627942705
- https://community.tableau.com/s/question/0D54T00000C6gnP/google-spreadsheet-file-is-too-large-to-be-exported-error
- https://community.tableau.com/s/question/0D54T00000WV6dySAD/tableau-couldnt-connect-to-google-sheet-data
- https://community.tableau.com/s/question/0D54T00000G36TK/limits-to-know-for-tableau-public-google-sheets
- https://community.tableau.com/s/question/0D54T00000CWeW0SAL/error-this-file-is-too-large-to-be-exported-when-extracting-data-from-google-sheet
The 10mb limit with Google Sheets is ambiguous when testing the number with true CSV file sizes and better to determine a way of “stopping” the data before it gets big.
Some interesting things to think through, we found 7mb, 10.3mb, 12.9mb, and 19.1mb CSV files coming from single Google Sheet connections and no popup error stopping Tableau Desktop from connecting to the data. Don’t consider this size to be your break/test.
Good to note; This is the size of the csv when downloaded via the Google Sheets/Data/. Your team may get hung up on this process, and we found it’s better to focus on a row count if you’re not using full stack engineering to complete the task.
Thank you for reading. If you are interesting in implementing a process that uses Google API, contact us to learn more.
Researched & Written by, Tyler Garrett founder of Dev3lop | Consulting Services.
