I am currently working on a script for validating localization tests in Google Sheets and I have run into some challenges with the logic. The main goal of the script is to 1) Go through all tabs, 2) Identify the column in row 2 that contains the text "Pass/Fail", and finally, 3) Scan down that column to find and return the rows that have "Fail" indicated.
The function I need to reference is named combined(). Step 1 seems to be on the right track, but step 2 is currently hardcoded and not dynamically searching for the specific text in the row. Step 3, however, has been successfully implemented.
Any assistance on this matter would be greatly appreciated! Thank you in advance for your help :)
https://docs.google.com/spreadsheets/d/1mJfDtAi0hHqhqNB2367OPyNFgSPa_tW9l1akByaTSEk/edit?usp=sharing
/*The purpose of this function is to cycle through all spreadsheets.
For each spreadsheet, it will search the second row to locate the column labeled "Pass/Fail".
Finally, it will identify all the failures in that column and return the respective rows*/
function combined() {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var r =[];
for (var i=0 ; i<sheets.length ; i++){//iterate through all the sheets
var sh = SpreadsheetApp.getActiveSheet();
var data = sh.getDataRange().getValues(); // read all data in the sheet
//r.push("test1"); //Testing to make sure all sheets get cycled through
/*I need a logic here to determine which column in row two contains "Pass/Fail"*/
for(i=3;i<data.length;++i){ // iterate row by row and examine data in column A
//r.push("test2"); //Testing to make sure the all
if(data[i][7]=='Fail'){ r.push(data[i])}; // if column 7 contains 'fail' then add it to the list
}
}
return r; //Return row of failed results on all tabs
}