Organization

Creating a Google Apps script

First, keep in mind that Google apps script is Javascript based. You don’t have to be an expert programmer, but you do have to master the syntax of the language. Therefore, if you don’t know the syntax of Javascript, I recommend you access the following link and perform the proposed activities. There are 16 modules that can be made in about 10 hours, but not all of them have to be made. The first 5 or 6 is enough. http://www.codecademy.com/tracks/javascript

Once we know the syntax of Javscript, let’s focus on an example script. In this case, we will make a script to get a form to reach the respondent by email. On my centre’s website we have a form in case someone from the educational community wants to send a complaint or suggestion to the address. It is a form made with Google that only has 4 fields: Name and Surname / Mailing address / Course (only if you are a student or family) / Description of the complaint or suggestion. What the script I want to submit must do is send a copy of the answers to the person who answers the form. If you have a GAFE this can be done by forcing the user to log in, but in this case we want the form to be answered by people who are not in our GAFE (families, companies…). First, of course, you need to create the Google form and access the spreadsheet where the answers to the form will end up. Within this spreadsheet, we will access Tools / Script Editor

script1

We’ll create a blank script and write the following code:

script2

/**
* Send the answers by mail 
*/ 

function onChange() { 
//Prepar begin of mail
var cap="Se ha enviado su sugerencia o queja correctamente. A continuación se adjuntan sus respuestas:"; 
var asunto= "Confirmación queja o sugerencia"; 

var cuerpomensaje=cap; 
var dest = ""; 
var camp = ""; 
var libroActual = SpreadsheetApp.getActiveSpreadsheet(); 
var hojaresp = libroActual.getSheets()[0]; 
var rangresp = hojaresp.getDataRange(); 
var ul_fila= rangresp.getNumRows(); 
var encontrado=0; 
//Look at the last row in the answers sheet
for (j=1; j<rangresp.getNumColumns()+1;j++){ 
camp = rangresp.getCell(ul_fila,j).getValue(); 
for (i=0;i<camp.length;i++){ if (camp.charAt(i)==='@'){ 
encontrado=1; 
} 
}
if (encontrado===1){ 
dest=camp; 
encontrado=0; 
} else { 
cuerpomensaje= cuerpomensaje+rangresp.getCell(1,j).getValue()+": "+camp+""; 
}
}
//Mandamos el mail 
GmailApp.sendEmail(dest, asunto, '',{ htmlBody: cuerpomensaje}); 
};

Let’s take a look at this code and see some interesting links to go deeper. The startup only serves to define variables (Mail subject to be sent / Mail header) and other programming variables. There are three specific ones to mention:

var libroActual = SpreadsheetApp.getActiveSpreadsheet(); 
var hojaresp = libroActual.getSheets()[0]; 
var rangresp = hojaresp.getDataRange();

To understand these variables, we’ll do very well with Google’s help. It is located in https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app
They show us all the objects of Google applications and their properties. In the variables that I have defined in the script, the CurrentBook variable is an entire calculation book. Specifically the active book. The variable hojaresp, is the first spreadsheet of the book. The variable rangresp is a set of cells, all of which have data. We could have directly obtained the last variable, rangresp, by typing

var rangresp = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getDataRange()

but that much at the outset might have been harder to understand. All scripts are based on this, objects and properties. With the help of Google, we can find anything from Google applications (cell values, email recipients, number of rows, text from a document…). Following the example,

var ul_fila= rangresp.getNumRows(); 
var encontrado=0; 
//look at the last row of answers sheet
for (j=1; j<rangresp.getNumColumns()+1;j++){ 
camp = rangresp.getCell(ul_fila,j).getValue(); 
for (i=0;i<camp.length;i++){ if (camp.charAt(i)==='@'){ 
encontrado=1; 
} 
}
if (encontrado===1){ 
dest=camp; 
encontrado=0; 
} else { 
cuerpomensaje= cuerpomensaje+rangresp.getCell(1,j).getValue()+": "+camp+""; 
}
}

we looked at cell by cell in the back row. To do this, we define an ul_fila variable that is the number of rows in the range with data. The last one has the answer we want to send. We can look at the value of each cell by making a loop for, so we will look at all the cells. To access its value, we use the getCell (to set the cell) and getValue (to get the value) properties. Since we don’t know in which field the user will indicate the mail, what we do is to look at letter by letter if that cell has the @ symbol. To do this, we use a second for, which compares each letter with the @.

Now all that’s left is to create the body of the message, where we’ll put the name of the field (which is in the first row) and then the answer.

The script ends by sending the email

