GAS – Google Sheets – Dynamic Menu and Functions for Sheet Selection

May seem like a sledgehammer to crack a nut, but I was looking for a way to satisfy two things:

  1. Change sheet tabs and know (script-wise) which tab I had come from and was going to
  2. Create a dynamic menu for sheet tab selection, so that I could get the user focused on a single tab, as opposed to having them all there along the bottom

I first tackled this the hard coded long winded way, capturing the names of all sheets and writing an individual function for each, then moved on the a pair forwards / backwards functions, using “getSheets()” to provide the sheet array. This then lead me to going all dynamic!

Started searching, and there were a couple of posts about placing parameters into custom menu items, but nothing that really cracked it, until I came upon this post:

where Václav Novotný provides a solution to create a dynamic custom menu, and dynamic scripting for functions to go with it. This was setup for a Google Doc, so I need to rework it a bit for my needs. All credit to Václav Novotný though for the meat on the bones. Continue reading

GAS – Google Sheets – Query All Columns with a Script

Came across a problem using query on a table, where I wanted to query all the columns. No easy way to write this into a function if you have 10 + columns, so set about writing a google apps script to handle this. The premise is simple enough, a data table with a list of people’s names in the first column, then a set of columns with colour names assigned to each person:


On the search page a straight forward drop down list of all the colours (using data validation from the datatable range!)

queryAllresult1 Continue reading

Imagemagick – Tips and Tricks No. 3

Add a Label to a set of images

First bash script coming up, I’ll explain the best I can: I have a set of images that are all square: w=h. I want to create an image of each image with a label on top,using the filename as the label (without the extension), with a white background, ending up with a 200×250 image with the same name as the original but with “-tab” added. IM will by default squeeze the font size so that the text label always fits. Continue reading

Imagemagick – Tips and Tricks No. 2


The ability to combine a set of images into one image in a variety of ways, essentially replicates the contact sheets of old. Here are a few ways to create a montage. It works best when all the images are the same height and width. IM does not understand page sizes, and will just create a simple image, regardless of the number of images, you can tell IM which images to use though. It also makes sense when testing and creating montages to save the output to a different directory, otherwise your montages will be included in the next iteration! Continue reading

Imagemagick – Tips and Tricks No. 1

Imagemagick (IM) is brilliant! If you want to do something to an image, Imagemagick can do it! Works on Linux and Windows. Can be scripted. Just use Imagemagick for your image manipulation needs.

Advert over. This and subsequent posts will serve as a reference for me, but also to show what imagemagick can do. Other than one liners, there will likely also be some bash scripting. I have also littered these posts with links to the IM documentation for all the features available with each of the actions. Continue reading

GAS – Sheets – Clean and Clear Layout

Sometimes, we like our spreadsheet to give focus to what is on it. General formatting: colours, borders etc. do help, but even better is to remove all unneeded columns and rows, along with other distractions. For this to work, the top of the last row needs to be visible. Here is a finished example: Continue reading