The resulting array was too large in Google Sheets-CodePudding

I'm using the following formula, and I'm getting: The resulting array was too large.

Any ideas? I have over 20k rows.

UPDATE: If I can accomplish this formula with a script, that would be great. Thanks!

=UNIQUE(ARRAYFORMULA({IMPORTRANGE(Links!B2,"uno!A:AF");IMPORTRANGE(Links!B3,"dos!A:AF");IMPORTRANGE(Links!B4,"tres!A:AF");IMPORTRANGE(Links!B5,"cuatro!A:AF");IMPORTRANGE(Links!B6,"cinco!A:AF")}))

CodePudding user response：

Try by script this way

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();  
};