Exception: This action would increase the number of cells in the workbook above the limit of 10000000 cells

Question

I have the following code but I'm getting the following error after running it. Is there any way to resolve this? The data that I'm retrieving has about that amount of cell but it may be retrieving blank rows but all the rows has formulas.

N# of rows per file:

• uno: 72850
• dos: 72850
• tres: 72850
• cuatro: 72850
• cinco: 72850

Total: 364250

function test() {
  var dest = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()

  var ss = SpreadsheetApp.openById('id2')
  var sh = ss.getSheetByName('uno')
  var values = sh.getRange('A1:AF'+sh.getLastRow()).getValues()
  dest.getRange(dest.getLastRow()+1,1,values.length,values[0].length).setValues(values)
  SpreadsheetApp.flush();

  var ss = SpreadsheetApp.openById('id2')
  var sh = ss.getSheetByName('dos')
  var values = sh.getRange('A1:AF'+sh.getLastRow()).getValues()
  dest.getRange(dest.getLastRow()+1,1,values.length,values[0].length).setValues(values)
  SpreadsheetApp.flush();

  var ss = SpreadsheetApp.openById('id3')
  var sh = ss.getSheetByName('tres')
  var values = sh.getRange('A1:AF'+sh.getLastRow()).getValues()
  dest.getRange(dest.getLastRow()+1,1,values.length,values[0].length).setValues(values)
  SpreadsheetApp.flush();  

  var ss = SpreadsheetApp.openById('id4')
  var sh = ss.getSheetByName('cuatro')
  var values = sh.getRange('A1:AF'+sh.getLastRow()).getValues()
  dest.getRange(dest.getLastRow()+1,1,values.length,values[0].length).setValues(values)
  SpreadsheetApp.flush();  

  var ss = SpreadsheetApp.openById('id5')
  var sh = ss.getSheetByName('cinco')
  var values = sh.getRange('A1:AF'+sh.getLastRow()).getValues()
  dest.getRange(dest.getLastRow()+1,1,values.length,values[0].length).setValues(values)
  SpreadsheetApp.flush();  
};

Answer 1

The error message seems to be related to current Spreadsheets limits in Google Sheets as @Cooper mentioned, currently the limit is the following:

?Up to 10 million cells or 18,278 columns (column ZZZ) for spreadsheets that are created in or converted to Google Sheets.

Creating a new sheet could do the work as the limits apply per sheets.

Answer 2

Try to replace getLastRow() by getLastDataRow() after adding

Object.prototype.getLastDataRow = function(col){
  var lastRow = this.getLastDataRow();
  if (col == null){col='A'}
  var range = this.getRange(col + lastRow);
  if (range.getValue() !== "") {
    return lastRow;
  } else {
    return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  }  
};

so ...

function test() {
  var dest = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()

  var ss = SpreadsheetApp.openById('id2')
  var sh = ss.getSheetByName('uno')
  var values = sh.getRange('A1:AF'+sh.getLastDataRow()).getValues()
  dest.getRange(dest.getLastDataRow()+1,1,values.length,values[0].length).setValues(values)
  SpreadsheetApp.flush();

  var ss = SpreadsheetApp.openById('id2')
  var sh = ss.getSheetByName('dos')
  var values = sh.getRange('A1:AF'+sh.getLastDataRow()).getValues()
  dest.getRange(dest.getLastDataRow()+1,1,values.length,values[0].length).setValues(values)
  SpreadsheetApp.flush();

  var ss = SpreadsheetApp.openById('id3')
  var sh = ss.getSheetByName('tres')
  var values = sh.getRange('A1:AF'+sh.getLastDataRow()).getValues()
  dest.getRange(dest.getLastDataRow()+1,1,values.length,values[0].length).setValues(values)
  SpreadsheetApp.flush();  

  var ss = SpreadsheetApp.openById('id4')
  var sh = ss.getSheetByName('cuatro')
  var values = sh.getRange('A1:AF'+sh.getLastDataRow()).getValues()
  dest.getRange(dest.getLastDataRow()+1,1,values.length,values[0].length).setValues(values)
  SpreadsheetApp.flush();  

  var ss = SpreadsheetApp.openById('id5')
  var sh = ss.getSheetByName('cinco')
  var values = sh.getRange('A1:AF'+sh.getLastDataRow()).getValues()
  dest.getRange(dest.getLastDataRow()+1,1,values.length,values[0].length).setValues(values)
  SpreadsheetApp.flush();  
};
Object.prototype.getLastDataRow = function(col){
  var lastRow = this.getLastDataRow();
  if (col == null){col='A'}
  var range = this.getRange(col + lastRow);
  if (range.getValue() !== "") {
    return lastRow;
  } else {
    return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  }  
};