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?

Answer 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);