AppInventor – Create Dynamic First List for Listpicker

Following on from my recent post on Dynamic Lists for Listpickers, I ventured a bit further.

Well I now have a rough version (and it is ugly!) that creates a dynamic list for the first list in a nested listpicker. This assumes a list of names or words in the master list, and that these are sorted alphabetically. The issue with creating such a thing is that, say for names, there are 26 letters that names could start with, but that the distribution of names is very uneven. In my sample list 3 letters took over 30% of the entire list. My aim was to present nested lists that could all be viewed without scrolling (between 10 and 12 items), but because of the above this is just not possible all the time. I only have the blocks here that create the first list for the listpicker, the A-D,E-H,etc. as the blocks probably need some optimisation before writing the whole thing out. But here goes: Continue reading

Google Sheets – ArrayFormula with Header/s

The array formula is a wonderful thing in Google Sheets, in most cases providing the opportunity to enter just the one formula and have it automatically fill down as new entries appear from elsewhere.

A clever trick though is to create an array within an array, which allows for header rows to be included.

Continue reading

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:

http://clav.cz/google-apps-script-menu-functions-with-parameters/

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 – Insert Checkmark to Cell and Conditional Formatting

A little timesaver. Google Sheets doesn’t have a “Special Characters” selector, so if you want to use one you have to open up a Google Doc, copy the character you want, then back to Sheets to use it. This example shows a simple apps script for inserting a checkmark to a cell. We will then look at conditional formatting of the cell/cells. Continue reading