Post #5 - Google Sheets Strike Back!
I know what you were all thinking. How has Dave gone three months without blogging about spreadsheets!? Well, you won’t be disappointed today, because I’m back at it again, diving into the Google Sheets Universe like a mathematically demented version of Doctor Strange. This time, I’m attempting to produce a dynamic Music Theory Harmony organizer, a way for theory students and composers to immediately visualize the Solar System of music in any given key.
If you’re already bored, you probably don’t want to keep reading, but if you find yourself exhausted from doom scrolling through the bog of polarizing news this past week, perhaps an escape from this universe is just what the doctor ordered. So first, some background!
Music is a Solar System!
To quote Kristen Bell’s iconic Disney Princess Anna, “Wait…what?” Yes, you read that right. Music is a solar system. Certain pitches and collections of pitches (called “chords”) actually exist within an aural gravitational field we call harmony. I’m not the first person that discovered this. Many famous dead people devoted their academic lives to studying and theorizing about the mysterious way that music interacts with our brains. One of the most famous ancient theorists was Jean-Phillipe Rameau who, as his name suggests, was from France. Rameau wrote an entire treatise on harmony in 1722 (brillianty titled Treatise on Harmony), and, as far as we know, at least a few people read it because his ideas are still widely circulated to this day. He talked about how root-position harmony moves in predictable patterns, and at the center of these patterns, acting like a mighty sun in a cosmic solar system, was the Tonic Chord. This is where we get the word tonal from, because it means we are operating in a harmonic system where the Tonic is at the center of the universe.
If you’re like me and you have a hyper-visual imagination, seeing these patterns laid out like a Solar System makes a lot of sense. Thus, I turned to Google Sheets to create a resource that would allow for someone to select any Tonic pitch and then see how every triad within that key would look in the Solar System layout. Had Rameau had access to the internet, I’m sure he would have designed this back in 1722, though it's possible he would have become addicted to Angry Birds and not finished his famous Treatise until the 1730s.
Designing the Solar System
To make this Google Sheet work, I knew I was going to have to get really good at INDEX and MATCH functions because I was going to have to be constantly pulling information from different matrices that would house all the raw data. I built one matrix that contained the pitches in every Major and Minor scale in our Tonal System.
The next matrix was going to be a calculated minefield of information that would come from the main Dashboard. Once you select the Tonic and choose its Natural or Relative minor key, the second matrix will pull that information from the first matrix, and through a series of CONCAT functions, display all of the triads in a scalar sequence.
While this one took me a while to figure out, the last step proved to be the real kicker. I had set manipulatable buttons on the homepage to allow for the user to select the harmonic or melodic minor scale. I needed to figure out a way for these buttons to trigger the INDEX formulas to look not just for the Natural or Relative minor scales, but for the appropriate Harmonic or Melodic scale as well.
It took me awhile, but I eventually figured out that, with a series of IF statements, I could get the formula to look up the appropriate row in the second matrix based on the minor manipulatives. After that, all I had to do was run a QUERY for the appropriate list of roman numerals based on the information pulled from the homepage to populate the Solar System with the correct chord symbols. After downloading a few Google Images and spacing everything correctly, I finally got my finished product:
Since I put a considerable amount of time into this, I figured the least I could do was try to make some money off of it. Thus, I have put the full version on my Teacher Pay Teachers site for the bargain deal of $5.00! If you are a music teacher and decide to use this with your students, I would love your feedback and ways it works well and ways it could be improved!
What’s Next?
And once again, like Batman surveying Gotham City from a rooftop after yet another evildoer is behind bars, I push back from my computer and wonder what Creative Problem will threaten my peaceful life in the coming weeks before school starts. If you are in need of a Creative Solution for a problem in your life, feel free to project the Google Sheets logo up into the sky, and I’ll come running! Until then, I’m likely going to spend too much time being emotionally invested in NBA Free Agency.
Stay creative, everybody!
