Goodbye Awesome Tables :(

It was all going so well… Having found Awesome Tables in 2016, the creation of tables , charts and graphs suddenly became simple and straight-forward, and made me look cool in front of my colleagues :)

Then I started checking the view stats, in November last we hit 926 views! Guess what happens when you (routinely) hit 1000 views, regardless of the number of different tables, and any within the same domain? Awesome Tables slaps a £5000 per annum fee on your usage!

My educational not-for-profit organisation was not going to stump up for that, no way, no how. I needed a solution, and alternative. I knew that Awesome used javascript and Google Visualizations / Charts on its back end (not forgetting the html and css), I therefore decided to apply myself to the somewhat steep learning curve of google charts/visualizations. Continue reading

Google Visualization – Basic Slideshow from Google Sheet

Here is the code for a simple automatic slideshow using google visualizations

Example Slideshow

Google Visualization – Dependent Controls / Filters

When building a chart with controls (using a dashboard), you very often want to filter, then filter again. Lets say we have a people directory, with names, locations, and countries. You create a CategoryFilter for each of these. But it would make sense to filter by country, then be left with only those in that country, before filtering by location. This is how you do it.

Set up your filters:

Set up the table

Now we need to do the binding of the controls and the table:

You can see that we bind the country to the location, then the location to the people, then the people to the table. In this way, the filters become dependent on each other. In the simple demo below, you should see how – when a country is selected only the locations for that country show in the Location drop down.

Here is a full demo of this in operation:

Demo Open in Browser

GAS – Create a Unique list from Cell based csv type Values – two ways

In my endeavours to move away from what might be a costly exercise in providing tables and charts for my users at work, I am delving heavily into google visualisations as a replacement. One feature I have yet to replicate is the “csvFilter”, whereby a column of cells in a table contains more than one value, separated by commas:

csvlist Continue reading

GAS Folders, Sub Folders and their Files

Google Drive is a wonderful thing, but when it comes to getting at a file with scripts things can get a little quirky. Every file and folder has a unique ID number (28 digits, letters and other marks) which can make it easy to locate it, if you know how. However you can of course have many files, or folders with the same name spread out across your Google Drive, which can create a bit of a problem when trying to find the file or folder. Very often you may have to iterate over “all the folders” in order to find what you want, using names to do this can be problematic.

I find I am routinely needing to grab a list of filenames, file IDs, or file types, and routinely searching the web each time I need to do this, therefore it was about time I tied down the required scripts in one place for reuse. The scripts I have put together will request either a folder name or ID in order to iterate over the files within that folder and return the results to a spreadsheet. The scripts can be used as a base, and edited to suit specific requirements by adding further properties to return, or through the use of if statements to narrow down selected files.

  • You can find the ID of the folder by looking in the url address bar if you have the folder open in Google Drive
  • You can also right click on the folder and then Shareable Link
  • Finding the name should be obvious! – use search in Google Drive to check for folders with the same name. It may be wise to do this occasionally if you use name a lot in your scripts, and to make your folder names unique

In my scripts I have chosen to return the filename, the mime type, the file ID, and to create a download link. I also include a helper script (courtesy of Sandy Good) that presents the mimetype in a more user friendly way. All four folder scripts rely on this. Thanks should also go to Brian P and AD:AM on the google group support forum for their code.

I will add a link to a google sheet which contains all the scripts, just make a copy of it to your own google drive, authorise the scripts and away you go.

Continue reading

GAS Import CSV from GMail Attachment

I have a weekly email sent to me with a csv attachment. This needs to be opened and the contents pasted into a google sheet, the data therein is then used for a variety of other applications running on Google sheets. Fortunately there is a way to automate this and to parse the csv to sheets. I have robbed and amended the code provided by Digital Inspiration to do this. The linked page also includes other examples of scraping csv data from an email attachment. The key to getting this to work is a consistent approach by my email sender, who needs to use the same email address and subject in order that the GMail filter I set up can assign a label to the email. The script is run from the receiving google sheet on a timed trigger, once a week.

The sheet needed sorting to meet requirements of others. Above code kept nice and short thanks to the parsing API provided by Google. Of course, this will work for a single user google account, calling on the gmail of that user. Because I have G Suite it is simple to create a separate account to handle this type of thing.

 

GAS – Three Crackers: 1. Insert image at specific place in a table, 2. Remove extra paragraphs created by 1, 3. Proportionally size your image

The code, used on a google sheet to add an image to a specific location in a google doc table, should be self explanatory! I use this when an image is needed in a spreadsheet data to google doc to pdf creation exercise (hmmm – had better post that up too 😉 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