Webpage photo gallery

The following JavaScript code is designed to present a selection of images as thumbnails with a bigger preview version displayed on mouse-over.

Screenshot of image gallery with thumbnails

Connect to MailJet API with Google Apps Script

The following Google Apps Script can be used to connect to the MailJet API, from where you can then send emails and manage contacts. There is a lot of documentation on how to connect via PHP, Python, Rub, C# etc but nothing suited towards Google Apps Script.

To connect you will need to have a MailJet account and your own API/Secret keys. In this example we will connect with the 'Contact List' aspect of the API. We need to create an encoded string which contains our API keys:

var encoding = Utilities.base64Encode(apiKey + ":" + secretKey);

From here we can set the HTTP headers authentication as part of the request, to authorise our account details:

var options = {
    'headers': {"Authorization": "Basic " + encoding},
  };

Then we can continue with the usual route of using the 'UrlFetchApp' to make the request and return our data.

Webpage background colour changer

The following JavaScript code is designed to randomly change the background colour of the webpage each time it is refreshed.

Screenshot of webpage colour

Bulk delete records from an SQL Table in Apps Script

The following code combines Google Apps Script and SQL to bulk delete records in an existing SQL Table, from data in a Google Sheet. This naturally follows on from being able to bulk add data in this post.

Rather than getting all of the data from the Google Sheet, this time we are just going to use the Student No which provides a unique value for each record (as it is never repeated). Once we have these values in an array we can loop through each row and use them in our SQL query.

Our query specifies which Table we want to remove the record from and a where condition that has to be met (without this all records would be deleted leaving the Table empty). The where condition in this example is finding a corresponding Student No match.

var sql = "DELETE FROM students "
+ "WHERE StudentNo=" + studentNo;

Bulk add data to an SQL Table in Apps Script

The following code combines Google Apps Script and SQL to bulk insert a Google Sheet of data into an existing SQL Table. This post improves upon this one for adding a single piece of ad-hoc data.

Screenshot of Google Sheet data for SQL Table

This time we have a Google Sheet of data with columns matching those in the students Table. We start by collecting this data into an array that we can loop through to push the values into an SQL query. When we create the query we construct it slightly differently however as we need to include variables rather than static text (as their value will change for each row when the script loops through).

var sql = "INSERT INTO students (StudentNo, FirstName, LastName, Shoesize) "
+ "VALUES ('" + studentNo + "','" + firstName + "','" + lastName + "','" + shoesize + "')";

Get data from an SQL Table in Apps Script

The following code combines Google Apps Script and SQL to query an existing Table to then copy the data into a Google Sheet. This post follows on from this one for adding data to an SQL Table.

This time we make use of a 'select' SQL query to pickup all columns (*) from the students Table. We use 'executeQuery' to run it as we are returning a set of data from the database.

var studentsTableData = stmt.executeQuery("SELECT * FROM students");

Next we collect some spreadsheet data and select our starting cell where the first row of data will be inserted from - A2 in this instance. We then create a 'while' loop that goes through row-by-row for each of the 4 columns and inserts data from the students Table.

We make use of a method called 'offset' which is detailed more in this blog post which allows us to iterate from the first column across to the last, then move down each row. Then 'getString' retrieves a string value from the Table's specified row and column which we can then enter into the cell.

var newCell = startCell.offset(row, i);
var value = studentsTableData.getString(i+1);
newCell.setValue(value);