Month: March 2017

Apps Script Basics (3) – Variables and getting & setting values

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 simple example, where we have some data about three different students and we want to put all that data together in one single table in a different part of a sheet. Then, we’re going to format it a little bit to make it look better. So, we will be getting data from these columns: And writing it into these columns, all with Apps Script. So first, what is a variable? It’s basically a container that stores information. You give it a name and state what’s in the container. All variables start with the keyword var. Then followed by the name for that variable. Apart from using keywords, you can call your variable pretty much anything you like. Above, we have two examples of variables. The first is variable called studentsName and we have assigned the name John to that variable. So, every time we refer to studentsName we are referring to the name John. The same goes for numbers. The second variable is called numberOfStudents and we have stated that the number of students is 10. Note, that text (or a...

Read More

Questionnaires – Automatically send feedback to individuals

It’s common to use questionnaires at the end of a course to get feedback on the course and teacher. When you have a lot of teachers, it can be hard work analysing that feedback and splitting into the different teachers. Here we’re going to look at how we can automatically send the individual feedback to each teacher, as soon as its completed by the student. Plus, retain a master feedback sheet with all the teachers on it for the head of department’s use. There are 4 documents we’re going to create: Questionnaire form Questionnaire master responses sheet Individual feedback sheet per teacher Questionnaire links sheet Here’s how they link together: Those who have been following my posts will see that this one combines various previous posts into one. Namely, the use of the QUERY and IMPORTRANGE functions, setting up forms, and creating short URLs. Setting up the Form Let’s start with the form. Create a new form and add the questions you want the student to answer. An example of one is below: Then connect that form to a spreadsheet to store the responses. Click on “Responses”, then the green spreadsheet icon. We want a new spreadsheet, so just click “Create”. This populates the Sheet with our questions. Setting up the master feedback sheet We have a sheet called “Form Responses 1” where all the form responses will be stored,...

Read More

Apps Script Basics (2) – First Docs and Forms Scripts

Following on from my last post on creating your first script, here we’re going to edit a Google Doc and also a Google Form. This will show you how you can access Docs and Forms, not just Sheets with Apps Script and it will introduce you to how it’s possible to add and edit the text in those documents. First Google Docs Script In this little script, we’re going to write some text in a Google Doc. To open the Script editor in Docs, click on “Tools” from the menu and then “Script editor”: We leave the function that is already written in there and in between the curly brackets, we type DocumentApp. Like the SpreadsheetApp in the previous post, this is telling the computer which class to access, in other words, whether it is a Google Doc, Google Sheet, etc. Then add a dot and various possible options will appear in the menu. First, we want to get the current active document. So, scroll down and you’ll see the method called getActiveDocument(). Click on it to add it. Add a dot again and a new menu will appear. Now we have the document we want to get the document’s body. So, scroll down until you see the method getBody() then select it. Add a dot and another menu will appear. Now, we want to add some text into the body of the document....

Read More

Apps Script – Creating & sharing Class folders in Drive

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 there is a little bit of flexibility depending on what folders you need, but of course the beauty of having the script is that you can always tinker with it to meet your exact needs. It will create a class folder in My Drive, a folder for each student, and if you want a separate classwork folder and a separate homework folder. It will share the class, classwork, and homework folders with all the students, but only share the individual folders with the specific students. Class Folder Creator sheet Here’s the Sheet that we will use to fill out the information required and to run from the script from. At the top, you add the class name, and then Y or N (yes or no), as to whether you want a classwork folder or homework folder or not. Underneath you add the students’ names and email addresses. These could easily be pasted in if you already have a list. The code Here are the main steps of the code: The code will get the details entered on the Sheet and...

Read More

Apps Script Basics (1) – First Script

Here I want to show you how easy it is to write some code using Apps Script. This is the code that sits in the background of some of the Google Apps. This post is for those taking their first steps into writing code, so I will explain everything step-by-step. First Google Sheets Script First, you’ll need to open the Script editor. This can be found in various places, but here we’re going to be using a spreadsheet, so we’ll open the one connected to that. Let’s dive straight in and write our first bit of code. We’ll start off in a Sheet. So, open a new Sheet from Drive. Then from the “Tools” menu, select the Script editor. Open the Script editor and you’ll be presented with a screen like this: The main white part of the page is where the script is written. By default, it already adds a bit of code in there for you. In a later post, I’ll explain the editor a bit more, but for now you’ll just need to type in the code, save it and then run it. Let’s go through it step-by-step. We’ll leave what’s already on the page, as all programs need a function to run the code. A function is just a way of grouping a bunch of code together, then by giving it a name will let the computer know...

Read More
  • 1
  • 2


eBooks available

Pin It on Pinterest