Save a sheet as pdf with onEdit()

Question

i'm trying to save a google spreadsheet as pdf and mail it to myself everytime, when a specific cell in the spreadsheet is edited. I have the script which saves the spreadsheet as pdf and emails it and it works and I also have an onEdit(e) script which does exactly what I want. But when I put them together it doesn't work. Here's the script:

function onEdit(e) {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("booking");
  var index = sheet1.getRange('O5').getValue();
  if(index == '#N/A') return;
   
  var email = "myemail@gmail.com";
  var subject = "Subject";
  
  var sheet3 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Invoice_ENG");
  var pdfname = ("PDF.pdf"); 
  var message = ("Hello!");
        
  //Since I can't export only one sheet, I create a new spreadsheet, copy the sheet
  //into the new spreadsheet and save the new spreadsheet as pdf
  var newSpreadsheet = SpreadsheetApp.create("Spreadsheet to export");
  sheet3.copyTo(newSpreadsheet);
        
  //I erase the default Sheet1 from the new Spreadsheet
  var ss = newSpreadsheet.getSheetByName('Sheet1');
  ss.activate();
  newSpreadsheet.deleteActiveSheet();
        
  //Create pdf --- THIS IS WHERE THE SCRIPT SOMEHOW STOPS!
  var pdf = DriveApp.getFileById(newSpreadsheet.getId()).getAs('application/pdf').getBytes();
  var attach = {fileName:pdfname,content:pdf, mimeType:'application/pdf'};
        
  //Send mail
  GmailApp.sendEmail(email, subject, message, {attachments:[attach]});
        
  //Erase the newly created spreadsheet
  DriveApp.getFileById(newSpreadsheet.getId()).setTrashed(true);
}

Is the "export as pdf" task too much to be done with an onEdit function? Because I'm doing the exact same thing in another script which I trigger daily and it works fine.

score 1 · Accepted Answer · answered Mar 03 '17 at 10:23

1

You should use an installable onEdit trigger as sending email cannot be done with simple trigger.

ScriptApp.newTrigger("onEdit")
   .forSpreadsheet(SpreadsheetApp.getActive())
   .onEdit()
   .create();

answered Mar 03 '17 at 10:23

Amit Agarwal

10,910
1
32
43

Cooper · Answer 2 · 2017-03-03T16:04:32.637

//Since I can't export only one sheet, I create a new spreadsheet, copy the sheet
  //into the new spreadsheet and save the new spreadsheet as pdf
  var newSpreadsheet = SpreadsheetApp.create("Spreadsheet to export");
  sheet3.copyTo(newSpreadsheet);

  //I erase the default Sheet1 from the new Spreadsheet
  var ss = newSpreadsheet.getSheetByName('Sheet1');
  ss.activate();
  newSpreadsheet.deleteActiveSheet();

I believe that activate is a method for sheets not for spreadsheets. You can open a spreadsheet from SpreadsheetApp.

//Create pdf --- THIS IS WHERE THE SCRIPT SOMEHOW STOPS!
  var pdf = DriveApp.getFileById(newSpreadsheet.getId()).getAs('application/pdf').getBytes();
  var attach = {fileName:pdfname,content:pdf, mimeType:'application/pdf'};

I believe you need to create the pdf file using DriveApp between these two statements. Possible with DriveApp.createFile

Save a sheet as pdf with onEdit()

2 Answers2

Linked