0

I am looking for a flexible custom function to be able to cross-join (cartesian product) 2 columns of varying lengths in Google Sheets.

My research so far has led me to ask because:

  1. using a combination of existing formulas in Sheets either produces some weird artifacts (counting blank cells as values to cross-join) or has limits to how many results it can produce.
  2. The JavaScript solutions online pertain to specific examples and have not worked for my case yet (I have very limited knowledge of JavaScript).

To further describe my request:

  1. It must be able to cross-join 2 columns of ANY length (if one column is longer than the other, I don't want it to utilize blank cells from the shorter one).
  2. I would love it to be flexible in that I can place it anywhere and use any combination of columns

Edit 1:
Sorry for any missing information, it's my first time posting here

So I'm not sure how many formulas I have tried but it's been over 20, I'm not sure how productive it would be to post those in here because I'm looking for JavaScript at this point.

Here's an example of some data sets and results I would like to get from this one script:

A small data set example, preferable would be able to done with 100 rows. I also would like to be able to have 1 column have 100 rows and one have 2 or whatever combo.

I've seen many scripts be dependent on the header name and stuff and I would like that not to be the case.

4
  • Welcome to Web Applications. Please show a custom function that you have tried, add some sample input data and the expected result and add more specific description regarding to your search efforts. P.S. 1. The term used in Google docs is "custom functions", 2. By search efforts we are referring searching this site for similar questions. Aug 26, 2021 at 21:28
  • Related 1, 2 Aug 26, 2021 at 21:32
  • Thanks, those examples have the results in two columns. Is there any way to make the results show in one column? Also, example 2 seems to need to reference specific column names, I would prefer not to do that. LMK if I am interpreting that wrong, though, I don't have much knowledge of the subject Final point: I would like to input the ranges for this formula as arrays (for example A2:A,B2B) Aug 26, 2021 at 21:43
  • You are welcome. Regarding if there is any way to make the results show in one column, yes there is. You might have to start by understanding how custom functions work in Google Sheets / Google Apps Script. If you need help with that please post a new question. Regarding this question while the edit you made makes it better it still has a lot of room for improvement to make it a good question for this site as the question needs more focus (ref. How to Ask). Aug 26, 2021 at 23:09

2 Answers 2

1

You can get that with a custom function, but it would probably make more sense to use a plain vanilla spreadsheet formula.

The example results you show is row-by-row concatenation rather than a Cartesian product of two sets. To get row-by-row concatenation with a spreadsheet formula, ignoring blank values, use this:

=arrayformula( flatten( filter(A2:A, len(A2:A)) & " " & transpose(filter(B2:B, len(B2:B))) ) )

To get an actual Cartesian product, ignoring blank values, use split() like this:

=arrayformula( split( flatten( filter(A2:A, len(A2:A)) & "µ" & transpose(filter(B2:B, len(B2:B))) ), "µ" ) )

You can handle more than two ranges using the same flatten(transpose()) pattern.

The question explicitly specifies JavaScript, so here's a custom function to do the same:

/**
* Creates a cross join of two or more ranges.
*
* The result is a Cartesian product of the input ranges,
* ignoring any blank values.
*
* @param {A2:A} range1 The first range to join.
* @param {B2:B} range2 The second range to join.
* @param {C2:C} range3 Optional. Any additional ranges to join.
* @return Cartesian product of the ranges, ignoring any blank values.
* @customfunction
*/
function CartesianProduct(range1, range2, range3) {
  // @version 1.0, written by --Hyde, 27 August 2021
  // @see https://webapps.stackexchange.com/a/158699/269219
  // @license https://hyde.mit-license.org/2021
  if (arguments.length < 2) {
    throw new Error('CartesianProduct expected at least two parameters but only got ' + arguments.length + '.');
  }
  let numResults = 1;
  const args = ([...arguments].map(arg => !Array.isArray(arg) ? [[arg]] : !Array.isArray(arg[0]) ? [arg] : arg))
    .map(arg => {
      arg = arg.filter(row => row.join(''));
      if (!arg.length) {
        throw new Error('CartesianProduct expected all parameters to have at least one non-blank value.');
      }
      numResults *= arg.length;
      if (numResults > 1e6) {
        throw new Error('CartesianProduct found ' + numResults + ' results, which is too many to handle comfortably.');
      }
      return arg;
    });
  const cartesian_ = (a, b) => [].concat(...a.map(row => b.map(b => [].concat(row, b))));
  const iterate_ = (a, b, ...rest) => b ? iterate_(cartesian_(a, b), ...rest) : a;
  return iterate_(...args);
}

To use the custom function to do row-by-row concatenation instead of a Cartesian product, process the results with join().

1
  • You're my hero. And you're right, the formula is better! I am also trying to learn JavaScript right now so it is helpful to see how you did that. Very much appreciate the time you took on this! Aug 27, 2021 at 13:51
0

The following custom functions have been made available in the formulas_by_top_contributors Addon:

=ARRAYFORMULA(
 MATRIX(SQLCROSSJOIN(A1:A4,1,B1:B2,2,FALSE),,1)
 &" "&
 MATRIX(SQLCROSSJOIN(A1:A4,1,B1:B2,2,FALSE),,2)
)

enter image description here

disclaimer: created and maintain the Addon

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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