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:

Questionnaire - 42

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:

Questionnaire - 1

Questionnaire - 2

Questionnaire - 3

Then connect that form to a spreadsheet to store the responses. Click on “Responses”, then the green spreadsheet icon.

Questionnaire - 4

We want a new spreadsheet, so just click “Create”.

Questionnaire - 5

This populates the Sheet with our questions.

Questionnaire - 6


Setting up the master feedback sheet

We have a sheet called “Form Responses 1” where all the form responses will be stored, and now we need a sheet for each teacher, where we’re going to store the feedback of that particular teacher.

Add a new sheet and type the name of the first teacher in cell A1.

Questionnaire - 7

In cell A2 we’re going to add a QUERY function, which will filter the feedback on the Form Responses 1 sheet and will display only the feedback for Barrie Roberts. Here’s the formula. The QUERY function is explained in detail in my post on the QUERY function.

Questionnaire - 9

In summary, it looks at the Form Responses 1 sheet, gets all the responses, looks for ones where in column E it matches the name in cell A1 (i.e. Barrie Roberts), then sorts it by date (column A), with the most recent at the top. With no responses, you’ll get the error message “#N/A”, which will disappear as soon as you start receiving responses.

Here, I’ve named the sheet “Barrie”.

Questionnaire - 8

To just test it, I’ve filled out the form with a response for Barrie Roberts and on the sheet “Barrie” the feedback automatically appears. I normally use this as an opportunity to format the sheet a little, e.g. change the column widths, etc. You can always delete this test responses from the form responses 1 page.

Questionnaire - 37

Then duplicate this sheet for every other teacher you have. The only things you will need to change is the name in cell A1 and the sheet name.


Setting up the Individual feedback sheet

The spreadsheet above has everyone’s feedback on it and usually we won’t want to share that with all the teachers. So, let’s set up the teacher’s individual feedback sheet.

First, create a new spreadsheet. Then we’re going to copy one of the teacher’s sheets to the new file. We do this, so that the formatting is the exactly the same in the new sheet, saving us some time and ensuring they look the same. So, from the master spreadsheet, click on a teacher’s sheet and select “Copy to”.

Questionnaire - 11

Then, click on “Recent” and select the new spreadsheet you created. Here, I had already called it “Questionnaires – Barrie Roberts”.

Questionnaire - 12

The sheet will appear and by called “Copy of…”. Delete Sheet 1 as it’s not needed and rename the ‘Copy of’ sheet.

Questionnaire - 17

Now, we will need to delete the name and QUERY function from cells A1 and A2.

Questionnaire - 13

Then add an IMPORTRANGE function in cell A1. This will look at the master sheet, and import the information from the sheet and cells specified into the individual sheet. For more information on how the IMPORTRANGE function works, see my IMPORTRANGE post.

In the brackets and between quote marks, I add the URL of the master sheet, add the sheet called Barrie, and state the cell reference I want. Note, I’ve used an open-ended one, so that it will important all the data from the sheet, no matter how many rows there are.

Questionnaire - 14

We then need to authorize the connection between the 2 sheets. Just hover over cell A1 and click “Allow access”.

Questionnaire - 15

As we can see, it’s imported the same information from the master sheet and will update automatically as more form responses are received in the master sheet.

Questionnaire - 38


Setting up the Questionnaire links

The last thing to do is to set up the links to the questionnaire. To aid analysis afterwards and for the whole system to work automatically, it’s essential that the information is in a specific format.

The most important thing in this example, is that the teacher’s name is always the same, i.e. “Barrie Roberts”. As, if we left it up to the students to type in the teacher’s name, we could get all sorts of variations, like Mr Roberts, B Roberts, etc. Then the QUERY function wouldn’t work. In our academy we also want feedback on things like the rooms, so, we also make sure we control the format of those.

To do this, we just create a sheet where we add the information about each class, then use that to create a pre-filled link, which will be shared with the students. In this example, that pre-filled form will include the class name, level and the teacher’s name. The good thing is that you only need to set this sheet up once.

Going from left to right, we add columns for the class, level, and teacher. Then a column for the short link we’re going to create. A column for where the full link we be created. In column F we’ll copy and paste the pre-filled link from the form. Then in columns G to I, we will take the infomation in columns A to C and automatically replace any spaces with “%20”, which will make sure the URL we finally create doesn’t have any spaces in it, which can break the link.

Questionnaire - 19

So, in cell G2 we add this substitute function. This will replace any spaces in cell A2 with %20. See my post on creating short URLs for more information.

Questionnaire - 20

We do the same for column H and I, for the level and teacher.

Questionnaire - 21

Questionnaire - 22

So, adding the class, level, and teacher to our sheet, we can see that in columns G to I, its replacing the spaces with the %20 character.

Questionnaire - 39

Now we need our pre-filled link from the Form. Open the form and click on the 3 dot menu and then “Get pre-filled link”.

Questionnaire - 23

Then complete the fields you want to pre-fill with placeholders. Here, I’ve just used the same name as the questions, so CLASS, LEVEL, and TEACHER.

Questionnaire - 25

Click Submit and at the top of the page, a pre-filled link will appear. Click on that and press ctrl+C to copy it.

Questionnaire - 26

Then paste it in cell F2.

Questionnaire - 24

Finally, in cell E2, we need to replace the placeholders with the information on our sheet in that particular row. To do this, we use the SUBSTITUTE function. This is looking at the URL in F2 and replacing the word CLASS with the contents of cell G2 (the class name), replacing LEVEL with cell H2 (the level), and TEACHER with I2 (the teacher’s name).

Questionnaire - 27

In cell E2, we now have a link which we could share with the students, which will open the form and already have the class, level, and teacher pre-filled out.

Questionnaire - 40


URL shortener

This next part is optional, but the one problem with the link we’ve created above is that it is very long. So, I usually create a short URL from that, which is much easier to share and use. The following is explained in more detail in my post on creating short URLs.

The code here is a little different from my earlier post on creating short URLs, in that you don’t need to select the range of URLs with this code, it automatically creates short URLs for all the full URLs in column E.

Add this code in the script editor.

Questionnaire - 35 (1)

To be able to run it you’ll have to activate the UrlShortener service. Click on Resources, then Advanced Google services.

Questionnaire - 30

Scroll down and turn UrlShortener on. Then click on Google API console at the bottom in blue.

Questionnaire - 31

In the bottom right corner you’ll see URL Shortener API. Click on that.

Questionnaire - 32

Then click Enable.

Questionnaire - 33

Click the arrow back and click OK.

Questionnaire - 34

The code is now ready to be run. Click Shorten URL and Shorten.

Questionnaire - 29

The first time you run it you’ll have to accept the usual authorisation checks, just click the blue buttons.

Then your short link will appear. This is what you would share with the students.

Questionnaire - 36

Clicking on the link will open the questionnaire:

Questionnaire - 28

As you can see, it has the fields we wanted pre-filled out.

I wouldn’t suggest going through all these steps just for just one class, as the sheet is set up to create links for numerous classes. Just copy down the cells E to I to as many rows as you like and the program will create URLs automatically for all of them based on the information you enter in columns A to C. So, you’ll end up with something like this:

Questionnaire - 41

Here are the links to make copies of the files above:

End of course questionnaire spreadsheet (the form is linked so will copy automatically)

https://docs.google.com/spreadsheets/d/1F4TXCUFBz5GREd8z8ilxDaSZpU6WKyqhY9-RK1NUrmI/copy

Individual questionnaire feedback spreadsheet

https://docs.google.com/spreadsheets/d/1ZnZ8S2_9oK6NNOE3ETgp0URI5EjM2_0D8VX6K4BrtVE/copy

Questionnaire links spreadsheet (including the short URL code above)

https://docs.google.com/spreadsheets/d/14bf_xG_cRfYOPj5Ku8ymLy-m3OZeXRYIZ-oZcHxhScQ/copy


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Google+Flipboard / Twitter)


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.

Class Folders - 3

Class Folders - 4

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.

Class Folders - 5

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 store them in the variables.
  • Then create the Class folder in My Drive.
  • Add the students as editors to that folder.
  • Check to see if you want a classwork folder or a homework one, and if so, create them.
  • Finally, it will create the individual student folders.

There are some similarities to the code I used in my post “Multiple folder maker“, so here, I will focus more on the new parts. Let’s look at it line by line:

Class Folders - 10

Lines 1 and 2: First we set up the function and get the Sheet.

Class Folders - 11

Line 5: We then get the sheet called “ClassFolders”.

Line 6: We get the class name we entered in cell B2. Here I’ve used the number of rows and columns, i.e. (2, 2). I could have in fact just used “B2” in the brackets.

Class Folders - 12

Line 9: Here we find out if the user wants a classwork folder or not by getting the value in cell B3, i.e. (3,2). We store it in the variable classwork.

Line 10: Similarly, we find out if the user wants a homework folder. We store this in the variable homework.

Class Folders - 13

Line 13: We get the row with the last student name and student email on it, which will the last row on the Sheet.

Line 14: Now we need to get the list of students. Here we start at row 6 and column 1 (A) and go down until the last row minus 5 (we don’t count the first 5 rows as we started on row 6), and we are just getting 1 column so we finish the range with a 1, then get the values in that range. We store these values in the variable studentsNames.

Class Folders - 14