//Send mail
GmailApp.sendEmail(dest, asunto, '',{ htmlBody: cuerpomensaje});

To do this we use another Google object. It’s not spreadsheet, it’s mail. In another section of the Google help page we can find Gmail objects: https://developers.google.com/apps-script/reference/gmail/

Once we have made the script and understood how it works, we just need to program it to run every time someone answers the form. We’ll do it with the triggers.

script4

We will configure the script we just created to run every time someone answers the form.

script5

Here it is important which user is activating the trigger, since this will be the user sending the mail. If we are creating the script with a teacher user, but we want the mail to be sent to a center user, we must share the sheet with the center user in edit mode and let it trigger the trigger.

If you want to see how this script works in practice, then there is the link to a form below. If it is answered, I will receive a confirmation email from you.

https://docs.google.com/a/insestatut.cat/forms/d/1k_0VSP6qP0S5f66MWe0vwztw-EzJi4sXVcFRhS14zx8/viewform

I think that Google apps scripts are a great tool, as they can be useful for many operations (copy data between sheets, combine information from documentaries and spreadsheets and send it by email…). If you know something about programming it is not difficult to get into its way of working and you can do whatever you want. They are ideal for automating otherwise manual processes.

Use G Suite calendars to reserve classrooms and resources

The configuration process is not easy, but once the initial configuration is done, the subsequent use is simple.

Initial configuration


The G Suiet administrator must enter the control panel and access the calendar properties. There you will have the Resources tab where you can create a calendar for a resource. In the example, I have created a calendar for a computer classroom.

calendar2

Once created, the administrator is the one who must share it. To do this, you must first find it, a task that will not be easy.

You must access your calendars and select the arrow to the right of Other calendars. And choose the Navigate interesting calendars option:

calendar3

Now you must select the More tab and choose Interesting resources for domain name.

calendar4

Finally, you will see the list of resource calendars you have created. All you have to do is subscribe. If you do, you’ll already have the calendar added to your calendars, just like a regular calendar. If there are times when the room is already occupied by regular classes, the administrator can add them in the usual way as events are added.

Now it’s time to share it with all the teachers at the school. It must be done in the same way as any other calendar and permission will be given to view all event information.

If teachers are shared with teachers one by one, they will be shown directly on their calendars in the Other calendars section. If it is shared with a Google group, teachers will need to follow the same procedure as the administrator to view it (navigate through the interesting calendars, tab more…), although it is not necessary for teachers to add the calendar in order to reserve resources.

So far the initial configuration.

Use of resource calendars


When a teacher wants to book a classroom, he or she must create an event on his or her calendar, on the day and time when he or she wants to make the reservation.

If you edit the event, on the right side you can choose the Rooms option. If the classroom is free at that time, you can select it and when you save the event will be reserved.

calendar5

If the classroom was already booked by another teacher, the classroom would not appear.

As I said, using the calendar is very simple, although teacher training will be necessary. But it’s worth it, because I think it’s important to have the management tools centralized in the same platform. It simplifies things for teachers.

Google Drive: Share folders or documents?

The idea is very simple. Director, for example, creates a folder structure and assigns permissions to each folder (starting from the top folder, as permissions are inherited from the top folder). An example would be:

  • Course 2013-2014
    • Groups
    • Timtables
    • RED (reuniones de equipos docentes)
      • 1r ESO
      • 2n ESO
      • 3r ESO
      • 4t ESO
    • Guardias
    • Creditos de síntesis
      • 1r ESO
      • 2n ESO
      • 3r ESO
    •  …

The folder to be shared is Course 2013-2014 and, if the permissions have been assigned, the other folders will be automatically accessible. If the Classes 2013-2014 folder is shared with all teachers, better through a group (Sharing documents and calendar with Google groups), everyone will receive an email with the link. When you open the folder, they will see its contents and a button that allows you to add this folder to your drive, to have it always located easily.

gdrive1

It will no longer be necessary to share the documents individually. By placing them in the corresponding folder, everyone will have immediate and organized access.

When a document is hung up, no mail is sent. If you want to notify, you will have to use the Send an email to the collaborators option, which is located inside the More button (if the document has been selected).

gdrive2

In the same way, you can create folders for Departments, Student Groups, etc.

In addition, if a new teacher joins the centre, you just need to provide him/her with the link to the initial folder and he/she will automatically have all the previous documents sorted in perfect order.

I think it’s a simple way for everyone to keep their documents in order.

Specific users for institute positions

At first, this may seem a bit chaotic, but in the long run it will bring improvements to the organization, especially for two reasons. The first is that changing people in office will be much better. And the second is that the person holding a position will have much more organized documents and mail.

