Save email to Drive add-on - get username

Overview blog post.

The following Google Apps Script code is the first to be run when the Gmail add-on is launched and is designed to prompt for a username of a student. The script is called via the 'onTriggerFunction' in the manifest file.

Save message to Drive add-on

We begin by building the card that requests this information and do so via the 'CardService.newCardBuilder' class, to which we need to create a 'newCardSection' to display our information. Next we create a 'textInput' widget to ask for the username:

var inputWidget = CardService.newTextInput()
    .setFieldName('userId')
    .setTitle('Enter username here')

Then add this to the section we created:

section.addWidget(inputWidget);

Save email to Drive add-on - overview

Over the next few blog posts I look to explore my development of a Gmail add-on that is designed to save the current open email in a specific Google Drive folder, by entering a value which corresponds to said folder.

Background
I was approached by a department a couple of months ago that were frustrated with saving student specific emails in a shared Team Drive folder. The process is quite cumbersome:

Open the email > click the Print all icon > change the printer to Save to Google Drive > click Save > now a PDF version of the email is in My Drive so you need to go in there and Move to the Team Drive folder ...

This was the current process for 1 cohort of students between about 4 members of staff, but they wanted to scale it up so that the next academic year they would have student folders for the new intake also, then subsequent years to come after that. Clearly this was going to become extremely time-consuming and needed a re-think.

So back in October I began to explore Gmail add-ons with a colleague, which use the concept of a card that contains a section composed of widgets to display information and interact via buttons. My initial exploration into this area (having only been learning Google Apps Script for about 1 month prior) was overwhelming and difficult to process. It was only once I got stuck into the meaty Student Feedback system did I really have the capacity to pick this up again and carry it into a completed project.

Batch get existing folder IDs & names - version 2.0

The following Google Apps Script is a revisit of this blog post for batch getting folder IDs and names from existing Google drive folders. I have made 2 changes/improvements:

1. The output array created at the end of the getFolders function is now 1-D. In reality it did not need to be a 2-D array (as in the original post) because it only contained one set of data. This also means we no longer need to specify output[0] when appending the data in the pasteData function.
2. Using 'appendRow' rather than 'getLastRow' - this was an extra unnecessary step as it meant we also needed to 'getRange' in order to use the last row. Now however data will always be written to the next empty row.

Here is an updated version of the code:

Get script time zone for a date

The following Google Apps Script is a quick example of a recent improvement I have learned when handling dates (example blog post of the old method).

Rather than defining the timeZone as GMT (or GMT+1 for daylight savings) in the 'formatDate' property, I will be starting to use 'getScriptTimeZone' when it is not bound to a Google sheet. This eliminates the issue of the script becoming inaccurate by 1 hour when the clocks go back/forward, as it will pick up the time zone from when/where the script is being run.

Get script time zone for a date.gs

Log actions performed by a user running a script

Overview
The following Google Apps Script code has been developed to provide a standalone function that you could include in your scripting projects to help determine which user ran it and what actions it has performed. It is one I hope to start embedding where suitable and is designed to output the information into a Google sheet - Date/Time, User, Action.

There have been a couple of instances where it would have been useful to know who and when a script was run, as part of diagnosing a fault. The function takes an input parameter of action which could simply be a variable with descriptive text at certain checkpoints in your script (particularly the start/end).

Example log output from script to spreadsheet

Get Gmail message attachment size

Overview
The following Google Apps Script code is designed to get the size of an attachment (in bytes) from each email message in a thread, within a specific label. I developed it as part of a project I am working on to bulk save emails to Google drive where I realised it is currently designed to save any attachments it finds as it goes through an email conversation. As people still send attachments via email a message thread may contain the same attachment more than once - which my script would save multiple copies of.

What I was exploring here was whether I could look for a difference in file size with the attachment to possibly indicate that it had been modified by a user and hence overwrite any existing version saved in Google drive, so there is only the one copy. The function does get the size of an attachment which (based on testing) varies if a document or spreadsheet was opened and 1 item changed, but I realised things were more complicated than that. A document could be opened and re-saved by an older version of a program (with no edits made) which could affect the file size, just because the size has changed does not mean that it is necessarily the version that wants to be saved either.

Batch get existing folder IDs & names

The following Google Apps Script code was developed to get the folder IDs of a large number of current Google drive folders along with parts of their name - for capturing in a spreadsheet. This was required for a project I am currently working on - to develop a Gmail add-on for saving an email into an existing drive folder. It is split into 2 functions.

