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

Question

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.
   
  }
}

Hi! Which variable will possibly hold the word fail?

onit
– onit

2022-03-30 23:03:06 +00:00
Commented Mar 30, 2022 at 23:03 — onit
– onit, Commented Mar 30, 2022 at 23:03
It could be any of them. It will be different on each row.

Ann
– Ann

2022-03-30 23:20:45 +00:00
Commented Mar 30, 2022 at 23:20 — Ann
– Ann, Commented Mar 30, 2022 at 23:20

EnzoC · Accepted Answer · 2022-03-30 23:44:16Z

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

onit · Accepted Answer · 2022-03-31 00:42:08Z

0

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;
    }  
  }
}

edited Mar 31, 2022 at 0:42

answered Mar 30, 2022 at 23:08

onit

2,38614 silver badges37 bronze badges

2 Comments

Ann Over a year ago

This looks promising, but I can't use getLastRow and getLastColumn because my data set is from something like row 20, colums 9 - 11 through row 120, colums 9-11.

onit Over a year ago

Just updated it, @Ann!

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related