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 Apps Script: How to force subscribe Google Group Members to a Google Calendar for G Suite

Google Apps Script, G Suite Admins, Google Calendar, Calendar API,  AdminDirectory, GroupsAp

Note! This article is for G Suite users and admins. You won’t be able to apply the same code to Consumer accounts. 

Google Groups in  G Suite can be a really convenient and clean way to share users to drive folders and file locations, email users and set up Google+ community locations. However, I have come across some issues with sharing Google Calendar to Google Groups.

When you assign a Google Group email to a Google Calendar it sends out an email inviting the users in the group to accept the calendar into their list of shared calendars. The user must then accept the invitation before the calendar is added to their Google Calendar list of shared calendars…calendar.

The users receive an email message like this:

Hello Goat_GroupAlpha,

We are writing to let you know that Billy.Goat@yagisanatode.com has given you access to view events on the Google Calendar called “Test”.

Click this link to add the calendar.

– The Google Calendar Team

The Problem

In a large organisation, not all users in a group will add the calendar using the link. This might be okay but if the calendar is important or the user, as is often the case, simply just forgot to add it, then there will be emails to you the admin in the future asking you why they don’t have the calendar that everyone else has and has now missed some important event…sigh.

Another issue is that when a new user is added to a group when the group has already been added to a calendar, then that user will not receive an email invitation to the calendar and won’t be able to see the calendar.

Almost a Solution

Continue reading “Google Apps Script: How to force subscribe Google Group Members to a Google Calendar for G Suite”

Google Apps Script: Google Standard Color Palette Picker for Sidebar and Dialog Boxes in Google Sheets, Google Docs and Google Slides

Google Apps Script: Dev Tools, Color Picker, Side Bar, Custom Prompt, HtmlService, onOpen, Sidebar, Dialog Box

I wanted to update one of my free Google Add-on apps that works with colour. What I had is just the standard HTML color input element where the user selects from the palette and that hexadecimal colour code is returned to Google Apps Script to be used in the App. The problem is that it is really hard to get a good colour match between the palette and Google’s own colour range that is accessible from the fill or text colour buttons.

Take a look at the comparison between the HTML color input element and the Google Sheet background colour palette in the image below.

Color input vs google sheet palette colours

That’s not a user-friendly tool to match colours with the standard Google palette.

So in the back of my mind, I had always wanted to create a tool for a sidebar or dialogue box that would allow the user to easily access the standard colours or use the custom palette provided by the HTML color input.

After finding a bit of time in my recent summer break I came up with this.

Google Color array picker

And this is how it looks in action:

Continue reading “Google Apps Script: Google Standard Color Palette Picker for Sidebar and Dialog Boxes in Google Sheets, Google Docs and Google Slides”

Google Apps Script: Hexadecimal Color Codes for Google Docs, Sheets and Slides Standard Palette

Google Apps Script – GSuite Standard Color Palette

I am planning on updating a few Google Apps Script projects and updates soon. To accomplish them, I needed to get the full array of colours and their hexadecimal codes from the Google Sheets, Docs and Slides dropdown menus for the text and fill colours.

Google Sheets Color Palette

I wanted to be able to easily access the hexadecimal codes for each of the custom colours that Google has.

In this short post, I want to share a number of array and object formats along with a quick Logger.log example for each one.

However, first of all here is the Google Sheet with the colours along with their hexadecimal name and the name that Google gives the colour.

You can check out the file here:

Continue reading “Google Apps Script: Hexadecimal Color Codes for Google Docs, Sheets and Slides Standard Palette”

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 Apps Script: How to check if there is a Tick Box (Check Box) in a cell or range

Google Apps Script: checkbox, Google Sheets

This is something  I came across today when working on a project. I needed my Google Apps Script to look at a cell or range and determine whether there is a checkbox in that Google Sheet cell. If there is, then my script would do something. Otherwise, I would throw an error alert to the user.

Off I went searching through the Google Apps Script docs hoping to find something like an isCheckbox() method.

No such luck on such an obvious solution, but after playing around with some of the other checkbox methods, I did find a solid alternative.

isChecked()

I didn’t feel that the docs were up to their usual amazing standard here. Thus the explanation below (Yes, I am also procrastinating from my project, but I’m also making an online note for myself).

isChecked() takes a range or a cell and returns:

Continue reading “Google Apps Script: How to check if there is a Tick Box (Check Box) in a cell or range”

Google Apps Script – URL Fetch JSON data from an API and add it to a Google Sheet – Ripple API example

Google Apps Script – UrlFetchApp, SpreadsheetApp, Ripple API, Time Triggers

I have been very fortunate of late to have the patronage of the Ripple XRP cryptocurrency community via XRP Tip Bot and Coil. This is no small part due to the support of user Recreational Rex from Twitter.

