SPEAKER: Thanks to the abundance of platforms that presently
exist for creating content, so many people are
able to create online white papers, ebooks,
informational videos, or a tips-and-tricks blogs
around different topics.
And it could be handy to use a Google Form to help requesters
receive these assets automatically by email
using a Sheets Apps Script.
And that's what I will cover in this episode.
Welcome to the Sheets to Apps Show,
where we talk about how to tame these tabular beasts
into productive applications.
[MUSIC PLAYING]
Google Forms can be used for so many different use cases
since they serve as a type of user interface
over a spreadsheet.
They also have a wide range of options
for being shared that span from being embedded
into a website or an email signature
or even being sent as a link via an instant message.
In our present use case for sending content
upon a one-time request, we can create a three-question form
that collects a user's email, their name,
and a list of four topics that you offer content
on as checkboxes, such as this example here.
But one friendly reminder for those
sending content on an ongoing basis
to folks who specifically subscribe to a mailing list--
it is strongly encouraged to include an opt-out link
in your emails to avoid being marked as spam, which can lead
to the unpleasant effect of getting your domain
name blocked by inboxes.
With that said, let's jump into the Google
Sheet, which is where the form responses arrive.
We will then visit our script from here
by clicking Tools and then the Script Editor
to highlight some key parts.
And note that there is an example
sheet with the code linked in the description of this video.
At the top, we listed all our constant variables,
such as the subject line of the email,
the unique ID of the Google Doc we
will use as the email template, and the links
to the content associated for every topic in the form.
Our function installTrigger() includes a Sheets method called
getActiveSpreadsheet().
This tells a script to leverage the current sheet
being used since this is a container-bound script
and authentication is already handled for us.
This also saves time in having to copy and paste
spreadsheets' unique identifiers every time
you make a copy of the sheet or share the script
with another team member.
Upon selecting this function from the dropdown menu and then
clicking the Run icon, it installs a recurring trigger
that runs a function called onFormSubmit().
And as its name implies, it runs a script
every time a new entry is submitted in the form.
MailApp is an Apps Script class that contains a function called
sendemail(), which does just that.
If there are topics selected--
a.k.a, the index is greater than zero--
then an email is sent using the address
from the form with the subject line mentioned before.
The body of the email is composed
by a variable called htmlBody, which downloads a Google
Doc into an HTML string serving as a template that contains
placeholders to insert a user's name and the links
to the topics they selected.
And to keep track of which emails were sent out,
there's a variable called status,
which stores this information and pushes it
into a column called Confirmation
within the spreadsheet.
For debugging purposes, we use Logger.log,
which is an Apps Script class that
contains a log function that helps
view the responses processed and whether an email was
sent when you click View Logs within the Script Editor.
And so finally, upon entering a request
for content via the form, the recipient
receives an email like this with their name and topic selections
provided.
Note this is just a simple Apps Script.
But you can add more logic for further automation based
on your own workflow if you found this video helpful,
please click Like and Subscribe.
Cheers.
[MUSIC PLAYING]