Search code examples
pdfgoogle-apps-scriptgoogle-sheetsgmail

Mail range not whole sheet - apps script


I have a Google Sheet. I'd like to mail that out as a PDF. The below code works to email the WHOLE sheet (one tab) as pdf. The &range doesn't do anything. I want it to, but it doesn't. Question: How can I change this code so it emails 'selected cells'? Bonus Question: why do I have to put ?exportFormat=pdf and &format=pdf?

      var url_base = ss.getUrl().replace(/edit$/,'');
      var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf
      + (sheetId ? ('&gid=' + sheetId) : ('&id=' + ssId))
      + '&range=E1:L25' // <<< THIS DOESN'T SEEM TO DO ANYTHING
      + '&format=pdf'                   //export format

Solution

  • Actually it does work. Finding the original definition of the source code I used as a base, I saw that I had the parameter set to email the whole Sheets file rather than one tab. After pointing to one tab, the &range parameter activated, and I got the desired result.

    Email workbook or tabs reference: Google Script - Send active sheet as PDF to email listed in cell

    And my own expansion with range capabilities and some other, I think, improvements.

    /*
    shNum = 0 for whole workbook or '0', or 0,1,2,etc for specific tab/sheet
    shRng = A1 address for desired range, eg 'E1:L25', ignored if not a single sheet shNum
    pdfName = text on top of pdf
    */
    function mailPdf(shNum,shRng,pdfName,email,subject,htmlbody) {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var ssId = ss.getId();
      var shId = shNum ? ss.getSheets()[shNum].getSheetId() : null;  
      var url_base = ss.getUrl().replace(/edit$/,'');
      var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf
          + (shId ? ('&gid=' + shId) : ('&id=' + ssId))
          + (shRng ? ('&range=E1:L25') : null)
          + '&format=pdf'                   //export format
          + '&size=letter'                      //A3/A4/A5/B4/B5/letter/tabloid/legal/statement/executive/folio
          //+ '&portrait=false'               //true= Potrait / false= Landscape
          //+ '&scale=1'                      //1= Normal 100% / 2= Fit to width / 3= Fit to height / 4= Fit to Page
          //+ '&top_margin=0.00'              //All four margins must be set!
          //+ '&bottom_margin=0.00'           //All four margins must be set!
          //+ '&left_margin=0.00'             //All four margins must be set!
          //+ '&right_margin=0.00'            //All four margins must be set!
          + '&gridlines=false'              //true/false
          //+ '&printnotes=false'             //true/false
          //+ '&pageorder=2'                  //1= Down, then over / 2= Over, then down
          //+ '&horizontal_alignment=CENTER'  //LEFT/CENTER/RIGHT
          + '&vertical_alignment=TOP'       //TOP/MIDDLE/BOTTOM
          //+ '&printtitle=false'             //true/false
          //+ '&sheetnames=false'             //true/false
          //+ '&fzr=false'                    //true/false frozen rows
          //+ '&fzc=false'                    //true/false frozen cols
          //+ '&attachment=false'             //true/false
    
      var options = {
        headers: {
          'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken(),
          'muteHttpExceptions': true
        }
      }
    
      var response = UrlFetchApp.fetch(url_base + url_ext, options);
      var blob = response.getBlob().setName(pdfName + '.pdf');
      if (email) {
        var mailOptions = {
          attachments:blob, htmlBody:htmlbody
        }
    
    
    MailApp.sendEmail(
          // email + "," + Session.getActiveUser().getEmail() // use this to email self and others
          email,                                              // use this to only email users requested
          subject+' (' + pdfName +')', 
          'html content only', 
          mailOptions);
    
      }
    }