New to Apps Script, need assistance on form submit, file creation from template and email

[Patrick Cloutier]

Hi Everyone,

I need help helping a friend !!

The form is to order doughnuts, where i need to grab responses, use my template with the markers to create a copy dropped in the google drive and also emailed to the client.

I know my code is real close but i'm missing a little something somewhere that an experienced eye will spot easily im sure.

Thank you in advance for any assistance provided.

Respectfully

Patrick

/**

 * Main function triggered by form submission

 */

function createInvoiceFromForm(e) {

  // 1. SETUP: IDs for your template and destination folder

  const TEMPLATE_ID = '1vJjS74UN82bT-uow1iqzQ7aGN03CFtVtK7_1XsyLU24'; // Found in the Doc's URL

  const FOLDER_ID = '1hdiwGsQ8MHEj6aHu8cn-RTJuRtj597hE';   // Found in the Drive folder's URL

 

  // 2. EXTRACT DATA: Get values from the form submission (e.namedValues)

  // Replace these keys with your EXACT form question titles

  const clientEmail = e.namedValues['Email'][0];

  const clientName = e.namedValues['Name'][0];

  const pickdate = e.namedValues['Pickup Date'][0];

  const don1 =e.namedValues['Doughnut 1'][0]

  const don1qty = e.namedValues['Doughnut 1 Qty'][0]

  const don2 = e.namedValues['Doughnut 2'][0]

  const don2qty = e.namedValues['Doughnut 2 Qty'][0]

 

  // 3. CREATE COPY: Copy the template using DriveApp

  const destinationFolder = DriveApp.getFolderById(FOLDER_ID);

  const copy = DriveApp.getFileById(TEMPLATE_ID).makeCopy({clientName}, destinationFolder);

 

  // 4. FILL TEMPLATE: Open as a Document and replace placeholders

  // Use {{Placeholder}} syntax in your Google Doc template

  const doc = DocumentApp.openById(copy.getId());

  const body = doc.getBody();

 

  body.replaceText('{{Name}}', clientName);

  body.replaceText('{{Email}}', clientEmail);

  body.replaceText('{{Pickup Date}}', date);

  body.replaceText('{{Doughnut 1}}', don1);

  body.replaceText('{{Doughnut 1 Qty}}', don1qty);

  body.replaceText('{{Doughnut 2}}', don2);

  body.replaceText('{{Doughnut 2 Qty}}', don2qty);

  // CRITICAL: Save and close to ensure changes are written before PDF conversion

  doc.saveAndClose();

 

  const pdfBlob = copy.getAs(MimeType.PDF);

 

GmailApp.sendEmail(clientEmail,"Details of your oreder",Hi ${clientName},\n\nPlease find the details of your order attached.\n\nBest regards){

  attachments: [pdfBlob]

}

[Michael O'Shaughnessy]

Hello Patrick,

Well, I see 3 things, not sure if any of them have anything to do with your errors:
First:
This line:
const copy = DriveApp.getFileById(TEMPLATE_ID).makeCopy({clientName}, destinationFolder); Not sure why you have {} around the clientName. It should just be the following: const copy = DriveApp.getFileById(TEMPLATE_ID).makeCopy(clientName, destinationFolder);


Second:
This line:

body.replaceText('{{Pickup Date}}', date);

You do not have a variable named "date". It should be:
body.replaceText('{{Pickup Date}}', pickdate);

Third:
This is where your big problem is. Your message is not a string.  I also suggest you just the MailApp since your are just sending emails.  GMailApp has a lot of other methods that you are not using so it adds a little overhead.  Take a look at this link:

https://developers.google.com/apps-script/reference/mail/mail-app#sendemailrecipient,-subject,-body,-options

Note the function parameters: sendEmail(recipient, subject, body, options).  And note what each parameter should be:

Parameters

Name	Type	Description
recipient	String	the addresses of the recipients, separated by commas
subject	String	the subject line
body	String	the body of the email
options	Object	a JavaScript object that specifies advanced parameters, as listed below

Advanced parameters

Name	Type	Description
attachments	BlobSource[]	an array of files to send with the email (see example)
bcc	String	a comma-separated list of email addresses to BCC
cc	String	a comma-separated list of email addresses to CC
htmlBody	String	if set, devices capable of rendering HTML will use it instead of the required body argument; you can add an optional inlineImages field in HTML body if you have inlined images for your email
inlineImages	Object	a JavaScript object containing a mapping from image key (String) to image data (BlobSource); this assumes that the htmlBody parameter is used and contains references to these images in the format <img src="cid:imageKey" />
name	String	the name of the sender of the email (default: the user's name)
noReply	Boolean	true if the email should be sent from a generic no-reply email address to discourage recipients from responding to emails; this option is only possible for Google Workspace accounts, not Gmail users
replyTo	String	an email address to use as the default reply-to address (default: the user's email address)

The body message you are sending is NOT a string.  AND I suggest you use the HTML body so the message looks better:

So right after your const pdfBlob change it to this:
const bodyMessage = `Hi ${clientName},<br><br>Please find the details of your order attached.<br><br>Best regards`;

MailApp.sendEmail(

clientEmail,

"Details of your order",

"",

{

htmlBody: bodyMessage,

  attachments: [pdfBlob]

    }

)

Hope this helps!!

--
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 visit https://groups.google.com/d/msgid/google-apps-script-community/a1252a26-206a-4ef8-81b1-2c7438bed8c0n%40googlegroups.com.

[Patrick Cloutier]

Hello Michael

So very grateful for your reply. Thank you for your help

I have adjusted accordingly.  Progress is being made.  

The new sheet gets created but the responses are not being dropped in it.

Here's my revised code.

function createInvoiceFromForm(e) {

  // 1. SETUP: IDs for your template and destination folder

  const TEMPLATE_ID = '1vJjS74UN82bT-uow1iqzQ7aGN03CFtVtK7_1XsyLU24'; // Found in the Doc's URL

  const FOLDER_ID = '1hdiwGsQ8MHEj6aHu8cn-RTJuRtj597hE';   // Found in the Drive folder's URL

 

  // 2. EXTRACT DATA: Get values from the form submission (e.namedValues)

  // Replace these keys with your EXACT form question titles

  const clientEmail = e.namedValues['Email'][0];

  const clientName = e.namedValues['Name'][0];

  const pickdate = e.namedValues['Pickup Date'][0];

  const don1 =e.namedValues['Doughnut 1'][0]

  const don1qty = e.namedValues['Doughnut 1 Qty'][0]

  const don2 = e.namedValues['Doughnut 2'][0]

  const don2qty = e.namedValues['Doughnut 2 Qty'][0]

 

  // 3. CREATE COPY: Copy the template using DriveApp

  const destinationFolder = DriveApp.getFolderById(FOLDER_ID);

  const copy = DriveApp.getFileById(TEMPLATE_ID).makeCopy(clientName, destinationFolder);

 

  // 4. FILL TEMPLATE: Open as a Document and replace placeholders

  // Use {{Placeholder}} syntax in your Google Doc template

  const doc = DocumentApp.openById(copy.getId());

  const body = doc.getBody();

 

  body.replaceText('{{Name}}', clientName);

  body.replaceText('{{Email}}', clientEmail);

  body.replaceText('{{Pickup Date}}', pickdate);

  body.replaceText('{{Doughnut 1}}', don1);

  body.replaceText('{{Doughnut 1 Qty}}', don1qty);

  body.replaceText('{{Doughnut 2}}', don2);

  body.replaceText('{{Doughnut 2 Qty}}', don2qty);

  // CRITICAL: Save and close to ensure changes are written before PDF conversion

  doc.saveAndClose();

 

  const pdfBlob = copy.getAs(MimeType.PDF);

  const bodyMessage = `Hi ${clientName},<br><br>Please find the details of your order attached.<br><br>Best regards`;

  MailApp.sendEmail(clientEmail,"Details of your order",

"",

{

  htmlBody: bodyMessage,

  attachments: [pdfBlob]

}

)

}

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/LALjZlj09Mw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAHNYQLhJKQPbyyLHoP1W%2BbGEOe7Tyz_xoF95suDM95hSEscy%3Dg%40mail.gmail.com.

[Michael O'Shaughnessy]

Ahhhh... the plot thickens!!!

You state: "The new sheet gets created but the responses are not being dropped in it.".  Is the template by chance a Google Spreadsheet?  If so then your step #4 in your code is all wrong.  

Are the emails being sent?  Is the attachment just a blank document?

Now, if you could make a copy of your template, change permissions to "anyone with the link can view", get rid of ANY proprietary info in the template then share the link with us we will have a better idea of wht your are trying to do.

Also, if you can give us an example set of data AND what you want the final document to look like that would be great.

I am extremely confident we will find a solution!!

Michael

www.MrOResolutions.rocks

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAMik30weHYrHphpdru47RTeA1JpQYB3bhBKO%2BsgvK_qS9wBy9Q%40mail.gmail.com.

[Patrick Cloutier]

Good afternoon Michael,

Great to read you again.  The Apps Script rookie in me sends love and gratitude your way.  This help is truly appreciated.

You know it, my template is a google sheet.

An email is being sent to notify of a failure only. No attachment.

The new sheet carrying the client name does get created and dropped in the proper folder.

As per your instructions, i have made a copy of my template.

You can access it here

Copy of Order Template - Google Sheets

The whole picture..

Thought a form would be a good way to go to collect doughnuts orders for my baker friend.

No online payment needed, just how many doughnuts and a desired date to pick them up.

With the usual Name, email, phone contact info to be picked up as well.

Approximately 100 different donuts with only 2 category of pricing. Regular and Premium doughnuts.

For the sake of making this simple, only 2 doughnuts choice in the form during the time i develop the winning recipe for my friend.

Thanks again

Regards

Patrick

My goal was to embed that form in his personal website as well.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAHNYQLgUCLKmdWHv5Mp_bY7QOGSEwOUUSkKSEL9jyk2v2dE-KQ%40mail.gmail.com.

[Michael O'Shaughnessy]

OK, yes your text replacement won't work on a google sheet.....

So, first let me share with you 2 free resources available on my site: www.MrOResolutions.rocks 

If you go to the following article: From BASIC to “Basically Magic”: My OETC 26 Recap you will find the links at the bottom.  You have to "purchase them," but they are $0.  Topic 3 in the  "Automate, Customize..." doc has a super simple text replacement example.  

Now, I also have 2 examples for you to review.  The first is the customer will get a nice email with their order information in it, no PDF attachment!!  Here is a link to the folder containing the form and spreadsheet:

https://drive.google.com/drive/folders/1ESbtWKteOLzC_ZAyN_Aj_XXKabedBF4l

The second is doing what you were trying to do initially: create a doc and attach it as a PDF.

https://drive.google.com/drive/folders/18J0pxiZfsCU2_io9S0qRswN23ytYioHS

This one has a template that you can format however you desire!  It is used just like your original code, but this time it will work.

The examples should be very well documented (and yes Claude.ai helped me!!!)

Please note that you, Patrick, have editor permissions on the 2 folders, anybody else has view only.

Take a look and let me know what you think.  Once you decide which way you want to go we can discuss how to personalize it for "Full Circle Doughnuts."

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAMik30z4WOX04n%3Duhw04ccyZvpH5zLzCk_RJVsatEn1EuN2MtA%40mail.gmail.com.

[Patrick Cloutier]

Good evening Michael,

I am without words, a whole new system ready and functional is far more than a bit of help on the code!!

Thank you so much.  I have chosen to go ahead with the file attached to the email and already started the implementation

of the absolutely fantastic solution you have so kindly provided.  Now i have a lot of doughnuts to put in!

Cheers

Patrick

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAHNYQLjMoJX9h_dP83O44P4DFnepuioyLrZQBH4-tkUrRq_GzA%40mail.gmail.com.

[Michael O'Shaughnessy]

Well I am glad to be of help!!  And that is what this community is about.

Please do not hesitate to reach out if you have any questions.

Have fun with the doughnuts!!

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAMik30wg37LH8h5hUk6D9SDtU0c6asRBmJoq1gYHJAy5pDW0bg%40mail.gmail.com.

[Patrick Cloutier]

Good afternoon Michael,.

I forgot to mention in my last email about your offer on the "basic to basically magic".  

I really wanted to take you up on that offer and educate myself more as knowledge is such a precious asset in life.

Turns out your website would not accept my Canadian postal code !!  I could not proceed with the purchase.

I feel very confident you'll have a solution deployed in no time to address this situation.

I'm doing good progress in my doughnuts data entry.  The volume did bring an additional question about the flexibility of the form.

I have categories of doughnuts i wish i could narrow down in the dropdown question of the form as it's a bit challenging to scroll through

over 100 different doughnuts to pick the desired one.  I bumped into the "Form Ranger" addon which is quite useful to manage this volume of choices from a sheet.

Works great but it wont go the extra mile i would need to efficiently narrow the selection down to 1 category.

Any suggestions on applying a category selection prior to doughnut selection?

Cheers

Patrick

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAHNYQLjMoJX9h_dP83O44P4DFnepuioyLrZQBH4-tkUrRq_GzA%40mail.gmail.com.