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
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.
May seem like a sledgehammer to crack a nut, but I was looking for a way to satisfy two things:
- Change sheet tabs and know (script-wise) which tab I had come from and was going to
- 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
Google Sheets doesn’t do special characters from the File Menu. Here is a script (must check if there is an Add-on for this! There is but wants an annual subscription!) that provides a few: Continue reading
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
This should be easy peasy, but it actually takes a fair bit of scripting to get it to work, especially if you have more than one tab on your base spreadsheet. Continue reading
I use this a lot, but not enough to remember how to do it each time!
Say you want to reference a range of cells but only the ones with values. First up you need to count the cells that have values. you do this with, for example: Continue reading
Doesn’t seem to matter which word processor you use, the label sheets never line up properly. No difference in Google Apps for this. But life can be made easier by pulling address data down from a list of rows and columns, and placing one full address in a cell, ready to go. Continue reading
Nice little equation that I can’t take credit for, can’t remember who provided it in the first place.
Say you have a list of peoples names in the format: Continue reading