Warm tip: This article is reproduced from stackoverflow.com, please click
google-sheets google-sheets-query

Create rows from combination of other rows

发布于 2020-03-31 22:57:37

I'm looking for a way to repeat records/rows based on two different tables, like below:

TABLE 1:

Col1  Col2
A     101
B     102
C     103

TABLE 2:

Col3
1
2
3

REPEATING RESULTS:

Col1   Col2   Col3
A      101    1
B      102    1
C      103    1 
A      101    2
B      102    2
C      103    2
A      101    3
B      102    3
C      103    3

In 'Microsoft Access' or SQL i can do it very quickly, by using SQL select string:

SELECT table1.column1, table2.column1
FROM table1, table2 

but i'm wondering if there is any way to do it in google sheets with QUERY function etc.

Questioner
Jacob Spitzer
Viewed
65
Iamblichus 2020-01-31 19:39

You can accomplish this via a custom function created in Google Apps Script. To achieve this, follow these steps:

  • In your spreadsheet, select Tools > Script editor to open a script bound to your file.
  • Copy this function in the script editor, and save the project:
function REPEAT(table1, table2) {
  var output = [];
  output.push([]);
  for (var i = 0; i < table1[0].length; i++) {
    output[0].push(table1[0][i]);
  }    
  for (var i = 0; i < table2[0].length; i++) {
    output[0].push(table2[0][i]);
  } 
  for (var i = 1; i < table2.length; i++) {
    for (var j = 1; j < table1.length; j++) {
      output.push([]);
      for (var k = 0; k < table1[j].length; k++) {
        output[output.length - 1].push(table1[j][k]);
      }    
      for (var k = 0; k < table2[i].length; k++) {
        output[output.length - 1].push(table2[i][k]);
      }       
    }
  }
  return output;
}
  • Now, if you go back to your spreadsheet, you can use this function just as you would do with any other function. You just have to provide the appropriate table ranges as parameters, as you can see here:

enter image description here

I hope this is of any help.