Getting started with creating a web app

This blog post is about starting with creating a web app from the ground up as I look to develop my skills in this area for possible future projects.

A web app like this sits independently from the other Google Apps meaning it is not bound to Google Sheets or Docs for instance, instead it provides a webpage that can be interacted with directly. At some point it is likely you will want the script to interact with Google Apps in some way so that you can achieve a desired task - this will come in later blog posts.

Code.gs
We begin with the function that is called when the webpage first opens and that will ultimately launch our index.html file. We use 'doGet(e)' to capture the HTTP request for which we can use the 'HtmlService' to then create the HTML webpage.

NOTE: This snippet of code is very much a 'copy and re-use' for each of your web app projects going forwards.

return HtmlService.createTemplateFromFile('index')
.evaluate()
.setSandboxMode(HtmlService.SandboxMode.IFRAME);

Get weekday name from a date - alternative

The following Google Apps Script code is an alternative to this blog post where we instead format our date in a way as to only extract the week-day name, saving us the effort of getting the week-day as a number and then looking this up in a 'switch'.

By making use of 'formatDate' and the pattern 'EEEE' within a few lines of code we have a workable week-day name that could be used in an if statement as discussed in the previous post.

Get weekday name from a date 2.gs

Get weekday name from a date

The following Google Apps Script code is designed to take a date, extract the day of the week from it (as a number initially, so Monday=1, Tuesday=2 ...) then use a 'switch' to determine the corresponding name of the day (eg Wednesday, Thursday ...).

The reason I was looking at this sort of code was for a project where a Google Form had to be completed in which the user selected a date in the future that should be a Wednesday. Without validation directly on the Form I tinkered around with the notion that Apps Script could first check the date before proceeding with handling the rest of the data.

The below example is just the code itself rather than also including the extra bits of a Google Form and extracting data directly from it. You could slot this function into you existing project and swap out the first part that simply gets the current date as a variable. When we use 'getDay' we can only return the day of the week as a number hence the need to then use the switch which looks through each case to match with the week-day number.

The result is a variable called day that now contains the name of the week-day which we could use an if statement to evaluate and proceed with the rest of our script.

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