I have a function that returns a string that are the cells that I need to scan with the UNIQUE() built-in function, but it does not process the returned value.

Tried putting them in quotes, didn't help.

function GetRange(sheet, ColumnLetter , Offset)
  var startColumn = letterToColumn(ColumnLetter);
  var string = "";

  for(var i=startColumn; i <= Offset; i++)
      string += sheet + "!" + columnToLetter(i) +":"+columnToLetter(i)+";";      

   string = string.substring(0, string.length-1);

   return string+"";

On the Sheet side I have this in a cell

=UNIQUE({GetRange("Raid","C", 30)})

I expected to get the returned string into the built-in UNIQUE() function, and execute the code like I would if I would have typed it in manually.

Instead, it just outputs the returned string into the cell like this

New contributor
Dosa Szabolcs is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

try like this instead:

 IF(LEN(Raid!A1:A), IF(Raid!C1:AL<>"", "♦"&Raid!C1:AL, ), )),,999^99)),,999^99)), "♦")))))
  • My experties is limited when it comes to Google Sheet and even in Java script. The first one gives back a normal UNIQUE that is per column, that is not ok for me And the second gives me an error that is the folowing "Text result of TRIM is longer than the limit of 50000 characters." – Dosa Szabolcs Jun 12 at 13:35
  • can you share a copy of your sheet? – player0 Jun 12 at 13:36
  • 1
    "Any sufficiently advanced technology is indistinguishable from magic" ... i do not understand it but it works ! :) – Dosa Szabolcs Jun 12 at 15:30


You're returning a string from the function. It can't be evaluated as a spreadsheet array literal by the spreadsheet.


Return arrays instead.


 * @param {Raid!C:AD} range - Range to stack as a array
 * @return The provided 2D array stacked up vertically
 * @customfunction
function stackUp(range) {
  return Array.prototype.concat.apply(
    range[0].map(function(col, i) {
      //for each column
      return range
        .map(function(row) {
          return [row[i]];
        }) //get each row in this column
        .filter(String); //ignore empty rows

Your Answer

Dosa Szabolcs is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.