Blog

Data - Excel vs Google Sheets

The limitations of using Excel as a data source are well known. If we want to store data in spreadsheets, how does Google Sheets compare? In this post, we'll walk through how to connect to a Google Sheets, and find out whether the Google Sheets connector applies the same file and row size limits as the Excel connector.

There are several limitations to using Excel as a data source. The main limitations are that it is not a delegable data source, and that the connector cannot work with any rows that are beyond 2,000. The connector also imposes a 2MB limit on the size of the Excel that it can work against.

If we want to use a spreadsheet as a data source, how does Google Sheets compare? Does the connector perform better than the Excel connector, and can it cope better with larger quantities of data? In this post, will walk through how to connect to a Google sheets spreadsheet, and we'll find out the answers to these questions.

Connecting to a Google Sheets spreadsheet

The first step is to create a spreadsheet in Google sheets. Next, we can add a connection to the Google sheets from an app through the data panel.

In my test, I converted an Excel spreadsheet with 10000 rows to Google sheets. When I attempted to create a connection, I received the following error:
"Range ([SpreadsheetName]!K2:K) exceeds grid limits. Max rows: 9999, max columns: 10"


This error appears to suggest that the spreadsheet contains too many rows. However, this is not the cause of the error, as we'll soon see.

How to setup a Google Sheet correctly for use in Power Apps

Unlike Excel, a spreadsheet in Google sheets can contain a short number of fixed columns. In my example (shown beneath), notice how the final column in the spreadsheet ends in column "J".



When Power Apps creates a connection to a Google sheets spreadsheet, it attempts to create a system column called __PowerAppsId__. The purpose of this column is to store a value that uniquely identifies each row.

My example spreadsheet ended in  column "J" and therefore, there was no space for Power Apps to create this column. Therefore, it's necessary to add an extra empty column so that Power Apps can add the __PowerAppsId__ column. Without this empty column, we'll receive the "Range exceeds grid limits" error when we attempt to connect.to the spreadsheet from Power Apps.

Limitations of Google Sheets

Due to the way that it isn't possible to define tables in Google sheets and that the first row must contain the column names (in text format), it's not possible to apply the data type at a column level.

I could find no way to format cells in such a way that Power Apps would recognise the correct data type for number and date columns. This was the biggest limitation - the fact that Power Apps treats all the data columns as strings.

Can we retrieve more than 2,000 rows?

The most important question is - can we retrieve data beyond row 2000 (ie, the row limit of Excel)? Unfortunately, the answer to this is no.

As the screenshot beneath shows, with the delegation limit set to the default value of 500, Power Apps does not show rows beyond row 500. Also, all data retrieval functions are non-delegable and the formula bar does not underline any of these non-delegable functions/operators with a blue squiggly line.

Note that in my attempt to set the data type of specific cells to numbers in Google sheets, I inadvertantly made it more difficult to work with the source data because Power Apps now returns string representations of numbers to 2 decimal places.

Conclusion

The Google sheets connector provides no additional benefit over the Excel connector in terms of accessing rows above 2000. In cases where we want to work with spreadsheet data in Power Apps, it's far easier to work with Excel and the Excel connector. Therefore, I would recommend Excel over Google Sheets.

For further details on the limitations of the Excel connector, my previous post here provides addditional details.

For reference, the link to the Google Sheets connector documentation is here.
Previous
Next
Related posts