javascript - When I hide a sheet in google sheets, it alters the output of my script -

i created script generate pdfs, save them drive , email them if required.

the script works fine.

just 1 weird issue, when hide sheet called "trafficagentpdf" , run script. creates pdf in drive, it's corrupt somehow. cannot opened google, when opening in browser, it's blank. unhide sheet, , works.

the trafficagentpdf sheet vlookup on sheet, show images instead of values. images small icons, , 3 used. red traffic light, amber traffic light , green. (thought i'd mention incase weird rendering issue)

here script. if unclear, let me know , annotate it.

function getagentname() {   var ss = spreadsheetapp.getactivespreadsheet(); ss.getrangebyname('header').clearcontent(); ss.getrangebyname('scores').clearcontent(); ss.getrangebyname('comments').clearcontent(); var sheet = ss.getsheetbyname("pdf creator"); var range = sheet.getrange("a2") var value = range.getvalue();   if(value != 0){  getagentdata(value);  }  else { browser.msgbox("you need go sheet named pdf creator , put agent name in cell a2");   }  }      function getagentdata(value){ var ss = spreadsheetapp.getactivespreadsheet(); var sourcesheet = ss.getsheetbyname("form responses 1") var sourcerange = sourcesheet.getrange(2, 1, sourcesheet.getlastrow(), sourcesheet.getlastcolumn()); var sourcevalues = sourcerange.getvalues();  sourcevalues.sort(function(a,b) {         return b[0]-a[0]     });   var agentdata = []; var commentsdata = []; for(i=0;i<sourcevalues.length;i++) { // defines data layout pdf. var agentname = sourcevalues[i][2]; var datetime = sourcevalues[i][3]; var calltype = sourcevalues[i][7]; var opening = sourcevalues[i][8]; var rootcause = sourcevalues[i][9]; var rootfix = sourcevalues[i][10]; var process = sourcevalues[i][11]; var consumer = sourcevalues[i][12]; var control = sourcevalues[i][13]; var wrapup = sourcevalues[i][14]; var dpa = sourcevalues[i][15]; var score = sourcevalues[i][22];   var comments = sourcevalues[i][16];   var agentrow = [datetime,calltype,opening,rootcause,rootfix,process,consumer, control,wrapup,dpa,score]; var commentsrow = [datetime,comments];     if(agentname == value && agentdata.length < 9) {       agentdata.push(agentrow) commentsdata.push(commentsrow) }  }    agentdata.sort(function(a,b) {          return b[0]-a[0]     });    commentsdata.sort(function(a,b) {         return b[0]-a[0]     });    var destsheet = ss.getsheetbyname("agentpdf"); destsheet.getrange("a1").setvalue(value + "'s quality score card"); var range = destsheet.getrange(6, 1, agentdata.length, agentdata[0].length); range.setvalues(agentdata);  var commentrange = destsheet.getrange(18, 1, commentsdata.length, commentsdata[0].length); commentrange.setvalues(commentsdata);   spreadsheetapp.flush();  emailspreadsheetaspdf(); }              function emailspreadsheetaspdf() {     var ss = spreadsheetapp.getactivespreadsheet();   var sheet = ss.getsheetbyname("pdf creator");   var = new date().tostring();   var weekcommencing = sheet.getrange("c1").getvalue();   var coachemail = sheet.getrange("c4").getvalue();   var coachname = sheet.getrange("a4").getvalue();   var agentname = sheet.getrange("a2").getvalue();   var agentemail = sheet.getrange("c2").getvalue();   var sendemail = sheet.getrange("a6").getvalue();    var subject = "quality scorecard - "+agentname + " created on: "+now;       var url = ss.geturl();   url = url.replace(/edit$/,'');      var monthnames = [   "jan", "feb", "mar",   "apr", "may", "jun", "jul",   "aug", "sep", "oct",   "nov", "dec" ];   var day = weekcommencing.getdate(); var monthindex = weekcommencing.getmonth(); var year = weekcommencing.getfullyear();  var clean = day + ' ' + monthnames[monthindex] + ' ' + year;     var bodycoach = "hello " + coachname + ". please find attached "+ agentname+ "'s quality scorecard." +" week commencing "+clean;   var bodyagent = "hello " + agentname + ". please find attached  quality scorecard." +" week commencing "+clean;    /* specify pdf export parameters   // from: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579     exportformat = pdf / csv / xls / xlsx     gridlines = true / false     printtitle = true (1) / false (0)     size = legal / letter/ a4     fzr (repeat frozen rows) = true / false     portrait = true (1) / false (0)     fitw (fit page width) = true (1) / false (0)     add gid if export particular sheet - 0, 1, 2,..   */    var url_ext = 'export?exportformat=pdf&format=pdf'   // export pdf                 + '&size=a4'                       // paper size                 + '&portrait=1'                    // orientation, false landscape                 + '&fitw=true&source=labnol'           // fit width, false actual size                 + '&sheetnames=false&printtitle=false' // hide optional headers , footers                 + '&pagenumbers=false&gridlines=false' // hide page numbers , gridlines                 + '&fzr=false'                         // not repeat row headers (frozen rows) on each page                 + '&gid=1809314965';                             // sheet's id    var token = scriptapp.getoauthtoken();        var response = urlfetchapp.fetch(url + url_ext , {       headers: {         'authorization': 'bearer ' +  token       }     });       var file = response.getblob().setname(agentname +"'s quality score card week commencing: "+clean+ '.pdf');      writefiles(file,clean);    if (coachemail != "no email"){   gmailapp.sendemail(coachemail, subject, bodycoach, {attachments:[file]});  }   if(sendemail == "yes"){  gmailapp.sendemail(agentemail, subject, bodyagent, {attachments:[file]});  }   ss.getrangebyname('header').clearcontent(); ss.getrangebyname('scores').clearcontent(); ss.getrangebyname('comments').clearcontent(); ss.getrangebyname('agentname').clearcontent(); ss.getrangebyname('coach').clearcontent(); ss.getrangebyname('sendemail').clearcontent(); }  function writefiles(file,clean) {      var rootfolder = driveapp.getfolderbyid("id here");     var subfolders = rootfolder.getfolders();       var testresult = false;    while(subfolders.hasnext()== true){      var folders = subfolders.next();      if(folders == clean)    {       testresult = true;     }      }     if(testresult == false){     rootfolder.createfolder(clean);   }         var destfolder = rootfolder.getfoldersbyname(clean).next();   if(destfolder.getfilesbyname(file.getname()).hasnext() == false){  destfolder.createfile(file); }  else {  var warning = browser.msgbox("warning", "this pdf exists. if wish overwrite file, press ok continue.", browser.buttons.ok_cancel);   if(warning == "ok"){ destfolder.getfilesbyname(file.getname()).next().settrashed(true);   destfolder.createfile(file);  }  }       } 

edit below

after poking , prodding , testing, seems when either using url create pdf, or getas function. hidden sheets omitted.

take simple test.

    function whatever(){  var ss = spreadsheetapp.getactivespreadsheet(); var pdf = ss.getas('application/pdf');  var rootfolder = driveapp.getfolderbyid("id here"); rootfolder.createfile(pdf);  } 

if have hidden sheets in spreadsheet, missed out. design or bug?

i'm using url method in can explicitly define gid of sheet want, , own spreadsheet, should work?

after discussions user, i've asked question google.

bug report submitted google

try 'unhiding' sheet, 'using' sheet, , 'hiding' sheet when done. there .hidesheet() method, there no .unhidesheet() listed on api documentation.

to 'unhide' sheet use .activate() method.

================

according api documentation:

activate()

"activates sheet. not alter sheet itself, parent's notion of active sheet."

// example assumes there sheet named "first"  var ss = spreadsheetapp.getactivespreadsheet();  var first = ss.getsheetbyname("first");  first.activate(); 

here api documentation link: https://developers.google.com/apps-script/reference/spreadsheet/sheet#activate

it nice if explained why need alter "the parent's notion of active sheet" in docs don't.