Modify Google App Script to Email PDF copy of a Google Sheet without making a copy in my drive

[Lori Nedd]

Hello, 

I have this Google App Script that I want to use to automate sending an updated timesheet every two weeks using a date/time trigger. 

When I run the script, it will send the email with a PDF version of the sheet attached to the email, however, each time I run it, it creates a copy in my main drive folder. 

I tried removing the line   "var SendBook = ss.copy(filename);" - but then it stops working. When I add a time/date trigger to this script it also works, but creates a copy. 

 

Here is the script that works but creates a copy: 

function SendEmailSheet1() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var filename = " Sheet1" + ss.getName();

  var SendBook = ss.copy(filename);

  var ToUser = "us...@gmail.com, us...@gmail.com";

  MailApp.sendEmail({

    to: ToUser,

    subject: 'Here is my test email!',

    body: 'Hi! I have attached my test timesheet. Thanks for the work, really appreciate it! -Lori',

    attachments: [SendBook.getBlob().setName(filename)]

  });

}

I have tried other scripts but they are more complicated and I am a beginner user. This one would be perfect if it did not create a copy. I also need to add a script that can be triggered to update a date in a cell that will update the timesheet every 2 weeks and uses this script to email the new pdf automatically. 

Thanks in advance!

Lori

[Keith Andersen]

It must first make a copy - then it takes that copy and sends it to the person. 

If you don't want copies in the drive to remain, simply create a timed trigger script to delete all copies in the folder.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/8cfabcfc-da52-4ca3-86cd-21835cb79eabn%40googlegroups.com.

[DimuDesigns]

When you remove the logic to copy the sheet, you also have to update the object you passes to MailApp.sendmail so that the `attachments` property references the original file (not the now obsolete copy). The modification points are denoted using red text.

Try this and see if it works for you:

function SendEmailSheet1() {

    var ss = SpreadsheetApp.getActiveSpreadsheet();

    var filename = " Sheet1" + ss.getName();

    var ToUser = "us...@gmail.com, us...@gmail.com";

    

    MailApp.sendEmail({

        to: ToUser,

        subject: 'Here is my test email!',

        body: 'Hi! I have attached my test timesheet. Thanks for the work, really appreciate it! -Lori',

        attachments: [ss.getBlob().setName(filename)]

    });

[Lori Nedd]

Hi guys, 

Sorry for the late response. 

DimuDesigns script worked for me and got rid of the copying issue. Now on to the script for automatically updating a date field and then running this script on a schedule.

Thanks so much for your help!!

[Peter Lawlor]

Hi Dimu,

For some reason I'm getting the error "ss.getBlob is not a function" - is there a way of exporting the sheet as PDF without having to set up CORS?

Thanks