Send content automatically via a Google Sheet and an Apps Script
Brief intro
So many people are able to create content online such as white papers, eBooks, informational videos, or blogs; around different topics. Ergo, it’s handy to link a Google Form on a website, email signature, or in an instant message to help people request assets you offer as a list of topics and have them automatically sent to them. This is possible if you add an Apps Script in the Google spreadsheet that automatically runs every time someone submits a form and sends an email using a Google Doc as its template.
Intent
In this article I will walk you through:
- How to try the Google Sheet, Google Form, and the Apps Script mentioned.
- How to optionally customize 3 things: the topics in the form & code, the email template, and the subject line of the email in the code.
- Walk through how the code works.
Try it
1)Make a copy of this Google Sheet (in your browser). It will automatically include a Google Form and the code you will need known as an Apps Script.
2)From your spreadsheet click on Tools > Script Editor. This brings you to the Apps Script editor.
3) Now run the script by clicking the “Select function” drop down > choose “installTrigger.” Then click the Run button (►).
4) When prompted, click the Review permissions and click Allow so the script can email on your behalf.
Important: If you get a warning that This app isn’t verified continue with the verification process by clicking Advanced and then scroll down and click the grey text at the bottom that says “Go to ….”
5) After granting permissions, a trigger will have been created for your sheet automatically. Visit this trigger momentarily by clicking the trigger icon (which looks like a clock) in the script editor.
Caution: If you run this script more than once, it will generate multiple triggers which would duplicate emails. Ensure you only run the script once and that there aren’t multiple triggers on the triggers page. If you do accidentally create more, just delete all the additional ones by hovering over each and clicking the 3 dots > then delete.
5) After granting permissions, return to your spreadsheet and locate Form > Go to live form. This will bring you to the Google form that people will see when you share its link. Fill out all the fields and ensure to use an email address you have access to, and click Submit.
Note: Ensure to enter the address of an email you can login.The email will include the name and the topics you populated. An email will not be sent if no topics are selected.
6) Return to your spreadsheet. You should have a row with values entered. One of it’s columns called Confirmation, will say Sent. This confirms that the topics you selected in the form are emailed.
**WAIT — I don’t see data entering my spreadsheet, now what?
Note: if you do not see data in your sheet it means the form may need to be relinked is all, so from your spreadsheet click Form > Go to live form. On the form, click the pencil icon in the top right corner to enter edit mode. Then click the Responses tab > 3 dots > Unlink form > confirm action to unlink. Then click on the 3 dots once more > Select response destination > Select existing spreadsheet > choose from the Drive files pop-up the first file called Copy of (auto-copied w/ sheet) Form to receive content >Select. Return to the the spreadsheet and visit the first tab (a new sheet was created called Form Responses 2), and click Form > Go to live form from Form Responses 2 — and your response should show up there almost immediately.
7) Finally, login to the email account you provided in your form entry and see if you received an email with the subject line: “Howdy” with an email body similar to this Google Doc’s template.
Optionally customize your script
1)[optional] The email sent out comes from a Google Doc template already setup. However, if you wish to customize that template, you can make your own copy by clicking this link and set permissions to viewable by anyone unless your audience are people within your organization, set to anyone in your organization. Then copy its URL address from the browser and replace the URL in the EMAIL_TEMPLATE_DOC_URL variable in the script's code.
Note: {{NAME}} and {{TOPICS}} in the Google Doc are placeholders that insert the name and the content links from the topics selected by the user upon them submitting the form.
2) [optional] If you wish to change the subject line of your email, replace the text in the EMAIL_SUBJECT variable in the script's code.
3) [optional] If you wish to rename the topics in the form, upon changing them, also paste the new topic names into the topicUrls variable in the script's code.
Caution: When renaming the topics in the form, ensure you paste the exact topic names in the code as well. Ensure the word is contained in the form’s choices. Ex: if you rename “(NUTRITION) Raw vegan recipes” to “(MINDFULNESS) Learning how to meditate in a busy world” in the form, make sure to go back the
topicUrlsvariable and replace “Nutrition” with “Mindfulness” + the link you want people to receive
How the code works
This solution is approx. 106 lines of code including all the comments :) If you wish to view the entire code you can find here (or in the sheet mentioned earlier if click Tools > Script Editor).
The first lines setup the Google Doc template by grabbing its URL, the email subject, and the topics that are offered in the form and their respective links.
The next few lines describe a function called `installTrigger`. Upon running this function in the Apps Script editor, it automatically creates a trigger in your Apps Script project that schedules another function to run right below it called `onFormSubmit` when a user submits a response.
`onFormSubmit` is a function that is called when a user submits a response. When it’s called it receives an event object which contains details such as the most recent responses submitted in the form, their row number in the spreadsheet, etc. Note the method trim is used to remove extra spaces a user may accidentally enter into the form.
A variable called topics is declared which will review the topics, if any, were selected by the form submitter using a for-loop. If no topics were selected it does not push any items to this variable’s list, preventing an email from being sent in the next step.
If there are topics selected (the index is greater than 0), then an email is sent using the address from the form, with the subject line that was listed at the beginning of the script in the variable `emailSubject`. The body of the email is composed by a variable called `htmlBody` which we will talk about in a bit. Also note that when topics are selected, the variable called `status` stores information on whether an email was `Sent` or if no email was sent because there were `No topics selected` in the end.
`MailApp` is an Apps Script class that contains a static method called `sendEmail` which does just that.
Here we create a variable called `sheet` which uses a method called getActiveSheet to locate the response sheet you are on. The variable called row locates the row of a new response in order to ensure that only that new row is processed, and not the entire spreadsheet, which otherwise would slow your script significantly once there were hundreds of responses. The variable `column` checks to see what the column location is of a new response and adds `1` to that number so that it locates the nearest empty column to write the status. The variable called status populates the empty cell on the same row to show if an email was sent.
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 in the script editor.
The next function called `createEmailBody` represents the topics list in HTML and uses the variable I mentioned earlier called `topicsHTML` to create a list (in bullet form) with the topic names and their respective hyperlinks embedded, so that the reader can click on each item listed and be taken to content.
The squiggle placeholders called {{NAME}} and {{TOPICS}} within the variable emailBody are replaced with the information entered into the form in the Google Doc serving as a template (we pasted it’s URL in the first lines of the script up top) for the body of the email.
Finally, the docToHtml function helps download the Google Doc template into the email body. We fetch the doc via the REST API using the UrlFetchApp Apps Script library.
We need to authorize through OAuth2.0 using the Drive scope permissions. Authentication is usually a cumbersome process, but thankfully Apps Scripts provides the ScriptApp.getOAuthToken() function to do that for us, but we also need to certain scope permissions for Drive, email, etc. We set these up by adding the scopes to the manifest file which is a JSON file that specifies basic information about the project.
Note: You can access it from the script editor in the sheet select View > Show manifest file.
What’s next? Check out more solutions you can use, from our G Suite Solution Gallery.
