Search Google Calendar event for Guests

The following Google Apps Script is designed to search a specified Google Calendar event and extract details about the Guests attached to it (eg their email address and status). The script is standalone so it only logs the output but you could copy it into a Google Sheet and write the data into the spreadsheet.

The 'CalendarApp' is used to perform the task and we start by getting hold of the relevant event, which for the purposes of demonstrating is hard-coded into the Apps Script but is likely to be taken from a spreadsheet in reality.

var event = CalendarApp.getEventById(eventId);

Next we want to get a list of the Guests which will be returned as an array.

var guestList = event.getGuestList();

Finally we need to loop through each Guest and extract their email address and status.

for (var i=0; i<guestList.length; i++) {
    var guest = guestList[i];
    var guestEmail = guest.getEmail();
    var guestStatus = guest.getGuestStatus();
}

Search Google Calendar for event details

The following Google Apps Script is designed to search through a given date on Google Calendar, look through the events and then extract their Title & Id. The script is standalone so it only logs the output but you could copy it into a Google Sheet and write the data into the spreadsheet.

The 'CalendarApp' is used to perform the task and we start by getting hold of the relevant calendar.

var calendar = CalendarApp.getCalendarById(calendarId);

Next we get an array of the events for our given date.

var allEvents = calendar.getEventsForDay(date);

As we now have an array we need to loop through each returned event and extract the Title & Id.

for (var i=0; i<allEvents.length; i++) {
    var event = allEvents[i];
    var eventTitle = event.getTitle();
    var eventId = event.getId();
}

Monitor a cell in a Google Sheet

The following Google Apps Script is designed to monitor a cell within a Google Sheet to see if any text has been entered into it (such as a name for a signature) upon which it will then send an email to a specified account. The email contains a link to the Google Sheet and the value that has been entered into the cell.

It was developed as part of searching for a solution to allow a large number of student spreadsheets to be created each of which then required sign-off at some stage. Rather than administrators constantly checking a large number of files regularly however this function would allow passive monitoring so they only needed to take action once an email has been received.

Screenshot of example cell to monitor

Add Note to cell on Google Sheet edit

The following Google Apps Script is designed to automatically insert a Note into the active cell in a Google Sheet when a user edits the content of it. The purpose is to explore an alternative way to interact with editing a spreadsheet than described in this blog post (which uses OnEdit(e)). In this instance we will be making use of the 'edit' installable trigger which needs creating in addition to the code we write.

The Note that will be added to the cell will include the current date/time and the value of the cell before it is edited. We will then collect this information each time the cell is edited - in effect creating a history of edits to a cell.

Screenshot of cell with Note showing edit history

Change Google Sheet cell colour on edit

The following Google Apps Script is designed to automatically change the active cell background colour in a Google Sheet when a user edits the content of it. This was created as I was learning about the various ways to deal with somebody 'signing' a spreadsheet by entering their name into a cell. By changing the colour it would be easier for somebody to see the change when glancing at multiple spreadsheets.

Screenshot of cell colour change