Looping through Datang to s in Row X on Sheet A and see if it matches Data in Row X of Sheet B and if so need to get data from Adjacent Cell

Question

So I have sheet that has data in Row X of Column C and D, so two cells worth of data. I'm going to store data from Row X into a variable to hold it. I then want to use the data, a numeric value from Row X Column C to find a matching value on another sheet called DataBase located somewhere in Column J. Once if find that number i want to take the data in the adjacent cell , Column K and store that in a variable. Then i need to compare those variables to see if they match if they dont change the color of the invalid data from sheet1 Column D and move onto the next Row of Column C and continue the process till im done.

function goodDataCheck() {


  var sheet = SpreadsheetApp.getActive();
  var bpNum = sheet.getDataRange().getValues();


  for(var i = 0; i < bpNum.length-1;i++)
  {
    var matcher = sheet.getSheetByName("Database").getDataRange(i + 1, 9).getValues(); // get value in cell i + 1 column 9 or "j"

    if(bpNum[i][2] == matcher[i][9])
      {
      var alleleRow = i;
      var currentAllel = sheet.getRange(alleleRow + 1, 9); // get value of 
      current cell


      if (currentAllel == matcher)
      {
        sheet.getRange(i, 3).setBackground('green')
      } else
      {
          sheet.getRange(i, 3).setBackground('yellow')
      }

    }
  }
}

i get following error Cannot find method getDataRange(number,number). (line 10, file "AllelGoodData

From your error message, I think that the reason of the error is getDataRange(i + 1, 9) of sheet.getSheetByName("Database").getDataRange(i + 1, 9).getValues(). getDataRange() doesn't use the arguments. If you want to get the range, how about using getRange(i + 1, 9)? I think that by this modification, the error message is removed or changed. This was not the direct solution, I apologize. — Tanaike
No i worded it wrong i just reworded it i hope a little more clearly — joneil27
i just thought of a roundabout way ill post the code here tomorrow, but in essence i create 2 arrays from the database sheet. I then take the value of Row X and Column C from sheet1 and map the first array to find the element of the matching value. Once i have it i use that element to locate the adjacent cell in database and match it to the value from Row X Column D. — joneil27

Leonardo Wlach Leonardo Wlach · Accepted Answer · 2019-05-17T01:53:11

This is probably happening because your call for

sheet.getSheetByName("Database")

returns

undefined

Try to debug the code or declare a new variable to keep the value of sheet.getSheetByName("Database") and check if this is really happening.

Looping through Datang to s in Row X on Sheet A and see if it matches Data in Row X of Sheet B and if so need to get data from Adjacent Cell

1 Answers