Learn Google APPS SCRIPT

In this final post on the 2023 improvements to Google Workspace in 2023, let’s look at the AI and coding side of things. Duet AI Google weren’t content with just bringing out Bard to compete in the AI ‘arms race’ last year, they also brought out Duet AI. This uses… Continue reading Summary of Google Workspace 2023 improvements (Part 3)

In this post, we’re going to look at how we can easily combine arrays of data on separate sheets in a Google Sheet and add them on one single sheet. We’re going to use the relatively new spread operator (…) to do this. Situation In this example, we have a… Continue reading How to combine arrays of data into 1 sheet

It’s almost World Cup time and I thought what better way to get into it than to create a little World Cup game amongst friends and colleagues using Google Sheets, Sites, and Apps Script. This year I decided to create a game where the participants guess how many goals will… Continue reading Learn how to make a World Cup game using Apps Script

In this post, we’re going to see how to split Google Slides automatically into separate files with Apps Script. Problem and solution The reason I wrote this script was to resolve a problem at work, where we have an admin system that produces a document with the certificates for the… Continue reading How to split Google Slides automatically

In this post, we’re going to look at a script to be able to make an automatic team maker which makes even teams from a selection of players. I play football every Sunday and every week my friends sign up to play and most weeks there are different players playing,… Continue reading How to make an automatic team maker app using Apps Script