Lines 17 and 18: We repeat the same for the students’ email addresses, which are in column 2 (B). We store these in the variable studentsEmails.

Class Folders - 15

Now we need to create the class folder.

Line 21: We’re going to create it in My Drive, so we use the DriveApp class followed by the createFolder() method.  Then we add the class name variable, className. Then we store this in the variable newFolder.

Line 22: We then get the ID of that new folder. Note, we could save a line of code here, by just adding .getId() on the end of line 21, like this:

Class Folders - 22

But, I’ll leave the steps in, so you can see what’s happening more clearly.

Line 23: Then we need to get that folder by its ID to be able to use it. We use getFolderById() to do so. Then we store it in the variable classFolder.

Class Folders - 16

Now, we add the students as editors of that folder. We do this by looping through the students’ emails and adding them one by one to the folder.

Line 26: We set up a simple for loop and continue it while its less than the number of email addresses (using the length of the array studentEmails).

Line 27: Each time we go around the loop, we want to add a student as an editor to the folder. We get the folder (classFolder) and use the addEditor() to add them. We use the variable s in square brackets to show where in the array we are. So, it will start in position 0 and continue to the end of the list.

Class Folders - 17

Now we want to see if the user wants to create a classwork folder and if so, create one in the class folder we recently created.

Line 31: We use an If statement to see if the classwork cell contains a “Y”.

Lines 32: This is similar to line 21, except here we’re going to create the folder not in My Drive but in the class folder. So, we get the classFolder variable and create the folder using that, and call it “Classwork”.

Class Folders - 18

Lines 36-38: We do exactly the same for the homework folder.

Class Folders - 19

Now we want to create the individual student folders. The folder creation is similar to before, except this time we will be looping down the list of student names previously stored in the variable studentsNames.

Line 41: We set up a for loop to go down the list of student names, using the i variable to track where we are in the list.

Line 43: We then create a folder in the class folder and name it with the current student name we are at in the loop. We do that by using studentsNames[i].

Lines 44-45: Then we get the ID of the student just created and get the folder by that ID.

Class Folders - 20

Now, we need to change the access rights for the student folders. As the student folders will be created in the class folder, they automatically assume the same access rights as the class folder. So, for each individual folder, we need to remove all the students except for the one who needs access to their personal folder. To do that we remove all the students as editors, then add the specific student.

Line 48: We set up a second loop, this time using the variable j to keep count. This will loop down the list of student emails.

Lines 49-50: Every time the loop goes around, it will remove a student email from being an editor of this folder. To do this we use the removeEditor() method and pass the studentsEmails variable to it. Then we close the j loop.

Lines 51-52: Then we need to add the student we want, who is the same as the student we created the folder for. So, we use addEditor() to add them and we use the i variable to identify which student it is. Then we close the i loop.

Class Folders - 21

Lines 55-56: Finally, I think it’s good to tell the user that the process has finished. So, here we display a toast message, using the toast method. Then in line 56 we close the function.

To run the code from a menu on the Sheet, I’ve also added this onOpen function (explained in previous posts):

Class Folders - 23


Running the code

Now let’s run an example with the information entered as below:

Class Folders - 4

Class Folders - 5

Class Folders - 24

Click on the “Folders” menu and then “Make & share class folders”.

The first time you’ll be asked to authorise the script, Just click the blue boxes.

Class Folders - 1

Class Folders - 2

This will take about 10 seconds and once you see the toast message, we will see the new folder called “Maths 101″in our My Drive.

Class Folders - 6

Double-clicking on that, we can see the sub folders we have created. There are the classwork, homework, and student ones.

Class Folders - 7

If we look at the access rights the class folder has (and also the Classwork and Homework have), we’ll see it’s been shared with everyone on my little list. Note, as I included myself in the list of “students”, I’m both a student and owner of the folder.

Class Folders - 9

Looking at the access rights of the “Baz Roberts” folder we’ll see it’s only been shared with me (Barrie Roberts-the owner) and the student “Baz Roberts”.

Class Folders - 8

The students get an automatic invite to the class folder and an invite to their individual folder.

Class Folders - 27


Hopefully, this will save you some time and effort. You can make a copy of the file and code here. In the same file, I’ve included this script, and the ones from previous posts: file maker and folder maker.

Here’s the full code:

Class Folders - 25Class Folders - 26


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Follow my Google+ Learning Google Apps Script Collection

Baz Roberts (Flipboard / Twitter / Google+)


Placement Test

Students who want to study a language at a language academy, have to do a placement test, which confirms their current level in that language and then tells the tester in which level they should be placed.

Here we’re going to see how a combination of Google Forms and Sheets, the functions VLOOKUP and ARRAYFORMULA and conditional formatting, can produce a simple but effective placement test.


SETTING UP THE FORM

First, we create the test using Forms. If you’re new to using Forms, then see my posts on how to use Google Forms.

In the test, I want the students to record some contact information and then below will be the test questions.

Here we have the fields to record their email address, name and telephone number, all of which are obligatory.

level-test-1

To set up the email address field so that it checks to see it’s a valid one, we go to the settings cog (on the right here).

level-test-2

Then tick “Collect email address”. Then click “Save”. This will add an email address field with data validation, which will check to see if an email has been entered. Note, that this always puts this field at the top of the form. Personally, I’d prefer to be able to move this, but at present that’s not possible.

level-test-3

Then we add the multiple-choice questions in our test.

level-test-4

I want the test to automatically correct this, so  click on the settings cog and this time select “Quizzes”. Then select the “Make this a quiz” option. Under “Release mark” choose “Later, after manual review”, so that the student doesn’t have the option of seeing their marks as soon as they’ve finished the test. I also untick all the options under “Respondent can see”.

level-test-5

Back in the questions part, we now need to tell the form,which questions are correct. Click on a question and then “Answer Key”.

level-test-6

Then click the correct answer. This will add a green tick next to it. Also, change the points to 1.

level-test-7

Repeat this for all the questions. Now we need to link the form to a Sheet. Under “Responses”, click the green Sheet icon.

level-test-8

We want to create a new spreadsheet, so leave the option selected and click “Create”.

level-test-9b


SETTING UP THE SHEET

This will open the Sheet and you will see the different fields from the Form on the sheet.

level-test-11

I usually format this a little and sometimes move the columns around to suit my needs, for example, like this:

level-test-12

Add a new page and let’s call it “Levels”. This is where we’re going to add the levels which correspond with the result from the test. In this example, we have 3 possible levels A1, A2, B1. In columns A and B and add the ranges for each of those levels. So, for example, if someone gets 6/10 in the test, the level they will go in is A2.

level-test-13

I’ve added a row (row 3) with just zeros, and nothing in the level column. This is important as we will see later, so that if there is nothing in the results column on the form responses sheet, nothing will be added to the levels column.

Now, let’s see if it works. The first time, I fill it out with the correct answers just to check everything is working ok. It also allows us to check future responses against this answer key, as we’ll see below.

level-test-14

As we can see the first response have been stored in the sheet fine.

level-test-15

Now we want the sheet to automatically add the appropriate level for the result. To do this we use two functions, VLOOKUP and ARRAYFORMULA. The VLOOKUP function, looks at the results in column B from B2 to the end of column B (it’s open-ended and will automatically continue as new rows are added), then looks up that result in the table on the Levels page in range A3:C6, and gets the level that’s stored in the 3rd column.

For more information on VLOOKUP see this post.

We add this to the ARRAYFORMULA function, which will automatically add this formula to all the rows below, so there’s no need to add the formulas for every row.

level-test-17

When we submit another test, as you can see the responses are added to the row below and the level is automatically added.

level-test-18

Note, that no level is added where there is no responses, this is because of the zeros line we added earlier in the levels table.


HIGHLIGHTING WHICH ANSWERS WERE CORRECT OR INCORRECT

Sometimes we also want to see which answers the students got right and wrong. We could do this back in the form, but usually we’re only working with the sheet, so it can be useful to highlight here.

