Dynamically Validating Multiple Google Sheet Tabs

Question

I am writing a script for google sheet validation on localization tests. I've gotten stuck on some of the logic. The purpose of the script is to 1) Iterate through all tabs. 2) Find the column on row 2 that has the text "Pass/Fail". Lastly, 3) Iterate down that column and return the rows that say Fail.

The correct script to look at is called combined(). Step 1 is close to being correct, I think. Step 2 has been hard coded for the moment and is not dynamic searching the row for the text. Step 3 is done.

Any help would be great :)!!! Thanks in advance.

https://docs.google.com/spreadsheets/d/1mJfDtAi0hHqhqNB2367OPyNFgSPa_tW9l1akByaTSEk/edit?usp=sharing

    /*This function is to cycle through all spreadsheets. 
On each spreadsheet, it will search the second row for the column that says "Pass/Fail". 
Lastly, it will take that column and look for all the fails and return that row*/

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 something here to find which column on row two says "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

}

Tanaike Tanaike · Accepted Answer · 2017-04-16T22:53:55

At first, it retrieves data at column g. It retrieves a result from the data. The result is 2 dimensional array. The index of each element of the 2D array means the sheet index. If the sheet doesn't include values in column g, the element length is 0.

For example, in the case of following situation,

Sheet 0 doesn't include values in column g.
Sheet 1 includes values in column g. There are "Fail" value at the row number of 3, 4, 5.
Sheet 2 includes values in column g. There are "Fail" value at the row number of 6, 7, 8.

The result (return r) becomes below.

[[], [3, 4, 5], [6, 7, 8]]

Sample script 1:

function combined() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var data =[];
  sheets.forEach(function(ss){
    try { // In the case of check all sheets, if new sheet is included in the spreadsheet, an error occurs. This ``try...catch`` is used to avoid the error.
      data.push(ss.getRange(3, 7, ss.getLastRow(), 1).getValues());
    } catch(e) {
      data.push([]);
    }
  });
  var r = [];
  data.forEach(function(e1, i1){
    var temp = [];
    e1.forEach(function(e2, i2){
      if (e2[0] == "Fail") temp.push(i2 + 3);
    });
    r.push(temp);
  });
  return r;
}

If I misunderstand your question, I'm sorry.

Dynamically Validating Multiple Google Sheet Tabs

1 Answers