And even more so, considering that these mails are not only used for the mail but also for all Google services.
Teachers should be well trained to know which mail to use at all times. I insist, not only to send mails but also to share documents. But if we do, we’ll avoid a lot of permit changes and lost documents.
At the beginning of the course, the passwords of the users of the positions are changed and given to the teachers who will occupy the position in question that year. Without having to do anything else, that teacher can automatically:

  • Access all documents previously created by the position user.
  • Edit documents that, as a teacher, I only had permission to view.
  • Modify Google Groups where the charge user is the owner.
  • View and modify sites that have been created with the user in the position.
  • Modify calendars that I could only view as a teacher.
  • ….
It is important to highlight teacher training when sharing files. It is necessary to be very clear if it is shared with a teacher or with the position held by a teacher, because if it is mixed, then chaos can be monumental.
For example, if the director of a center is a teacher of Natural Sciences, we must use his nominal mail (nombreprofesor@nombreinstituto.es) for those mail and documents internal to the department. It would also be a good idea to use the teacher’s nominal mail to share information with students. By contrast, the post office user should be used by post and site organization documents, direccion@nombreinstituto.es.
Having Google Drive well organized, using groups to share documentation and calendars and using users for fees, the start of the course should be very simple, and we should not have any problem with people and charges that do not have permission to view documents or calendars they need.

Share documents and calendars with Google Groups

What are google groups?
Anyone who has a google user, either general (gmail) or from their own domain (a google apps for education), can sign in to Google Groups. https://groups.google.com

Groups can be created there, which are nothing more than user lists. For example, we could create a group of teachers from all the faculty at claustre@nombreinstituto.es.
These groups allow for several functions, but the most common are two: 1) Sending emails to many people by entering only one address 2) Sharing documents and calendars with many people with one address.
This second feature is the one I will deal with in this article.

First, a note. If a user creates groups, these groups are only seen by him. Therefore, each teacher should create his or her own groups. This would mean that working with groups would be impractical, since every time there is an incorporation all teachers should modify the groups.
If you have Google apps for education, these groups can be created by the administrator. This way, all users in the organization can use them and only one person needs to do the maintenance. In addition, the administrator can designate for each group an owner user, who will be responsible for administering it.

But let’s assume the groups are already created. We can have groups for many things: for all the teachers of a cloister, for a teaching team, for a group of students, the heads of department…

What are the peculiarities of sharing with groups?
Sharing with groups is very simple. We enter the document we want to share, choose the Share button and add the e-mail address of the group. All members will receive the link by email and have access.
The problem arises when someone is added to a group. Suppose a new student or a long-term substitute teacher arrives. The corresponding administrator enters the corresponding group and adds the new user.
The problem is that, even though the new user is in the group and despite the group having shared documents, the new user does not have access.

I don’t know what the reason for this is, but I talked to the people at Google Ireland and they confirmed it to me. When a document is shared (same with calendars) with a group, group members will only have access if the first time they access through the link.
This, the first time it usually happens, as the document link is sent by mail to everyone. But if someone deleted the mail without following the link, it would be the same as new users. As much as I wanted, I couldn’t access the document. Searching for it with the search tool would not work, nor would searching for it in the All Documents folder.
Once you follow the link once, then it appears in the search engine and in the All Documents folder, but not before.

What solutions do we have?

After thinking about it, the easiest solution I’ve found is to share folders instead of documents. You create a folder, for example, with the name Course Documents. From there, the subfolders are created as needed. Guards, Teacher Team Meetings, Assessments…

Instead of sharing documents one by one, folders are shared. In fact, sharing the highest level folder is enough. And this folder is shared exclusively to groups.
Each subfolder is shared back to groups with specific permissions.

We can now hang all the documents we want inside the subfolders that will take the permissions of the folder where they are.

This way, we can make sure that when a new user comes, we only have to send him a single link. The one with the highest level folder.
By following this link, you will see all subfolders and all documents (with permission).

If you use calendars, here we have no choice but to send you a link for each of your shared calendars. But, usually, a center doesn’t have too many calendars.

To put it all together, in our centre we ended up creating a Google Site (we have called the Teacher’s Desk) where all the information a teacher needs is located. We have provided links to calendars, shared folders, course dates, direct links to publishers’ digital platforms, the most usual websites, information from teaching teams…

On the centre’s website we have placed the link to this site and when a new teacher arrives, we give him/her an email from our domain name, we put him/her in the corresponding group and explain that he/she enters through the centre’s website to the Desktop. And he finds everything there.