Select the answers from the second response (row 3 (e.g. G3:P3).

level-test-19

Right-click and select Conditional formatting.

level-test-20

This will open the conditional format rules side bar. See my post on Conditional Formatting for more info.

level-test-21

First, let’s apply this formatting to all the columns from G to P. Currently we only have G3:P· selected.

level-test-23

Change the range to an open-ended one, so that the range will always go down to the last row on the sheet. This means even if more rows are added, all responses received will be formatted.

level-test-24

Click on the drop-down menu “Cell is not empty”. Then select “Custom formula is”.

level-test-22

Here, add the formula “=G3=G$2”. This will check if the answer in G3 is the same as G2 and if it is, it will fill the cell green. This will also check all the other cells in the range and will update the cell references accordingly. So, it will check H3 is the same as H2 and so on. The use of the dollar sign means in rows below, the answer row reference won’t change, e.g. it will check that G10 is the same as G2 and so on.

level-test-25

Now we need to add an extra rule to show the incorrect answers in red. Click on “add new rule”.

level-test-26

Here, we want to check if G3 is different from G2 and if so to fill the cell in red. So, we use the not equal symbols: <>

level-test-28

The problem with this is that as we are using an open-ended range, this will fill all the cells on the sheet in red that aren’t the same as the answer key.

level-test-29

So, we need to change the formula so that it checks to see if the answer is different AND if the row isn’t blank. We use the following AND function, which checks both conditions.

level-test-27

Now, it only highlights the rows with answers in them.

level-test-30


Here’s the link to make a copy of the Sheet and Form.


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Follow my Google+ Learning Google Apps Script Collection

Baz Roberts (Flipboard / Twitter / Google+)


Multiple FOLDER maker

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 already read my post “Multiple FILE maker”, then this code is essentially the same, just with two main differences, we use the methods getFolderbyId() and we use createFolder(). So, if you read it, you may want to skip through and just note the differences.

First, we have a Sheet where we will enter the details. There are three parts.

multiple-folder-maker-10

In cell A3 we type the fixed part of the folder name want, i.e. The part that is the same in all the newly-created folders.

multiple-folder-maker-11

In column B, we type the variable parts of the folder name, i.e. The parts we want to be unique in each folder.

multiple-folder-maker-12

Finally, in cell C3 we paste the full URL of the folder we want to put the new folders into.

multiple-folder-maker-13

We then choose ‘Make Multiple Folders’ from the Creator menu.

multiple-folder-maker-14

Here are the folders it made, all individually named:

multiple-folder-maker-15

Obviously, it works best when there are a lot of folders to make.


THE CODE

Let’s go through the code step-by-step:

multiple-folder-maker-2

Line 1: First, we set up our function, here I’ve called it makeFolders().

Line 4: Then, we get the current active spreadsheet, using getActiveSpreadsheet().

Line 5: Then, we get the active sheet, using getActiveSheet().

Line 6: Now, let’s get the fixed name. It’s in row 3, column 1 (I.e. A3), so in the getRange() method we add (3,1), then get its value using getValue().

Line 7: Now, we need the variable names. First, we need to get all the cells in the second column (B), from row 3 to the bottom row. We need to find out where the bottom row is, so here I’ve used getMaxRows() which gets the last row on the sheet, whether it has any values or not. I subtract 2, as we will be starting from row 3, so need to discount the first 2 rows.

Line 8: We use getRange() again, but this time we’re getting a series of values, so we need to tell it where to start and where to end. In the brackets we state: starting row, starting column, number of rows, number of columns. So, here we’re starting in row 3, column 2, down to the bottom row using the bottomRow variable, and we only want 1 column. Then we get all the values with getValues(), which will store them in the variableNames array variable.

Lines 10 to 22, are ways to check the user has entered information into the relevant cells. The program could work without them, but often it’s important to include some kind of checks to deal with users doing things that they shouldn’t, as invariably, they will always do something, that’s not expected or what in your mind wasn’t logical! Here I’m going to use some simple if statements to check that they’ve filled in the sheet correctly before running the program.

multiple-folder-maker-3

Line 11: First, I want to check that they’ve entered something into the fixed name cell. So, I’ve used an if statement to check if the variable fixedName is blank, using the double speech marks.

Line 12: If it is blank, I want it to show an alert message, telling the user it’s blank. To do so, we use getUI() and the type of message is an alert. Then in the brackets we add the text we want to display. Line 13 closes the if statement.

multiple-folder-maker-4

Line 16: Here I get the URL from cell C3 (row 3, column 3) and store it in the variable folderUrl.

Line 17: Then, I check if the variable folderUrl is empty.

Line 18: if it is empty, as before, I want to display an alert.

Now, I want to check that both fixedName AND fileUrl are NOT empty. If they aren’t empty it will run the rest of the code and make the copies, if one is empty, it won’t run the code. This means that if one of the alerts is shown above, it won’t run any code afterwards, making sure no copies are made by mistake.

multiple-folder-maker-5

Line 22: Here we first check if the fixedName isn’t equal to a blank. Then, use the double ampersands, which mean AND, to also check if the folderUrl isn’t blank. If they both aren’t empty it runs the following code.

multiple-folder-maker-6

Line 25: Now, we need the folder ID from the folder URL, in order to create folders in that folder. We use the match() method (more info here) and use some regex to extract just the ID part. Don’t worry about how this works exactly, but if you’re interested in the use of regular expressions, go to this page.

Line 27: Now, we use that ID stored in folderId, to get the folder by its ID, using the DriveApp class and the getFolderById() method, which basically, gets the folder we want to create folders in, then stores it in the variable getRootFolderId.

Now, we finally get to the point where we make the multiple folders. We’ll use a simple for loop for this. We want it to loop down column B making copies as it goes down, but then stop when it reaches an empty cell in that column, i.e.  The end of the list.

multiple-folder-maker-7

Line 31: I’ve set up a counter variable called n, and we’ll keep going down until we hit the bottom row.

Line 32: But to prevent it making files with no variable names, I’ve included an if statement to check to see if the current variable name is blank, using variableNames and the current array number in the square bracket. Remember this is looping down the array variable variableNames, so it’s going from zero to potentially the bottom row. If it does find a blank it breaks out of the loop, i.e. It doesn’t continue going through the array. Also, note the use of a double equals sign, not a triple one, as we are comparing an array value with something empty and they are not the same type, so a triple equals sign will fail.

Line 34: This is the line that creates the folders we want. We use the folder ID stored in the variable getRootFolderId we created in line 27. Then, use the createFolder() method and in the brackets state the name we want. Here, we’ll going to join the fixed name with the variable name). Line 35 closes the loop.

multiple-folder-maker-8b

Line 36: Finally, just to tell the user the process has finished, I’ve added a toast message, which will pop up in the bottom right-hand corner, when the program has finished. Here it frees to the active Spreadsheet ss, then in the brackets contains 3 parts: message, title, number of seconds it will be displayed.

Line 37 and 38: This closes the if statement from line 22, and the function.


Adding a menu

To run the program from a menu in the spreadsheet, I’ve also added a separate script file called onOpen, which will add a little menu to run the code.

Line 1: Call the function onOpen(), so that it opens automatically every time the spreadsheet is opened.

Line 2: Get the active spreadsheet, note I’ve left a comma at the end not a semi-colon, as the next 2 lines are connected to it.

Lines 3-4: Set up a variable called menuItems to add the menu details. The keyword var isn’t needed here as it using the one from line 2. Then we set up an array with the sub-menu name and the function it will run. Carefully note the syntax used here.

Line 5: Now, we add the menu to the spreadsheet using the addMenu() method to the active spreadsheet. In the brackets, we include 2 parts: menu name, and the variable menuItems.

multiple-folder-maker-9


Here’s the link to the Sheet and code.

Here’s the complete code:

multiple-folder-maker-1

The regular expression in line 25 is from here:

http://stackoverflow.com/questions/16840038/easiest-way-to-get-file-id-from-url-on-google-apps-script 


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Follow my Google+ Learning Google Apps Script Collection

Baz Roberts (Flipboard / Twitter / Google+)


Multiple FILE 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 Script.

First, we have a Sheet where we will enter the details. There are three parts.

multiple-file-maker-9b

In cell A3 we type the fixed part of the file name want, i.e. The part that is the same in all the copies of the file.

multiple-file-maker-10

In column B, we type the variable parts of the file name, i.e. The parts we want to be unique in each file.

multiple-file-maker-11

Finally, in cell C3 we paste the full URL of the file we want to copy.

multiple-file-maker-12

We then choose Make Multiple Files from the Creator menu.

multiple-file-maker-13

Here are the files it made, all individually named:

multiple-file-maker-15

Obviously, it works best when there are a lot of files to make.


THE CODE

Let’s go through the code step-by-step:

multiple-file-maker-2

Line 1: First, we set up our function, here I’ve called it makeFiles().

Line 4: Then, we get the current active spreadsheet, using getActiveSpreadsheet().

Line 5: Then, we get the active sheet, using getActiveSheet().

Line 6: Now, let’s get the fixed name. It’s in row 3, column 1 (I.e. A3), so in the getRange() method we add (3,1), then get its value using getValue().

Line 7: Now, we need the variable names. First, we need to get all the cells in the second column (B), from row 3 to the bottom row. We need to find out where the bottom row is, so here I’ve used getMaxRows() which gets the last row on the sheet, whether it has any values or not. I take away 2, as we will be starting from row 3, so need to discount the first 2 rows.

Line 8: We use getRange() again, but this time we’re getting a Series of values, so we need to tell it where to start and where to end. In the brackets we state: starting row, starting column, number of rows, number of columns. So, here we’re starting in row 3, column 2, down to the bottom row using the bottomRow variable, and we only want 1 column. Then we get all the values with getValues(), which will store them in the variableNames array variable.

Lines 10 to 22, are ways to check the user has entered information into the relevant cells. The program could work without them, but often it’s important to include some kind of checks to deal with users doing things that they shouldn’t, as invariably, they will always do something, that’s not expected or what in your mind wasn’t logical! Here I’m going to use some simple if statements to check that they’ve filled in the sheet correctly before running the program.

multiple-file-maker-3

Line 11: First, I want to check that they’ve entered something into the fixed name cell. So, I’ve used an if statement to check if the variable fixedName is blank, using the double speech marks.

Line 12: If it is blank, I want it to show an alert message, telling the user it’s blank. To do so, we use getUI() and the type of message is an alert. Then in the brackets we add the text we want to display. Line 13 closes the if statement.

multiple-file-maker-4

Line 16: Here I get the URL from cell C3 (row 3, column 3) and store it in the variable fileUrl.

