Google Apps Script Inner Join Ranges

Question

I would like to "inner join" 2 data-ranges (SHEET 1 & 2) by "ID" and output its result in SHEET 3 (as shown below)

For all sheets the first row is only the header, the actual values start from the second row.

Sheet 1 is the entire Database (3000 rows), and Sheet 2 (1000 rows) is a selection of Sheet 1 with additional data. Each entry (ID) is only present once in each Sheet.

Sheet 3 should show comprise only the rows of Sheet 2 but with the data of the corresponding row (by ID) of Sheet 1.

var sheetOneVals = sheetOne.getDataRange().getValues(); sheetOneVals.splice(0, 1);

var sheetTwoVals = sheetTwo.getDataRange().getValues(); sheetTwoVals.splice(0, 1);

How can this be done? https://stackoverflow.com/a/17500836/379777 was almost what I wanted except that it uses Keys, which I don't have in my case.

SHEET 1:

     A         B           C      D
  ------------------------------------
1 | Name | Description | Price | ID  |
  ------------------------------------
2 | ABC  | Bla1        | 10    | 123 |
  ------------------------------------
3 | DEF  | Bla2        | 8     | 234 |
  ------------------------------------  
: | :    | :           | :     | :   |
: | :    | :           | :     | :   |
  ------------------------------------

SHEET 2:

    A     B      C     D         E
  ---------------------------------------
1 | ID  | Cat1 | Cat2 | Cat3 | Nonsense |
  ---------------------------------------   
2 | 123 | B    | C    | D    | xyz      |
  ---------------------------------------
: | :   | :    | :    | :    | :        |
: | :   | :    | :    | :    | :        |
  ---------------------------------------

SHEET 3 (desired result):

     A       B      C     D       E         F         G
  ----------------------------------------------------------
1 | ID   | Cat1 | Cat2 | Cat3 | Name | Description | Price |
  ----------------------------------------------------------    
2 | 123  | B    | C    | D    | ABC  | Bla1        | 10    |
  ----------------------------------------------------------
: | :    | :    | :    | :    | :    | :           | :     |
: | :    | :    | :    | :    | :    | :           | :     |
  ----------------------------------------------------------

Kriggs · Answer 1 · 2015-04-02T14:36:17.893

2

You want just the logic or someone to code it all? The logic that would work easy and fast is:

Start and object, with the Key as the ID.
Iterate trough sheet 1 and save all values as objects in those IDs.
Iterate trough sheet 2 saving again new keys, in the ID.
Parse the object to a Double Array.

Here's some stub:

Initiate myObject
Get Sheet1 and sheet2
for( lines in sheet 1 )
  myObject[ lineId ] = {}
    for( cols in sheet 1[lines] )
      myObject[ lineId ][ columnHeading ] = columnValue

for( lines in sheet 2 )
  for( cols in sheet 1[lines] )
    myObject[ lineId ][ columnHeading ] = columnValue

You'll end up with a duplica ID, as the property key and a property in it with the value, but no biggie.

Initiate arrayToPaste
Initiate currLine with 0
for( props in myObject )
  Initiate first array key as an Array
  for( keys in myObject[ props ] ){
    arrayToPaste[ currLine ].push(myObject[ props ][ key ])
  }
  Increase currLine

Paste arrayToPaste to sheet

Just insert the Heading somewhere, can be in the Initialization, after the first for, when pasting, as a different array, etc...

And I also have a question, why the parseInt?

edited Apr 02 '15 at 14:36

answered Apr 02 '15 at 12:33

Kriggs

3,731
1
15
23

Thank you for your answer. You are right, parseInt was useless. I removed it. However, I don't think your solution solves the problem. I edited the text and maybe it is more clear what I want now. – user3797772 Apr 02 '15 at 13:37
Yes, it will. All values will be saved in an object, with the id as the key, directly constructing the object would look something like this: `myObject = { 123:{Cat1:'B',Cat2:'C':Cat3:'D',Nonsense:'xyz',Description:'Bla1' ,Price:10,ID:123,Name:'ABC' }, 234{same as 123 with differente values}};`, this is perfect to parse as a double array, with 123 and 234 being the lines (not the literal line number), and every key in it beeing a column. – Kriggs Apr 02 '15 at 13:42
The logic is already coded in the linked s.o. in the question. – Zig Mandel Apr 02 '15 at 14:57

score 1 · Accepted Answer · answered Apr 02 '15 at 15:21

I ended up with the following solution. I am probably not good enough to entirely understand your solution. I am sure that your solution is much faster.

function createSheet3(){
  var ss        = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("sheet1");
  var sheet2 = ss.getSheetByName("sheet2");
  var sheet3 = ss.getSheetByName("sheet3");

  var sheet3Header = [Name,Description,Price,ID,ID,Cat1,Cat2,Cat3,Nonsense]

  var sheetOneVals = sheet1.getDataRange().getValues();
  sheetOneVals.splice(0, 1);

  var sheetTwoVals = sheet2.getDataRange().getValues();
  sheetTwoVals.splice(0, 1);

  var consolidatedArr = new Array();

  for each (var item in sheetTwoVals){  
   for (var i in sheetOneVals) {
     if(item[0] == sheetOneVals[i][3]){
       consolidatedArr.push(sheetOneVals[i].concat(item));
     }
    }
  }
  sheet3.clear({contentsOnly:true});
  sheet3.getRange(1,1,1,9).setValues(sheet3Header);
  sheet3.getRange(2,1,consolidatedArr.length,9).setValues(consolidatedArr);


  SpreadsheetApp.flush();                          
  Utilities.sleep("200"); 
  if(consolidatedArr.length > 0){
    var last = sheet3.getLastRow();                     
    var max  = sheet3.getMaxRows();
    if (last !== max && max-last > 1) {sheet3.deleteRows(last+2,max-last-1);}
  }
}

Google Apps Script Inner Join Ranges

2 Answers2