Finding two elements within an array with IF statement using Apps Script

I am very new to programming and I am trying to locate two consecutive values within a 1-d array in google sheets and the answers need to be highlighted in bold in the google sheet. The first value should be greater than 5 (located for example in cell A3) and the next value should be less than 4.99 (and located in C3). I have tried two methods but can't seem to get the Code to work - I don't know if I am over-complicating the problem or not. The first method was to make the row into 2 separate arrays of elements so that if element J is greater than 5, and element k (which looks at the element next to J) is less than 4.99.

var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();  var ui2 = SpreadsheetApp.getUi();  var data1 = ss.getRange(10,2,1,20).getValues()[0]; //Variable 1 - to find the ON value B10:U10  var data2 = ss.getRange(10,3,1,20).getValues()[0]; //to find the OFF value;  var onA = 5.00;  var on = data1 > onA;  var offA = 4.99;  var off = data2 < offA;    for(var i = 0; i < 20; i++){  for(var j = 0; j < 20; j++){    if(data1[i] < 5.00 && data2[j] < 4.99){    ss.getRange(10,2,1,20).setValue(data1[i]).setFontSize(12).setFontWeight("bold");   ss.getRange(10,3,1,20).setValue(data2[j]).setFontSize(12).setFontWeight("bold");   break;  }   else {     ui2.alert("the values are not found");     break;   }}}}  

My other method was not search the cells as a single array - this works but once I add a message to a else statement to state the values are not found it would repeat this alert for the same number of times as the loop.

var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();  var ui2 = SpreadsheetApp.getUi();    for(var j = 2; j < 22; j++){ // j is column number  for(var k = j+1; k < 23; k++){    var result = ss.getRange(10,j).getValue();  var result2 = ss.getRange(10,k).getValue();  var resulta = result > 5.00;  var resultb = result2 < 4.99;      if (resulta == true && resultb == true) {      ss.getRange(10,j).setValue(result).setFontSize(12).setFontWeight("bold");    ss.getRange(10,k).setValue(result2).setFontSize(12).setFontWeight("bold");      break;    } else {      break;      ui2.alert('Values not found');  }}}}  

Any help would be amazing and very greatly appreciated!

https://stackoverflow.com/questions/66508846/finding-two-elements-within-an-array-with-if-statement-using-apps-script March 07, 2021 at 01:52AM