0

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

2
  • Welcome, Elise. Your spreadsheet requires a "request" for access; please change this to accessible by anyone with the link. You need to do as much as possible to provide volunteers with the incentive to consider your question. The "request" may put off some people, and the delay may not be convenient to re-consider your question. – Tedinoz Feb 17 at 23:29
  • Hi - apologies @Tedinoz, my sheet is now public. Thanks! – Elise Feb 18 at 20:06
0

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]);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.