Line 17: Then, I check if the variable fileUrl is empty.

Line 18: if it is empty, as before, I want to display an alert.

Now, I want to check that both fixedName AND fileUrl are NOT empty. If they aren’t empty it will run the rest of the code and make the copies, if one is empty, it won’t run the code. This means that if one of the alerts is shown above, it won’t run any code afterwards,  a king sure no copies are made by mistake.

multiple-file-maker-5

Line 22: Here we first check if the fixedName isn’t equal to a blank. Then, use the double ampersands, which mean AND, to also check if the fileUrl isn’t blank. If they both aren’t empty it runs the following code.

multiple-file-maker-6

Line 25: Now, we need the file ID from the file URL, in order to make copies of the file. We use the match() method (more info here) and use some regular expressions to extract just the key part. Don’t worry about how this works exactly, but if you’re interested in the use of regular expressions, go to this page.

Line 27: Now, we use that ID stored in fileId, to get the file by its ID, using the DriveApp class and the getFileById() method, which basically, gets the file we want to copy.

Now, we finally get to the point where we make the multiple copies. We’ll use a simple for loop for this. We want it to loop down column B making copies as it goes down, but then stop when it reaches an empty cell in that column, i.e.  The end of the list.

multiple-file-maker-7

Line 31: I’ve set up a counter variable called n, and we’ll keep going down until we hit the bottom row.

Line 32: But to prevent it making files with no variable names, I’ve included an if statement to check to see if the current variable name is blank, using variableNames and the current array number in the square bracket. Remember this is looping down the array variable variableNames, so it’s going from zero to potentially the bottom row. If it does find a blank it breaks out of the loop, i.e. It doesn’t continue going through the array. Also, note the use of a double equals sign, not a triple one, as we are comparing an array value with something empty and they are not the same type, so a triple equals sign will fail.

Line 34: This is the line that makes a copy of the file we want. We use the fileId stored in the variable fileToCopy we created in line 27. Then, use the makeCopy() method and in the brackets state the name we want. Here, we’ll going to join the fixed name with the variable name). Line 35 closes the loop.

multiple-file-maker-8

Line 36: Finally, just to tell the user the process has finished, I’ve added a toast message, which will pop up in the bottom right-hand corner, when the program has finished. Here it frees to the active Spreadsheet ss, then in the brackets contains 3 parts: message, title, number of seconds it will be displayed.

Line 37 and 38: This closes the if statement from line 22, and the function.


Adding a menu

To run the program from a menu in the spreadsheet, I’ve also added a separate script file called onOpen, which will add a little menu to run the code.

Line 1: Call the function onOpen(), so that it opens automatically every time the spreadsheet is opened.

Line 2: Get the active spreadsheet, note I’ve left a comma at the end not a semi-colon, as the next 2 lines are connected to it.

Lines 3-4: Set up a variable called menuItems to add the menu details. The keyword var isn’t needed here as it using the one from line 2. Then we set up an array with the sub-menu name and the function it will run. Carefully note the syntax used here.

Line 5: Now, we add the menu to the spreadsheet using the addMenu() method to the active spreadsheet. In the brackets, we include 2 parts: menu name, and the variable menuItems.

multiple-file-maker-14


Here’s the link to the Sheet and code:

https://docs.google.com/spreadsheets/d/1kYJpljR1rkZZO0eFN8u8UupBsdps_xpL6BHhS7PpSAQ/copy

This code can of course be modified to suit your needs, as for example, you may want to let users create files without a fixed part to the file name or maybe without any variable names so all the files are named the same.

Here’s the complete code:

multiple-file-maker-1

The regular expression in line 25 is from here:

http://stackoverflow.com/questions/16840038/easiest-way-to-get-file-id-from-url-on-google-apps-script 


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Follow my Google+ Learning Google Apps Script Collection

Baz Roberts (Flipboard / Twitter / Google+)


Pimp up your Sheet – Programmatically!

When working with spreadsheets we can spend quite a long time making them look good and if we have to make the same sheets over and over again, that’s a lot of wasted time. Here we’ll look at how you can format a Google Sheet via Apps Script. The focus of this post is to show you some of the common methods available to get Apps Script to format your sheets automatically. Quite often, these would be part of larger program which get the data from somewhere and create a new sheet with it, then format the way you want. However, it could also be used where you want consistency between different sheets.

Due to their simplicity, I also think it’s a great place to start if you are new to Apps Script. If you are new, please see my earlier posts, which explain how to open the Script Editor, etc.

There are 3 scripts. In the first, we’ll cover the following:

  • adding borders
  • changing the font and size
  • setting the cell wrap status
  • setting the horizontal and vertical alignment
  • setting the number format
  • changing to bold
  • changing the cell background colour
  • deleting unwanted rows and columns
  • adjusting the column to specific widths and to adjust to the text

In the second and third scripts, we’ll quickly look at hiding rows and columns, to set up the sheet with the information you want in it, then be able to reset it by showing all the rows and columns again.


SCRIPT 1

As an example, we going to format a list of classes. So we are going to go from this:

pimp-up-your-sheet-36pimp-up-your-sheet-37

To this (which will take the program about 3 seconds):

pimp-up-your-sheet-41

Here are all the steps one-by-one:

pimp-up-your-sheet2

Let’s go through it step-by-step:

First we set up the function, here I’ve called it “formatSheet1” and we get the active Spreadsheet and the sheet we want, in this case, the sheet is “Sheet1”.

pimp-up-your-sheet-9

Then I want to set up some variables to store the rows and columns in the sheet.

Line 8: Here I’ve used getDataRange() to get the range that contains all the data in the sheet and stored it in the variable allCells, which we’ll be using throughout the script.

Lines 9 and 10: Here I also want to store the number of rows and columns for later use. To do so, I use the getLastColumn() and getLastRow() methods.

pimp-up-your-sheet-10

I also want to get a couple of specific ranges that we’ll use later.

Line 13: I get the header row by using getRange() and in the brackets I put (starting row, starting column, number of rows, number of columns). I’ve used the variable numColumns for the number of columns.

Line 14: Plus, I get the comments column, using getRange().

pimp-up-your-sheet-11

Now let’s start formatting the sheet.

Line 17: First, let’s add borders to all the cells which contain data. We use the setBorder() method and use it with the range stored in allCells, i.e. all the data. In the brackets, there are 6 parts: top, left, bottom, right, vertical, horizontal, which refer to the sides of the cells. We state “true” for each of these parts to add complete borders.

pimp-up-your-sheet-12

Now let’s change the font to Calibri and set its size to 11 to all the data.

Line 20: To set the font we use the setFontFamily method and add the name of the font in the brackets. Make sure you use a capital letter, otherwise it won’t work properly. To set this to all the data, we add it to the range stored in allCells.

Line 21: To set the font size, we use the setFontSize() and in the brackets state the size.

pimp-up-your-sheet-13

Line 24: In the comments column I want to set a smaller font size, so, I use setFontSize() with the commentsColumn variable, which contains the comments column range.

pimp-up-your-sheet-14

Line 27: Now let’s set the text so it wraps if it is long. We use the setWrap() method with the header row. To activate it, we put ‘true’ in the brackets.

Line 30: I also want to do the same, for all the comments column, so I use the same method with the commentsColumn variable.

pimp-up-your-sheet-15

pimp-up-your-sheet-16

Now, let’s set the alignment of the text, both horizontally and vertically.

Line 33: First, I want to set the horizontal alignment for all the data to “center”. We use the setHorizontalAlignment() method and state the type in the brackets.

Line 34: Then, I want to set just the horizontal alignment of the comments column to “left”.

pimp-up-your-sheet-17

Lines 39: We do the same for the vertical alignment, using the setVerticalAlignment() method. First, we set the vertical alignment of all the data to ‘middle’.

Line 41: Then we set the vertical alignment of the header row to ‘bottom’.

pimp-up-your-sheet-18

Now let’s set the number format for the class number and dates.

Line 45: First we get the class column via getRange().

Line 46: Then we use the setNumberFormat() methods to that range and state the format in the brackets. In this case, I want the class number to be two digits, so that ‘1’ will be ’01’. To do this, we use the format “00”.

Line 49: Then we get the columns which contain dates, which are from column 8 (column H) to column 18 (column R). Note, we start from column 8 and then including this one, include 11 columns to the right.

Line 50: This time I want the date format to be “dd/mm”, for example, “20/03”.

pimp-up-your-sheet-19

Line 53: Now let’s add bolding to the header row. We use the setFontWeight() method to do so, and in the brackets, add “bold”.

pimp-up-your-sheet-20

Now I want to colour header cells in different colours. First, I generally find it easier to state the hexidecimal codes beforehand and assign them to easy to recognise names. Plus, if different cells are using the same colour, it’s easy to change the colour by just updating the hexidecimal code for that colour.

In the file, I’ve added a sheet which contains all the hexidecimal codes for all the default colours in the colour palette.

Lines 56 to 63: I set up the variables as below. Remember to include the hash.

pimp-up-your-sheet-21

Then we are ready to set the different colours to the backgrounds of the cells.

Line 65: I want to set the first 6 cells in the header to be blue, so first I get the range.

Line 66: Then I set the background colour, by using setBackground() with that range and in the brackets refer to the colour in the variables we set up beforehand, here’s it the variable blue.

