CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Mr. M. Perzan
Mr. M. Perzan

Reputation: 23

GSheet - making sure all but one sheet is hidden

I am working on a gradebook program in GSheet and have run into a new snag. I need to hide all the sheets but a "Student Report" sheet and then pdf and email them. It was working great, until I got a report that the script did not complete hiding the sheets and then continued on to make the pdfs. I added in a "sleep" but was hoping for a more proactive approach.

I came up with this idea but it just times out. What I am hoping is to have a do-while loop that keeps checking on the hidden attribute of the sheets....once all but one is hidden (i.e. Student Report)....it should go ahead. Any help would be appreciated.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets=SpreadsheetApp.getActiveSpreadsheet().getSheets();
var sheetName = "Student Report";

for(var i=0;i<sheets.length; i++){
if(sheets[i].getName()!=sheetName){
  sheets[i].hideSheet();
  }}
 var StudentReport = ss.getSheetByName("Student Report");
 ss.setActiveSheet(StudentReport);
 do {

 for (var k=0;k<sheets.length;k++){   
      var CountVisible = sheets.length;
    if(sheets[k].isSheetHidden()){
        CountVisible = CountVisible -1;
        Logger.log(CountVisible);
       }} 

  } while (CountVisible!=0);
//added wait time to esnure all sheets are properly hidden before next step
// Utilities.sleep(400);

Alternately, if someone knows how to just pdf a single sheet...that would solve the problem nicely.

Upvotes: 1

Views: 83

Answers (2)

Cooper
Cooper

Reputation: 64042

This should be sufficient to hide all but one sheet.

function hideAllSheetsExceptThisOne(sheetName) {
  var sheetName=sh||'Student Report';//default for testing
  var ss = SpreadsheetApp.getActive();
  var sheets=ss.getSheets();
  for(var i=0;i<sheets.length; i++){
    if(sheets[i].getName()!=sheetName){
      sheets[i].hideSheet();
    }
  }
  SpreadsheetApp.flush();
}

Upvotes: 1

Jakel181
Jakel181

Reputation: 17

I had to do something similar earlier this year, and this code proved to be very helpful. https://gist.github.com/ixhd/3660885

Upvotes: 0

Related Questions