Search code examples
google-apps-scriptgoogle-sheetspdf-generationurlfetch

PDF attachment corrupted


After the issue with GmailApp declaratrion has benn resolved, I'm stuck now with a new problem, regarding my pdf attachment which is unreadable.

Prior to this bug, the pdf attachment was working perfectly.

I'm doubting that the var "blobresponse" or "pdfOpts" are the source of the problem, but maybe I'm wrong.

What's going wrong with the script ? Could you please explain me ? Here is a reproducible example

function emailAsPDF() {

 SpreadsheetApp.flush();
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 ss.setActiveSheet(ss.getSheetByName("Recherche"));
 var sheet = ss.getActiveSheet();
 var gid = sheet.getSheetId();
 var pdfOpts = 
    'exportFormat=pdf&format=pdf'+ // export as pdf
    '&size=0' + // paper size letter / You can use A4 or legal
    '&portrait=false' + // orientation portal, use false for landscape
    '&fitw=true' + // fit to page width false, to get the actual size
    '&sheetnames=false&printtitle=true' + // hide optional headers and footers
    '&pagenumbers=true&gridlines=false' + // hide page numbers and gridlines
    '&horizontal_alignment=CENTER&vertical_alignment=CENTER' +
    '&fzr=true' + // do not repeat row headers (frozen rows) on each page
    '&attachment=false' +
    gid;
// '&size=0&fzr=true&portrait=false&fitw=true&gridlines=false&printtitle=true&sheetnames=false&pagenumbers=true&attachment=false&gid='+gid;
 
 
 SourceSS = SpreadsheetApp.getActive();
 var SourceSheet = SourceSS.getSheetByName("Recherche");
 var url = 'https://docs.google.com/spreadsheets/d/' + SourceSS.getId().replace(/edit$/, '') + '/export?exportformat=pdf&format=pdf' + pdfOpts;
 //var url = 'https://docs.google.com/spreadsheets/d/'.replace(/edit$/, '') + '/export?exportformat=pdf&format=pdf' + pdfOpts;
 var token = ScriptApp.getOAuthToken();
 //var options = {
      
//options = {muteHttpExceptions: true};
 //var options = {
  var blobresponse = UrlFetchApp.fetch(url, {
    headers: {
        'Authorization': 'Bearer ' +  token,
      },
      muteHttpExceptions: true}
    );
  //options);
  var CandidateName = ss.getRangeByName("Nom.Candidat").getValue();
  var blob=blobresponse.getBlob().setName(ss.getName() + " - " + CandidateName +".pdf" );
  var emailAddress = Session.getActiveUser().getEmail();
  var signature = Gmail.Users.Settings.SendAs.get("me", emailAddress).signature; 
  var mess = "Voulez-vous envoyer votre rapport  à l'adresse : " + emailAddress;
  var ans= Browser.msgBox("Courriel", mess, Browser.Buttons.YES_NO);
  if (ans===Browser.Buttons.NO){return;}
  var mess="Votre rapport a été envoyé à l'adresse : " + emailAddress;
  //var ss=SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Recherche");
  var data = ss.getSheetByName("Données");
  const corps = data.getRange("A24:E27").getValues();
  const htmlTemplate = HtmlService.createTemplateFromFile("HtmlSigTemplate");
  htmlTemplate.corps = corps;
  var emailSubject = "Vérifications pré-emploi complétées" +" - "+ CandidateName;
  const htmlForEmail = htmlTemplate.evaluate().getContent() + "--" + "<br>" + signature;
  //console.log(htmlForEmail);
  GmailApp.sendEmail(
    emailAddress, 
    emailSubject, 
    corps,
    {htmlBody: htmlForEmail,
    attachments:[blob]});
  Browser.msgBox("Courriel", mess, Browser.Buttons.OK); 
}


function parentFolder() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var file = DriveApp.getFileById(ss.getId()); 
  var ParentFolder= file.getParents();
  while ( ParentFolder.hasNext() ) {
    var folder = ParentFolder.next();
    folderID=folder.getId();
     Logger.log(folderID);
  }
  return folderID;
}

Solution

  • I found the culprit after a long and painful research. You notice that the last line in the variable pdfOpts was missing a little detail

    Old code:

    var pdfOpts = 
        'exportFormat=pdf&format=pdf'+ // export as pdf
        '&size=0' + // paper size letter / You can use A4 or legal
        '&portrait=false' + // orientation portal, use false for landscape
        '&fitw=true' + // fit to page width false, to get the actual size
        '&sheetnames=false&printtitle=true' + // hide optional headers and footers
        '&pagenumbers=true&gridlines=false' + // hide page numbers and gridlines
        '&horizontal_alignment=CENTER&vertical_alignment=CENTER' +
        '&fzr=true' + // do not repeat row headers (frozen rows) on each page
        '&attachment=false' +
        gid;
    

    New code:

      const pdfOpts = '&top_margin=0.30&bottom_margin=0.30&left_margin=0.25&right_margin=0.25'
        +'&size=LETTER' // paper size letter / You can use A4 or legal
        +'&portrait=false' // orientation portal, use false for landscape
        +'&fitw=true' // fit to page width false, to get the actual size
        +'&sheetnames=false' // hide optional headers
        +'&printtitle=true' //and footers
        +'&pagenumbers=true' // hide page numbers 
        +'&gridlines=false' //and gridlines
        +'&horizontal_alignment=CENTER'
        +'&vertical_alignment=CENTER'
        +'&fzr=true' // do not repeat row headers (frozen rows) on each page
        +'&attachment=false'
        +'&gid='+gid;