Tuesday 31 March 2020

Google Drive Picker for Folder ID

The following Google Apps Script and HTML code is used to create a simple Google Picker that will let a user navigate their Google Drive and select a folder from which we can then extract its ID and create a clickable Url link.
Screenshot of Google Picker in Drive
Screenshot of Google Picker in Drive

Tuesday 24 March 2020

Batch add data to an SQL Table in Apps Script

The following code combines Google Apps Script and SQL to insert a Google Sheet of data into an existing SQL Table in batches, rather than row-by-row, to greatly improve performance. This post improves upon this one for bulk adding data.

By using the 'addBatch' method we can collate each row of student data into a single SQL statement that can be used to connect to the database once and execute multiple times. In order to do this we use 'prepareStatement' to which we can then feed in our values
var stmt = db.prepareStatement("INSERT INTO students (StudentNo, FirstName, LastName, Shoesize) "
                                 + "VALUES (?, ?, ?, ?)");

Tuesday 17 March 2020

Create a Tickbox to select all other Tickboxes

The following Google Apps Script is designed to enable a select-all Tickbox within Google Sheets so that multiple Tickboxes can be un/ticked by clicking just the one. In this example there are 3 master Tickboxes which will un/tick a selection of others based on the specified ranges.
Screenshot of Google Sheet with Tickboxes
Screenshot of Google Sheet with Tickboxes

Tuesday 10 March 2020

Animate an element in jQuery

The following jQuery code animates a square box by sliding it across the webpage after clicking a button.
Screenshot of button and square to animate
Screenshot of button and square to animate
The blue square is created by using CSS styling. Clicking the button then applies the 'animate' method to the #square 'div' moving it a set distance across the webpage (800px in this example) at a speed of 1 second.

Tuesday 3 March 2020

Search for artist in iTunes API

The following Google Apps Script takes the name of an artist from a Google Sheet, passes it to the iTunes API and returns a list of albums, song titles, artwork, etc all formatted into a Google Sheet table.
Screenshot of artist details in Google Sheet table
Screenshot of artist details in Google Sheet table