Lines 68 to 96: I continue in a similar way across the sheet.

pimp-up-your-sheet-40

One thing I like with Sheets, is the ability to delete unwanted rows and columns. We could hard code this, in other words, state the rows or columns we want to delete, but let’s get the code to work out which rows and columns don’t have data in them, and thus, delete those.

Line 99: First, we need to find out how many columns there are in the sheet. We use the getMaxColumns() to find this out. I’ve stored this in totalColumns.

Line 100: Then we do a bit of maths, we take the number of columns that contain data (stored in the variable numColumns) away from the total number of columns (totalColumns). I’ve stored this in numOfColumnsToDelete.

Line 101: Now we delete the empty columns. We use deleteColumns() and in the brackets there are two parts: the column position we’re going to start at, and how many columns we’re going to delete. The first is the variable numColumns + 1, as we don’t want to delete the last column with data but the one to the right of it. the second, we use the variable numOfColumnsToDelete which we worked out before.

pimp-up-your-sheet-23

Lines 104 to 106: We follow the same process for the rows, just we use the equivalent row methods and variables.

pimp-up-your-sheet-24

The final thing I want to do is change the column widths. The first 7 columns I want to adjust automatically according to the data in them.

Line 109-111: Here we can use a simple for loop to resize each of the columns in turn. I start the variable i at 1 (column 1) and it goes up by 1 each round of the loop until it reaches 7 (column 7). More info on loops here.

Line 110: Each round of the loop, it will automatically resize the column based on the widest piece of data in that column. The first time in the loop the i in brackets will be 1, which refers to column 1.

pimp-up-your-sheet-25

Line 114-116: I use a similar loop to adjust the widths of columns 8 to 18 to ’60’.

Line 115: This time I’m stating the exact width, so we use the setColumnWidth() method. In the brackets we need to state the column number and the width.

Line 119: Here I want to make the width of the comments column ‘120’, so I’ve used the variable numColumns to identify the comments column, as I know it’s the last column, then add the width ‘120’.

pimp-up-your-sheet-26


SCRIPT 2 – Hiding rows and columns

Sometimes we have a sheet which is full of data but we’re just interested in a part of it and only want the relevant parts showing. In this little script, I just want to show how easy it is, to set up a sheet showing just the rows and columns you want, by hiding the unwanted ones.

As in the first script we get the active spreadsheet and Sheet 1.

pimp-up-your-sheet-28

I want to only show term 1 information, so I hide columns L to R as they refer to term 2 and 3.

Line 6: First, I need to get the range of columns use getRange(). Note, the range refers to the first row, which is sufficient as it by default means it includes the columns we want.

Line 7: Then we use hideColumn() to hide those columns.

pimp-up-your-sheet-29

This time I want to hide the rows with the French classes, so I need to hide rows 4 and 10.

Lines 10 and 11: Similar to above, I get the ranges, and here including just column A is fine, it just needs to include the row number.

Lines 12 and 13: Then we use hideRow() to hide the respective rows.

pimp-up-your-sheet-30

So, potentially at a click of a button, we can hide the rows and columns we want, leaving just the relevant information.


SCRIPT 3 – Unhiding all rows and columns

This is great, but we will want to reset the sheet to display all the rows and columns.

As before we get the active spreadsheet and Sheet 1.

pimp-up-your-sheet-31

Line 6: First, I want to unhide all the columns, so I will need to know how many columns there are. So, here I’ve used getLastColumn().

Line 7: Then, we need to get the full range of columns, using getRange() and getting from column 1 to the number of columns in numColumns.

Line 8: Then, we use unhideColumn() to unhide all the columns in the range.

pimp-up-your-sheet-33

Then we do the same for the rows. The only difference is we’re referring to rows not columns, so we use the respective methods.

pimp-up-your-sheet-34


Here’s the link to make a copy of the sheet and code:

https://docs.google.com/spreadsheets/d/1S21Km_0R1-K9An8zX9NntXBHdQWGrm9VY1ryIHs1Gj8/copy

In the spreadsheet, you will find 4 sheets.

‘Sheet 1’: the sheet that the scripts work on

‘unformatted’: a copy of the unformatted data

‘formatted’: what the data looks like once formatted

‘ColourRefs’: As a bonus, I’ve added the hexadecimal references for all the default colours in the colour palette in sheets. In the script edittor, you’ll find a fourth script, which is what I used to get those reference numbers.


Here are the full scripts:

pimp-up-your-sheet-42pimp-up-your-sheet-38pimp-up-your-sheet-39

SCRIPT 2 – HIDING ROWS AND COLUMNS

pimp-up-your-sheet-27

SCRIPT 3 – UNHIDING ALL ROWS AND COLUMNS

pimp-up-your-sheet-32


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Follow my Google+ Learning Google Apps Script Collection

Baz Roberts (Flipboard / Twitter / Google+)


Book inventory

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:

book-inventory-4

Front page – This is what the user will see and use. In the yellow part, we’ll be able to find out the location of a book from its code. They enter the book number in cell B4 and the current location appears in B5. In the green part, we’ll be able to change the location of a book or in fact up to 10 books all in one go. The user adds the book numbers in cells B11 to B20 and enters the location in B10. Then from the menu they’ll run the program to update the list.

book-inventory-1

Coursebooks – On this page we have titles of the books, level, which part of the set it is (e.g. SB: student’s book), its individual reference number, and its current location. Columns D and E are what we’re going to use.

book-inventory-12

Ref – This page is used to create the drop down menus on the Coursebooks page using data validation.

book-inventory-11

In cell A2, I want to list the titles of the books we have on the Coursebooks page, but without duplicating any. So, I use the UNIQUE function and combine it with the SORT function to sort it into ascending order.

book-inventory-2

In column C I’ve listed the possible levels. In column E the possible type of book.

In column H I write the possible locations of the book, then to save me having to sort this list alphabetically every time I add a new person or location, I’ve added in cell I2 the SORT function below, to sort it alphabetically automatically and this is what the Coursebooks sheet will use.

book-inventory-3


THE CODE

There are two pieces of code here, one runs when the file is opened, and the second runs when the “Update locations” is selected from the “Books” menu.

CODE 1

It’s going to create a menu, clear any unwanted previous information on the Front sheet, highlight cell B10 to save having to click on it, and display a message when it’s finished.

book-inventory-31

Here’s the code step-by-step:

First, I start with a function and call it onOpen(). This tells the file to do something when the file is opened.

book-inventory-6

Line 3: Then I get the current spreadsheet and store it in the variable ss for later use in the code. Note, that I’ve added a comma at the end of lines 3 & 4, so that lines 4 and 5 are part of this.

Line 4-5: This states the name of the menu item and tells it which function to run, when someone clicks on it. This is stored in the variable menuItems and is stored as an array, so you need to make sure you enclose it all in square brackets and as these are menu properties, you’ll need to enclose them in curly brackets too.

If you use this code for your own projects, just copy lines 3 to 6 and change the text parts in inverted commas.

Line 6: This adds the menu to the spreadsheet. The first part “Books” is the name of the menu you will see on the menu bar, then the second part refers to the information in Lines 4-5, i.e. the menu item and associated function to run.

The rest of the code sets the sheet up ready to be used.

book-inventory-7

Line 9: When the user opens the file, I want them to start on the first page.  To select that specific sheet I want, I use the getSheets method and state that I want sheet 0. The getSheets method gets all the sheets in the spreadsheet and puts them in an array. This is why the first sheet is sheet 0, as arrays start with 0 (i.e. are zero-based). Now it’s got sheet 0 the setActiveSheet method makes the sheet I want the active one.

Next, I want to clear any book numbers or location that may have been previously entered in the range B10 to B20.

book-inventory-32

Line 12: First, I get the range I want using the getRange method, and here I enter the range between inverted commas.

Line 13: Then I get the range and use the clearContent method to clear it.

As the user more often than not, will want to fill out the new location in B10, I want the sheet to highlight that cell when they open the file, so they don’t have to click on it.

book-inventory-9

Line 16: First, I get the cell range using the getRange method and store it in the startCell variable.

Line 17: Then, I make that cell the active range, using the setActiveRange method and refer to the variable startCell where the range is stored.

Finally, just to tell the user the sheet’s ready to use, I want to display a little toast message, which is a pop-up message that appears in the bottom right-hand corner of the screen.

book-inventory-10

Line 20: This uses the toast method and has 3 parts to it in the brackets. The message you want to display, the title of the message, how many seconds you want the message to display.


CODE 2

Now let’s look at the second piece of code. We could add it at the bottom of the code we’ve just seen, but I often keep different functions separate just so it’s easier to read and to find the specific code you want.

First, let’s change the first one from “code” to “onOpen”. On the left of the screen you’ll see the list of script files you have for this particular project. To do so, just click on the little triangle next to the title and choose “Rename”, then type in a new title. You don’t need to add .gs at the end, it’s added automatically.

book-inventory-33

Now, let’s create a new Script file. Go to File>New>Script file.

book-inventory-30

Then, type in the title of the Script file and press OK.

book-inventory-34

You’ll see both script files on the left-hand side of the screen. Now, we’re going to work with the enterBookLocation one, so click on that file to open it up in the editor.

book-inventory-28

Here’s the complete code we’re going to use:

book-inventory-13

