About Metric Rat

Facilities Manager by trade, working at an independent school for children with special needs (autism & learning difficulties). Enjoys classic cars (1962 MG Midget owner), running an L322 Range Rover (and also an Audi cab 3.0 and MX5, whilst constantly reparting my daughters Ford Ka) and fiddling with computers (which generally means fixing everyone elses problems with Windows, whilst I sail along happily on Xubuntu Linux :) ). Play guitar, bass and ukelele for fun, and when time allows do a spot of sailing and keep active in the garden. My blog is in essence an echo of my daily trials and tribulations and triumphs :)

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

AppInventor – Post Data to Google Sheet

Thought I would write this one up on here, can’t take any credit for it, as original idea posted on youtube (credits below), but if I have it here it is easier to grab and use when I need it.

I have previously worked up a solution using a Google Form to submit data to a google sheet from an AppInventor app, but this method uses a standalone google apps script to do the same, but without the baggage of the google form. We will need to:

  • Have a google account (we want to use)
  • Create a spreadsheet, and set this out with the columns we need
  • Create a google apps script
  • Create an App Inventor app with the correct blocks

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

Google Sheets – Google Apps Script – One to One Meeting Tracker

I was presented with a word document last week with a table full of names of staff and dates of their one to one meetings (who uses spreadsheets for managing this sort of thing ;)), ….”Can you make it so that an email gets sent to the supervisors when it is time for them to hold another one to one meeting with their staff?”

“Yes, of course.”

First job was to grab the data and convert it into a manageable list. Ended up with 8 columns: Name, Supervisor, Job Role, Department, Date of Meeting, Completed, Notes/Comments, date for Next Meeting. The notes were to allow for entries of reasons why the meeting could not take place, e.g. maternity leave or long term sick. There was all sorts of rubbish written in the table that wasn’t a date, people who had left and started during the year, which all had to be cleared out. Eventually got a neat set of data to play with. Continue reading

Google Sheets – Using Query to Find a Value in any Column

The saga continues on from this and this to find a workable solution to find a value from any column using query. I happened upon another route (see accepted answer), which was to use a spreadsheet function to generate a csv list of columns which could then be applied to the select query. Credits as always to the original poster! I then needed to modify the substitutions to create more of the select query, and found I could build the whole query syntax in two cells then apply these to the query function. I was so chuffed with myself i made up a proper spreadsheet layout with comments and made a video of it for demonstration purposes. 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 – 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:

queryAlldatatable

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

queryAllresult1 Continue reading

GAS – Google Sheets – Query the whole table ? No you can’t !

You would think that this should be straight-forward, but it is not.

A table full of data, with many columns, and everything to the right of Column C will have come from a list of items, but this will be different for each row, and i do not know how many columns there may eventually be. Continue reading