2013-10-13

Is google docs just a toy?

Lately I'm working on a project involving the spreadsheet application of google docs.
I've chosen it because it's cross platform, it's easy to share, it's scriptable using javascript with little effort.
Things went fine with all the simple test cases, but when I begun using it with real-life data I came into this:


But I'm well far from the declared limits of the application itself:

Google spreadsheets size and complexity limits

Learn how to keep your spreadsheet up-and-running by avoiding size and complexity limits.

Size limits

If you're close to exceeding Google spreadsheets size limits, you'll see a message at the top of your spreadsheet that indicates what limit you're about to reach.
  • Number of cells: 400,000 total cells across all sheets
  • Number of columns: 256 columns per sheet
  • Number of formulas: 40,000 cells containing formulas across all sheets
  • Number of tabs: 200 sheets per workbook
  • GoogleFinance formulas: 1,000 GoogleFinance formulas
  • ImportRange formulas: 50 cross-workbook reference formulas
  • ImportData, ImportHtml, ImportFeed, or ImportXml formulas: 50 functions for external data.
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.

Google forms size limits

These size limits also impact the spreadsheets that collect form responses (for example, if you send a survey). To determine the number of responses that a form can handle, take the number of questions in your form and the number of cells containing other data into account.

Complex Calculations

Google spreadsheets also have complexity limits. Every time a cell is updated, any cell that references it will also be recalculated. If formulas become too complex or take too long to calculate, the spreadsheet will timeout during calculation.
Formulas that increase the complexity of a spreadsheet include:
  • VLOOKUP, QUERY, SUMIF, and many similar formulas that take a large range of cells as input.
  • Volatile formulas (e.g., NOW, RAND, OFFSET, INDIRECT) are recalculated every time the spreadsheet is modified. If there are a large number of formulas that depend on cells with volatile formulas, they will be re-calculated on each edit, which may slow down a spreadsheet.
  • Import-based formulas (e.g., IMPORTRANGE) are recalculated periodically and magnify complexity.


we're talking about a 4000 rows spreadsheet here, and all the scripts works fine on smaller sheets.
Update:
I investigated a little more: it seems that I hit the 40.000 formulas limit.
Quite a low limit: I had to copy some columns and paste back only the values to go on.