Month: January 2017

Request form – Sending automatic emails

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 which will contain a short message and a link to the sheet containing the details. There are three parts to this: Setting up the Form (inc Email collecting and Data validation) Setting up the Sheet Writing the code Part 1 – Setting up the Form to record details of class request Open the Sheet and select Tools>Create a form, so that it is automatically linked to this spreadsheet. To set up a question to collect the user’s email. Click on “Settings”. Click “Collect email address. If you want the user to receive a copy of their request, then click “Response receipts”. You then have a choose of always sending them a copy or letting them choose this option in the Form. Here I always want them to receive it. Click “Save”. Back in the Questions part, you can see it’s automatically created a question which will check for a valid email. Then I add a telephone question with a short answer. Here I want it to check the user has only entered numbers. Click on the 3 dots on the bottom-right...

Read More

Google Sheets Functions – QUERY

The QUERY function is in a category all on its own. It’s an extremely powerful function that will let you filter, sort, group, pivot, basically extract data from a table and present it in numerous ways. At first it can look daunting, with its own language and syntax, but once you dip your toe into the QUERY pool you’ll realise that things are not so complicated and that with just one function, you can extract and analyse your data with ease. As always the best way to learn how to use it is through examples, and in this post we’re going to use two main sources of data, some questionnaire feedback, and some data from a HR department, building the complexity up step by step. Analysing questionnaire feedback Here we’ve used a Google Form to collect feedback on the teachers, the classrooms, and admin information at the end of every course in an academy. Each row is a student’s piece of feedback. They grade the various criteria from 1 to 5, 5 being ‘excellent’. Below is a snippet of that table of data. In the next few examples, we’ll see how easy it is to analyse this data, each time with just one QUERY function. Example 1a – Selecting the relevant data from the master data The head of studies wants to look at the feedback for her teachers, and...

Read More

Google Sheets Functions – INDEX and MATCH (VLOOKUP alternative)

In a previous post, we looked at how we can quickly look up tables for certain information, using the VLOOKUP function. This function is great but it does have some limitations. Firstly, when you look up information in the table you always have to look to the right for the matching information. Secondly, if you add a new column to the table, this messes up the references and it returns the wrong information. This is where the functions INDEX and MATCH come in. With a combination of these two, you can look either left or right in a table, and it adapts to any added columns. INDEX First, let’s see how the INDEX function works. Here we have 2 columns of data (columns A and B), we can pick out a certain cell’s data by referring to its position in the table of data. First we state the table range (A1:B3), then state the row number we want (3), then the column number (2). This returns the value in the cell (300). INDEX and MATCH Example 1 – Finding the classroom a specific teacher is in Usually we don’t already know the exact row and column we want, and this is where MATCH comes in. Here we have a table which continues a list of teachers and which classroom they are in. I want to be able to type in...

Read More


eBooks available

Pin It on Pinterest