Start a new topic

Google Sheets limits us to 2 million cells per document. Any workarounds?

Google Sheets limits us to 2 million cells per document. Does anyone have any workarounds for this limitation?


1 person likes this idea

Hi! 


We at Supermetrics would also be glad to know if someone has found a way to go beyond 2 million cells.


If not, we have our API product which might help in some cases: http://supermetrics.com/smapi/



Best regards,

-Supermetrics Team


1 person likes this

Hi Jouni,


According to the API product page, the product is not yet available. Any tentative dates in terms of the roadmap for release?

Hello,


API product is still under development but if you would like to talk more about getting onboard as early user, please contact zhao.hanbo@supermetrics.com.


Best regards,

-Jouni Hyötylä / Supermetrics

I have a workaround for this problem using Google Bigquery. Google released a way to sync Sheets and other external data sources with Bigquery in 2016. The setup process is somewhat tedious and it does not require any code but it helps to know SQL. Here's how it works: 1. You need to create a Bigquery account and setup a Project with billing enabled. 2. You create a dataset in your Bigquery Project to house your tables. 3. You create new tables in your new dataset by selecting the options to connect Google Sheets. In order to see the data in Bigquery, you must first define the table schema in the table's settings in Bigquery. This is the tedious part because you need to manually enter the title of each column's header and you need to define each column's type, such as STRING, INTEGER, etc. 4. Once your table is setup and properly connected with a Sheet, you can use SQL to query and preview your data. 5. Once you have multiple Sheets properly connected, you can use SQL queries to join the tables, and you can save your queries as "views" which are essentially new tables that can be virtually any size. There are some limitations and issues to be aware of however. First, each connected sheet is still restricted by the 2M cell limit. Second, it is easy to query federated tables in Bigquery but it seems like many 3rd party tools are having issues with this, including Supermetrics and even Data Studio 360. The Data Studio team has actually been trying to solve the issue for months with no luck. Sometimes the tables cannot be queried at all, and other times, the tables can be queried just a few times before returning a Google Drive quota error even though your Drive API is not actually receiving any hits according to Google's API console. If it turns out that the app you are using is having trouble connecting to your federated vews, a workaround for the workaround is required. You will need to convert your federated views into native tables. This can probably be done through google's cloud console if you know how to code, or platforms like OWOX or XPlenty can help with this. Once you have your data consolidated into native tables, you can pretty much use any 3rd party tool that connects with Bigquery.

3 people like this
It would be awesome if Supermetrics were able to upload directly into Bigquery instead of Sheets. I assume this could be done with the API but it would be great if there was a user interface for setting this up the same way we are able to schedule email alerts in the Sheets Add-on.

Hi everyone,


we are looking in to the possibility of uploading to Bigquery. As soon as we have something new about it, we'll let you know.

Thank you so much for your tips in this matter!


-Supermetrics Team


1 person likes this

Hi everyone,


I currently use a Google App Script Job to move Google Spreadsheet Data to BigQuery. Of course it would take a few manual uploads to fill BigQuery with historical Data, you can automate the process of appending new data and query the Table afterwards.


Otherwise I use one Central Spreadsheet where I collect all Data for visualisation and pull only the data I need via IMPORTRANGE from other spreadsheets into that spreadsheet. The plugin IMPORTSHEETS could help, too. But it is not free. I would be nice if supermetrics offered a similar way to pull data from other sheets.


Greetings,

Till

Hi, any update on this? (in terms of BigQuery + Supermetrics)


I'm also especially interested in the solution from the user Ad Analytics -> do you have maybe some additional material on how to convert federated views into native tables using Google Cloud Console? I have programming experience so I'm thinking of looking into this in the meantime.

Hi,


unfortunately no news yet but we are working on a possible solutions. You'll get the new for example by following us on Twitter: @Supermetrics.


Best regards,

-Supermetrics Team

Google Spreadsheet does not distinguish between cells with data and empty cells. So, if you delete the columns you are not using, they won´t count towards the cell limit. I find it very hard to use up all the cells this way. This is by far the simplest workaround for this problem.

Thanks for your suggestion! Unfortunately for us, that's what we've already been doing to help with give us some extra cells. However we eventually hit the limit pretty quickly given the size of our data.

Hello,

We have been looking to see when the API tool will be out of beta.
Any updates available?
Thank you.

Hi!


We're closing in to the release date. Most likely within the next 2 months.



Best regards,

-Supermetrics Team


4 people like this
Login or Signup to post a comment