Create a seat booking form with Google Forms, Google Sheets and Google Apps Script

Google Forms, Google Sheets, Google Apps Script: SpreadsheetApp, FormApp

In this tutorial, we are going to build a simple seat booking Google Form. It will contain a registeree’s name and the session that they wish to attend.

Each time a registeree submits a request to book a seat for a session, that seat is taken from the list displaying only the remaining seats for that session for when the next user submits the form.

Take a look at the example below:

Basic Seat Booking Form Google Forms

If all the seats have been booked for a seminar, then that seminar will not appear on the form. If all seats have been booked for all seminars, then the Google Form will close.

We will even create a  live list of attendees that we can embed on our website using Google Sheets.

Google Forms can’t do this for you out of the box. We will need to use Google Sheets to store our bookings and do some manipulation of the data. Then, we will use Google Apps Script to update the Google Form with the number of seats each time the form is submitted.

The Google Apps Script script has been prepared in a way that someone with limited coding skills can use it as a template by plugging in a few global variables. Alternatively, a more advanced user can incorporate the code into a larger project.

I’ll assume you have the basic abilities to create a simple Google Form and have used Google Sheets.

This tutorial can be followed as a useful step-by-step guide or if you are more confident, you can simply jump to the code using the contents below.

Continue reading “Create a seat booking form with Google Forms, Google Sheets and Google Apps Script”

Google Sheets: How to create a task completion check sheet.

Google Sheet: IMPORTRANGE, IF, TODAY, conditional formatting, tick box, alternating colors, protect sheet and ranges, share sheet 

Sometimes you have teams, staff or students who all need to complete reports and tasks by a certain date, but you need a way to keep track of when the reports are complete so that you can compile them later.

The following tutorial is a simple tick box-based Google Sheet that the user ticks when they have finished writing their quarterly report. I use something like this in my day to day to keep track of a number of reporting processes.

This is very much a follow-along guide for Google Sheet Beginners to help develop some basics skills and think about how to design Sheets for the workplace. Feel free to read what you need or skip to the end for a link to the google sheet for you to make a copy of.

Here is what we will complete by the end of the tutorial.

Task completion check sheet google sheets

If the user’s tick box had not been ticked in Column ‘C’, Column ‘D’ will automatically report Overdue in red and Column ‘E’ will report the number of days overdue.

There is a space set aside for administration to note the reminders that they have sent to the user and the last date the reminder was sent. Admin can easily copy the emails and send a message to those staff who have not completed their report by the deadline.

Let me walk you through the workflow for creating this:

Continue reading “Google Sheets: How to create a task completion check sheet.”

Google Sheets: How to hide formula error warnings where there is no data or the data divides by zero

Google Sheets, IFERROR, ISBLANK, IF

Have you ever set up a Google Sheet with formulas that you drag all the way down to the bottom? See all those messy error values littering your otherwise immaculate spreadsheet when those formulas don’t reference a value? It sucks, right?

Formual errors referencing empty values Google Sheets

What if I told you that you could hide all those messy errors? What if I told you that you could truly have that pristine nirvana that you have been imagining that your Google Sheet project would truly be?

Drink the Kool-Aid and strap in, my friends, as your very own guru in a goat takes you on a journey of enlightenment into hiding formula error when:

Continue reading “Google Sheets: How to hide formula error warnings where there is no data or the data divides by zero”

Multiple Cell Data and Formula Reference With Find and Replace – Google Sheets

One of my biggest regrets when I first got started with Google Sheets and spreadsheets in general, was not taking full advantage of Find and Replace.

Find and Replace is the penicillin of the Spreadsheet world. It can rapidly cure all sorts or issue with a simple set of commands. It is seriously amazing stuff.

Yeah! Yeah! We all know about Find and Replace.

I know! I thought so too, but then I started to really use and identify how I could use it to quickly:

  • Change template sheets.
  • Fix bulk errors in formulas.
  • Change parts of cells.
  • Replace values in the whole spreadsheet, one sheet or a selected range.

Before we get started, you need to know the short cut for the Find and Replace tool. This will come in handy in all sorts of programs and apps.

  • PC – Ctrl + H
  • Mac – CMD (⌘) + H

I’m going to go ahead and continue the examples using PC, because, you know, Mac.

Continue reading “Multiple Cell Data and Formula Reference With Find and Replace – Google Sheets”

How do I reverse the Rows in A Column in Google Sheets?

Google Sheets: SORT, INDEX, ROWS

Reverse Rows in Column in Google Sheets
My favourite Killjoy members. Column A – From most favourite. Column B Saving the best for last.

Sometimes you have a need to reverse a list quickly in Google Sheets.

That’s pretty easy to do if the list is sorted alphabetically. Just go on into the <Data> menu and choose from one of the sort functions. But what if the data you want to flip is not in alphabetical or numeric order?

Below are 3 ways to reverse your data:

For the examples below, I’ll be using a list of my favourite Killjoys characters. Yeah, I’m a sci-fi geek.

Continue reading “How do I reverse the Rows in A Column in Google Sheets?”