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

Google Sheets – replace filter with vlookup for array formula

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.

http://chandoo.org/wp/2014/10/28/multi-condition-vlookup/

http://codingisforlosers.com/learn-vlookup-formula-google-sheets/

https://stackoverflow.com/questions/22260953/google-spreadsheet-vlookup-with-arrayformula-plus-multiplication-of-returned-val

https://productforums.google.com/forum/#!searchin/docs/parallel$20lookup$20solution/docs/36A0epDlIdE

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

Incron gotchas

Of which there are three:

  1. Make life simple and do all your incron as root. I know you shouldn’t but it helps to avoid all sorts of permissions issues. You may need to create an allow file “/etc/incron.allow” and put either root or your sudo name in it.
  2. Ensure that you only have one “SPACE” (not a TAB) between the three elements on an incron job: e.g.

    /etc/samba/smb.conf IN_MODIFY /etc/init.d/samba restart

  3. incron, like cron, requires full paths for just about everything, so if you are calling a script, use the full path to it, and use the full path to all programs inside the script.

Google Sites (Classic) – Embed Live (working) Google Sheet “Range”

This one has been annoying me for a while but I finally arrived at a solution. It is especially usefeul when working inside a G Suite domain, and you wish to share information on a spreadsheet with some selectable cells (e.g. with drop downs) via a classic googlesites page, so for people with the right permissions they can use the functionality of the sheet without causing damage to the underlying data.

The default options and gadgets for presenting a spreadsheet offer too much of the sheet for my liking.

The way to sort this out is to create a web App that can be displayed on the page. The apps script is created on the website (Manage Site > Apps Script), and once the two parts are created inside a project, the app can be deployed and will then show up when added to you page.

So here is what you need to put in your code.gs Continue reading

GAS – Send Event to Calendar from Spreadsheet

Managing a Google Calendar from a spreadsheet is fairly straight-forward.
You do need the calendar ID and all your ducks lined up on the sheet.

in it’s simplest form:

Continue reading

GAS – Send an html Email

Much the same as sending a normal email, but this time you include a body with html tags: