Auto-calculate rooms needed for a conference 🎟️ in a Google Sheet via an Apps Script

The why

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 article I will share how to create your own formula using an IF statement in JavaScript, that you can reuse if desired by making changes to it.

Quick note on the power of including emojis

Additionally I include emoji symbols in my sample, not just because they are fun, but to encourage users to leverage them anywhere to make communications more explicit. Whether it’s calendar invites, project plans, bulleted email topics — anything that is text-based can have these symbols pasted in, and help transmit ideas with your team by 10x because it offers efficient visual queues, as I’ve learned at Google (as I have in my title). Fun note you can paste emojis in any text interface because they are written in unicode by copy and pasting them from https://emojipedia.org/

What is a custom function?

A custom function is 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 2 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 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.

What’s nice about a creating your own formula is that you can call it into a spreadsheet like you would a normal function. For example my favorite function for inserting data into my Google Sheet from another sheet is entered as =IMPORTRANGE() in a cell, and in the parenthesis you include the exact location of the data’s range. With a custom function, if you have a the number of participants in cell C2 for example, you can enter =ROOM_TYPE(C2) in another cell to use it as a formula from your script. Let me walk you through it.

Follow me on Twitter / Source code in GitHub

Try it

1. Open a Google Sheet’s Script Editor

Create a new Google Sheet (type sheet.new in your browser) and locate Tools > Script editor menu at the top of the sheet. This brings you to a free script editor that it is tied to that sheet, called an Apps Script. It’s like a macro and can be used to connect Google and non-Google products. It’s free to use and you can share the sheet with others to make a copy and customize their own logic.

🔧Optionally: you can make a copy of my sample sheet here.

2. Write your Apps Script

• For this example, you can paste the following lines of code in the script editor.

• Then click the Save button.

• Note I added the emojis directly in my formula to display in my sheet 😮

3. Call your custom function from a cell

Return to your spreadsheet and enter =, the entire function’s name, and the position of the cell of your data input you wish to have processed by that function. In this case, the data input is the number of registered participants. in cell E2, and so in F2 I enter the following:

=ROOM_TYPE(E2)

You can then drag that cell all the way down so that it performs the same calculations throughout the entire column.

4. Visualize results

Creating a pivot table helps to quickly tally how many room types are needed for all the sessions. You can do so by highlighting all the columns and clicking Data > Pivot table > Create (keep default option to create a ‘new sheet’)

In the new sheet, select the following options to make the pivot table display the room types for all the sessions:

1. Rows > Room Type > Order > Ascending
2. Values > Add > Name of Session > Summarize by > COUNTA