LIMIT BEING REACHED

[Tanner Leach]

I have running the following function:

function writeToSpreadsheet(tab,rows,spreadsheet) {

  var to_write = convertRowsToSpreadsheetRows(tab,rows);

  var s_sheet = SpreadsheetApp.openByUrl(spreadsheet.getUrl());

  Logger.log(spreadsheet.getUrl());

  var sheet = getSheet(tab, spreadsheet);

  

  var numRows = sheet.getMaxRows();

  if(numRows < to_write.length) {

    sheet.insertRows(1,to_write.length-numRows); LINE 217

  }

  var range = sheet.getRange(1,1,to_write.length,to_write[0].length);

  range.setValues(to_write);

}

and giving me:

This spreadsheet has reached the cell per spreadsheet limit. Create a new spreadsheet to continue working, as you may not be able to add any more rows or columns. For more information on size and complexity limits in Google spreadsheets, visit the <a target=_blank href=>Google Docs Help Center.</a> (line 217) The Report I am running is no where near the max size, What is going on here?

[Anash Oommen]

Hi Tanner,

I remember that Google spreadsheet used to have a limit on the number of cells (400K?) as well as number of rows. If you can share your account details by using "Reply to Author", I can take a look.

Cheers,

Anash P. Oommen,

AdWords Scripts Team.

[Tanner Leach]

Thank you for the response but I got it working.

Cheers!

Tanner

[Chris]

Hi Tanner,

i have the same problem. How did you get it working?

[Alexander Wang]

Hey Chris,

That error indicates that the spreadsheet is hitting a limit. There are 2 versions of Google Spreadsheets. The newer one supports 2 million rows while the older has significantly smaller limits. If you're using an older spreadsheet, maybe it's easiest for you to simply copy the data over into a new spreadsheet and use that. You can read up on spreadsheet limits here:

https://support.google.com/drive/answer/37603?hl=en

You should try opening the spreadsheet that your script is updating in a browser. Maybe there are extra sheets, rows, or columns that you can remove to bring the spreadsheet under these limits?

If the spreadsheet appears to be small, you can reply privately to me with your spreadsheet and I can take a look. It'd also help me to have your account id plus the name of your script so I can see the exact error you encountered.

Cheers,

Alex