As before, let’s go through it step-by-step. First, start off with a new function called enterBookLocation and in line 3, let’s get the active spreadsheet.

book-inventory-14

Now, we want to get the details entered on the Front page, i.e. the location and the book numbers.

book-inventory-15

Line 6: We get the sheet called “Front page” using the getSheetName method. This is stored in the frontpage variable. Note, I could have used the getSheets ()[0] method as in the other code, but I just wanted to show an alternative method.

Line 7: Here we want to get the location entered, so we get the value in cell B10. Note, we’re using the getValue() method as it’s just one cell.

Line 8: Here we want to get the range of cells from B11 to B20, in case the user has entered up to 10 books. This time we use the getValues() method as there are 10 cells and this stores the values in an array variable, which we will call books. We will use this to find the books in the inventory.

Now, we need to get the list of books on the Coursebooks page.

book-inventory-16

Line 11: First, we get the sheet called “Coursebooks” with the getSheetByName method and store it in the coursebooks variable.

Line 12: Then we need to get the last row so we are able to get the list of books from the top to the last one on the list. To do this, I use the getLastRow() method.

Line 13: Now we can get the book numbers in column D. So, first we get the range using getRange and in the brackets we put the following information (starting row, starting column, number of rows, number of columns). So, here we start from row 2, column 4, down to the last row, and we only want this 1 column. Then we need to get the actual values in that range, using getValues(). So, now we have an array variable called bookNumbers which contains all the books.

Line 14: As we want to update the locations, we need to get the range of locations too. So, as before we use getRange and this time get column 5. We only need the ranges and not the values in those ranges, so we don’t need getValues(). We store this in the variable locationRows.

I want it to get a book number from the Front page then look it up on the inventory, and once found will get the corresponding location and change it to the one entered on the Front page. Then it will get the next book number and do the same until it comes across an empty cell on the Front page, when it will stop and go to the end of the code.

First, we need to set up a for loop which will go one by one down the list of book numbers on the Front page.

book-inventory-17

Line 19: We start with for and in the brackets we put 3 pieces of information:

(starting number of the variable; the condition which while it’s true will continue the loop; how much we’re going to increment the variable by each time it loops)

In this case, the variable bn starts at 0; it will continue while it is less than 10; every time it loops around the variable bn goes up by one (++ means +1). Then in curly brackets we put some kind of action. I’ve opened the brackets in line 19 and the closing bracket is on line 36. So, all the lines between 19 and 36 are included in this loop.

Now we create a second for loop to go down the book list and look for a matching number.

book-inventory-18

Line 22: The for loop is similar to the one above, but this time I use the variable RN (row number) and use the variable numRows as the condition, so that it will start at the top and go down the list one by one to the bottom. The open curly bracket is on line 22 and the closing one is on line 35.

Line 23: Every time it goes down the list we want it to check to see if the current book number on the list (stored in the bookNumbers variable) is the same as the book number on the Front page (stored in the books variable). To do this, we use an if statement. We put the condition we want to check in between brackets.

Here we get the value from the array variable bookNumbers and at position RN in our array. The zero is because our array is only one line of values, so the number in the second square brackets will always be 0. So, the first time it loops, it gets the value in the variable bookNumbers at [0][0], which is the first book on our list.

Then we use the triple equal signs to show we want the bookNumbers value to equal the books value. Now we need the books value at position bn, 0, which is the first book number, i.e. The value in cell B11.

Next I’ve add a second if statement to check to see if it has come to the end of the list of book numbers entered on the Front page, as this will speed up the program as it won’t continue to loop down the range B11 to B20 if it comes across an empty cell.

book-inventory-19

Line 26: Here it’s checking to see if the value in books is equal to “”, in other words, an empty cell.

Line 27: If it does, it “breaks” out of the loop, which means it doesn’t continue the loop and goes to the next part of the code after the loop (in this case line 37).

If there is a value and it the book number matches a number on the list, it performs the code in lines 31 to 33.

book-inventory-20

Line 31: Here we want to get the cell where the values match. This uses the getCell(row, column) method. For the row we use the variable RN and add 1 to convert it from its array position to its actual row on the sheet. The column is 1 as we are only using 1 column.

Line 32: Then we need to make that cell the active one and set its value with the new location. So, we get the sheet coursebooks, use the setActiveSelection method and use the location we just got in line 31. Then we set the value with the value from the location variable from the Front page.

Line 33: As it’s found a match, it doesn’t need to continue looking down the list, so we break out of the current loop by using {break}. Again this is to speed up the program.

Lines 34 to 36: These contain the closing curly brackets for the loops above.

The loop continues updating the location of each book until it comes across a blank cell in range B11 to B20.

Finally, once it’s updated the locations, I want it to return back to the Front page and highlight cell B10 again ready for future use, using similar code to what we saw in the onOpen function.

book-inventory-21

Line 38: This gets the first sheet.

Line 39: This gets the cell B10.

Line 40: This sets that cell as the active one.

Line 41: Closes the function.


Program in action

The user enters a new location (often a person) in cell B10. Then enters the books they want to link to that location. Here I’ve added two.

book-inventory-22

They click the Books menu, then Update Locations.

book-inventory-23

book-inventory-24

As we can see on the Coursebooks page, it’s updated book 0002 and 0005 to Paul. So, you can imagine with an inventory with hundreds or thousands of books, this will save the user a lot of time, as they can update up to 10 books in seconds, rather than scrolling up and down the list looking for the books to update.

book-inventory-25


Finding a location of a book

In the yellow part I’ve added a quick way to find out where a book is by entering the book number. This is particularly useful when a book is found and we want to find out whose it is.

The user types in the book number in cell B4 and the location appears in B5.

book-inventory-27

There’s no coding involved with this one, just the following VLOOKUP and IFERROR function. Using the VLOOKUP function, this looks up the number enters in B4 in column D on the Coursebooks page and gets the corresponding location from column E. If the book isn’t on the list, it comes back with the message “Book not found” by using the IFERROR function.

book-inventory-26


Here is a link to make a copy of the file, which contains the code above.

https://docs.google.com/spreadsheets/d/1vC6KDJXMmN1xwqS9sThUP8BbqyPJQ5TLyGl03XGFbvM/copy

I did think of writing a program to enter new books but to be honest we found it was just easier to add the book to the bottom of the list using the drop down menus to speed up the process.

If you want to know more about how if statements and for loops work I recommend reading the information on the W3Schools site:

For loop

If statement


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Follow my Google+ Learning Google Apps Script Collection

Baz Roberts (Flipboard / Twitter / Google+)


Automatically emailing info from a form submission

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 there has been a submission. This is building on the code from my previous post, so the areas which are the same I will only briefly describe here. If you want more details see my previous post.

This is the email that we’re going to send automatically:

class-request-v2-16

Here’s the full code:

class-request-v2-1class-request-v2-2

Let’s look at it step by step. First, we set up the function and then get the active spreadsheet.

class-request-v2-3

Then we get the sheet which contains the information we want, which in this case is on ‘Form Responses 1’.

class-request-v2-4

We want to get the values on the last row, which is the latest form submission. There are 3 steps to do this. We need to get the last row on the sheet, the last column and then get the range using that information.

Line 10: We get the last row of the form responses 1 sheet (using the getLastRow method) and store it in the variable called lastRow.

Line 11: Similar to the lastRow we get the last column (using the getLastColumn method) and store it in the variable lastColumn.

Line 12: Now we want to get the values in the last row, from the first column to the last column. We do that by getting the range (using the getRange method). This takes 4 pieces of information; starting row, starting column, number of rows, number of columns). So we start on the lastRow, we start at the first column, we only want 1 row, and we want to collect data until the lastColumn. Then we add getValues to get the values in that range.

class-request-v2-5

Now we have an array variable called lastRowValues which contains all the data from that last row. Now let’s assign a variable for each piece of information on that row, so that we can use the variables later in the code and so we can clearly see what’s in those variables.

Lines 15-21: Here we’re extracting a specific bit of information from the lastRowValues array, and so need to state the position of the information. The array contains this at the moment:

[29/01/2017 15:43:01], [Ian Student], [brgablogse@gmail.com], [123456789], [Mon/Wed 17:00-18:00], [06/02/2017], [31/03/2017], [To prepare for the CAE exam.]

There’s only one line in our array, so all the values we look up will start with position [0] . Then, for example, the name “Ian Student” is at position [1] in the array (remembering that the first item is [0] in an array). So, we write lastRowValues[0][1]; to get the name.

We then repeat the same format for each piece of information we want, just changing the position in the array each time.

class-request-v2-6

In lines 19 and 20, we’re getting the dates from the form submission and we could use them directly from the sheet. The problem is that they won’t be in the format they are on the sheet. They will look like this:

class-request-v2-15

Not the easiest date to read and normally we don’t want the full date, so let’s change the format of the date to the way we want it. For this email, I want the format to be DD-MM-YYYY, e.g. 06-02-17.

Line 24: First I check the user has in fact inputted a date. So, I write an IF statement to check if the variable startDate isn’t empty. The If statement syntax is:

if (variable + logic) {do something if it’s true}

!== means not equal; so I’m checking to see that the variable startDate isn’t equal to empty “”. If there is a date, lines 25 & 26 are run, i.e. the parts inside the curly brackets.