In this post, we’re going to look at template literals. What can you do with them? Uses of template literals Mixing single and double quotes First of all, these live in between back-ticks (`) instead of single or double quotes. e.g. This then means we don’t have to worry about… Continue reading JavaScript – Template Literals

Here, we’re going to see how we can automatically format rows on a Google Sheet when a Google Form response is submitted. When the form responses come in normally, they have the default format below. But I want them to be formatted as below. So, changing the cell backgrounds to… Continue reading Automatically format rows for Google Form responses

In this post, we’re going to see how we can very quickly format multiple Google Sheets with a little help from Apps Script. The idea of this post, came from the Department of Education in Hawaii, where they have about 300 schools and a guy who works there told me… Continue reading Quickly format multiple Google Sheets with Apps Script

In this post we’re going to look at how we can get data from an Excel spreadsheet and import some of it into a Google Sheet. As an example, we’re going to upload an Excel which contains the current month’s royalty data from Amazon and add it to a central… Continue reading Importing Amazon royalty data from Excel to Google Sheet

In this post, we’re going to see how we can use Google Calendar to record the number of hours we’re working to make sure not overworking! On my Google Calendar I add events to show the periods I’m working (in this case on admin). A script will then get these… Continue reading Calculate hours worked from Google Calendar

In this post, we’re going to look at how we can remove duplicates from a list on Google Sheets and also store those duplicate rows on a separate sheet. Google Sheets has a remove duplicates feature but it only removes the duplicates and sometimes you also want to have a… Continue reading Remove and separate duplicates in Google Sheets

What’s different in Google Workspace to a free Gmail account? This post will show you some of the differences between a paid Google Workspace account and a free personal Gmail account and quite often Google Workspace users aren’t aware of some of the extra features Workspace offers, so let’s have… Continue reading What’s the difference between a Google Workspace and a free Gmail account?

Apps Script libraries In this post we’re going to look at how we can connect an Apps Script project to a library file. So, first, why would we want to do that? You want to connect to an external project, which you haven’t written. There’s an example of this in… Continue reading Apps Script – How to deploy a library

In this post, we’re going to look at how we can quickly make short bit.ly links for pre-filled Google Forms using Google Sheets and a bit of Apps Script. Bit.ly links are great but if you have to make a lot of them in can be a slow, tedious process.… Continue reading How to quickly make short pre-filled URLs

In this post we’re going to look at how to set up a trigger in Apps Script and look at some of the options available. So, first of all, what’s a trigger? A trigger automatically calls a function in the script project based on some kind of event. This could… Continue reading Apps Script: How to automate a script

In this post, I’ll show you how to authorize an Apps Script project. This could be a project that you’ve written yourself or it could be a program someone else has written and something like Google Sheets has displayed a pop up window asking you to authorize the code. The… Continue reading How to authorize an Apps Script project

In this post, we’re going to look at creating a simple mail merge program, which will use a draft email as the basis of the final email that will be sent out. The idea is to have a program which is flexible enough to send different messages out and that… Continue reading Mail merge using draft emails

In this post, we’re going to look at a simple issues reporting system, where the teacher fills out a Google Form to report the issue. This is stored in a Google Sheet, and an email with the summary of the issue is either sent to the maintenance person or IT… Continue reading Issues reporting with translation

Making multiple copies of folders in Google Drive is a slow, boring task. You select the folder you want, make a copy, then have to rename it, then repeat this again and again. Here you’ll learn how to create multiple folders and name them individually, really fast using Apps Script. If you’ve… Continue reading Multiple FOLDER maker

Making multiple copies in Google Drive is a slow, boring task. You select the file you want, make a copy, then have to rename it, then repeat this again and again. Here you’ll learn how to make multiple copies of a file and name them individually, really fast using Apps… Continue reading Multiple FILE Maker

One popular method to get your students to learn is to categorise things or to put them in order. Here, we’re going to look at making a card sorting game, which could be used for both. This is the web app we’re going to make: The code There are two… Continue reading Card sorting game using Google Sheets & Apps Script

This post continues from the previous post on the Range class and triggers with more examples of how to use it. changing the background colour based on edits to a range sorting a table by multiple columns copying part of a range and creating a new sheet with that range Example… Continue reading Apps Script Basics – Range class & triggers (pt 2)

With all our classes moving from 99% face-to-face to 100% online, we had the situation where we needed to carry out all our exams online too. A relatively easy solution was to use Google Forms, which was easy for students to fill out and for us to receive the answers.… Continue reading Online exam maker using Apps Script

Moving to teaching on line has presented some new challenges and one of those has been how to convert some great classroom activities so they can be done on line. One of those is a simple card game where in our foreign language classes, we help students learn new language… Continue reading Ladder card game using Google Sheets

Here we’re going to make a simple ladder card game using Apps Script and Google Sheets. In our foreign language classes, one of the classic activities we do to help students learn new language, is to print phrases onto card, then cut the cards up. We then write the answers… Continue reading How to make a ladder card game with Apps Script

In this final part of a 3-part post (see part 1 and part 2), we’re going to look at how we get the parents form submissions, send them a confirmation email with the appointment details, and record the appointment on the appointment list for the teacher. The main steps are:… Continue reading Parents Evening Appointment System – Part 3

This is part 2 of a 3-part post on setting up a parents evening appointment system. Part 1 looked at setting up the documents and in particular the pre-filled form links. Here, we’re going to look at the code which will create the forms for each class and which will… Continue reading Parents Evening Appointment System – Part 2

This is the first of a three-part post, where we’re going to look at how you can create an appointment system using Google Forms and Sheets and with the use of Apps Script, how you it will update the available times on the forms and how it will send automate confirmation emails… Continue reading Parents Evening Appointment System – Part 1

In this post we’re going to look at how you can set up a Google Forms quiz with Apps Script. As an example, I’m going to take you through the example that was posted on the G Suite Developers blog here. I’ve tweaked it a little just to create a… Continue reading Apps Script Basics – Making quizzes in Google Forms

In this post, we’re going to expand on my previous post on setting up form validation and improve the clocking in and out form we set up, so that the same form can be used for different employees. We’re going to use this example to see how page navigation can… Continue reading Apps Script Basics – Form page navigation

In this post, we’re going to look at automatically setting up validation on a form. Validation allows us to control what the user inputs on the form, for example, to make sure they enter a number, make sure they write more than 10 words, etc. Clocking in and out form… Continue reading Apps Script Basics – Form validation

In this post, we’re going to look at how we can work with the responses a form user submits. We’ll look at two main ways, 1) Getting the form responses from a Google Sheet, 2) Getting the form responses directly from a Google Form. To show some practical uses of… Continue reading Apps Script Basics – Using Form Responses

Following on from my previous post on creating and updating Google Forms, here we’ll look at adding and updating multiple questions to a form. Creating multiple questions in a Google Form Here in a Google Sheet, I have a set of reading comprehension questions, which I want to add to… Continue reading Apps Script Basics – Creating & updating a multiple question form

In this post, we’re going to look at some of the ways you can control the cells on your spreadsheet, using the methods connected to the Range class. At the time of writing, there were nearly 200 different methods connected to this class. So, as you can imagine there are… Continue reading Apps Script Basics – Range Class & Triggers (pt 1)

In this post, we’re going to look at how we can get the computer to react to data in a spreadsheet and to user input. We’re going to look at the following: If, else if, and else statements – to allow the program to make decisions Create your own menu… Continue reading Apps Script Basics – if, prompt, menu, & onOpen trigger

In this post, we’re going to look how we can create kids reports from a Google Sheet and convert them into individual PDFs made from Google Docs, then email them to the parents. This could of course be adapted to send any types of reports. The teachers will fill in the… Continue reading Make & Send Kids Reports

In this post, we’re going to look at another key area in JavaScript and Apps Script and indeed in many coding languages, that of arrays, which are just special variables which allow you to store multiple elements in a single variable. I’ll also introduce you to the Execution log, where… Continue reading Apps Script Basics – Arrays, Execution log

In this post, we’ll look at a way to create a quick and simple system to report maintenance and IT issues in the classroom. The teacher fills out a Google Form on their phone, this gets logged on a Google Sheet, and as we work in a multilingual environment, it… Continue reading Apps Script – Issues reporting form, log & email

In this post, we’re going to look at loops and how they can make repetitive tasks really easy and how they can save you so much coding. One of the things computers are good at is doing repetitive tasks quickly and accurately. For example, if we want to print the word Hello! a… Continue reading Apps Script Basics – Loops

In this post, we’re going look at how variables are set up and how they can store various pieces of information. We’re also going to look at reading and writing data from a spreadsheet, which is one of the most common tasks when working with one. I’m going to use a… Continue reading Apps Script Basics – Variables and getting & setting values

Teachers using Drive often need to set up folders for their class and for their individual students. Doing it in Drive is not the most exciting job to do. So, to make your life easier, here’s a script, which will set up your folders for you. I’ve made it so… Continue reading Apps Script – Creating & sharing Class folders in Drive

In this post, we’ll look at setting up a simple clocking in and out system using Google Forms and Sheets. This idea originally came from a real life situation where we had to implement a clocking in and out system to meet a change in Spanish law. With our teachers… Continue reading Apps Script – Clocking in & out system

Here we’re going to make a simple book inventory, where we’ll be able to control the location of the books and also find out where a book is. This uses a mixture of GAS code and Sheets functions. We’ve got 3 sheets: Front page – This is what the user… Continue reading Create a Book inventory with Google Sheets & Apps Script

Here we’ll look at how to set up automatic emails, which contain information submitted by the Google Form user. The beauty of this is that the information is sent to you (and others) without you having to do anything and without you having to check the spreadsheet to see if… Continue reading Automatically emailing info from a form submission

One of the most useful things I’ve learnt to do with Google Apps Script, is to email people automatically when a form is submitted. It has countless uses and in this example, we have a user requesting a private class via a Google Form. The relevant parties will receive an email… Continue reading Request form – Sending automatic emails