The folders have a consistent naming convention:

• SURNAME, Forename (Preferred Forename) (Student No) (Username) - Subject

Example folders to extract details from

Split filename with 'brackets' and 'comma' by a regex

The following Google Apps Script code was developed as a means to extract specific parts of a Google drive folder name (shown in the next blog post) by using a regular expression (regex). It is possible to achieve the same effect by using 'split' more than once to query the folder name but I wanted to achieve it all within a single line.

Regex breakdown - /[( ),]+/  - https://regex101.com/

• //  start/end of regex.
• [ ]  match any character in the set.
• ( ),  match curly brackets and comma.
• +  match between 1 and any number of items.

An example folder name has been used in the code below to show you this concept.

Convert Doc to PDF and move into a new folder

The following Google Apps Script code was developed to create a PDF version of a Google Doc, move it into a new folder, remove its parents so it only existed in the new folder (typically also exists in My Drive) and then trash the Doc. I first used this tool during the Exceptional Circumstances system, but have used it again during a script for saving emails from Gmail so I thought it was worth sharing on its own here.

In my example I have used fictional IDs but you would get these directly from existing files/folders via the 'DriveApp'. Firstly I get the Doc via 'getFileById' then 'getAs' PDF and 'createFile' so I now have a PDF version. Now I can 'getParents' to find the current location of where the PDF exists in Google Drive - and this is usually My Drive. Next I move the PDF file into the new folder location ('addFile') and then loop through its parent locations, removing it from there ('removeFile') so it only exists in the new location. By getting the parent locations before moving the file I ensure that the new folder I move it to is not included and hence the PDF will remain there.

The final step is to delete the original Google Doc file ('setTrashed').

Convert a string into a date format

The following Google Apps Script code was used to convert the date acquired from an email via 'getDate' into a more suitable format that was used as the filename when saving the email.

For example purposes I have used the date string from an email directly in the code below. Once I create a new date variable I use 'formatDate' to strip out the time and convert it into YYYY_MM_dd so giving me a value of 2018_12_20 in this instance. I use underscores rather than full stops as I hit a problem when converting the file to PDF with full stops - Google drive thought they were part of the extension and so I would end up with a filename of only 2018.12.pdf.

Convert text from lowercase to uppercase

The following JavaScript code was developed to convert a surname value in a spreadsheet from lowercase 'toUpperCase'. The reason for this was that the uppercase version was used as part of of a subsequent filename in Google drive.

In the example below I have just created a variable directly in the code, but it is likely you will get this from elsewhere, eg a spreadsheet.

Student Feedback system - overview

Over the last few blog posts I have aimed to break down the student feedback system that was developed for the Law department. Here I look to summarise the project for you and bring it all together with the necessary links so you have the resources to download a version of the system yourself.

Outline
The department was assessing student contribution to learning activities and wanted to have a process for giving students some feedback at the end of each term. Tutors were already using a Google form to write up notes for files however this did not go to students, something they wanted the solution to do. This process needed to be automated to save tutor/admin time and it needed to account for a student having two tutors who would want to be able to collaborate (not something a Google form allows). The solution also needed to be flexible for part-time tutors to complete it - so online and available from any location.

Student Feedback system - create student documents

The final stage of the student feedback system involved creating individual Google docs for each student, containing their feedback, to be archived on the network and linked to in the spreadsheet.

The following Google Apps Script does this by getting all of the data from the Group Feedback sheet, along with the ID of the template Google doc (config sheet) which will be copied and edited for each student (a process similar to this blog post). We will also need to access the master spreadsheet to create a link to each students' doc against their name, so we get this via 'openById' at the beginning of the script to be able to scan through and use it later on.

I wanted all of the documents to go inside a specific folder for each Group, so next I 'split' the filename (got via 'getName') and extract the first part of it (so 'A1', 'B1', 'C1' ...). I then use the 'DriveApp' to 'createFolder' inside of the main feedback Groups folder with the extracted Group name, descriptive text and a term time value acquired from the config sheet that administrator can update as necessary.

Student Feedback system - send emails

The following Google Apps Script code is designed to get student feedback from the spreadsheet so it can be emailed to each student in-turn. It then updates the master Google sheet with an acknowledgement that emails for a Group have been sent.

Example of Email Sent? column with timestamp