Line 25: First, let’s get the month from the date and add 1. This is because January is 0, so we need to add 1 to return a real month number. We use the getMonth() method to extract the month. Then we store it in the month variable.

Line 26: Now we need to create the date format we want. There are 3 main parts, date (day number), month, and year. To get the date, we use the getDate() method, then we add a hyphen, then we add the month we extracted in line 25. Then we add another hyphen, then we get the year with the getFullYear() method. This is then stored in the startDateEdit variable. So, it will create a date like this: 06-02-2017.

Line 28: What happens if the IF statement returns false, well I want it to put a few dashes instead of a date. Here we use the ELSE statement, which has the same syntax as the IF statement and will run if the above IF statement is false. So, here I just set the variable startDateEdit as three dashes “—“.

class-request-v2-7

The finish date follows the same pattern as above.

class-request-v2-8

I also want to include the spreadsheet URL, so I get that by using the getUrl() method and store it is the variable urlOfSS.

class-request-v2-9

Now let’s create our email with the information we’ve extracted above.

Line 44: We set up the variable message, to store the information and in the first line type a short introduction. Note, at the end of every line from line 44 to 54, we have to add a “+” to show every line is connected together, and everything is within the message variable. Also, in the email, I want to list the details, one line after another, so we use “\n ” to add a line break.

Line 46: To add the name, next to the line break we type the text “Name: ” and close it with the inverted commas. Then we need to add the variable name, putting pluses (+) either side.

Lines 47 to 54 are written in a similar way. The final line of the email, line 55, ends with a semi-colon and doesn’t have a plus.

class-request-v2-10

Now, we get the email addresses from our list on the sheet called “Emails”.

Line 58: Use the getSheetByName() method to get the sheet. Then we store it in the emails variable.

Line 59: Then we get the last row of the emails using the getLastRow() method and store it in the variable numRows.

Line 60: We get the values in the range using the methods getRange and getValues.

class-request-v2-11

Here it’s getting the range from row 2, column 2, to the bottom of the list of emails, and only this 1 column.

class-request-v2-14

Then we set the subject line of the email. Here I’ve added a fixed part in the title “Private Class Request – ” then added the variable name, so that the person’s name of that particular request will appear in the subject line. This is stored in the variable subject.

class-request-v2-12

Then we send the email using the Gmail class and sendEmail() method. Then add the 3 variables, emailTo (the email addresses), subject (the subject line), and message (our email message).

class-request-v2-13

Remember to activate the onFormSubmit trigger, as explained in my last post.


As this has touched on JavaScript areas such as, arrays, if, and else statements, if you’re not confident using these, I would suggest you read the following on the excellent W3schools website:

arrays / if else

Here’s the link that let’s you make a copy of the spreadsheet I’ve used, which will copy the spreadsheet, the linked form, and which contains the above code.


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Follow my Google+ Learning Google Apps Script Collection

Baz Roberts (Flipboard / Twitter / Google+)



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:

  1. Setting up the Form (inc Email collecting and Data validation)
  2. Setting up the Sheet
  3. 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.

class-request-33

To set up a question to collect the user’s email. Click on “Settings”.

class-request-2

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”.

class-request-1

Back in the Questions part, you can see it’s automatically created a question which will check for a valid email.

class-request-4

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 of the question, and then click “Data validation”.

class-request-3

I then change the data validation option to “Number”.

class-request-5

Then click on “Greater than” and change it to “Is number”.

class-request-6

class-request-7

Then I add start and finish date questions. With the new AI, when I type “Start date, it automatically changes the question type to “Date”.

class-request-8

class-request-9

Finally, I add a Details questions, which automatically changes it to a Paragraph style question.

class-request-10


Part 2 – Setting up the spreadsheet

Open the spreadsheet and on the ‘Form Responses 1’ tab you’ll see the questions in row 1. I like the fact that the sheets with a form linked to it, now have a GForm symbol on it.

class-request-11

Add a second tab and rename it ‘Emails’.

class-request-13

In that sheet, type in the email addresses you want the request to go to. In column A I’ve added their names and in column B their emails.

class-request-12


Part 3 – Writing the code

In the Sheet, click on Tools>Script Editor.

class-request-14

Click on “Untitled project” and give it a new name.

class-request-15

Then click “OK”.

class-request-17

We want the program to run when there’s a form submission, to get the spreadsheet URL and then email a group of people a short message telling us there’s a new request, including the URL, so we can easily click on it to open the sheet to see the request details. Here’s the code we’re going to use:

class-request-24

Let’s look at the code section by section. Note that you don’t need to add the comments (the parts after //) but it can help you follow the code and remember what each bit does.

First we set up a function called onFormSubmit() and open the function with the curly brackets.

class-request-25

Then we want to get the active spreadsheet. Here we set up a variable called ss, which we will refer to throughout the program. Then we use the SpreadsheetApp class with the getActiveSpreadsheet method.

class-request-26

Now we want the spreadsheet URL. Here we set up a variable urlOfSS, which is where we will store the URL. Then we use the ss variable we just created and use the getUrl() method to get the URL.

class-request-28

Now we need a message in our email.

Here let’s create a variable message to store the message. We put the text we want within inverted commas, as it is a piece of text. I also want to add the URL, so I add a + at the end to show it’s connected to the text.

Then I want to put the URL on a new line, so I add “\n “ which in HTML is a line break. Then I add another + and add the variable urlOfSS.

class-request-29

Now we want to get the email address of those we’re going to send the email to. Let’s look at this line by line.

Line 17: Using the getSheetByName method we get the Emails sheet. Put the name of the sheet in the brackets between inverted commas. We’ll store this in the variable emails.

Line 18: We want to get the last row on the sheet that has data in it. Assuming the sheet is set up as describing in part 2 above, then this will tell us that the last row is row 3. This uses the Sheet class followed by the getLastRow() method. We store this in the variable numRows. We’ll use this info to get the correct range in the next line.

Line 19: Now we need to get the email addresses in sheet. We use the Sheet class again and this time get a specific range by using the getRange method. This has 4 parts: starting row, starting column, number of rows, number of columns, So, in our example, we’re:

  • starting in row 2
  • starting column 2
  • getting the number of rows from the variable numRows (which in this case is 3)
  • there is only 1 column

So, in the brackets we write (2, 2, numRows, 1)

Important: This will get the ranges but not the values within those ranges. So, we need to add another method, getValues() which will stored the actual email addresses in the variable emailTo.

class-request-30

Now we need a subject line for our email. We store the text we want in the variable subject.

class-request-31

Finally, we of course want to send the email. Here we use the GmailApp class followed by the sendEmail method. In the brackets we add the three pieces of information we collected earlier:

  • emailTo (Email addresses we’re sending to)
  • subject (Email subject line)
  • message (Email message)

To close the function we always add a curly bracket at the end.

class-request-32

Note that all the lines except lines 2 and 26 have a semi-colon at the end.

This program uses the onFormSubmit trigger, which allows the program to run automatically when a Form is submitted. This needs setting up, otherwise it won’t run automatically, but it’s simple to do.

In the Script Editor, click on the clock icon.

class-request-18

This will open the Current project’s triggers menu. A new program won’t have any set up. Click on the blue “No triggers set up. Click here to add one now.”.

class-request-19

Here you need to tell it what to run and how to run it. All of these are simple drop down menus. By default the onFormSubmit program will be selected. Now let’s add the event details.

class-request-20

Click on “Time-driven” and change it to “From spreadsheet”. This will change the other menus.

class-request-21

Click on “On open” and change this to On form submit.

class-request-22

You should be left with this. This runs the function “onFormSubmit” when a form is submitted to the spreadsheet. Click “Save”.

class-request-23

Forgetting to set up the trigger is a common mistake to make.


Here’s the email that is sent out.

class-request-34-1


Here’s a link to make a copy of the above spreadsheet. The linked form will be copied automatically and the code will already be in the Script Editor.

https://docs.google.com/spreadsheets/d/1fVkLj-dI4ig9MJT7uihhgiwWD7MDtANANsDOKmOQSLU/copy

The email in this post is quite basic and performs a similar function to the automatic email you can receive when setting up Notification rules, found in the Tools menu. However, this does allow you to email a group of people, plus it’s a springboard for my next post, which will focus on how you can extract data from the latest form submission and include it in the email.


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Follow my Google+ Learning Google Apps Script Collection

Baz Roberts (Flipboard / Twitter / Google+)


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.

functions20-1

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 she doesn’t need to know the classroom feedback or the admin feedback. So, the info she needs is from column A to H, as shown below:

functions20-2

In cell A1 on a different page, I’ve written the following QUERY function:

functions20-3

There are 2 main parts to a QUERY function, 1) the data range, 2) the query

So, in the first part (in orange) we look at the page called “Questionnaire” and range A1 to column N (note this is an open-ended range as we will receive more entries in the future).

In the second part we tell the function what to select. So, in this example, we want columns A to H. We add the column letters followed by commas. The query part always needs to be within speech marks, so we put it before select and at the end before the bracket.

“select” is one of the keywords within the QUERY language which tells the function what to do. Here are some of the other ones, most of which we will see in the following examples.

functions20-33


Example 2a – Filter by a teacher’s name

Now the head has decided that he wants to look at the feedback of a particular teacher. She wants the following information:

functions20-4

Here’s the QUERY formula I entered in cell A2:

functions20-5

It’s the same as before except that at the end I’ve stated a condition:

