Google-sheets – Jagged array from Google Spreadsheet range

google sheetsgoogle-apps-script

The range in my Google Spreadsheet looks like this:

| A                 | B   | C                 | D   | E               | F   |
|-------------------|-----|-------------------|-----|-----------------|-----|
| Cersei, Lannister | 111 | Finger, Little    | 112 |                 |     |
| Cooper, Sheldon   | 222 |                   |     |                 |     |
| White, Walter     | 331 | Ehrmantraut, Mike | 332 | Pinkman, Jessie | 333 |

I'd like to get a jagged array for my user-defined function, that would look like this:

array = [
["Cersei, Lannister 111", "Finger, Little 112"], 
["Cooper, Sheldon 222"], 
["White, Walter 331", "Ehrmantraut, Mike 332", "Pinkman, Jessie 333"]
]

I wanted to get there by doing things with separate array elements (e.g. using splice in a while loop so I can get pairs of cells, name + number).

But it seems that it will take all the cells within each row and join them into one string element. So the result is something like

["Cersei, Lannister 111,Finger, Little 112", "Cooper, Sheldon 222", "White, Walter 331,Ehrmantraut, Mike 332,Pinkman, Jessie 333"]

So then when I for instance use input.join("-") it will put the delimiter at the end of each original row, between 112 and Cooper for instance, instead of between the original spreadsheet cells (between Lannister and 111, 111 and Finger and so on).

How do I import the range into the function to get separate cells as array elements?

Best Answer

A custom function will always receive either a rectangular array or a scalar value (number, string, boolean, or datetime) as each of its arguments. So the conversion from rectangular to jagged array has to happen inside the function. For example, by calling jagged(arr) on the input, where jagged is the following function: 

function jagged(arr) {
  var rowLength = arr.map(function(row) {
    return row.reduce(function (a, b, i) {
      return b === '' ? a : i+1;
    }, 0);
  });
  return arr.map(function(row, i) {
    return row.slice(0, rowLength[i]);
  });
}

This function first computes the actual length of each row and then truncates the rows accordingly.

I emphasize that the call to jagged needs to happen from within the custom function that will use it, like so: 

function myFunction (arr) {
  jarr = jagged(arr); 
  // proceed to work with the jagged array
}

Putting =myFunction(jagged(A1:F3)) in a spreadsheet cell won't do it: the Sheets will automatically inflate the return values of jagged to a rectangular array before passing them to myFunction.

Related Topic