CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Yacasuone
Yacasuone

Reputation: 79

Search for a match between 2 values on different sheets and retrieve data from an other column into the first sheet with google app script

Here is the case : I have 1 sheet with data in a column and I would like, if in my second sheet (in an identified column) there is a data match, retrieve data from an other column into my first sheet.

To be more explicit :

I can't figure out how to store this data and make the input in the other sheet.

Below what I have found so far : 

function updateNote() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssDeCo = ss.getSheetByName("Deco");
  var lr1 = ssDeCo.getLastRow();
  var ssDataBase = ss.getSheetByName("Database");
  var lr2 = ssDataBase.getLastRow();

  for (var i = 2; i < lr1; i++) {
    var gencodDeCo = ssDeCo.getRange('E').getValue();
    for (var j = 2; i < lr2; i++) {
      var gencodDataBase = ssDataBase.getRange('A').getValue();
      var evalDataBase = ssDataBase.getRange('D').getValue(); 
      if (gencodDeCo[i] == gencodDataBase[j]) {
         ssDeCo.getRange('I').setValue(evalDataBase);
      }
    }
  }
};

Upvotes: 0

Views: 79

Answers (1)

Tedinoz
Tedinoz

Reputation: 7949

The OP is trying to match data from Sheet1 to Sheet 2, and then fill the cell in Column I of the corresponding row on Sheet 1 with data from Column D of the matched Sheet2 value. The OP's problem was in getting the relevant Sheet 2 Column D data and then saving it to the appropriate row of Column D in Sheet1.

The relevant differences to the OP code are:

  • Execute all getValues() methods prior to the loop => faster, more efficient code.
  • Database data is obtained in two transactions.
    • 1) all the data on the spreadsheet (including both Column A and Column D), and
    • 2) Column A alone; this is to create a single array to use for matching.
  • Deco data is obtained once only - for Column D.
  • The values for Deco Column D and Database Column A are 2D arrays; in order to simplify matching, they are both "flattened" to 1D arrays.
    • a 2D value of getValues() might look like this:
      [[r88], [g90], [h105], [i119], [j125], [k133], [l142], [m154], [n164]]
    • a 1D array, after "flattening", would look like this:
      [r88, g90, h105, i119, j125, k133, l142, m154, n164]
  • There is a single loop based on values from Deco Column D.
    • Each value is compared to Database Column A.
    • The Indexof method is used to find matches.
    • The resulting value is the first index at which the value can be found in the array, or -1 if it is not present.
  • The relevant value from Database Column D is obtained and "pushed" onto a temporary array ("updateColI").
  • When the loop is complete, the newly created array is used with a setValues() method to update the values in Deco Column D

I have left a number of "Logger" statements in the code so that the user can find relevant values as the code proceeds.

function so5813783501() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssDeCo = ss.getSheetByName("Deco");
  var lr1 = ssDeCo.getLastRow();
  var ssDataBase = ss.getSheetByName("Database");
  var lr2 = ssDataBase.getLastRow();

  // get the data from database
  var dbRange =ssDataBase.getRange(2,1,lr2-1,4);
  // Logger.log("DEBUG: dbRange = "+dbRange.getA1Notation());
  var dbData = dbRange.getValues();

  // get ColA data from database
  var dbColA = ssDataBase.getRange(2,1,lr2-1,1);
  // Logger.log("DEBUG: dbColA = "+dbColA.getA1Notation());
  var dbColAData = dbColA.getValues();

  // flatten the data on ColA of the database
  var flatCola =dbColAData.reduce(function(a, b){return a.concat(b);});
  // Logger.log(flatCola); // DEBUG

  // get the data from Deco
  var decoRange = ssDeCo.getRange(2,5,lr1-1,1);
  // Logger.log("DEBUG: decoRange = "+decoRange.getA1Notation());
  var decoData = decoRange.getValues();
  // Logger.log(decoData); DEBUG

  // flatten the Deco data array
  var flatDeco =decoData.reduce(function(a, b){return a.concat(b);});
  // Logger.log(flatDeco); // DEBUG

  var updateColI = [];

  // Loop through the Deco data to find match on Database
  for (var i=0;i<decoData.length;i++){

    // find a match on database Column A
    var idx = flatCola.indexOf(decoData[i][0]);
    //Logger.log("DEBUG: i = "+i+", value = "+decoData[i][0]+", and idx = "+idx)

    // if idx = -1, then no match, otherwise macth value if the sequence number on the database.
    if (idx !=-1){
      // do something
      updateColI.push([dbData[idx][3]]);
    }
    else{
      updateColI.push([""]);
    }
  }

  // Logger.log(updateColI); //DEBUG

  // update Deco
  var fillDeco = ssDeCo.getRange(2,9,lr1-1,1);
  Logger.log("DEBUIG: Fill Deco range = "+fillDeco.getA1Notation())
  fillDeco.setValues(updateColI);
}

Upvotes: 2

Related Questions