Include adjacent cells when copying matched values to other sheet

Question

I have a script that compares values in cells in two different sheets on a google spreadsheet. It can successfully match find the matches and copy them to a 3rd sheet. However, I want to expand the selection to the column left of the matches and copy over both the matches and adjacent cells.

For clarification: Spreadsheet 1 has emails and majors, spreadsheet 2 has majors under what college they belong to. I am matching the majors so I can get the emails of only the people in a specific college. Right now all it copies are the cells with the major, but I would like it to copy the cells with the email as well (which are one column to the left).

function match_CoE() {
  //Set variables for active spreadsheet, input data, comparitive data, and finalized match input.
  var sh = SpreadsheetApp.getActive();
  var input = sh.getSheetByName('AOP 2018-2019').getRange("C1:C").getValues();
  var values = sh.getSheetByName('Major-College-Conversion').getRange("A72:A75").getValues();
  var output = sh.getSheetByName('College of Education Contacts');
  var match = [];

  //Compare Input to Pre-determined values for matches
  for (i in input){
    var setInput = input[i][0];
    var exists = false;

    for (j in values){
      var setValues = values[j][0];
      if (setValues == setInput){
        exists = true;
        break;
      }
    } // end for j
    if (exists == true){
        match.push([setInput])
    }
  }//end for i

  //Copy matching values to new sheet.
  output.getRange(1, 1, match.length, 1).setValues(match);
}

Why don't you declare var mail = sh.getSheetByName('AOP 2018-2019').getRange("D1:D").getValues();, push its value into another array when exist == true, and populate its value in output the same way you do with match ? — HaPhan
@HaPhan I'm unsure what you mean? Wouldn't that just copy over all the data from the column? — Jacob Mcdowell
Expand your obtained range (e.g. B2:B -> A2:B), and use Array#filter. Note that right now you have a nested loop - for every element of input, you rescan potentially all of values. See my answers here, here, and here for examples that optimize similar tasks. Once you've edited your code to attempt to solve your task, I or others will be happy to help fix the code you've written (vs. just doing it all for you). — tehhowch
Thank you @tehhowch and @HaPhan! I was able to figure it out, you guys were of great help! — Jacob Mcdowell

Jacob Mcdowell Jacob Mcdowell · Accepted Answer · 2018-08-13T00:25:17

I expanded my search range to include the email list and shifted the set data I was matching against over a row. Then changed the setInput variable in the loop to +1 narrowing the search to just the majors and adding a second variable (setInputFina) to return just the emails I was able to generate the list!

function match_CoE() {
  //Set variables for active spreadsheet, input data, comparitive data, and finalized match input.
  var sh = SpreadsheetApp.getActive();
  var input = sh.getSheetByName('AOP 2018-2019').getRange("B1:C").getValues();
  var values = sh.getSheetByName('Major-College-Conversion').getRange("A72:B75").getValues();
  var output = sh.getSheetByName('College of Education Contacts');
  var match = [];

  //Compare Input to Pre-determined values for matches
  for (i in input){
    var setInput = input[i][+1];
    var setInputFinal = input[i][0];
    var exists = false;

    for (j in values){
      var setValues = values[j][+1];
      if (setValues == setInput){
        exists = true;
        break;
      }
    } // end for j
    if (exists == true){
        match.push([setInputFinal])
    }
  }//end for i

  //Copy matching values to new sheet.
  output.getRange(1, 1, match.length, 1).setValues(match);
}

function copySet_CoE() {

}

Include adjacent cells when copying matched values to other sheet

1 Answers