Overview: I have implemented a Google Forms that tracks a user's responses and the responses are stored in an Google Sheets spreadsheet.
I have also implemented a function in Apps script, with the following goals:
Current issue: The user responses are successfully sent to the Google Sheets as anticipated, and their responses are converted to a Pdf file on my Google Drive, but the emails are not being sent.
function afterFormSubmit(e) {
const info = e.namedValues;
const pdfFile = createPDF(info);
sendEmail(e.namedValues['Email Address'][0],pdfFile);
}
function sendEmail(email,pdfFile){
GmailApp.sendEmail(email,"Here is Your PDF","Your PDF is Attached.",{
attachments: [pdfFile],
name: 'Sent From Example'
});
}
function createPDF(info,filename){
const pdfFolder = DriveApp.getFolderById("1iCVsXmeSMHOpISQslJhX7r842aSHrqos");
const tempFolder = DriveApp.getFolderById("1iCVsXmeSMHOpISQslJhX7r842aSHrqos");
const templateDoc = DriveApp.getFileById("18zVh4aL2Dbt76l6XqwIh2KHeJf6JkY-lMU63MRVOQUM");
const newTempFile = templateDoc.makeCopy(tempFolder);
const openDoc = DocumentApp.openById(newTempFile.getId());
const body = openDoc.getBody();
body.replaceText("{date}", info['Timestamp'][0]);
body.replaceText("{Email_Address}", info['Email Address'][0]);
body.replaceText("{selected_responses}", info['Which of these Positive Attitudes do you struggle with?'][0]);
body.replaceText("{Post-Att-01}", info['Choose a positive attitude that you find difficult to maintain.'][0]);
body.replaceText("{Happens-When-01}", info['IT HAPPENS WHEN'][0]);
body.replaceText("{Triggered-By-01}", info['TRIGGERED BY'][0]);
body.replaceText("{RC-Thought #1-01}", info['Rational Counter-Thought #1'][0]);
body.replaceText("{RC-Thought #2-01}", info['Rational Counter-Thought #2'][0]);
body.replaceText("{RC-Thought #3-01}", info['Rational Counter-Thought #3'][0]);
body.replaceText("{Account-Partner-01}", info['Accountability Partner'][0]);
body.replaceText("{Post-Att-02}", info['Select another positive attitude that you find difficult to maintain.'][0]);
openDoc.saveAndClose();
const blobPDF = newTempFile.getAs(MimeType.PDF);
const pdfFile = pdfFolder.createFile(blobPDF).setName("Worksheet Summary");
tempFolder.removeFile(newTempFile);
}
createPDF
, no value is returned. I guessed that this might be the reason for your current issue.removeFile
has been deprecated. Ref At tempFolder.removeFile(newTempFile);
, you might want to remove newTempFile
?When these points are reflected in your script, how about the following modification?
In this modification, the function createPDF
is modified.
const blobPDF = newTempFile.getAs(MimeType.PDF);
const pdfFile = pdfFolder.createFile(blobPDF).setName("Worksheet Summary");
tempFolder.removeFile(newTempFile);
}
const pdfFile = pdfFolder.createFile(blobPDF).setName("Worksheet Summary");
newTempFile.setTrashed(true);
return blobPDF; // or return pdfFile;
}
The whole modified function is as follows. By the way, it seems that filename
is not used.
function createPDF(info, filename) {
const pdfFolder = DriveApp.getFolderById("1iCVsXmeSMHOpISQslJhX7r842aSHrqos");
const tempFolder = DriveApp.getFolderById("1iCVsXmeSMHOpISQslJhX7r842aSHrqos");
const templateDoc = DriveApp.getFileById("18zVh4aL2Dbt76l6XqwIh2KHeJf6JkY-lMU63MRVOQUM");
const newTempFile = templateDoc.makeCopy(tempFolder);
const openDoc = DocumentApp.openById(newTempFile.getId());
const body = openDoc.getBody();
body.replaceText("{date}", info['Timestamp'][0]);
body.replaceText("{Email_Address}", info['Email Address'][0]);
body.replaceText("{selected_responses}", info['Which of these Positive Attitudes do you struggle with?'][0]);
body.replaceText("{Post-Att-01}", info['Choose a positive attitude that you find difficult to maintain.'][0]);
body.replaceText("{Happens-When-01}", info['IT HAPPENS WHEN'][0]);
body.replaceText("{Triggered-By-01}", info['TRIGGERED BY'][0]);
body.replaceText("{RC-Thought #1-01}", info['Rational Counter-Thought #1'][0]);
body.replaceText("{RC-Thought #2-01}", info['Rational Counter-Thought #2'][0]);
body.replaceText("{RC-Thought #3-01}", info['Rational Counter-Thought #3'][0]);
body.replaceText("{Account-Partner-01}", info['Accountability Partner'][0]);
body.replaceText("{Post-Att-02}", info['Select another positive attitude that you find difficult to maintain.'][0]);
openDoc.saveAndClose();
const blobPDF = newTempFile.getAs(MimeType.PDF);
const pdfFile = pdfFolder.createFile(blobPDF).setName("Worksheet Summary");
newTempFile.setTrashed(true);
return blobPDF; // or return pdfFile;
}
afterFormSubmit(e)
of the container-bound script in Google Spreadsheet is installed as the OnSubmit trigger, the function is automatically run and the email is sent with the attachment file.newTempFile
, you can also use Drive.Files.remove(newTempFile.getId())
instead of newTempFile.setTrashed(true)
. But, at that time, please enable Drive API at Advanced Google services.