Google scripts; Alter Matching function that searches spreadsheet by column, return row of match

Question

I need to alter my Matching function, that matches two columns on separate sheets and returns a the matches to a third sheet so that it returns the row of the match

I have been trying, my head is spinning, no success

Thanks

function RunMatch() {

  // 0 is the LookIn sht column # to match on 
  // 1 is the LookWith sht column # to match on 
  MatchCols("LookInSheet","LookWithSheet","PostbackSheet",0,1)  

}

function MatchCols(sShtName,tShtName,pbShtName,matchIn, matchWith){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var LookInSht = ss.getSheetByName(sShtName);
  var LookWithSht = ss.getSheetByName(tShtName);
  var PBSheet = ss.getSheetByName(pbShtName);

  var LookIn = LookInSht.getDataRange().getValues();
  var LookWith = LookWithSht.getDataRange().getValues();

  var list = [];

  for (i in LookIn){
    var curName = LookIn[i][matchColIn];
    var exists = true;

    for (j in LookWith){
      var curCheck = LookWith[j][matchColWith];
      if (curCheck == curName){
        exists = false;
        break;
      };
    }; // end for j

    list.push([exists ? "" : curName]);

  } // end for i

  PBSheet.getRange(1, PBSheet.getLastColumn() +1, list.length, 1).setValues(list);

}

What happens when you run the code? Where do the function's parameters come from? — Finn Smith, Aug 06 '16 at 01:07
When I run the code I get a column of the `matched` elements on the row of the match posted back to the `postback sheet`. I set the parameters. I updated the question with how I call the function — xyz, Aug 06 '16 at 01:57
I understand what your code is doing, but I don't understand what you want it to do. Instead of creating a column in the `PostbackSheet` with the matched values, you want it to create a column with the number of row that matched or both values? — ocordova, Aug 07 '16 at 03:31
No, I want it to post back the entire row of cells where a match is found, not just the matched cell — xyz, Aug 07 '16 at 13:07

score 0 · Answer 1 · answered Aug 08 '16 at 00:34

0

If I understand correctly, you want the PostbackSheet to contain the entire row from LookIn when the value in the matchColIn column of LookIn is found in the matchColWith column of LookWith.

Instead of using exists, just add the row to list when you find the match:

if (curCheck == curName){
  list.push(LookIn[i]);
  break;
}

Then, when you write to PBSheet, use the size of list to make sure the range is the correct size:

PBSheet.getRange(1, PBSheet.getLastColumn() +1, list.length, list[0].length).setValues(list);

answered Aug 08 '16 at 00:34

Tiffany G. Wilson

573
3
11

Tiffany, that almost gets me there!. But I need to `postback` the rows on the rows the matches were found in `LookWith` So if a match occurred on row 5 of LookWith the postback of the entire row in`PostbackSheet` would be on row 5. I think that is what `list.push([exists ? "" : curName]);` was doing posting pack on the row the match was found on (I think). Can you show me how to do this last part?. I tried mixing codes but no success! Thanks – xyz Aug 08 '16 at 02:31
The first thing that comes to mind is to create a second array `var rowNum = []` right after you define `list`. Then, after `list.push(LookIn[i])`, do `rowNum.push(j)`. You could then do a loop through `var k=0; k – Tiffany G. Wilson Aug 08 '16 at 03:11
Tim, if that works for you, let me know and I'll update the answer. – Tiffany G. Wilson Aug 08 '16 at 03:18
Tiffany, thanks!, it is late here, I will test it first thing in the morning and let you know – xyz Aug 08 '16 at 03:25
Sorry for the many comments but I missed my 5-minute edit window. The above suggestion is the same as writing to `PBSheet` using row `j+1` each time you find a match, so instead of `list.push()` you just write the row `LookIn[i]` to `PBSheet`. It's not elegant since you're invoking `setValues()` as many times as matches you have, but it will work. – Tiffany G. Wilson Aug 08 '16 at 03:25
Tiffany, I tried your suggestion and it did post back the data but it adds `9 empty rows` between each row posted back – xyz Aug 08 '16 at 17:40
In `LookWith` sheet there are `9 rows` that have `no` matches to the column in`LookIn` sheet – xyz Aug 08 '16 at 17:41
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/120463/discussion-between-tiffany-g-wilson-and-tim). – Tiffany G. Wilson Aug 08 '16 at 21:33

Google scripts; Alter Matching function that searches spreadsheet by column, return row of match

1 Answers1