Month: April 2017

Make & Send Kids Reports

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 spreadsheet and as its a Google Sheet will be able to do it at the same time as each other, using drop-down menus to make it quick and easy to do. The code will convert this information into individual PDF reports, which will be stored on our Drive and then sent via email directly to the parents. The process is completely paperless and free! Setting up the spreadsheet In one sheet, I’ve set up the columns as below: We have two classes with a few students in each. Then we have the areas we want to report to the parents. In the blue part, their language skills and knowledge; in the pink part their behaviour; their attendance, exam marks, and the recommendation for the following year. In the final column, we will add the links to the individual PDFs. Create a second sheet and call it “Options”. This is where we’re going to store the options for some drop-down menus on the main sheet. First, let’s set up the drop-down menus for the language feedback. Select the cells where you...

Read More

Apps Script Basics (5) – Arrays, Logger, Execution Transcript

In this post, we’re going to look at another key area in Apps Script and indeed many coding languages, that of arrays, which are just special variables which allow you to store multiple items in a single variable. I’ll also introduce you to the logger, where you can store see what’s happening in your code without for example, having to print it on a sheet. Plus, the execution transcript, which shows you the steps your code is taking and how long they take. Introducing the logger Following on from my post on loops, let’s take a look at some looped data in the logger. Here we have some data in a sheet. We’re going to loop down the data and put it one by one into the logger. With all the examples in this post, we’re going to get the active sheet, so line 3, you’ll see repeated for each of the functions below. You’ll use this line repeatedly for a lot of programs you write, where you’re working with a spreadsheet and the current sheet. Now we want to loop down the list on the sheet. Line 5: Here we know we have 20 numbers, so we set the loop to start at row 1 and finish at row 20 (<21). Here I’ve used the variable r as the counter. Line 6: Then we get the cell at that point...

Read More

Apps Script – Issues reporting form, log & email

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 uses Google Translate to automatically translate the issue before emailing the relevant people. Setting up the form and sheet I will assume you know how to create Google Forms and link them to Google Sheets. If don’t then check out my posts on Google Forms. I’ve created a simple form for the teacher to fill out. They fill out the problem, whether it’s an IT or maintenance issue, their name, and choose one of the rooms from the drop down menu. So, nice and quick to fill out. Then link that with a Google Sheet and you should then have the questions in that sheet, as below (on the sheet called “Form responses 1”): Then, add 3 more columns on the end and label them, Translation, Comments, and Status. Plus, I’ve added a bit of formatting. Then, add a second sheet and rename it “Emails”. Add in the first column, the emails of those who will receive a maintenance issue and in the second column, those who will receive an IT issue. The code Now, we need to add...

Read More

Apps Script Basics (4) – Loops

Here, 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. For example, if we want to print the word Hello! a number of times down a column without a loop, we would have to repeat the same line of code for each of the rows, as you can in the code below: The only thing changing each time is the row reference in the getrange() method. Imagine if we wanted to write this 100 times, that’s a lot of code! With a loop, as we’ll see in the examples below, we can simplify this to just 1 loop and 3 lines of code. A loop just allows you to repeat a section of code as many times as you like. Below, we’re going to look at seven simple examples of how loops can be used. In the spreadsheet, there are 2 sheets, one called Numbers and the other called Names. Loop 1 – Print “Hello!” 20 times down column A Open the script editor in your Google Sheet and replace the default code with the above one. Line 2: We start with the function and here let’s just call it loop1. Line 3: Now we want to get the sheet called Numbers. So, we use the SpreadsheetApp class, then get the active spreadsheet (i.e. the...

Read More

Forms & Sheets – Attendance System

In this post we’re going to make an attendance system using Google Forms and Sheets. The user will fill in the attendance on the form (possibly on their mobile) and the data will then be stored and organised automatically per student. This will use a Form add-on called FormRanger and will use a range of Sheet functions to do organise the data for you. We will make an attendance sheet like this: And an attendance summary like this: Setting up the form Create a new form. The form itself is very basic and just includes the date of the class and a question where we’re going to populate the class list. Make the date question a required one, just to make sure it’s always entered. Click on the settings cog and under General, the only option I select is “Edit after submit”. This can be useful if a student arrives a bit late and I’ve already done the attendance. It will allow me to go back to the class list and update it. Under Presentation you may want to tick the Show link to submit another response, if you do, the attendance of more than one date at a time. Change the default confirmation message. Then we need to link the form to a Sheet. Click on Responses and the green sheet icon to create a spreadsheet. By default, it’ll create a...

Read More


eBooks available

Pin It on Pinterest