Google Spreadsheet stopped storing data before 400000 cells are consumed

Question

I have a spreadsheet which collects users' feedback data. According to Google Documentation, size limit for a Google Spreadsheet is 400000 cells. I have written a GAS code which checks the total number of consumed cells in the spreadsheet. In case consumed cells' count is alarming, GAS creates a duplicate copy and clears data from the current spreadsheet.

Now, the spreadsheet suddenly stopped collecting data submissions since Jan 28, 2014. I checked the total cells consumed and found that it still needed approx 2500 cells to be 400000.

I looked through the Google documentation again and found:

"Spreadsheets also have overall storage limits. Some spreadsheets may reach these before hitting the 400,000 cell limit, particularly when individual cells have large amounts of text. In such cases, the spreadsheet will go into read-only mode to prevent data loss."

I tried looking for any method in Google Apps Script or Google Script where I can check a spreadsheet's data size with respect to its storage limit. I didn't find any such reference.

Can you please help on this? Is there any method in Google Apps Script to find out: 1. what is current data size of a Google spreadsheet? 2. what is the storage limit for a Google spreadsheet?

Thanks in advance.

Answer 1

No its not possible by apis. Its not just byte size. Other things like total number of formulas also affect it. Converting to xls will only be an aproximation.

Answer 2

My test is to download the spreadsheet as an Excel file. I have one that's close to 5.5MB. Anything higher than that usually stops working. I haven't tried to automate that check, but perhaps it's possible to:

1. Use DriveApp to get the file as xlsx
2. Measure file's Blob's size