Library Subject Guides
In the fifth and final part of the course, we'll look at some useful extra features of Apps Script you can use to further your code and explore where you can go next with your Apps Script coding.
Work through the page in order if you're learning, or use the links below to jump to specific bits:
So far in the course, we have learnt how to work with spreadsheets, documents, and forms, how to automate using ifs and loops, and how to use triggers to make Apps Script code run as a result of events. As you'll have seen from the projects, this can allow us to do quite a lot with Apps Script, automating things like creating documents or having things happen when a form is submitted.
Along the way, we've also looked at a range of JavaScript concepts that are useful for Apps Script, like variables, arrays, loops, if conditions, and objects. There's been a lot of pieces of the puzzle to put together!
For this final part of the course, we will be focusing on some additional elements and features that can make your code go further and allow you to improve the kinds of things you can now build using skills from the previous weeks.
Throughout this course, we've shown you one or two ways of writing things in Apps Script. As you've done the projects, you might have found different ways to do things to how we chose to do it - that's great! In coding there's often many ways to do things and it is good to explore different options.
Apps Script code can be "improved" by making it more efficient to run, by making it easier for users to interact with the system, or by making it less likely to break or have errors. As you develop your Apps Script skills, you'll pick up ways of doing this through experience (and getting other people to test what you've made).
When creating Apps Script projects, there are many ways you might tweak and improve your code, maybe as you learn more or try out different things. For example:
You can use the Execution log to see how long scripts take to run. This can be useful if you're trying out different ways of writing code for something to see which is most efficient.
You might want your Apps Script code to send an email, for example to notify someone of something happening like a document being created or a date being matched in a spreadsheet. When you add sharing permissions to a Google Drive file using Apps Script (as we'll look at in the next part of this section) the user will automatically get an email by default, but otherwise you will need to always write Apps Script code whenever you want an email to be sent.
There are two ways to work with email in Apps Script:
These two apps have different features, and the MailApp is the one you need to send an email, so that's the one we will be looking at.
There are only two commands built into the MailApp: sendEmail() and getRemainingDailyQuota(). Unsurprisingly, the one we need to use to send emails is sendEmail().
There are a range of parameters you can put inside the brackets of sendEmail() and you can view them all of the Apps Script Developers Reference page for MailApp. The most useful one is:
MailApp.sendEmail(recipient, subject, body, options);
where:
Typically, instead of writing all of these things inside the brackets of sendEmail(), you will store them inside variables and then put the variable names inside the brackets instead. Here's an example of doing this:
var emailAddress = 'test@email.com';
var subject = 'Helpful email';
var body = 'This is a helpful email.';
var options = {noReply:true; cc: 'another-test@email.com'};
MailApp.sendEmail(emailAddress, subject, body, options);
When using MailApp, you must create a text string containing your entire email message. It is easier to break this into sections using += to add more content to the body variable, so it is easier to read in the Apps Script editor. If you want line breaks in your email (as you probably do), then you have to use \n, which means "new line" and is like hitting Enter on the keyboard (the backslash \ is an "escape character" to tell Apps Script the next value isn't text, and the n means "new line"). For example:
var emailBody = "Dear user \n\n";
mailBody =+ "This is an automated email to say that a script did something good. \n\n";
mailBody =+ "All the best, the person who wrote this script";
In this livecoding exercise, we are going to use Apps Script to send an email.
Google Drive works using sharing permissions to control who has access to files, so you can use Apps Script to add or remove permissions to files or folders. This can be useful when creating new documents or folders using Apps Script, as you may want them to be shared with specific people.
You don't always need to set the permissions on an individual file or folder level. Google Drive uses inheritance for sharing permissions, which means that if you set sharing permissions for a folder, all files and folders inside will inherit the same permissions. So you can share a folder and then create files inside it with Apps Script, and those files will all be shared with anyone the folder is shared with. You can also create files and folders on a Shared drive and anyone with access to that Shared drive will have access to the files and folders.
To set permissions, you must use DriveApp because you are working with a file or folder, and like with making a copy, you need to get the file first, using its ID. So you would use DriveApp.getFileById() or DriveApp.getFolderById() first, and store that in a variable. Then, you take that variable and use the .addViewer(), .addCommenter(), or .addEditor() command with an email address inside the brackets to set permissions.
// get the file and folder
var theFile = DriveApp.getFileById(docID);
var theFolder = DriveApp.getFolderById(folderID);
// add sharing permissions to the file and folder
theFile.addViewer(emailAddress);
theFile.addCommenter(secondEmailAddress);
theFile.addCommenter(thirdEmailAddress);
theFolder.addViewer(fourthEmailAddress);
theFolder.addCommenter(fifthEmailAddress);
theFolder.addEditor(sixthEmailAddress);
There are also plural versions of these commands (e.g. .addEditors(): which needs an array of email addresses rather than a single email address inside the brackets.
If a user already has access to a file or folder, adding higher permissions will "promote" their access (e.g. they already have View access, but they are given Edit access using Apps Script, they will be "promoted" to having Edit access).
Just like in Google Drive itself, you can remove access to files and folders as well as grant it. It works exactly the same, except you use .removeViewer(), .removeCommenter(), or .removeEditor(); with the email address of the user to remove inside the brackets.
theFile.removeViewer(emailAddress);
theFolder.removeEditor(sixthEmailAddress);
In this livecoding exercise, we are going to learn how to add sharing permissions to items in Google Drive using Apps Script.
Though it sounds like a tasty breakfast, a toast in Apps Script is a type of alert you can create to show a popup window in the lower right hand corner of a spreadsheet containing a message (unlike the example below, there is not actually a picture of toast involved).
Toasts are useful when you want to display to the user the progress that the script is making, for example if they are running code using a menu in a spreadsheet and you want them to know that documents are being created or something has been done behind the scenes. Is is particularly helpful when you are looping through data, as it can take a while for the spreadsheet to update and people might not be sure if the script is running.
To create a toast, you need the active spreadsheet, which you are likely to already have stored in a variable called ss. If so, the syntax is:
ss.toast('string message', 'string title')
You need to include the string message, but you don't have to include a string title if you don't want. Notice the order - if you want to have a title and a message, you write the message first, so it may look like you are writing it backwards!
In this livecoding exercise, we are going to look at how to create 'toast' notifications to keep the user informed about the script's progress in a spreadsheet.
You may have noticed that throughout this course, we haven't dealt with Google Calendar events or anything else that works with dates. That's not because we forgot about them, or didn't notice that Project 4 involves arranging events - it is because working with dates and times in Apps Script can be quite tricky, so it can be useful to get your head round the basics before you start working with dates and times. We'll take a look at an introduction to working with dates and times (and Calendar events) here, but you will probably find that you will need to read more about JavaScript dates online if you're going further with date options (like comparing dates).
If you're not looking to do anything using dates or Google Calendar right now, feel free to skip this box for now, and return to it when you're ready - dates and times are really the next step in Apps Script.
When working with dates and times, we will be using JavaScript Date Objects. These are a type of data in JavaScript that can store dates and times, and as they are a feature of JavaScript rather than Apps Script specifically, you can find lots of useful guidance online about how these work. We'll look at the basics, but you might want to also look at the w3schools guidance on JavaScript dates, starting with the Date Objects:
To create a new date in Apps Script (and JavaScript) you can use the command new Date(), either with nothing inside the brackets (that creates a date object that has the current date and time) or with other pieces of information in the brackets to create a specific date, like a date string (text formatted as a date) or a specific list of year, month, day, hours, minutes, seconds etc.
var todayDate = new Date();
A date in Apps Script has a specific default format, which for 08/01/2024 would look something like Mon Jan 08 11:30:34 GMT+00:00 2024. If you ran the code above and then logged that value, it would be in this format. We'll look at how you can change the format for more user-friendly outputs soon.
You might find that the date and/or time you get back is incorrect, because your Apps Script project is set to the wrong timezone. To change this, click on the gear on the lefthand side of the page to go to Project Settings and then adjust the timezone as needed.
To create a date object out of a specific date that isn't the current one, you can supply information inside the brackets of the new Date() command. For example, you can give existing dates that you might have elsewhere, such as in a spreadsheet of data or collected from a form. These are in string format, so must be inside quotation marks. There are specific date input formats you can use in Apps Script/JavaScript that it will be able to 'read' and turn into a date object. That w3schools page linked has in-depth guidance on different input formats you can use.
var oneDate = new Date("2024-01-02");
var anotherDate = new Date("04/03/2023");
var dateAndTime = new Date("January 11, 2024 10:30:00");
var anotherDateAndTime = new Date("2024-08-15T10:00:00");
You'll notice that 2024-08-15T10:00:00 is quite a weird looking date and time! It is an ISO Date-Time format and the date and the time must be separated by a capital T. See w3Schools's JavaScript date format guidance for more on this format.
Whichever format a date string has, when you turn it into a Date Object using new Date(), it will have the output format above (Mon Jan 08 11:30:34 GMT+00:00 2024) unless you set it otherwise.
As we've seen, when you create a date object the default format is not particularly user-friendly, like Mon Jan 08 11:30:34 GMT+00:00 2024. If you want to write that date back into a document or email or spreadsheet, for example, then you might want it to be in a more user-friendly format, something that people might expect to see and read as a date (e.g. 08/01/24).
To do this, we can use the Apps Script Utilities service, with the command Utilities.formatDate(). The full syntax might be:
Utilities.formatDate(new Date(suppliedDate), timeZone, format);
Where:
By doing this, you can create whatever date format you need when you have a date object. It is particularly useful when logging the date/time that something happened, as you can create a date object of the current date and time, and then format it in a more readable way for any users of the system.
If you're working on an Apps Script project that works a lot with dates and times, you can also create a little function that can convert a date to a specified format. Here's an example of one you could use:
function formatDate(suppliedDate, format){
//find script timezone
var timeZone = Session.getScriptTimeZone();
//generate formatted date
var formattedDate = Utilities.formatDate(new Date(suppliedDate), timezone, format);
//return formatted date to function that called this one
return formattedDate
}
And you could call this function in other functions, for example (with date being a variable containing a date):
var formattedDate = formatDate(date, 'dd/MM/yyyy');
Once you have a date object, you can get and set parts of the date object, for example the hours, minutes, seconds, year. The commands to do this are pretty self-explanatory in terms of naming: getHours(), getMinutes(), getSeconds, getFullYear, etc, and setHours(), setMinutes(), and so on...
These commands allow you to modify dates and times in various ways. You might remove the time part of a date (by setting them to 0) to be able to compare two dates, as date objects contain times as well as dates so the times must match if you want to use comparison operators like ==.
Now, we have enough pieces of the puzzle to be able to create a Google Calendar event using Apps Script. The CalendarApp allows you to work with Google Calendar, and the process is similar to working with other apps: you must get the calendar you want to create the event in using its ID, and then using the createEvent() command on that calendar, supplying the relevant information.
You can find the ID of a Google Calendar by going into Calendar's Settings, then selecting the calendar in question from the list on the left-hand side and scrolling down to the Integrate calendar section to find Calendar ID. For the main calendar connected to a Google account, this will be the email address of the user in question. Just like with a file or folder, store this ID in a variable in your Apps Script code, to use with the CalendarApp.getCalendarById() command.
// store calendar ID in a variable
var calendarID = 'test@gmail.com';
// get the calendar using its ID
var calendar = CalendarApp.getCalendarById(calendarId);
Once you have the calendar stored in a variable, you can do various things with that calendar, including using .createEvent() to make a calendar event. createEvent() needs an event title, a start date-time and an end date-time (you can create these in various ways, but using the ISO Date-Time format can be easiest initially - see the guidance about creating a new date object above), plus optionally additional options stored as a JavaScript object similarly to using sendEmail(). The options can include a description, location, guests, and whether invite emails are sent to any guests. You can read more about this on the Apps Script developers reference site section for createEvent().
You can create a calendar event without storing anything as a variable, or you can get the ID of the event, similarly to getting the ID of a document copy, so that you could write this back into a spreadsheet or store it in case the event needs to be accessed or deleted.
//create calendar event without variable
calendar.createEvent('My Event Title', startDateTime, endDateTime);
//create calendar event and store ID in a variable (and having event title as a separate variable)
var eventID = calendar.createEvent(eventTitle, startDateTime, endDateTime).getId();
//create calendar event with additional options
var eventDescription = "This will be a great event."
var location = "The big room";
var guests = "guest1@test.com,guest2@test.com";
var options = {'description':eventDescription,'location':location,'guests':guests,'sendInvites':true};
var eventID = calendar.createEvent(eventTitle, startDateTime, endDateTime, options).getId();
You may find that sometimes you don't quite get your event right the first time, which is why it can be useful to have the ID that you can then use to delete the event if you cannot find it manually in Google Calendar (or you can search Google Calendar for it if you gave the event a distinctive title). The first date in JavaScript is January 1 1970 (as all dates are stored as a number of milliseconds after that date), so sometimes incorrectly set up calendar events will find themselves happening then!
You can also create calendar events using the Calendar API, which allows you to modify more of the settings for the Calendar event, such as adding video conferencing and setting if guests can modify the event or invite others. See these blog posts on create a Google Calendar Event via the Calendar API and Bulk create Google Calendar events with optional Meet or Zoom for in-depth examples of doing this (but be warned that these are more complex).
In this livecoding exercise, we are going to look at how to create calendar events using Apps Script.
When creating an Apps Script project (that is, writing Apps Script code to do something), it is important to document what you do. Writing out details like what the code does, which files are used with it, and any changes you've made since then will help you in the future to understand what the code was designed to do, so you'll be able to update it if needed or fix it if it breaks.
Writing comments in your code, as we discussed earlier in the course, is an important part of this process, but it is also helpful to create some kind of separate document with more in-depth information about the Apps Script code and the files used by it. This document can be shared with others using the code and passed on to other people if you want to hand over the Apps Script project to them.
Here's one example of a Google Doc template for writing up an Apps Script project. You might find a different format - like a spreadsheet - more helpful to you, or you might want to capture different information about your project.
If your Apps Script project is designed for other people to use, but not be involved with the coding part, then you should also write instructions on how they should use it. For example, if the user needs to go to a custom menu item in a spreadsheet to run the Apps Script code and needs to make sure certain fields are filled out first, give this in a document or on a sheet on that spreadsheet as clear step-by-step instructions. Doing this will help with user errors!
If you've been working through this guide, you've made it to the end of the course - congratulations! But where could you go next?
If you're not starting thinking about this already, the next step is to think of projects you would like to create yourself using Apps Script and start planning out what they would do and how they might work. Use the example projects from this course to help you to consider all the things you might need in your code, and remember that every Apps Script project is different so you might have different requirements. You can always return to this site to remind yourself of anything you need!
If you're looking for inspiration for projects or examples of what else you can do with Apps Script, then this blog by a member of the DISC team from the University of York (who you might recognise from some of the livecoding videos) shares a range of examples of Apps Script code:
As you go further with Apps Script, don't forget that Google's Apps Script Developers reference guide is very useful for knowing what you can do and how commands work.
You might also want to go further with JavaScript, which will help you with some of the features of Apps Script and also allow you to branch out into other web coding as well.
Finally, if you're a member of the University of York and you have any questions about Apps Script, you can get in touch via itsupport@york.ac.uk or join the #apps-script-chat public channel on Slack.
And if you have any feedback/comments about this guide, get in touch via itsupport@york.ac.uk