functions20-37

This looks at column C and returns anything that matches “Fred”. I.e. it only returns the feedback for Fred.


Example 2b – Filter by a teacher’s name using a cell reference

The head has decided that she doesn’t want to have to change the teacher’s name within the formula every time she wants to look at the feedback of a different teacher, she wants to enter the teacher’s name in cell B1 and wants the formula to update accordingly.

That’s no problem, although the syntax looks a little ugly. Here’s the formula:

functions20-6

At the end, instead of the name “Fred” I’ve put the reference to the cell. In QUERY function you have to use this syntax: ‘”&B1&”‘ basically so it knows it’s a cell reference.

functions20-38

As you can see it produces the same information as before and now if the head wants to see another teacher’s feedback she only needs to change the name in cell B1.

functions20-4


Example 2c – Filter by a teacher’s name and sort the date in descending order

By default, the data is sorted from the oldest date to the most recent, but when there is a lot of data this means that to see the most recent and probably most relevant data, the person has to scroll down. We can remedy that easily by sorted the data by date in descending order, as we can see below:

functions20-8

To achieve this, I’ve used the same formula as before except at the end I’ve added an ORDER BY part:

functions20-9

functions20-39

This orders (or sorts) column A (the dates) and the ‘desc’ tells it to do it in descending order. If you want to tell it to do it in ascending order, write asc.

Carefully note the syntax, as one tiny error will stop this from working.

Also note that, the QUERY results aren’t formatted and the column widths aren’t automatically adjusted. This needs to be done manually either beforehand, or afterwards.


Example 3a – Filter the data between 2 dates

The head also wants to be able to filter the data for a particular period of time, e.g. september 16. As you can see below, the data has been filtered between 1/9/2016 and 30/9/2016.

functions20-31

Here’s the formula I’ve added in cell A2:

functions20-32

functions20-40

The new part is at the end. First, we tell it to look in column A for a date bigger than or equal to the date in inverted commas. Make sure you add the word date, to tell the function that you’re looking for a date and not some text.

functions20-41

Then in the next part, we add ‘and’ to tell it to look for 2 criteria. Then tell it to look for a date less than or equal to the date in inverted commas.

Note that with dates you need to write the date in the following format:

YYYY-MM-DD

Even if the date format in your sheet is different as it is in my one.

So to summarise, it gets columns A to H, and returns rows that meet the 2 criteria, i.e. 1/9/2016 to 30/9/2016.


Example 3b – Filter between 2 dates using cell references

As we saw earlier we can replace the actual dates in the formula with cell references.

functions20-10

The only thing is that to do this we need quite a complex looking formula. Here’s the formula I’ve added in cell A2:

functions20-11

Here’s the formula broken down into its component parts:

functions20-44

functions20-45

functions20-46

To replace the actual date we need to use the following formula after the word ‘date’:

functions20-42

This gets the date in cell D1, puts it into the correct format. The same goes for the second date:

functions20-43

If anyone reading this knows of simplier syntax to do the same thing, I’d love to hear it, as I couldn’t find on-line a better way to do this, but I’m open all ears!


Example 3c – Filter between 2 dates and by teacher

Finally, the head wants to filter the feedback between the two dates, by a specific teacher, and order the feedback by date in descending order, as you can see below:

functions20-12

As you can see the formula is getting pretty long, but you can also see that it’s made up of parts and you can extract what you want by adding extra parts.

functions20-13

Here’s the query part broken down:

functions20-44

It gets columns A to H.

functions20-45

Finds rows where the date is greater than or equal to the one in cell D1.

functions20-46

And that also is less than or equal to the one in cell F1.

functions20-14

AND where the name in column C is the same as the name in cell B1.

functions20-47

Then order the results by the dates in column A in descending order.


Example 4 – Filter against various criteria

Here the admin manager wants to use the questionnaire feedback to see how good the information is that is given to the students when they sign up and how well the service was in the office. He particularly wants to know if there was any low feedback in any of the areas under his control, so wants to know if the course info, payment info, or office service was rated less than 3 by anyone.

functions20-15

To create the table above I’ve added the following formula in cell A1:

functions20-16

Here I’ve selected 5 columns and notice that I’ve put column B at the end. This shows that when selecting the columns, you don’t have to have them in the same order as the original data. This is extremely useful at times.

In the second part, I set the criteria, i.e. he’s looking for values which are less than 3 in each of the columns, L, M, and N. To include 3 different criteria, I’ve used the ‘or’ keyword, so that it will return rows if any one of them have a value of less than 3 in it.

As we can see in the table above, it’s found 3 results where the course information was rated poorly, and we can see from the level that it was related to level B1, so clearly some work is need there. Plus, there is one incident where the service in the office was rated poorly, which may need investigating.


Example 5a – Returning the averages of data

The director of the school wants to know if there are any classrooms that are rated more poorly than others. He wants to see if the average rating is different for any of the classrooms. In the table below, we can see that clearly, there is a problem with class A1, as it is rated poorly and much lower than the others.

functions20-17

To do this, I’ve added the following formula in cell A1:

functions20-18

This time I’m interested in the classrooms in column I and the average of the scores given in column J. First, I select column I, then select the average of column J, then I group them by classroom, in other words by column I.

We can return the average, count the number of entries, return a maximum or minimum in that column, or sum up the entries, using the following syntax:

functions20-34

Quite often these work with the ‘group by’ keyword, to be able to return the results.

Note, there is an empty row in row 2, as the data range is looking below the original data and into empty rows, and it will return one. This can be eliminated by stating the exact range of your data as we will see next, but the downside is that if more data is added the range will have to be updated.


Example 5b – Returning the averages of data and ordering them

Following on from the example above, we can adjust our returned information by sorting the feedback by the lowest to the highest, i.e. in ascending order. Here we can see class A1 is the lowest rated.

functions20-19

Here’s the formula I wrote in cell A1:

functions20-20

The first part is as before, then it’s followed by:

functions20-48

This orders the results by the average of column J (in column B), in ascending order.


Example 6 – Pivot information using QUERY not pivot tables

To finish off this first part, let’s look how we can pivot the information to see the averages of 2 criteria for each teacher. We want to look at the “is clear” and “is organised” categories. If you’re familiar with pivot tables, this works in a similar way, but with the bonus of doing everything right within the QUERY function.

Here the data has extracted the information below. It looks like Fred’s class organisation may need improving a little.

functions20-21

To get this output, I’ve written the following formula in cell A1:

functions20-22

This selects the average of column D (“is clear”) and the average of column E (“is organised”) as the criteria, then pivots it by teacher (column C), so that we see an average of each criteria for each teacher.

We could look at every criteria per teacher, just by adding the average for each criteria column, e.g. avg(F), avg(G), etc.


Analysing a HR database

OK let’s look at a different set of data now. Here we have employee database with some information about them.

functions20-23


Example 7 – Returning average salaries per department

The HR director wants to know what the average salary is per department from the data above. Here’s the end result:

functions20-24

In cell A1, I’ve written the following formula:

functions20-52

This selects the departments (column B), and the average of the salaries (column C) grouped by department.


Example 8a – Listing salaries per employee in descending order

Here he wants to see the salaries per person in descending order, without any of the other information.

functions20-25

In cell A1, I’ve written the following formula:

functions20-53

This selects columns A and C, and sorts column C in descending order.


Example 8b – Limiting the number of results

The HR director actually only wants to see the 5 highest salaries. We can use the formula and add a limit to it, to show the following:

functions20-27

Here’s the formula:

functions20-54

In the last part I’ve added ‘limit 5’. This returns the first 5 rows.


Example 9 – Ordering by more than 1 criteria

Let’s now look at how we can order our results by 2 or more criteria. Here the HR guy wants to see the employee names, their departments and salaries. He wants the data organised by department then by salary, with the salaries going from highest to lowest.

functions20-29

To do this, I’ve written the following formula:

functions20-30

This selects columns A, B, and C (employee, department, and salary), orders first by department (B), then by salary (C). Note the syntax, after ‘order by’ you just add the first column letter, then the second one after a comma.


Example 10 – Relabelling column headers

Finally, let’s look at how we can rename the column headers to something different from the original data. This can be useful, if the original data is from a computer output and the column headers aren’t in everyday English, or you may simply want to change them.

Here I’ve changed the column “Employee” to “Name” using the QUERY function.

functions20-35

To do this, yes you’ve guessed it, I’ve added the following formula in cell A1:

functions20-36

The new part is at the end, (label A ‘Name’). This tells it to rename column A with the word ‘Name’. To add more labels, just add a comma and the column letter and new name.


A couple of final comments about QUERY. Be careful where you place your QUERY function, as you need to make sure that there is nothing in the cells particularly below it, as otherwise it’ll throw an error.

The syntax is very exact, so make sure you notice in the examples, how the punctuation is used.

If you want to play around with the data in this post, here’s a link to the sheet, which will prompt you to make a copy of it:

https://docs.google.com/spreadsheets/d/1PcJhiNcLxPViyCuPuYkVODW7xSKK1T1538EJtYKDKGs/copy

Despite this being a long post, I’ve only scratched the surface as to what QUERY is capable of. To learn more go to Google’s page on the query language:

https://developers.google.com/chart/interactive/docs/querylanguage


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Flipboard / Twitter / Google+)