Google-sheets – Show a unique combination of first column with a set of columns in Google Sheets

google sheetsgoogle-apps-script

I'd basically like to something like:

Name  Friend_1  Friend_2  Friend_3
----  --------  --------  --------
A     a_1       a_2       a_3
B     b_1       b_2

To:

Name  Friend
----  ------
A     a_1
A     a_2
A     a_3
B     b_1
B     b_2

Some kind of cartesian product of the text in column Name with the text in column Friend_1, Friend_2, Friend_3.

I suspect I'll be using something like TRANSPOSE and CONCAT but I'm not sure.

Best Answer

It looks like this custom function works quite well:

function combinations(input) {
  var output = [];
  for (var i = 0; i < input.length; i++) {
    for (var j = 0; j < input[i].length; j++) {
      other = input[i][j];
      if (other.trim() !== '') {
        output.push([input[i][0].trim(), other.trim()])
    }
  }
  }
  return output;
}

Short Answer

Replace if( r.getColumn() != 5 ) by if( r.getColumn() != 5 && r.getRow() != 1 ) .

Long answer

&& is the AND operator r.getRow() != 1 compares the row of the edited cell with the row number that you want to be skipped.

Full code minimal modification

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  var r = s.getActiveCell();  
  if( r.getColumn() != 5 && r.getRow() != 1) { //checks the column & row
    var row = r.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT-07:00", "MM-dd-yyyy HH:mm:ss");
    SpreadsheetApp.getActiveSheet().getRange("E" + row.toString()).setValue(time);
  };
};

Full code improved

Taking advantage that onEdit() event gets an event object that include the range.

function onEdit(e) {
  var r = e.range;
  var s = r.getSheet();  
  if( r.getColumn() != 5 && r.getRow() != 1) { //checks the column & row
    var row = r.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT-07:00", "MM-dd-yyyy HH:mm:ss");
    s.getRange("E" + row.toString()).setValue(time);
  };
};

References

Google-sheets – Google Sheets Query to select a large range of columns without manually entering each column

You can generate a comma-separated string of column names with a formula such as

=arrayformula(join(",", substitute(address(1, column(C:F), 4), "1", "")))

(here C:F is the example of a range of columns). The above formula returns the string C,D,E,F which is suitable for use in a query. Optionally, the arrayformula part can go outside. Example:

=arrayformula(query(A1:F, "select " & join(",", substitute(address(1, column(C:F), 4), "1", "")) & " where A <> ''"))

For a short range like C:F this obviously isn't worth the effort, but if you have something like M:AT, then it's probably going to save time.

Explanation

column returns a column number
address returns A1 notation of the cell at the top of that column (option 4 means no $ will be used)
substitute removes row number 1
join joins by commas