How to get `indexOf()` cells value or convert an object to a String?

Question

My code is trying to "search" a spreadsheet for a row with two specific text lines. When the line is found within the first 30 rows a value of A1:A1 should replace a word in a document.

My problem is that I can not count the words with indexOf() because it needs a string and getValue()/getDisplayValue() produces an object/array. Furthermore js's includes() is not supported. Does someone know a solution?

function myFunction1() {

var tss = DocumentApp.openById("targetspreadsheet");
var docBody = tss.getBody();  


var sourcess =  SpreadsheetApp.openById("source spreadsheet");
var sources  = sourcess.getSheets()[0]; 


  for (var i = 1; i = 30 ; i++){

    var rowValues = sources.getRange(i, 1, 1, sources.getLastColumn()).getDisplayValues();
    Logger.log(rowValues);

    if (rowValues.indexOf("Word1") >= 1 && rowValues.indexOf("Word2") >= 1){
      var name = sources.getRange("A1:A1").getValues();
      docBody.replaceText("TextToReplace", name)} 

    else {continue;}  

    }

}

Maybe I overlooked a solution while searching but so far I just found solutions working js and not google script. If so I'm sorry; otherwise if you can help me: thank you! Cheers

Answer 1

Since you are searching individual rows for 2 specific words you can combine the values of each in the row using Array.join(). Example:

rowValues[i].join(" ")

Also, instead of obtaining the values of each row via loop you can obtain the values of all 30 rows at one time and then loop through them, like so

var rowValues = sources.getRange(1, 1, 30, sources.getLastColumn()).getDisplayValues();
  for (var i = 0; i < 30 ; i++){ 
    Logger.log(rowValues[i].join(" "));}

The advantage being, lesser overhead. So faster execution of the script and overall a good practice. The rowValues will be a 2D array like so:

  rowValues = [["Row1Col1","Row1Col2",... ,"Row1ColLast"],
              ["Row2Col1",...,"Row2ColLast"],
              ...
              ...
              ["Row30Col1",....,"Row30ColLast"]]  // End of array

So when you do rowValue[0].join(" ") it return the following value

rowValue[0].join(" ") => "Row1Col1 Row1Col2 ...  Row1ColLast"

which is a string and can be searched using the indexOf(). So your final code would look like this:

function myFunction1() {

var tss = DocumentApp.openById("targetspreadsheet");
var docBody = tss.getBody();  


var sourcess =  SpreadsheetApp.openById("source spreadsheet");
var sources  = sourcess.getSheets()[0]; 


 var name = sources.getRange("A1").getValue();
 var rowValues = sources.getRange(1, 1, 30, sources.getLastColumn()).getDisplayValues();
  for (var i = 0; i < 30 ; i++){ 
    Logger.log(rowValues[i].join(" "));
    if (rowValues[i].join(" ").indexOf("Word1") >= 1 && rowValues[i].join(" ").indexOf("Word2") >= 1){
      Logger.log("found Text "+name)
      docBody.replaceText("TextToReplace", name)
    }
    else {
      continue;
    }  
  }


}