Saturday 31 August 2019

Clear cell content on Google Sheet edit

The following Google Apps Script is designed to clear the adjacent cell to the right of the one that has just been edited - so long as it is within the column we specify. It was developed as I was testing the available options for a Stock Inventory system where an item that was to be marked as back in stock would automatically clear an email timestamp in the adjacent cell (ready for the whole ordering process to start again).

We make use of the 'onEdit(e)' trigger to achieve this task which takes an 'event object' - e - that is passed to the function so we can determine which cell has been edited. This example focuses upon taking action on edits in column 2 only.
Screenshot of coloured columns to highlight actions
Screenshot of coloured columns to highlight actions

Friday 30 August 2019

Adding basic Apps Script and CSS to a web app

This web app project now builds on the foundations we established here and adds the following:
  • A CSS Style Sheet for adjusting layout, colours, font-styles, margins, etc;
  • An event listener to wait for the webpage to fully load before running other functions;
  • An Apps Script function to get the email address of the person visiting the web page.

Screenshot of webpage with CSS styling and email address of visitor
Screenshot of webpage with CSS styling and email address of visitor

Thursday 15 August 2019

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);

Saturday 10 August 2019

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

Friday 9 August 2019

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.