Google Sheets, Google Apps Script: SpreadsheetApp, PropertiesService, Binary Search, onOpen, Button
Ever had to open a huge data entry spreadsheet an all you want to do is enter your data and move onto another task? Instead, you have to waste precious time navigating all the way down to the bottom of the page to add your data.
Wouldn’t it be cool to just jump down to the first empty row and get clicky-clacking away?
Behold, dear biped! This friendly coding goat has a little gift for you.
In this tutorial, we are going to tackle two ways to automatically move down to the first available empty cell. One will be super easy and the other slightly more tricky, fun and versatile (You can thank one of the Google Apps Script outreach gurus for the inspiration for this one).
We’ll show you how to set up this little code so it can be run via a button at the top of your page or when you open the Google Sheet workbook each time.
Note! As always feel free to read the entire tutorial or take what you need. There is a quick start guide for those people who just want to copy and paste into their own projects and a deep dive for those who want to understand the code.
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:
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.
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:
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.
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.
Google Apps Script, G Suite Admins, Google Calendar, Calendar API, AdminDirectory, GroupsApp
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:
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
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.
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.
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).
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.
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 firstname.lastname@example.org is okay, but email@example.com is not).
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.
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.
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.
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).
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 Tutorial, you made a column of UNIQUE Date Time Stamps.
Realising that this was not user-friendly you grabbed just the date data from Column Aand put it into Column Busing 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.
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:
In this tutorial, we are going to tackle some Intermediate level Google Sheets concepts including using functions like:
MIN and MAX
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.