Full disclosure here, I don’t really know all that much about cryptocurrencies. I knew about Bitcoin and Ethereum but really didn’t really invest any time and money into looking at these growing forms of value exchange.

But now I’m a little curious.

I thought it would be fun to see what the value of XRP was against a fairly standard metric like the USD and keep a record of this exchange rate daily over a month or so (Mrs Yagi just read the ‘fun‘ in the last sentence and rolled her eyes).

I also thought it would be pretty cool to see how many exchange providers (Gateways) that conduct USD-XRP exchanges are around and see if there is much difference their exchange rate day-to-day among them.

Fortunately for me, the XRP Ledger provides a freely available open-source ledger that can be accessed via the Ripple Data API. The API can return JSON objects from which we can extract the data we need.

Of course, to record and display the daily exchange-rates I went straight to Google Sheets. I gathered the relevant XPR data using Google Apps Script’s UrlFetchApp Class and pushed it to my Google Sheet with SpreadsheetApp Class.

Here is a live embed of the XRP Google Sheet that is updated via a daily time trigger via Google Apps Script.

Continue reading “Google Apps Script – URL Fetch JSON data from an API and add it to a Google Sheet – Ripple API example”

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 Apps Scripts: Create Time Triggers to automatically send email task reminders from a Google Sheets check list

Google Apps Script: Time Triggers, ClockTriggerBuilder, ScriptApp

One of the most powerful things about Google Apps Script is the ability to automatically complete tasks with time-driven triggers. You can set up your code to run any one of your functions in your script by the minute, hour, day, week, or month. You can even set it to run based on a specific date.

Have a look at the time-driven trigger options below:

    • Specific Date and Time: Based on: YYYY-MM-DD HH: MM format
    • Minute: Every minute or every 5, 10, 15 or 30 minutes.
    • Hour: Every hour or every 2, 4, 6, 8 or 12 hours.
    • Day: At a time within an hourly bracket. For example:
      • Midnight to 1 am,
      • 3 pm to 4 pm
  • Month: On a specific day of a calendar month at a specific time. For example:
      • Every 3 day of the month at between 3 am and 4 am.

There are two approaches to setting up these time-based triggers. The first one is by simply using the Trigger creator inside the G Suite Developer Hub. This allows you to select the function that you want to add a trigger to for your project and fill out a simple form to determine when you want your trigger to run.

Gsuite Developer hub time trigger

Alternatively, you can create a time trigger programmatically using the ClockTriggerBuilder class.

In this tutorial, we will build on a previous project we’ve called The Badger, that contains a task check off Google Sheet. If the user does not check off their sheet by the due date, then we can send them an HTML email reminder.

Google Apps Script email remind from Google Sheet The Badger 2

Don’t worry, I will provide the starter code and the Google Sheet for you in a minute. However, if you want to learn how I created the email reminder for The Badger you can follow the tutorial here:

Google Apps Script: Create an HTML email reminder service from Google Sheet data.

If you want to see how I created the Spreadsheet, you can check out this link:

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

Setting Up

Continue reading “Google Apps Scripts: Create Time Triggers to automatically send email task reminders from a Google Sheets check list”

Google Apps Script: Create an HTML email reminder service from Google Sheet data.

Google Apps Script: SpreadsheetApp, Utilities, MailApp, UrlFetchApp, HtmlService, onOpen

Ever wanted a quick way to send reminders to staff, letting them know that their task is overdue?

I often have to provide Google Sheet checklists that administration team managers can crosscheck to see if their team members have complete a certain task.

Reminding staff to complete a task can be time consuming and irritating. What if you could send an email to all those staff members who haven’t completed the task with a simple click of the button?

In this tutorial, we will learn how to create a Google Apps Script HTML email mail merge service that will send an email to a staff member who has not completed their task, indicated by a missing tick on a tickbox.

We will use the sample sheet below:

the badger Google Sheet

The Google Sheets reminder is triggered by a custom menu item.  You can see it at the top right of the image.

When the reminder is sent, it collects the rows of staff that have not indicated that they have completed the task.  It then sends an email to those staff members  with the reminder below:

HTML Email Reminder Google Apps Script
Get it! Badger! Ha!

As you can see, I’ve added some custom HTML to highlight the header and modify the width of the image. The email contains a number of unique pieces of data gathered from the Google Sheet Row:

  • The name of the user.
  • The deadline.
  • The name of the sheet (In this case, Tester).
  • The number of days overdue.

It also contains a link to the check sheet for the user to update once they have completed their task.

Back at The Badger Google Sheet, an alert window appears once the code is completed and also indicates any emails that could not be sent.

To get started you can Make a Copy of the Google Sheet by following this link:

Continue reading “Google Apps Script: Create an HTML email reminder service from Google Sheet data.”