samedi 6 mars 2021

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!

Aucun commentaire:

Enregistrer un commentaire