To launch the function a menu item called Feedback > Send Student Email is used to call the sendEmailsConfirm function. I created this as a check for the user to ensure they intended to send emails rather than accidentally clicking it (as you will see in a later post there is more than one item in this menu in the final product).

Student Feedback system - HTML form - part 3

Continuing from Student Feedback system - HTML form - part 2 we now look at the final function to return data from the form to the spreadsheet.

3. writeBack

Example completed student feedback

The code takes an input of data from the form which it then assigns to variables as we extract the required items. In deciding which data to write back to the row for the student feedback we evaluate the value of whoAmI (from getActiveUser) to tutor 1, tutor 2 or neither (in which case the whole row of data is written to as the assumption is an administrator). Depending on which option it is determines the array of values we assign from the form and to which columns ('setValues').

Student Feedback system - HTML form - part 2

Continuing from Student Feedback system - HTML form - part 1 we now look at the form itself. We have the relevant data from the getValues function wrapped up in a suitable json string that can be 'parse'd back into JavaScript via the HTML.

2. htmlForm

HTML Student Feedback Form

Student Feedback system - HTML form - part 1

This section focuses on the main part of the student feedback system designed for the Law department - the HTML form used by tutors to provide student feedback, as such it has been broken down into 3 parts:

1. getValues - the function designed to lookup the name of a student and gather their corresponding feedback (if any) to pass this through to the HTML form.
2. htmlForm - the form which provides a user-friendly way to add/change student feedback data through dropdown and free-text boxes.
3. writeBack - the function that takes the data from the submitted form and puts it back into the spreadsheet depending on which tutor has completed it.

Student Feedback system - get active spreadsheet user

The following Google Apps Script code was developed to determine if a tutor accessing a Google sheet was tutor 1 or 2, which would later display options on a form they could edit. The tool achieves this by getting their email address from the active session, looking up their name in a table, then checking this against a particular column. The result is a variable set to You are 1 or You are 2 which I then used in an if statement for the rest of the script.

We start by getting the email address from the active tutor ('getActiveUser') logged into the spreadsheet. We then get details from the Tutors sheet (which was populated during the Bulk add Drive permissions post). Now the relevant tutor name can be acquired from an email address if match (see Lookup email address post for further details). Back on the Group Feedback tab it is necessary to 'split' the Tutor 1: and Tutor 2: strings in turn so that we just have the names (so John Smith rather than Tutor 1: John Smith as in my example). A simple if can now determine whether there is a match and return the tutorName variable.

Student Feedback system - bulk add Drive permissions

Following on from Bulk create Group spreadsheets we can now look at adding permissions to allow the relevant tutors to have access to the files. The following Google Apps Script code is designed to:

• Lookup the two tutors belonging to a Group and find their email address.
• Extract the file ID from the Url that links to this Groups spreadsheet and add them as editors.
• Open the Group spreadsheet and add the tutors to the relevant column headings and Tutors sheet tab.

Lookup the tutors
The first part of the script starts by looping through a Group at a time (so 'A1' then 'B1', etc) and it creates another loop inside this to handle the two tutors in-turn, it then creates a third loop that looks-up a matching tutor name to find their corresponding email address (a process detailed in this blog post). Once we have the tutor name / email address they can be pushed into an array for later use.

Student Feedback system - bulk create Group spreadsheets

The following Google Apps Script code was developed as part of a larger student feedback system and is designed to create a number of Group spreadsheets (eg 'A1', 'B1', 'C1') with the relevant students (listed in a spreadsheet) contained within them. So at its peak it went down a list of 400+ students, creating a spreadsheet for 36 individual Groups, then pulling through only the students that belonged in each of those Groups.

Students and their Groups

Group spreadsheets containing relevant students

Data Validation - check for blank cells

The following Google Apps Script code was developed as a tool to check that all the necessary data existed in a spreadsheet before another function was triggered. The reason for this was that if any data was missing it would affect the student Group files I was creating (eg no email address or name) that would eventually prevent further functions from running at a later date. So I needed a way to go through a sheet and ultimately make sure there were no blank cells, at the same time however I wanted to produce a user-friendly message if there was a blank (rather than just preventing another function from running).

I developed the below validation tool to go through each column in turn and down the row looking for a blank (it checks about 1,700+ individual cells in the project I use it). If a blank cell is found the code identifies the column/row and calls a popup function to run, then returns a flag value to determine how the parent function should behave.