Apps Script - Searching an array for a string and return true or false if that string is present-CodePudding

I am using Apps Script with Google Sheets. I have written some code to get the value in a cell and save it to a variable. I then created an array with all those variables. I want to be able to look through the array and look for the word "fail" and return true if it is present or false if it is not present. It only has to match once in the array for it to be true.

This is what I have so far:

function myFunction() {

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var activeSheet = ss.getActiveSheet();

  for(var i = 20;i<=120,i  ){

      var main = activeSheet.getRange(i, 8).getValue();
      var state = activeSheet.getRange(i, 9).getValue();
      var scroll = activeSheet.getRange(i, 10).getValue();;
      var log = activeSheet.getRange(i, 11).getValue();

      const testArray = [main, state, scroll, log];
//need some code here to look for the string "fail" in the array and if it is there, return true, otherwise return false.
   
  }
}

CodePudding user response：

Try this code instead. I've made your testArray as an empty array, then insert each of the value of the cells inside the array. You can then use the .includes function

function myFunction() {

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var activeSheet = ss.getActiveSheet();
 
  for(var i = 20;i<=120;i  ){

    var testArray = []

    var main = activeSheet.getRange(i, 8).getValue();
    var state = activeSheet.getRange(i, 9).getValue();
    var scroll = activeSheet.getRange(i, 10).getValue();
    var log = activeSheet.getRange(i, 11).getValue();

    //insert each value to array
    testArray.push(main,state,scroll,log);

    //check if word fail is in array
    var checkWord = testArray.includes("fail");
    console.log(checkWord);
   
  }
}

Or if you want it to only be checked once you can put the empty array and syntax to check the word outside the for loop. See code below:

function myFunction() {

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var activeSheet = ss.getActiveSheet();
 var testArray = []

  for(var i = 20;i<=120;i  ){

    var main = activeSheet.getRange(i, 8).getValue();
    var state = activeSheet.getRange(i, 9).getValue();
    var scroll = activeSheet.getRange(i, 10).getValue();
    var log = activeSheet.getRange(i, 11).getValue();

    //insert each value to array
    testArray.push(main,state,scroll,log);

    //check if word fail is in array
  }
    var checkWord = testArray.includes("fail");
    console.log(checkWord);
}

Let me know if this works!

Reference: https://www.digitalocean.com/community/tutorials/js-array-search-methods

CodePudding user response：

Here's a simple suggestion:

function myFunction() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var activeSheet = ss.getActiveSheet();
var rng = activeSheet.getRange(20,9,20,4);
var data = rng.getValues();

var confirmation = false
for(var i = 0; i<=data.length, i  ){
    var rowData = data[i];
    if(rowData.indexOf("fail")>-1){
    confirmation = true;
    }  
  }
}