My world on the web
You might be interested in Jon Dron and Terry Anderson’s book Teaching Crowds. I discussed it here.
Google Sheets is great for quickly spinning up dashboards and analysis, but getting raw data into Sheets from databases can be tedious.
In this post we cover a few ways to get data from your SQL database into Google Sheets.
Every Monday I send out a Google Sheets tip email and occasionally I’ll include a formula challenge. I posted Formula Challenge #3 — to alphabetize a string of words separated by commas using a single formula — in January 2020 and had over 150 replies! It would have been too time consuming to reply to … Continue reading Gmail Mail Merge For A Specific Label With Apps Script
I help organizations make the switch from Microsoft Excel to Google Sheets through workshops, webinars and online courses.
I’m a Google Developer Expert specializing in Google Sheets. I’ve taught spreadsheet workshops and courses for the past four years.
There are a lot of myths surrounding Goog…
Best wishes to all of you for 2020! This is my 5th annual review post. I’m proud and thankful that I get to write this post every year, because it means I’m still running my own show and my business is still going. At this time of year I like to pause. Stop doing the … Continue reading 2019 In Review And A Look Forward To 2020
By far the biggest challenge of my life continues to be the balance of being a good husband and father whilst running my own business and keeping fit. It’s hard to not feel like you’re failing on all these fronts, despite going full tilt all the time. All one can do is keep trying!
This reminds me of a piece from Austin Kleon about the changes moving from one child to two.
I’m thinking about writing a Google Sheets book (perhaps something like this Excel one) — would you buy it?
You can reply to this highly scientific poll with a quick Yes / No email. If you want to add why, I’d appreciate that insight as well! 😉
See how you can use the INDIRECT function to create dynamic named ranges in Google Sheets, which expand automatically if new values are added to the range.
I recently revisited a timetable solution that I developed to support schools with creating a basic timetable based on core data of:
One of the problems that was uncovered was that the system we were trying to feed the information into would not accept staff codes for staff who were not yet active in the system. My initial answer was to find each of these staff members in the final output and delete their codes. The question that was raised was whether this could occur at the beginning of the process by leaving the codes associated with inactive staff blank in the list.
This created a problem where the blank cells were not being recognised and were subsequently not being generated in the output. I found if I added a blank space then my original formulas would work. I therefore added an IF formula to replace any blank cells with a blank character CHAR(32). This meant I replaced:
JOIN(",",'Basic Classes'!$C3:C103)
With:
ARRAYFORMULA(JOIN(",",(IF(ISBLANK('Basic Classes'!$C3:C103),CHAR(32),('Basic Classes'!$C3:C103))))
The issue with this though is that there would never be 100 classes. However, I wanted to create a flexible solution, therefore put in a range that would suffice all contexts. By replacing blanks with CHAR(32), this meant every timetable created had 100 teachers. This would not work.
To work around this, I created a series of dynamic ranges using your example of named ranges. I entered this formula in a cell and referenced it to create a named range. To get the right range I used the form list (Column B) as a reference as this is a required cell in my data collection:
"'Basic Classes'!$C3:C"&COUNTUNIQUE('Basic Classes'!$B$3:B)+2
The final formula was:
ARRAYFORMULA(JOIN(",",(IF(ISBLANK(INDIRECT(TEACHERS)),CHAR(32),INDIRECT(TEACHERS)))))
This meant that the blank cells were only replaced for classes without teachers.
Here is a copy of the spreadsheet if you are interested.
Working analog clock built with a single formula in Google Sheets, involving the NOW, SPARKLINE, SEQUENCE & other functions
If you’re building complex formulas, then I advocate following a one-action-per-step approach.
What I mean by this is that you build your formulas in a series of steps, and only make one change with each step.
The Onion Method is a framework by which to approach hard formulas, and consists of these three elements:
- Put each new step of the formula in a new cell
- Label each step with a simple “Step 1”, “Step 2”, etc. in adjacent cells
- Change the background color of each formula cell, so they can be easily found
Learn to do Automation with Apps Script in this premium, online video training course.
RE2 is a fast, safe, thread-friendly alternative to backtracking regular expression engines like those used in PCRE, Perl, and Python. It is a C++ library. – google/re2
Learn how to setup Vlookup to work with multiple criteria, by combining input criteria, creating helper columns or even searching with dynamic arrays.
Also on: Read Write Collect
The ORDER BY clause sorts our data. We can specify column(s) and direction (ascending or descending). It comes after the SELECT and WHERE clauses.
14. Let’s sort our data by population from smallest to largest. Modify your formula to add the following ORDER BY clause, specifying an ascending direction with ASC:
=QUERY(countries,"SELECT B, C, D ORDER BY D ASC",1)
Create two other courses. The shortlist at the moment includes: 1) “Beyond Sheets” on what to do when your data outgrows Google Sheets, 2) Regular Expressions in Google Sheets, 3) a Data Studio course, and 4) Google Sheets for Educators
Thought I would place my vote for the ‘education’ course. Although all of your courses provide insight and opportunities within education, I feel that there is real scope for an explicit education offering. Although there are some great offerings in regards to introductions (including your own), I think that a course which collated a range of educational scenarios would be a real benefit.
A free, introductory course teaching Google Apps Script from scratch
I have read a number of posts:
I look forward to this new offering. I always enjoy the way in which Collins’ breaks things down with his courses.
Come and learn how to use Pivot Tables in Google Sheets. This webinar will last for approximately 1 hour. All the example datasets will be made available to registered users.
