SPEAKER 1: Although Google Sheets has more than 400+
built-in functions, I love to share how to create
personalized functions to solve for individual needs.
In this episode, I will share how
to create your own formula using an if statement in JavaScript
in order to calculate what size room
you need based on the number of attendees
that registered for an event.
What's nice, too, is that you can reuse this bit of code
to slightly modify it for other use cases.
Welcome to The Sheets to Apps Show,
where we talk about how to tame these tabular beasts
into productive applications.
In this video, you can find a spreadsheet
with a sample script, along with step-by-step instructions
on other helpful resources.
First, make a copy of that sample sheet.
Then let's chat about what is a custom function.
It's a formula you create and can call into your sheet
in order to perform calculations that can otherwise
be extremely manual, error-prone,
or take precious time.
For example, let's say you work in event planning
and have a two-day conference with over 18 sessions
to plan for, and each session has a different number
of registered participants.
Taking the time to figure out how
many medium, large, and extra large sized rooms
is needed, but time-consuming if done by hand.
This is where a custom function can help look
at the number of registered participants
you have in a Google Sheet column,
and then automatically assign which type of room
is needed in another column, and even
include emoji symbols to help create quick visual references
for you and your team.
Note I encourage users to use emojis to make communications
more explicit, whether it's calendar invites,
project plans, email subjects, bulleted lists, or anything
that is text-based, as these symbols can be pasted in
and help transmit ideas with your team 10x,
because they offer efficient visual cues,
as I've personally learned at Google.
And as a fun fact, you can paste emojis in any text interface,
because they're written in Unicode, by copying and pasting
them from sites like these.
Now, returning to the subject of Google Sheet functions,
one of my favorite built-in functions
is IMPORTRANGE, because it helps me import data
from another spreadsheet.
Next, from your sheet copy locate Tools, then
Script Editor at the top.
This brings you to a free script editor
that is tied to that sheet, called an Apps Script.
You will notice the script is an if statement that
includes emojis in its results.
This logic creates a formula we can use in the spreadsheet
to analyze values and automatically populate
an outcome.
Next, in order to try this custom function out,
return to your spreadsheet, and in cell f2, type the formula
ROOM_TYPE like this.
And there you have it--
a formula you created to automatically
calculate how many large, medium,
and small rooms you will need.
Note this is just a basic example,
and you can use this same process
for more advanced calculations.
Hope you found this episode helpful, my friends.
If you did, please click Like and Subscribe
for future episodes.
Cheers.