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!)
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
Cor blimey, this one was a toughy to get to the bottom of! No amount of googling brought me the solution I needed, however by combining resources from three/four places I finally got there.
Now the filter function in Google Sheets is a lovely thing, but it doesn’t play nicely if you want an array formula, that is a formula that automagically fills down as you add more data to a sheet. So you have to convert over to using vlookup (or other method) to get the result you want. Now on the face of it vlookup will only handle one criteria, but it can be forced, with a fair bit of jiggery pokery to handle multiple criteria, and then work as an array formula.
Here was the problem. Continue reading
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
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