[MUSIC PLAYING]
SPEAKER: Gathering inputs from multiple sources
and then taking action on each becomes
taxing when the task needs to be realized with frequency
and by multiple stakeholders.
An example is quarterly or annual budget approvals
for a finance team.
In this episode, I will share how
you use an app script in a Google Sheet
to automatically create a budget submission form that you
can share with end users.
And when their responses arrive in the sheet,
you can collaborate with other reviewers
to send emails in bulk, depending
on whether you are approving, rejecting, or asking for more
information about the request.
What's even more handy is that we
use Google Doc as an email template and insert
information from the Sheet, such as a user's name, the budget
values they entered, or any special comments you
would like them to be notified.
Welcome to the Sheets to Apps show,
where we talk about how to tame these tabular beasts
into productive applications.
In this video's instructions, you
can find a spreadsheet with a sample script included,
along with step-by-step instructions
on other helpful resources.
The first step is to make a copy of that sample sheet.
The script uses a custom menu to display several sub-options
from the sheet.
This menu is added to the interface thanks
to an on-open simple trigger, available in Apps Script.
Each option has a name and executes a function.
This script uses several arrow functions,
which is a feature in modern JavaScript, that
allows you to write transformations that map,
filter, and reduce data.
From the top of your Sheet, select the custom menu
Send email, Create form.
You are then prompted for permissions to run your script.
Select Review permissions.
Note, if you encounter a warning page that
says this app is not verified, proceed
to click Advanced, Go to, and then Allow so
that your script sends as you.
Next, visit your spreadsheet, and in a few seconds,
you have a new tab called Form Responses 1,
and it has nine column headers, each belonging
to the questions in your form.
Note-- if you want to have multiple forms to keep
responses separated, you run the Create Form script
more than once.
This will create a new form each time,
and link it to a different tab in your sheet
with its own unique instance of the form.
You can always visit each form by selecting the Form menu
option at the top of its respective response sheet.
Next, from the Spreadsheet tab called Form Responses
1, visit the Form menu, and then Go to live form.
It should look like this.
Submit four responses to create test responses in your sheet,
and ensure to use your own email address so you
can receive the test emails.
Note the URL address of the form is
the link you can share with other teams to fill out.
Next, from your Form Responses 1 tab,
insert three columns to the left of the column called Timestamp,
and name Column A Action, Column B Comment, and Column C Status.
Next, in Column A, create dropdown options
by selecting the top of the column
so that the entire column is highlighted, and choose
the data menu at the top of the sheet, then Data validation.
Next, in the field called Cell range, modify A1 to A2.
This way it skips the header.
And in the Criteria field, change the option
to List of items, and enter the following values with commas
in between each.
Next, click Save.
The Action column will now have the following options.
Next, let's look at the Google Docs
we use to send email by visiting the tab called Templates.
This tab contains three template names
in their own row in Column A, with their respective
your URLs in Column B. These templates insert data
from the response sheet wherever there
is text in curly brackets.
Next, return to your Responses tab,
and populate Column A and B with each action type
once, and enter comments in order to test your emails.
Next, locate the custom menu at the top
of your responses sheet called Send emails,
and choose Approved.
After a few seconds, the row you marked as approved
is populated with today's date and the name of the template
used to email that user.
After the Status field is populated,
you receive an email like this.
Next, locate the custom menu option Send emails again,
but this time select Research needed.
Note you can delete the status that was already populated
from a prior email sent if you wish
to send a different email to the same user,
especially after a user sends you additional documents.
Next, after the Status field is auto-populated for the Needs
research row, let's run the script
to send an email when a budget is not
approved by selecting the custom menu option
Send emails, Not approved.
It's important to wait for each request to finish,
as it will cancel a previously-triggered email
action if it has not completed it and entered a timestamp.
And finally, note that of the four options in the Action
column, only three have email workflows.
The one that is called Review in progress
simply helps you or your team know that a new response is
being reviewed, and no outcome has been assessed just yet.
And there you have it--
a spreadsheet workflow that both creates a form
to collect information from other teams,
and enables you to send emails based on certain actions
that you wish to take.
Well, community, hope you found this episode helpful.
If so, please click Like and Subscribe
to receive alerts of future episodes.
Cheers.
[MUSIC PLAYING]