Google-sheets – How to include VLOOKUP with a loop for a list of data on Google Scripts

google sheetsgoogle-apps-script

I have a list of data on Sheet1 columns A-N, and a list of updates on Sheet2, columns A-N. I have a code that imports data to Sheet2, and I would like to add to my code a similar function to VLOOKUP. In essence, I want the code to check the values in Column C on Sheet1 within my list of updated data in Column B on Sheet2. If it finds a match, I want the code to copy/paste the updates in the corresponding columns for that found value in that row in Sheet 1. If it doesn't find a match, I want it to copy/paste the whole row from Sheet2 to the bottom of the list in Sheet1.

In my example file, I have two yellow cells on Sheet 2 highlighted to show what I want the code to find. Essentially, the script should look up the value in Column C on Sheet 1, search for the same value in Column B on Sheet2, and if it finds it, to pull in the updated cells and copy/paste to the corresponding cell on Sheet1. I also have a new set of data in row 5 of Sheet 2, and the code should copy/paste this whole row to Sheet 1.

I have the start of a code, but I know it needs much more:

function TEST() {
  var sh = SpreadsheetApp.getActiveSheet();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var last=ss.getLastRow();
  var data=sh.getRange(1,1,last,2).getValues();

  for(nn=0;nn<data.length;++nn){
    if (data[nn][1]==valB){break} ;
    //Copy/pastes updates if a value is found
    //Copy/pastes whole row if value isn't found
  }
}

Example Sheet

Best Answer

You have search values in a given column on a given sheet. You want to use those values to search for matching values in a different given column on a different sheet. If a match is found, then you want to update any differences from the search row to the matched row. If a difference is not found, you want to append the entire search row to the un-matched sheet.

Consider the following script:

function wa_15100603() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // identify the sheets
  var searchName = "Sheet2";
  var lookupName = "Sheet1";
  var search = ss.getSheetByName(searchName);
  var lookup = ss.getSheetByName(lookupName);

  // establish some sheet-related variables
  var searchSearchCol = 2; // column B
  var lookupSearchCol = 3; // column C
  var colNum = 14; // column N
  var searchStartRow = 2; // first row of data
  var lookupStartRow = 6; // firt row of data

  // calculate the number of rows of data in search
  var searchLR = search.getLastRow();
  var searchRows = searchLR-searchStartRow+1;
  //Logger.log("DEBUG: SEARCH SHEET: start row = "+searchStartRow+", and last row = "+searchLR+", number of rows = "+searchRows)

  // calculate the number of rows of data in lookup
  var lookupLR = lookup.getLastRow();
  var lookupRows = lookupLR-lookupStartRow+1;
  //Logger.log("DEBUG: LOOKUP SHEET: start row = "+lookupStartRow+", and last row = "+lookupLR+", number of rows = "+lookupRows)
 
  // get data
  // lookup range is declared so that it can be used later 
  var lookupRange = lookup.getRange(lookupStartRow,1,lookupRows,colNum);
  var lookupData = lookupRange.getValues();
  //Logger.log(lookupData); // DEBUG
  var searchData = search.getRange(searchStartRow,1,searchRows,colNum).getValues();
  // Logger.log(searchData); // DEBUG

  // get a 1D array for search search column
  var searchSearchRange = searchData.map(function(e){return e[(+searchSearchCol-1)];});
  //Logger.log(searchSearchRange); // DEBUG
  // get a 1D array for lookup search column
  var lookupSearchRange = lookupData.map(function(e){return e[(+lookupSearchCol-1)];});
  //Logger.log(lookupSearchRange); //DEBUG


  // loop through each value in the search range to find a match in the lookup range
  for (var v = 0;v<searchData.length;v++){

    // find the search  term in the lookup range
    var result = lookupSearchRange.indexOf(searchSearchRange[v]);
    //Logger.log("DEBUG: v = "+v+", result = "+result+", search value = "+searchSearchRange[v])
  
    // if term is found, result is the row index on search
    // otherwise, if not found, then result will be -1
    if (result != -1){

      // found a match, so test all the values on this line
      // Logger.log("DEBUG: Match:  Search value:"+searchSearchRange[v]+" was found on lookup sheet line#:"+result)
    
      // loop through the values on the respective row for search and lookup
      for (var i=0;i<colNum-searchSearchCol-1;i++){

        
        if (searchData[v][(+searchSearchCol+i)] == lookupData[result][(+lookupSearchCol+i)]){
          // if search value = lookup value then do nothing
          //Logger.log("DEBUG: i:"+i+",search:"+searchData[v][(+searchSearchCol+i)]+", lookup:"+lookupData[result][(+lookupSearchCol+i)]+", Match- No action")
          
        }else{
          // if search value <> lookup value, update lookup value
          //Logger.log("DEBUG: i:"+i+",search:"+searchData[v][(+searchSearchCol+i)]+", lookup:"+lookupData[result][(+lookupSearchCol+i)]+", Update the lookup data")
          lookupData[result][(+lookupSearchCol+i)] = searchData[v][(+searchSearchCol+i)]

        }

      }
      
    } else {
      // if result = -1, then append whole row to lookup
      // Logger.log("DEBUG: no match for this search search term, copy entire row to lookup")
      // but first, pad a blank cell in the search data so that the search term will appear in column C
      //Logger.log(searchData[v]); // DEBUG before
      searchData[v].splice(0, 0, " ");
      //Logger.log(searchData[v]); // DEBUG after
      lookup.appendRow(searchData[v]);

    }
  }

  // update the lookup data for any data changes
  lookupRange.setValues(lookupData);

  return;
 
}

Lookup sheet: Before

Before

Lookup sheet: After

After

PROCESSING LOGIC

  • Get the data for both sheets

    • var lookupRange = lookup.getRange(lookupStartRow,1,lookupRows,colNum);, var lookupData = lookupRange.getValues();
    • var searchData = search.getRange(searchStartRow,1,searchRows,colNum).getValues();

  • Create 1D arrays for the search values on both sheets by using the Javascript map method (ref)

    • var searchSearchRange = searchData.map(function(e){return e[(+searchSearchCol-1)];});
    • var lookupSearchRange = lookupData.map(function(e){return e[(+lookupSearchCol-1)];});

  • Using the Javascript indexOf method (ref), loop through the search values using the 1D array values to find a matching term in the lookup range

    • var result = lookupSearchRange.indexOf(searchSearchRange[v]);
    • the value of result will be either:
      • Match: the (zero-based) row number of the matched search value
      • No match: -1

  • if there is a match (if (result != -1){), then

    • compare all the values on the search row and the matched lookup row, and update any differences to the lookup row.
      • lookupData[result][(+lookupSearchCol+i)] = searchData[v][(+searchSearchCol+i)]
      • the lookup row number (result) and the search row number (v) could have different row index values, but these variables maintain consistency from sheet to sheet.
    • then in the last command of the script, update the lookupRange with the changes
      • lookupRange.setValues(lookupData);

  • if no match, then append the entire row

    • BUT
      • columns on search term sheet = 13
      • columns on lookup sheet = 14
    • AND
      • search term column = B (2)
      • lookup term column = C (3)
    • SO
      • insert a dummy (nil) value at the start of the search term row array to create 14 column values and align search terms to column C
      • searchData[v].splice(0, 0, " ");
    • THEN
      • append the row
      • lookup.appendRow(searchData[v]);
Related Topic