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”

How to stop getting Google document requests to edit from users outside your organisation from publicly viewable files.

G Suite – Paid editions, Gmail, Google Sheets, Google Docs, Google Slides, Google Drive

Recently, I had updated all the course materials in a learning management system (Not Google Classroom. Sorry Google) to only use G Suite files like Docs, Sheets and Slides for students to access.

For me, this was a pretty logical step. It allowed course creators to update their files live when they had to correct errors or make minor changes quarter-to-quarter or year-to-year. Administratively, it meant that files did not have to be accounted for, deleted and updated every time a change was made.

The Problem

While time-consuming, the changeover went well as we changed all of the student resources into Google files. We set all the files to be accessible to anyone with the link can view (The organization does not have student accounts on the same domain as the staff).

Google sharing settings

However, in the back of my mind, there was a problem I knew I needed to address. The dreaded request for access to edit. With over 3,500 students on the program and hundreds of files for them to access, it would be a huge pain if some of these students clicked that view and requested edit access.

Google Docs View Only - Request Edit Access

There is no way to prevent users from not being able to do this within the document’s share options. Likewise, my organisation may want to allow requests inside the domain (for example name@yagisanatode.com is okay, but name@gmail.com is not).

The Solution – Gmail Filters

Continue reading “How to stop getting Google document requests to edit from users outside your organisation from publicly viewable files.”

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

Google Sheets: Intermediate Chart Building, workflow

This is Part 3 of this Google Sheets Intermediate Project Course. However, this part of the course can also stand on its own.  If you are looking for part one or two, you can get it here:

Part 1 || Part 2

In this tutorial, we will dive into how to build charts. We’ll look at how we prepared our data for our chart. We’ll then cover some more detailed ground so that we can get our chart just right.

We will create a chart displaying the day change of two USD-XRP currency gateway agencies Bistamp and Gatehub.

The data we will be using to build our chart can be found here:

XRP Google Sheet

Contents

Back to Top

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

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

Google Sheets: ARRAYFORMULA, IFERROR, SPARKLINE, Conditional formatting, workflow.

This is Part 2 of this Google Sheets Intermediate Project. If you are looking for part one, you can get it here:

This project compares two currency exchanges rates, USD-XRP, in two different exchange gateways Bitstamp and Gatehub.

Contents

Back to Top

Recap

In the first part of our tutorial, you made a copy of the raw Exchange Volume data. Then in a new Google Sheet tab called Tutorialyou made a column of UNIQUE Date Time Stamps.

Realising that this was not user-friendly you grabbed just the date data from Column A and put it into Column B using the LEFT function. You then used some conditional formatting to highlight every Sunday automatically.

Next, you added two columns to display the daily exchange rates for the two gateways using FILTER. You then used two colours to identify which gateway had the highest or lowest exchange rate for that day.

You then grabbed the average of the two gateways before displaying their percentage difference.

By this stage everything looks like this:

Google sheets - Percentage Difference with Symbol
Tutorial – Google Sheet

Back to Top

Stage 2

In this tutorial, we are going to make it even easier for our reader, by setting up a column to display the gateway name of the highest exchange for each day. We’ll then put in a block of columns that will display data about the day change of the average of the two gateways. Finally, we will add a SPARKLINE graph into each row so the user can, at a glance, see how the average exchange has been tracking for the past 7 days.

By the time you are done your chart should look like this:

Google Sheets XRP Daily Exchange Rate
Back to Top

Highest Exchange Rate Gateway

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

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”

My live Google Form response in Google Sheets won’t update in another Sheet Tab that references them. What do I do?

Google Sheets – Intermediate, Arrays, Form Data

Picture this. You have set up your Google Sheet and attached your Google Form data to it so you might get a tab that looks a little something like this:

Form Response Tab Google Sheets

You probably don’t want to mess with this tab because the Form is still live.

Instead, you decide to create a new Google Sheet tab that you want to automatically transfer all the data into, including the current form response data and any new form responses you might get.

The Common Mistake

A common, though mistaken, approach to this is to do a cell-by-cell transfer of data. For example, we would grab the first data cell of our “Form responses 1” Sheet tab and in a new sheet tab cell we would write:

We would then drag that cell across to the right to cover all the columns. Then all the way down to the bottom of the page to cover the current responses and any new responses added.

That might look a little like this:

Cell-by-cell display of data from another Sheet Tab

The problem

This looks like it might work, right? Let’s test it out by adding in a new form response. For me, it will be the 6th response and will appear on row 7 of the ‘Form  Responses 1’ sheet tab.

Continue reading “My live Google Form response in Google Sheets won’t update in another Sheet Tab that references them. What do I do?”

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”

GSuite Group Email Reply Settings: How to reply to the group or just the sender of the group in Gmail

Gsuite: Groups for Business, Gmail

Note: This post is for those who have Gsuite admin permissions. 

GSuite groups are a great bulk email tool. They can be used:

  • To send announcements to a group.
  • As a collaborative email for teams to communicate with each other.
  • As an internal or public facing support email.
  • To send emails and notifications to groups that may require a response only to the sender.

Surprisingly, all these reasons for using group emails all have different priorities and expectations for receiving and replying to emails.

Continue reading “GSuite Group Email Reply Settings: How to reply to the group or just the sender of the group in Gmail”

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”