yogi_Compute Latest (and Earliest) Non-Blank Score For Each Of Categories 1 To 5 From Maximum Of 20 Tests

                                          Google Spreadsheet   Post  #1391
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Oct 08, 2013
question by: Philip Duker (http://productforums.google.com/forum/?zx=pod4a6fmf695#!mydiscussions/docs/kbj1Gr4G2sc)

'Hello Yogi,

  Thanks so much for the blog post.  I had a little trouble following it (and it might have been my fault as I've been altering the spreadsheets since I posted this yesterday).

I do have a new question though that I'm hoping you might be able to answer.

I've got an ordered series of sheets containing mixed scores (some numeric, some text).  I'd like a function that can pull the most recent score (whatever that may be) from the sheets.  The problem is that some sheets will have blanks and others will have data, so I can't just reference one sheet.  I was trying to write my own function to do this, but I'm not sure if that's the best approach.  This is the code that I'm working on so far using Google API.

function PullRecent() {  // Pull most recent score from the sheets

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var average = spreadsheet.getSheets()[0]; // get the average page
  var cell = average.getActiveCell(); // get the active cell that I want the data written to
  var cellR = average.getRow(cell); // get the row of this cell
  var cellC = average.getColumn(cell); // get the column of this cell
 
 // pull all of the rest of the sheets in the spreadsheet
  var assignments = spreadsheet.getSheets(); 

// what I hope this next formula would do would be to go through each sheet until it finds a non-blank value
 for(var r=1; assignmentdata!="" ;r++){
    var assignmentsheet = spreadsheet.getSheets()[r];
    var assignmentdata = assignmentsheet.getRange(cellR,cellC,1,1).getValue();
    if(assignmentdata !=""){
    return assignmentdata;
      break;
    }
      
    }
  }

Any suggestions and help would be most welcome.

Thanks,
Phil

-------------------------------------------------------------------------------------------------------------------------------------------------------
in the following I present a formula based solution to a bit more generalized problem