Custom function parameters too large

Question

There is a Google Sheets that has over 2,000 rows and over 500 columns and there is a Google Apps Script custom function that takes cells range as parameter and posts their data to external API.

Function calls in the following form: =someFunctionName(A1:SG1000)

Parameters processing:

function someFunctionName(arr) {

  ...

  var payload = JSON.stringify({
    'rows': arr
  });

  ...
}

The problem is that there is a limit on the size of the parameters of the Google Apps Script custom function.

If function call is for one hundred rows all works fine, but if there are several hundred rows there will be limitation error:

"Custom function parameters too large."

Is there any way to figure out Google Apps Script custom function for large custom function parameters?

Don't use custom functions? Get the array directly from the sheet and execute? — TheMaster
@TheMaster Posted answer accordingly to your idea to get array directly. Just use string parameter in function. Thanks! — Bo.

Bo. Bo. · Accepted Answer · 2019-07-03T11:11:35

Take the cells range as String:

=someFunctionName("A1:SG1000")

Convert String to Range and get values in: [][]

function someFunctionName(arr) {

  ...

  var rVals = SpreadsheetApp.getActiveSpreadsheet().getRange(arr).getValues();
  var payload = JSON.stringify({
    'rows': rVals
  });

  var payload = JSON.stringify({
    'rows': arr
  });

  ...
}

Custom function parameters too large

1 Answers