Archive

• Jul, 2019
• Sep, 2018
• Aug, 2018
• Jul, 2018
• Feb, 2018
• Jan, 2018
• Dec, 2017
• Nov, 2017
• Oct, 2017
• Sep, 2017
• Aug, 2017
• Jun, 2017
• Apr, 2017
• Aug, 2016

Delve into the archive. Every post, every thought, article, tutorial and coding example right here, at your fingertips.

July, 2019

• Denormalise & join tabular data into simple rows – Like a database query join, how to denormalise tabular data to create expanded rows, ideal for mail-merges and data imports.

September, 2018

• Clean Browser Cookies at Mac OS X log off – How to use a logoff script to clear authenticated web sessions on a Mac

• Google Calendars on the Web – Displaying Google Calendar events on the web, with this simple diary banner for your site.

August, 2018

• Protecting Data with Google Drive – How to best protect and secure confidential data using Google Drive and Google Apps / G-Suite

July, 2018

• Dealing with confidential information in schools – Using a simple traffic light system for categorising types of information commonly found in schools and the classroom.

• Format values based on whether they appear in another column – How to use conditional formatting to highlight cells that appear in another list.

• How to concatenate columns in a query – How to concatenate columns in a query and return the results.

• Fundamentals of Web Filtering & Logging – With ever more intrusive, and ever more expensive, web filtering options available - here is a fundamental review of the technology to help you decide what approach suits you best!

February, 2018

• Analysing Event Feedback with Forms & Sheets – Dynamically and professionally handle your event feedback, gathered with a google form, analysed with sheets.

• Monitoring Data with Google Sheets – Easily monitor data collected weekly, with formatting to highlight missing & notable values.

• How to transpose values to column headers, including extra blank columns – How to transpose values to become headers, adding extra blank columns in between the values as well.

• How to identify dissimilar duplicate rows in sheets – How to identify duplicate rows in a sheet that have dissimilar values, such as multiple grades for the same student in the same subject from different teachers.

January, 2018

• Logging the IP Address of your Raspberry PI to a Google Sheet – How to get your Raspberry PI to log it’s assigned IP Address to a Google Sheet every time it boots up!

• Address columns by their header name rather than letter position – Columns can move, and when using complex formulas (such as importrange), this can lead to problems. Here is how to address a column by its header value, rather than it’s position.

• Running a Basic Helpdesk with Google Sheets – Create a simple helpdesk and request ticketing solution with just forms and sheets.

December, 2017

• Simple Bulk Emailing from Google Sheets – Send better looking emails to a bulk set of email addresses, straight from Google Sheets.

November, 2017

• Analysing Data with Custom Conditional Formatting – How to use conditional formatting to visually analyse data, including non-numeric data such as grades, levels and categories.

• Filtering data quickly and easily using the QUERY Function – How to create simple ‘drop-down’ filters in sheets to easily and quickly filter an imported range of data rows.

October, 2017

• Generate Unique Usernames using Spreadsheet Functions – How to create a list of unique usernames from a list of given names and surnames.

• Showing Menus in Container-Bound Google App Scripts – How to show menus and sidebars configuration to write more flexible App Scripts.

• Using Configuration Data in Google App Scripts – Combining user and general configuration to write more flexible App Scripts.

• How to use dates in the QUERY function – Use the QUERY function to return results before, on or after a certain or relative, date.

• Counting Email Address Domains using the QUERY Function – How to create a summary list of unique email domains from a list of email addresses, and count how many times they appear in that list.

September, 2017

• Caveats when deleting G-Suite Accounts – If you are using Google Sheets or Google Apps Script in your domain, here are a few caveats you need to be aware of when deleting a G-Suite User Account.

• Using Grades in Google Sheets – Using grades in a spreadsheet doesn’t have be difficult, here is how to do it elegantly.

• How to aggregate rows and columns in sheets – How to aggregate data from different rows and columns for further processing.

• Configure your Chromebook to connect via a Proxy Server – If you are using a Chromebook in a school environment, you may need to make a few small adjustments to your network settings to successfully connect via a proxy server.

August, 2017

• Google Form Response Notifications – Use Google Apps Script to send notifications to different recipients based on a form value (e.g. organisation).

June, 2017

• Sparklines with Google Sheets – Simple, quick and useful. Sparklines let you visualise your spreadsheet data as small graphs, inside a single cell.

• Finding a character from a list in a sheets cell – How to use the FIND function with an array of values to search for individual characters or words.

• Finding Duplicates Using Sheets – How to identify duplicate values or rows in Google Sheets.

• Embed IMPORTRANGE in Formulas – How to embed the IMPORTRANGE function within other functions in Google Sheets.

• The QUERY Function – Transform, filter and aggregate sets of data using the QUERY function.

• The IMPORTRANGE Function – Use the IMPORTRANGE function in Google Sheets to aggregate and link information across spreadsheets.

• The CONCATENATE Function – How to add or combine text or cells in Google Sheets.

• The SPLIT Function – Breaking down text values into their constituent parts using the SPLIT function.

April, 2017

• The INDEX & MATCH Functions – How to link data from one part of a Google Sheet to another (or even between different sheets) using the INDEX and MATCH functions.

August, 2016

• Markbooks with Google Sheets – A start of term chore, but also an opportunity to brush up on your spreadsheet & data-handling skills to make the rest of the year easier and more productive!