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: Counting and Filtering Rows where Cells Contain Particular Values.

Google Sheets: COUNTIF, ARRAYFOMULA, FILTER, REGEXMATCH, REGULAR EXPRESSIONS (RE2)

Okay, wait! Stop!…

…I know the byline contained regular expressions, but I promise you I won’t just leave you with the formula for you to figure out your own jumbled mass of jibberish characters or inundate you with a list of commands and leave to attempt to piece it all together.

I promise to give you some clear examples with an explanation of each to you can apply it to your project.

Take a deep breath, mate, and let’s get cracking.

We are going to look at two related scenarios:

Scenario 1:

Imagine that you have a huge list of items. You have a hunch that some of the cells contain certain values of interest for you. You want to build a new list with only those values in them.

Imagine that you have a list of full names, and you want to use Google Sheets to create a new list of full names that only contain John.

Scenario 2:

You have that same huge list again, but this time you only want the total count of all the rows that contain certain values in each cell.

Imagining that list of full names again, you are now going to get a total count of all full names that contain John in it.

 

We’ll first go through how to create these formulas and then provide a number of clear examples on some common criteria for searching cell for values using REGEXMATCH and regular expressions.

Continue reading “Google Sheets: Counting and Filtering Rows where Cells Contain Particular Values.”

Google Apps Script: Create a Select All Checkbox to Tick Checkboxes in Any Desired Range in Google Sheets

Google Apps Script: isChecked(), switch, filter, map

One Checkbox to Rule them All

Now that’s a fantasy novel I could be my gums into.

Have you ever created a Google Sheet projects where you could really use a select all checkbox (they call them ‘Tick-boxes’ in Google Sheets)? Sure you can copy a range of tickboxes and paste the same range etc. But can you really trust your users not to mess that up?

People are used to select-all checkboxes in their computerised lives. It’s always better to work with familiarity to provide a better user experience rather than try and teach the user on the fly how to do something your way.

With this in mind, I went about creating a select-all checkbox for Google Sheets. Here is a little demo of how it all works. All the black background Tick boxes are select All boxes. These boxes have been assigned a range of other checkboxes that will be either checked or unchecked depending on the main select-all boxes state.

Google Select All Demo with Google Apps Script
Select All Demo

As you can see, the select all checkbox only changes the tick boxes in the assigned range. It does not affect any other non-tick box data in the same range.

To get this up and running on your own project, all you need to do is copy and paste in the two code files in your Google Apps Script editor. The first code file is the function that runs the check-all code. The other file stores all the select-all checkboxes and the ranges that they will affect.

Let’s take a look at the code and then run through a quick use guide before finishing off with an example.

Those of you who want to dive into the nuts and bolts of the code, there will be more explanation of each element of the code at the end.

The Code

Continue reading “Google Apps Script: Create a Select All Checkbox to Tick Checkboxes in Any Desired Range in Google Sheets”

Google Sheets Intermediate Project: Comparing the Best Daily Currency Exchange Rates Between Two Agencies – XRP/USD Example Part 1

Google Sheets: FILTER, ARRAYFORMULA, UNIQUE, WEEKDAY, LEFT, MIN, MAX, IFERROR, Conditional formatting, workflow.

In this tutorial, we are going to tackle some Intermediate level Google Sheets concepts including using functions like:

  • FLITER
  • ARRAYFORMULA
  • MIN and MAX
  • IFERROR
  • SPARKLINE
  • UNIQUE
  • WEEKDAY
  • LEFT

We are then going to make our Google Sheet more presentable by doing some neat tricks with Conditional Formatting.

However, probably the most important lesson is to consider throughout the whole process is the workflow of this project. I will try and get you into my headspace and talk about:

  • How I came to do things in a certain way.
  • What I tested before applying to my Google Sheet.
  • The order I did things to create the test.

When I am learning something, I find the most benefit in learning by following a project. As you follow along you don’t have to read everything. Actually, I encourage you to get the basic idea of the stage before giving it a try. Then come back to the tutorial for troubleshooting.

I hope you enjoy the following project…

Continue reading “Google Sheets Intermediate Project: Comparing the Best Daily Currency Exchange Rates Between Two Agencies – XRP/USD Example Part 1”

Google Apps Script: Delete Rows Based on a Column’s Cell Value in Google Sheet

Google Apps Script, SpreadsheetApp, Google Sheet, Iteration

A not too uncommon task for Google Sheets users is to delete rows based on a cell’s values in a certain column. Often you don’t want to change the row position of the values so sorting the values and then deleting them in blocks is out.

All you want to do is iterate through the rows in the Sheet searching your selected column for the text or value that will trigger your code to remove the assigned row. Then delete the row.

Rinse and repeat until all rows have been checked.

This task can be automated with Google Apps Script in a number of ways depending on the size of your data set.

Small Data Sets

The most logical approach to delete rows in a small data set might be like this:

In the Globals section, lines 5-7 get the Spreadsheet, Sheet and Range Data. We use the SpreadsheetApp Class to get this information. For your own use, you would change the SS and SHEET variables to suit your project. The RANGE variable is the full range of data

Next, we have the parameters we will use to decide what rows we will delete. The DELETE_VAL variable is the text value in the row you want to delete. When the code sees this, it will delete the row.

The COL_TO_SEARCH variable tells the code which column to search. In your Google Sheet, you will have a letter across the top to determine the columns. We want to change this to a number. In coding, numbers start at zero. So, if your column to search is, say column “D”, then you would count:

0:A, 1:B, 2:C, 3:D

deleteEachRow()

Continue reading “Google Apps Script: Delete Rows Based on a Column’s Cell Value in Google Sheet”

Google Sheets – Remove The Lowest Grade for Each Student on a Course

Google Sheets – MIN, FILTER, INDEX, MATCH, SUM, COUNTIF

In the region of the world that I work in, it is a pretty common occurrence for university courses to run weekly assessment. At the end of the course, all the weekly assessment is then added together minus the lowest piece of assessment.

For lecturers with small course sizes, this is a pretty simple task that you could simply eyeball if you have a small enough group, but what if your course runs into the thousands with half a dozen tests to choose from. Eyeballing is just not going to do it.

Recently I was asked to do the same thing for the program that I manage. Over an 8 week term, we run 7 assessment at the end of each week for our students.  My job was to find the lowest grade out of the 7 assessment and drop it, taking note of the assessment unit that I dropped for each student. 

I use Google Sheets for this purpose for its ease of use and sharability.

This is an example dataset of the 7 assessment (in this case, weekly tests) in Google Sheets. We need to remove the lowest grade from each student. As you can see not all students have their lowest grade in the same Unit test. 

List of 7 grades for each student - Google Sheets

Continue reading “Google Sheets – Remove The Lowest Grade for Each Student on a Course”

Google Sheets – Get the Top 2 Grades for Each Group in a Spreadsheet using RANK and FILTER

This week I was asked to provide a list of the top two student grades in each of the 100 classes in our university program. 

Normally, this would be a pretty easy task if there were only a half dozen or so classes. Simply sort by group (class) and then Grade. Finally, copy and paste the top 2 ranked students of each group in a new sheet – print and hand to the boss. 

However, with 100 classes this was going to turn into a time-consuming chore and one that could be prone to errors. 

To solve this problem, I took advantage of Google Sheets RANK.EQ and FILTER formulas. Let’s take a peek at the formula now and go through the steps in detail later.

Let’s move on to an example.

Continue reading “Google Sheets – Get the Top 2 Grades for Each Group in a Spreadsheet using RANK and FILTER”