I'm currently doing a project where I have users fill up a simple form of Name, Email address and upload a single pdf.
When form is submitted, the destination GSheet of that form takes in Name, Email address and the URL of the uploaded PDF. After form submission, I am able to do an auto reply with an email with html that says "Received your form submission with the attached file." But is it possible to attach that uploaded pdf in that email just using the URL? I am not able to find a ClassDrive for GetFileByURL. Only could find getfilebyname/type.
I can think of another way which is to have the user manually put in the name of the file being uploaded in the form and have the google app script to getfilebyname. I am trying to avoid human error however. Human error like name of the file uploaded and the filename typed in the form does not match. :)
function onSubmit(e) {
var ss = SpreadsheetApp.getActive();
var range = e.range
var row = Number(range.getRow());
var upFolder = DriveApp.getFolderById("ADDRESS OF WHERE THE UPLOADED FILE FROM THE FORM IS");
var formReceived = HtmlService.createTemplateFromFile("HTML Email"); //get the html template
var parent_name = ss.getRange('B' row).getValue();
var parent_email = ss.getRange('C' row).getValue();
var fileURL = ss.getRange('D' row).getValue();
ss.getRange('E' row).setValue("Error! Email not sent.");
//Tag input into the html
formReceived.parent_name=parent_name
//Once the form is submitted, the URL will show up on the Gsheet
//in column D. Is there a way to attach the file from that URL into the mailapp below.
//Not using the line below (getfilesbyname).
var invoicereceipt = upFolder.getFilesByName("Can be manual input from form");
if(invoicereceipt.hasNext()){
MailApp.sendEmail({
to: parent_email,
name: parent_name,
htmlBody : formReceived.evaluate().getContent(),
attachments: [invoicereceipt.next().getAs(MimeType.PDF)],
});
var now = new Date()
var k = Utilities.formatDate(now,'GMT 8',"YYYY.MM.dd HH:mm:ss")
ws.getRange('E' index).setValue(k);
}
}
CodePudding user response:
I believe your goal is as follows.
- You want to send an email when the Google Form is submitted. At this time, you want to attach the uploaded PDF file to the email.
- Your script is existing in Google Spreadsheet linked with Google Form.
- Your function of
onSubmit
is installed as the OnSubmit installable trigger.
Modification points:
- The email title is not included. In this case, an error occurs.
ws
,index
are not declared.- In this case, I thought that the event object can be used.
When these points are reflected to a script, it becomes as follows.
Modified script:
function onSubmit(e) {
var [, parent_name, parent_email, fileURL] = e.values;
var formReceived = HtmlService.createTemplateFromFile("HTML Email");
formReceived.parent_name = parent_name;
MailApp.sendEmail({
subject: "sample mail",
to: parent_email,
name: parent_name,
htmlBody: formReceived.evaluate().getContent(),
attachments: [DriveApp.getFileById(fileURL.split("=").pop())],
});
// e.range.offset(0, 1).setValue("Error! Email not sent."); // I'm not sure whether you want to use this line.
}
The uploaded PDF file is retrieved by
DriveApp.getFileById(fileURL.split("=").pop())
.In this case, please delete the installable OnSubmit trigger and reinstall it again. By this, the additional scopes are included.
Note:
- This is a simple modified script. So please modify it for your actual situation.