CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Benjamin Williams
Benjamin Williams

Reputation: 1

Google Scripts that converts a sheet to pdf and emails it as an attachment fails after a few successful runs

I have implemented a google script to isolate a specific sheet in a spreadsheet and then convert the sheet to a pdf and email the pdf. The code extracts the email address to send the email to from the sheet itself.

The total number of sheets in the spreadsheet are 9. The code works perfectly until sheet 6/7 (it is inconsistent in where the first fault occurs).

The first 5 or so emails work as intended with the correct sheet being emailed as a pdf that is fully functional.

Emails are sent to all the addresses, but the attached pdfs are unopenable and include the error "Whoops! There was a problem previewing this document" after sheet 6/7.

var d = new Date();
var month = new Array();
month[0] = "January";
month[1] = "February";
month[2] = "March";
month[3] = "April";
month[4] = "May";
month[5] = "June";
month[6] = "July";
month[7] = "August";
month[8] = "September";
month[9] = "October";
month[10] = "November";
month[11] = "December";
var n = d.getMonth() +1;
var Year = d.getYear();
if (n == 12) { n = 0; Year += +1;} 
var m = month[n];


var subject = m + " " + Year; 
var body = 'body text.'
var Clients_No = 9;

function sendEmailWithPdfAttach() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssID = SpreadsheetApp.getActiveSpreadsheet().getId();
  var source = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = source.getSheets();
  var SpreadsheetName = source.getName();

  SpreadsheetApp.flush();



  for (var no_sent = 0; no_sent < Clients_No; no_sent++){
    var sheetNum = no_sent;//no_sent;    
    var SheetName = sheets[sheetNum].getName();
    var SheetID = sheets[sheetNum].getSheetId();
    var Sheet = source.getSheetByName(SheetName);
    var range = Sheet.getRange(1, 9); //change to 7, 2
    var email = range.getValue();
    var range_account_open = Sheet.getRange(14, 2);
    var Account_open = range_account_open.getValue();

    if (Account_open !== "Account Closed") {
      var url = ss.getUrl();
      url = url.replace(/edit$/,'');
      url = url + 'export?exportFormat=pdf&format=pdf' +
        '&size=A4' + //paper size
        '&portrait=true' + 
        '&fitw=true' +
        '&gridlines=false' + 
        '&fzr=false'; 

      var request = {
        "method": "GET",
        headers: {
        'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken()
      },
        "muteHttpExceptions": true
      };
      var url = Drive.Files.get(ssID)
      .exportLinks['application/pdf'] + "&gid="+SheetID;
      var blob = UrlFetchApp.fetch(url , request).getBlob();
      blob.setName(sheets[sheetNum].getName() + ".pdf"); 
      MailApp.sendEmail(email, subject, body, {attachments:blob});


      //Utilities.sleep(1000);
    }
  }
};

What must I change so that all emails sent have pdfs that are openable and functional?

Upvotes: 0

Views: 190

Answers (1)

abielita
abielita

Reputation: 13469

You may try the suggested actions in this link:

Hope this helps!

Upvotes: 1

Related Questions