I am trying to compare values on 2 different columns values to ensure that they exist(on Sheet 1) Once the script is aware that they exist I need to access sheet1's column for quantity and add that value to sheet 2's quantity column. The problem is I am unsure of how to just get the location/index of the foreach loop and offset a setValue to another column without setting the value to the entire column(I dont want to do that if the product name of column A does not exist in Sheet1)

Here is the code example of how i am trying to do it

I have included it in a pastebin because I could not figure out how to format the code to paste ( sorry i'm super new at this!)

<https://pastebin.com/EKB2n9kA>

Sheet1 incoming data https://drive.google.com/file/d/1eLNeOZZbdeCDfMMImksVRnBXwKxpHIO_/view?usp=sharing

Sheet2 'base' data to add quantity values to https://drive.google.com/file/d/1h26H9eQgZapd2Y0LVamhRPYme-8LmVF0/view?usp=sharing

example of expected/wanted results https://drive.google.com/file/d/1-0ozD5PrbIq-otG4j7kAyLufQFjDR5Hi/view?usp=sharing

I have also attached 3 different reference photos

Sheet 1 is 'incoming' data to read Sheet 2 is our 'base' data and where Sheet1's quantity column needs to be added to the third screenshot is the expected result(having the script skip over rows that do not contain matching data but still being able to get the quantity value based on the index/location the value was found)

Any insight on how to achieve this would be sincerely appreciated

I have tried pushing the results into an empty array but it does not seem to give much useful info the way I am doing it.

I have also tried just getting an offset range (getRange("G2:G").offset(0,3).setValues() to set the results but it sets the value of the entire column instead of only where the values match for each column being compared.

CodePudding user response：

Hopefully this will help

function comparingColumns() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const osh = ss.getSheetByName("Sheet1").
    const[h,...vs] = sh.getDataRange().getValues();
  vs.forEach((r, i) => {
    //comparing column A to Column D
    if (r[0] == r[3]) {
      //getting column G of same row in sheet1
      osh.getRange(i   2, 7).setValue(r[6]);
    }
  })
}

CodePudding user response：

From your <https://pastebin.com/EKB2n9kA>, it seems that your current script is as follows.

function compareCol() {
  var ss = SpreadsheetApp.getActiveSpreadSheet();
  var s1 = ss.getSheetByName("Sheet1");
  var s2 = ss.getSheetByName("Sheet2");
  var datA = s1.getRange("A2:A").getValues();
  var datB = s2.getRange("A2:A").getValues();
  var quantityA = s1.getRange("C2:C").getValues();
  var quantityB = s2.getRange("D2:D");
  let matchingCol = [];
  for (var i in datA) {
    for (var x in datB[i]) {
      if (datA[i][0] === datB[x][0]) {
      }
    }
  }
}

Modification points:

• In your script, the if statement in the loop has no script. And, quantityA, quantityB, and matchingCol are not used.

When your script is modified for achieving your goal, how about the following modification?

Modified script:

function compareCol() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s1 = ss.getSheetByName("Sheet1");
  var s2 = ss.getSheetByName("Sheet2");
  var datA = s1.getRange("A2:C"   s1.getLastRow()).getValues().reduce((o, [a, , c]) => (o[a] = c, o), {});
  var datB = s2.getRange("A2:D"   s2.getLastRow()).getValues();
  var matchingCol = datB.map(([a, , , d]) => [datA[a] || d]);
  s2.getRange(2, 4, matchingCol.length).setValues(matchingCol);
}

• In this modification, when the value of column "A" in "Sheet2" is not found from column "A" of "Sheet1", the value of column "D" of "Sheet2" is used. If you want to remove this, please modify it as follows.

  • From

      var matchingCol = datB.map(([a, , , d]) => [datA[a] || d]);
    

  • To

      var matchingCol = datB.map(([a]) => [datA[a] || null]);
    

References:

• reduce()
• map()