Getting error "This action would increase the number of cells in the worksheet above the limit" with 50,000 cells

Question

I am trying to get a CSV downloaded from an URL into a new, empty Google sheet using script editor.

 var raw = UrlFetchApp.fetch(final_url, options).getContentText();
 var data = Utilities.parseCsv(raw, "|");
 Logger.log(data[0].length); /// 17 (columns)
 Logger.log(data.length); /// 2849 (rows)

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet();
 sheet.getRange(1, 1, data.length, data[0].length).setValues(data);

As far as I understand it, I have a total of 48433 cells, but when I try to write it to the sheet, I get the error:

This action would increase the number of cells in the workbook above the limit of 5000000 cells.

Any ideas?

Solution 1

Issue:

Insufficient cells in the sheet to setValues data. And setValues() incorrectly throws the error

This action would increase the number of cells in the workbook above the limit of 5000000 cells.

This might be due to the content of the csv file(and therefore the resulting array data). During testing, I was able to add 2800rowsx17 columns of data to a sheet with 1000rows x 26 columns without issues.

Solution:

• Manually increase the number of rows/columns in the sheet OR

• Try sheet#insertRows to insert rows before calling setValues

Snippet:

sheet.insertRows(sheet.getLastRow(), data.length);

Solution 2

Without seeing the actual sheet and the CSV file we can only speculate as to what the issue is. My best guess is that you haven't taken into consideration that the 5,000,000 cells restriction applies to the entire "workbook". So if you have multiple tabs (sheets) then the sum of cells used across those sheets cannot exceed 5,000,000 cells.

Comments

[TheMaster]

What's the value of data.length and data[0].length, if you log them?

[MariaMH]

It's commented in the code, 17 and 2849, just to make sure which dimensions I was working with.

[TheMaster]

How many cells are already in your sheet? Try in a new sheet perhaps

[TheMaster]

Could you try Logger.log(data.every(function(row){return row.length === 17})) to see whether every row has 17 columns or only the first row

[MariaMH]

It returns "true". But I tried Logger.log(data.every(function(column){return column.length === 2850})) and it was false. But I don't understand why

[MariaMH]

It's a blank sheet.

[TheAddonDepot]

Can you share a link to the CSV file?

[TheMaster]

I tried Logger.log(data.every(function(column){return column.length === 2850})) and it was false. ReNaming row as column won't give you columns, but that doesn't matter. How many rows and columns in the blank sheet ? Make sure to create 3k rows and 18 columns and try again

[MariaMH]

I can't believe it was that easy. Thank you! I'd upvote it, but I don't think you can in an a comment.

[TheMaster]

Great..I'll add a answer. Consider accepting it.

[MariaMH]

My sheet is empty, and I only have one of them.

[TheAddonDepot]

Are the rows in the CSV of variable length?

[MariaMH]

All the rows have the same length, 17.

[user147529]

hi, i've tried to insert rows so there are more rows in the sheet than my python dataframe but still getting the error, any idea what's happening?

[TheMaster]

@user147529 Check the size of the array after parsing. Do follow my debug comments in the question: stackoverflow.com/questions/58061314/…

[user147529]

thanks, my data is in python, not google apps scripts. the shape is (49868, 11). i did manage to run the code by reducing the dataframe, however, i couldn't see the output in the sheet. perhaps i'll start another Q