Google-sheets – Google script match values from a column in one sheet to a column in another sheet

google sheetsgoogle-apps-script

I'm trying to filter two columns in Google Spreadsheets, say A in sheet 1 (which contains work order numbers) and A in sheet 2 (which contains the same strings of work order numbers but they are hyperlinks). I'm trying to compare the values in both columns and match every work order with a hyperlink named the same. So if sheet 1 cell A3 has "1234" and sheet 2 column A2 has hyperlink "1234", filter the hyperlink to sheet 1 cell R3 to be on the same row as the work order.

I'm currently able to do this in a crude way where I copy a filter formula to all cells in target as in:

=IFERROR(FILTER({'SHEET 2'!A$2:A},('SHEET 2'!A$2:A) = ('SHEET 1'!A3)),"")
=IFERROR(FILTER({'SHEET 2'!A$2:A},('SHEET 2'!A$2:A) = ('SHEET 1'!A4)),"")

That works but this does not work:

=IFERROR(FILTER({'SHEET 2'!A$2:A},('SHEET 2'!A$2:A) = ('SHEET 1'!A3:A)),"")

If it worked it could have helped me match all hyperlinks to work orders with only one filter function in the first cell.

Otherwise now I'm trying to implement it in Google script so that I don't have a formula copied on all hundreds of rows. I have this so far, but it's not working. It runs for several minutes and only returns a few random 1, 0 and undefined before I cancel it because it's taking too long.

function shop_traveler_filter(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("SHEET 1");
  var maxrow = sheet.getMaxRows();
   //var vals = sheet.getRange(3,1,maxrow,1).getValues();
  var sheet2 = ss.getSheetByName("SHEET 2");
  var maxrow2 = sheet2.getMaxRows();
   //var vals2 = sheet2.getRange(2,1,maxrow2,1).getValues();
  for (var i = 3; i < maxrow; i++) {
    var values = sheet.getRange(i,1).getValue();
    for (var j = 2; j < maxrow2; j++) {
      var values2 = sheet2.getRange(j,1).getValue();
      if (values[i] == values2[j]) {
        sheet.getRange(i,18).setValue(values2[j]);
      }
    }
  }
}

How to get this right?

Best Answer

Your task is better described as matching or lookup rather than filtering: you want at most one entry to be returned. This can be efficiently done with vlookup 

=arrayformula(iferror(vlookup('SHEET 1'!A2:A, 'SHEET 2'A2:A, 1, False)))

This looks up every element of 'SHEET 1'!A2:A in the column 'SHEET 2'A2:A and returns what is found. The arrayformula part ensures that everything is handled at once.

You may still run into performance problems, in which case you should either get rid of any empty rows at the bottom of the sheet (they are being looked up, too) or to filter out the blanks. That is, replace:

  • 'SHEET 1'!A2:A with filter('SHEET 1'!A2:A, len('SHEET 1'!A2:A))
  • 'SHEET 2'!A2:A with filter('SHEET 2'!A2:A, len('SHEET 2'!A2:A))

I'll add some remarks on the script, although I haven't studied it much.

  • Use getLastRow() instead of getMaxRows(), so that the loop runs only over the rows with data. Same for columns.
  • Do not call setValue in a loop. Instead, store the values you want to insert in a suitable array output, and after the loop call setValues(output). You can see such an example here.
Related Topic