How do I refer to a Google form record when using HTML template in App Script?

Question

Is it possible to add namedValues in a scriptlet when trying to use HTML template to send mails in App Script? If yes, how please, if no, how can I refer to each question on a Google form using scriptlets so that on form submission, the HTML template will evaluate() each record submitted? Below is the code

function sendEmail(e) {
var named_values = e.namedValues;
var name = named_values["Name"]
var timestamp = named_values["Timestamp"]
var subject = "Outlet "+ timestamp +""

const outlet = HtmlService.createTemplateFromFile('outlet');
outlet.named_values = named_values;
const message =  outlet.evaluate().getContent();

MailApp.sendEmail({
to: '[email protected]',
subject: subject,
htmlBody: message
});
}

...and below is the template code

<!DOCTYPE html>
<html>
<body>
<ul><li><b>Company:</b> <?= name ?></li>
<li><b>Loaction:</b> <?= timestamp ?></li></ul>
</body>
</html>

I need the script to pick 'Name' and 'Timestamp' from the data submitted last from the Google form and send it to mail using the HTML template. Thanks.

Please provide an example of what you wish to do

Cooper
– Cooper

2023-01-16 23:37:26 +00:00
Commented Jan 16, 2023 at 23:37 — Cooper
– Cooper, Commented Jan 16, 2023 at 23:37
What do you mean by "namedValues"?

Wicket
– Wicket

2023-01-17 00:05:19 +00:00
Commented Jan 17, 2023 at 0:05 — Wicket
– Wicket, Commented Jan 17, 2023 at 0:05
I've added the App Script code and the HTML template code.

Ibitoyei
– Ibitoyei

2023-01-17 08:07:35 +00:00
Commented Jan 17, 2023 at 8:07 — Ibitoyei
– Ibitoyei, Commented Jan 17, 2023 at 8:07

Matias CG · Accepted Answer · 2023-01-17 17:06:29Z

0

e.namedValues is an object, meaning it has properties, these properties are the form titles and their values are the answers people submits in the form.

The representation of the namedValues object on the google doc is the following:

{
 'First Name': ['Jane'],
 'Timestamp': ['6/7/2015 20:54:13'],
 'Last Name': ['Doe']
}

On your code you need to access the values of e.namedValues + the specific title field you want to get. For example, in order to get the timeStamp, you do it the following way:

let named_values = e.namedValues;
let timeStamp = named_values.Timestamp;

In order to make you script work you should apply this concept to your code. A way of doing this is the following:

function sendEmail(e) {

 let named_values = e.namedValues;
 let name = named_values.Name;
 let timeStamp = named_values.Timestamp;
 let subject = "Outlet "+timeStamp;

 let outlet = HtmlService.createTemplateFromFile('outlet');
 outlet.timeStamp = timeStamp;
 outlet.name = name;
 let message =  outlet.evaluate().getContent();

 MailApp.sendEmail({
  to: '[email protected]',
  subject: subject,
  htmlBody: message
 });
}

And the HTML should look like this:

<!DOCTYPE html>
<html>
 <body>
  <ul><li><b> timeStamp: </b> <?= timeStamp ?></li>
  <li><b> Name: </b> <?= name ?></li></ul>
 </body>
</html>

edited Jan 17, 2023 at 17:06

answered Jan 17, 2023 at 16:23

Matias CG

4213 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ibitoyei Over a year ago

it worked and I appreciate. But added an HTML comment to the code, but the comment was not included in mail received. What can I do about this please?

Matias CG Over a year ago

Hi, is the comment another field from the form? if yes, you can assign it's value to a variable the same way the code assigned values of Name and TimeStamp, then you include that also in the outlet and in the HTML file you create a new <li> section with the comment.

Ibitoyei Over a year ago

It's something like <!-- comment - - >

Collectives™ on Stack Overflow

How do I refer to a Google form record when using HTML template in App Script?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related