How to perform Cartesian Join with Google Scripts & Google Sheets?

Question

I have found code for a basic 2 column Cartesian join but I have a slightly different situation.

There are 3 Variables/Columns: Color, Color ID, & Item.

I'd like every combination of Color and Item. I'd like the 3rd Variable, Color ID to be attached to the hip with the Color Variable.

Here is an example spreadsheet. Columns A-C are the input data and Columns E-G is the desired output (order is non-important.)

https://docs.google.com/spreadsheets/d/1LgWttzY317T3N66Wk2JbDq8nETSGl1HCxY8u8i0jhl4/edit#gid=0

Answer 1

I believe your goal as follows.

• You want to achieve the following conversion using Google Apps Script.

  • From

      Blue   74  Shirt
      Red    48  Pants
      Green  55  Shoes
                 Hat
                 Socks
                 Backpack
    

  • To

      Blue   74  Shirt
      Blue   74  Pants
      Blue   74  Shoes
      Blue   74  Hat
      Blue   74  Socks
      Blue   74  Backpack
      Red    48  Shirt
      Red    48  Pants
      Red    48  Shoes
      Red    48  Hat
      Red    48  Socks
      Red    48  Backpack
      Green  55  Shirt
      Green  55  Pants
      Green  55  Shoes
      Green  55  Hat
      Green  55  Socks
      Green  55  Backpack
    

• In your sample Spreadsheet, Green, 55 has all the same value which is Backpack. But from your sample pattern, I thought that you might have wanted the avove conversion.

If my understanding is correct, I would like to propose the following flow.

1. Retrieve the values from the columns "A" to "C".
2. Transpose the retrieved values.
3. Create the array for putting to Spreadsheet.
4. Put the values.

When above flow is reflected to the script, it becomes as follows.

Sample script:

Please copy and paste the following script to the script editor of the Spreadsheet, and run myFunction. By this, the result values are put to the columns "I2:K".

function myFunction() {
  const sheetName = "Sheet1";  // Please set the sheet name.

  // 1. Retrieve the values from the columns "A" to "C".
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const values = sheet.getRange("A2:C" + sheet.getLastRow()).getValues();

  // 2. Transpose the retrieved values.
  const [color, id, item] = values[0].map((_, i) => values.map(r => r[i]).filter(String));

  // 3. Create the array for putting to Spreadsheet.
  const res = color.flatMap((e, i) => item.map(g => [e, id[i], g]));

  // 4. Put the values.
  sheet.getRange(2, 9, res.length, res[0].length).setValues(res);
}

Result:

When above script is run for your sample Spreadsheet, the following result is obtained.

References:

• map()
• flatMap()

Answer 2

As a simple Google sheets formula,

• Join A & B with a delimiter
• Join the above result with a TRANSPOSEd C and the delimiter. This creates a Cartesian product matrix
• FLATTEN the above result
• SPLIT by the delimiter to get 3 columns back again.

=ARRAYFORMULA(SPLIT(FLATTEN(A2:A4&""&B2:B4&""&TRANSPOSE(C2:C7)),""))

• Use INDEX/COUNTA, if you want a open ended range.

Answer 3

Tanaike's answer is great and please accept his answer, but this is just in case you want to use this as a custom function (formula) in your sheet:

function cartesian(startCol) {
  const sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const vals = sh.getRange(2, startCol, sh.getLastRow(),3).getValues();
  const input  = [...Array(3)].map((_, i) => 
                 vals.map(r => r[i]).
                 filter(e=>e!=''));
  const res = input[0].map((color, i) => 
              input[2].
              map(item => [color, input[1][i], item])).
              flat();
  return res;
}

and then use =cartesian(startCol) where startCol is 1 in your case as the first column of the input data:

Answer 4

As per the answer of TheMaster, just with "growing" table sizes:

=ARRAYFORMULA(SPLIT(FLATTEN(offset(A2,0,0,counta(A2:A),1)&""&offset(B2,0,0,counta(B2:B),1)&""&TRANSPOSE(offset(C2,0,0,counta(C2:C),1))),""))

I've added the formulas to the example spreadsheet in the question.