CODEWITHSUNDEEP
arraysgoogle-apps-script
Marcel Alexis
Marcel Alexis

Reputation: 3

Gscript seek one value of one sheet and write to the first sheet

I am trying to get this to work. Basically, I have two sheets (sheet1 and sheet3) in a google sheet file. I have one column A in sheet 1 with values (1,2,3,4,5,6) and one in sheet 2 with values (1,2,3) - both starting from row 2. I'd like to have sheet1 updated in column B as the following when values do not find match in sheet3 1 OK 2 OK 3 OK 4 not Ok 5 not Ok 6 not Ok

I tried with arrays and ended up with the following

function IDValidation1() {
  var ss= SpreadsheetApp.openById('1-GWOrXt-rbfpocrJjO-5ywDXu2oG4y1x14na252525');
  var sh1=ss.getSheetByName('Sheet1');
  var sh2=ss.getSheetByName("Sheet3");
  var sh1rg=sh1.getRange(2,1,sh1.getLastRow(),1); //header is row 1
  var sh2rg=sh2.getRange(2,1,sh2.getLastRow(),1);//header is row 1
  var sh1cvA=sh1rg.getValues();
  var sh2cvA=sh2rg.getValues();
  for(var i=0;i<sh1cvA.length-1; i++){
    for (var j=0;j<sh2cvA.length-1; j++){
      if (sh1cvA[i]==sh2cvA[j]){
          sh1.getRange("B"+[i+2]).setValue("Ok");
          }
      else {
          sh1.getRange("B"+[i+2]).setValue("Not Ok");
          }
    }
  }}

How could I make it work? At the moment it always update with "Not Ok" but I would expect a "Ok" for first three values

Thank you for your help!

Upvotes: 0

Views: 35

Answers (2)

Marcel Alexis
Marcel Alexis

Reputation: 3

Thank you, for your help, I could fix the code. I came up with a few more fixes to my code and here is the final version

function IDValidation1() {
  var ss= SpreadsheetApp.openById('fileid');
  var sh1=ss.getSheetByName("Sheet1");
  var sh2=ss.getSheetByName("Sheet3");
  var sh1rg=sh1.getRange(1,1,sh1.getLastRow(),1); 
  var sh2rg=sh2.getRange(1,1,sh2.getLastRow(),1);
  var sh1cvA=sh1rg.getValues();
  var sh2cvA=sh2rg.getValues();
  for(var i=0;i<sh1cvA.length; i++){
    for (var j=0;j<sh2cvA.length; j++){
      if (sh1cvA[i][0]==sh2cvA[j][0]){
        Logger.log("IF "+sh1cvA[i][0]+sh2cvA[j][0]+i+j);
        sh1.getRange(i+1,2).setValue("OK");
                  }
    }
  }}

Upvotes: 0

dbmitch
dbmitch

Reputation: 5386

You're treating the output from getValues like it's a one-dimensional array. Even though you're asking it only for one column - it's still going to return a two-dimensional array with a fixed value 1 for your column.

getValues() Returns the rectangular grid of values for this range.

Returns a two-dimensional array of values, indexed by row, then by column

Change your comparison from

if (sh1cvA[i]==sh2cvA[j]){

To

if (sh1cvA[i][0]==sh2cvA[j][0]){

Upvotes: 2

Related Questions