did you sign in with real email? i have one bounced one
check your spam maybe
Yes I did. I got the welcome email but nothing after submitting the form.
i created an App, with codes and tricksā¦ you can check there
what email did you used?.. just give me first letters
Kyleā¦
it did sent ā¦ i have confirmation welcome emailā¦ and sales email
search your email box for subject:
App & Web consulting / development, we received your offer
Found it. It was flagged as spam. Weird because the welcome email was not.
Will you be adding this PDF script to this app too?
yes, simple version of itā¦ this one is a monster
Haha I can imagine. The rendered page is very nice. The basics of moving entered values to a PDF and emailing that PDF as an attachment (all in Scripts with no 3rd party resources) would be very helpful though.
1 Like
i worked couple of days on this oneā¦ plus it has big sheets formulas supportā¦ so just a script will not create this
I may reach out for some help with something similar in the future. Iāll PM you once I get to that point.
function emailSpreadsheetAsPDF() { DocumentApp.getActiveDocument(); DriveApp.getFiles(); // This is the link to my spreadsheet with the Form responses and the Invoice Template sheets // Add the link to your spreadsheet here // or you can just replace the text in the link between "d/" and "/edit" // In my case is the text: 17I8-QDce0Nug7amrZeYTB3IYbGCGxvUj-XMt8uUUyvI const ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/17I8-QDce0Nug7amrZeYTB3IYbGCGxvUj-XMt8uUUyvI/edit"); // We are going to get the email address from the cell "B7" from the "Invoice" sheet // Change the reference of the cell or the name of the sheet if it is different const value = ss.getSheetByName("Invoice").getRange("B7").getValue(); const email = value.toString(); // Subject of the email message const subject = 'Your Invoice'; // Email Text. You can add HTML code here - see ctrlq.org/html-mail const body = "Sent via Generate Invoice from Google Form and print/email it"; // Again, the URL to your spreadsheet but now with "/export" at the end // Change it to the link of your spreadsheet, but leave the "/export" const url = 'https://docs.google.com/spreadsheets/d/17I8-QDce0Nug7amrZeYTB3IYbGCGxvUj-XMt8uUUyvI/export?'; const exportOptions = 'exportFormat=pdf&format=pdf' + // export as pdf '&size=letter' + // paper size letter / You can use A4 or legal '&portrait=true' + // orientation portal, use false for landscape '&fitw=false' + // fit to page width false, to get the actual size '&sheetnames=false&printtitle=false' + // hide optional headers and footers '&pagenumbers=false&gridlines=false' + // hide page numbers and gridlines '&fzr=false' + // do not repeat row headers (frozen rows) on each page '&gid=1030891993'; // the sheet's Id. Change it to your sheet ID. // You can find the sheet ID in the link bar. // Select the sheet that you want to print and check the link, // the gid number of the sheet is on the end of your link. var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}}; // Generate the PDF file var response = UrlFetchApp.fetch(url+exportOptions, params).getBlob(); // Send the PDF file as an attachement GmailApp.sendEmail(email, subject, body, { htmlBody: body, attachments: [{ fileName: "Invoice" + ".pdf", content: response.getBytes(), mimeType: "application/pdf" }] }); // Save the PDF to Drive. The name of the PDF is going to be the name of the Company (cell B5) const nameFile = ss.getSheetByName("Invoice").getRange("B5").getValue().toString() +".pdf" DriveApp.createFile(response.setName(nameFile)); }
6 Likes
this is the base structure for it
1 Like
Great, thanks! Looks like a great starting point for me.
1 Like
Is it just paste the code as script? How about deployment?
I did (most of) that recently for @Mishta_P. Iām sure he wonāt mind if I share.
In this case, the script was:
- reading the responses of a questionnaire
- writing these to a copy of a GDoc template
- converting the template to PDF
- saving the PDF to a GDrive folder
- returning a URL link to the created PDF, which is written back to the sheet
Here's the guts of it...
function create_pdf(sheet, row, folder_id, template_id) {
var headers = sheet.getRange(1,1,1,sheet.getLastColumn());
var questions = [];
var column_numbers = [];
var last_col = headers.getLastColumn();
for (var col=3; col<=last_col; col++) {
var bg = headers.getCell(1,col).getBackground();
if (bg == '#ffff00') {
var text = headers.getCell(1,col).getValue();
questions.push(text);
column_numbers.push(col-1);
}
}
var responses = [];
var data = sheet.getRange(row,1,1,sheet.getLastColumn()).getValues().shift();
column_numbers.forEach(function (col) {
var response = data.slice(col, col+1);
if (response == 'true' ) { response = 'ā
' }
responses.push(response);
});
var dob_index = questions.indexOf('Date of Birth');
var fname_index = questions.indexOf('First Name');
var lname_index = questions.indexOf('Last Name');
var submit_date_index = questions.indexOf('datecomp');
responses[dob_index] = new Date(responses[dob_index]).toISOString().slice(0,10);
var submit_date = new Date(responses[submit_date_index]).toISOString().slice(0,10);
var fname = responses[fname_index];
var lname = responses[lname_index];
var pdf_file_name = fname + lname + submit_date;
var folder = DriveApp.getFolderById(folder_id);
var template = DriveApp.getFileById(template_id);
var temp_doc_id = template.makeCopy().getId();
var temp_doc = DocumentApp.openById(temp_doc_id);
var body = temp_doc.getBody();
while (questions.length > 0) {
body.replaceText('{' + questions.shift() + '}', responses.shift());
}
temp_doc.saveAndClose();
var temp_file_id = temp_doc.getId();
var temp_file = DriveApp.getFileById(temp_file_id);
var pdf = DriveApp.createFile(temp_file.getAs('application/pdf'))
pdf.moveTo(folder).setName(pdf_file_name);
var url = pdf.getUrl();
temp_file.setTrashed(true);
return url;
}
6 Likes
Not at all, was all your work. I wasnāt sure if I was allowed to share it 
2 Likes
you have to set triggers, and prepare sheets