An Introduction to Programming through Google Spreadsheets

Ever since I started my current job, I've become "the spreadsheet gal." I only briefly worked with them before. When working on my finances, I used various formulas in the spreadsheet to determine what percentage of my monthly income was going towards bills. I found it very useful due to the fact that formulas are dynamic, rather than static. For instance, if I got a raise at work, I'd have a higher monthly income. Since the percentage spent on bills was created by a formula that referenced my monthly income, that percentage automatically adjusted!

Do you know what that sounds like? A variable! I'm guessing everyone reading this has used a variable in math. An example would be y =  x + b. y, x and b are all variables here. When either x or b changes, y will change as a result (usually, depending on what pair of numbers you use). 

You'll also see variables in programming. We can take the math equation above, and transform it into programming: valueY = valueX + valueB. What we're seeing here is the exact same thing as an algebra formula, but it programming terms, or syntax. So let's see how spreadsheets can teach us the logic behind programming. 

Our algebra equation is y = m + b. To make this a little easier to understand, let's apply the real world to this. 

y = total cash in bank account

m = cash in checking account

b = cash in savings account

With this math formula, we're adding out checking account balance to our savings account balance to come up with our total cash. If you're signed up for direct deposit, then your total cash will automatically increase once your paycheck as been deposited. So what would this look like in programming? 

totalCash = total cash in bank account = y

checking = cash in checking account = m

savings = cash in savings account = b

Using these variables, this is what our statement will look like: totalCash=checking+savings

This is the same things as above! When your direct deposit hits, your total cash will automatically adjust. Why are variables superior to static numbers? I'd like to show you by using a spreadsheet:

So looking at this spreadsheet, it appears that these are just numbers. If I was adding things by hands, then each and every week I would update all three variables - totalCash, checking, and savings (or in the case of a spreadsheet, cells B1, B2, and B3). However, if you could examine what are the actual contents of each cell, you would see that totalCash actually contains =ADD(B2, B3).

What we're doing here is making the number in this cell equal to cell B2 plus B3. We're adding B2 and B3 together to make our total! So what if you had a lot of things to add together? HSA account, checking, savings, cash on hand, and the value of your inherited collection of Beanie Babies? That would be a lot of cells to reference! B2 plus B3 plus B4 and so on. There's an easier way though!

Remember, in programming, if you have to do the same thing more than once, you can write a script for it! There's no need to repeat something over and over again. If you can find a shortcut, use it. So here's what our new spreadsheet would look like:



Hey, look at that! Our total cash increased! But did I manually add each number in a calculator, and then enter that value into B1? Nope! I added all of the numbers in an array! Our array here is the numbers in each row. Arrays are something down the road in programming, so trust me here. In a spreadsheet, how do I add the numbers in column B to create my total cash? With the following formula: =SUM(B2:B6)

With this formula in cell B1, I'm creating the sum of cells B2 through B6. The spreadsheet does the math for me, just like programming will do the math as well. I just have to assign the values! Now, for something a bit more interesting.

Let's say I'm having trouble staying awake during the day. The 3 o'clock yawns are getting more intense lately (just an example to make my point, right? Right?! Oooo-*yawn*-kay!). What I'd like to do is keep track of how is, on average, how much sleep do I get each day? Or even each week? Because we're total nerds, and can't afford a FitBit, let's create a spreadsheet!

Before diving into a spreadsheet without regard, let's state our variables. Let's come up with a plan!

So what do I need to record? What do I need to enter? Well, I will definitely need to enter how much sleep I'm getting each night, so one variable for each day of the week. I'll record my estimated amount of sleep I get each night, and assign those to variable named after each day of the week.

From there, I want to know on average how much sleep I'm getting each day. This sounds straight forward - just add them and divide by seven! But that would give me the average sleep I got just for this week. What if I wanted to know my average for each week from the start of this year? Ahhhh, that makes it a tad more complicated, but more fun.

So I'll need to find the number of days from the start of the year to this point, from January 1st through February 28th. Do I pull up the calendar and count each day? Nope! If you do it more than once, program it! So I want to find the difference between today's date and January first. Well what do you know, Google Sheets has a formula for that: DATEDIF!

DATEDIF is used in the following format: DATEDIF(DATE(2015,1,1), DATE(2015,2,28), "D"). The first date is your start date, and the second one is your finish date. The D on the end returns our time back in days, so the number of days between date one and date two. Now, if we used it like this, we'd have to enter a new date every single day. Programmers don't do that! Google Sheets has a TODAY function, calling today's date without entering anything else. So now our formula looks like this: DATEDIF(DATE(1969,7,16), TODAY(), "D").

What do I do from there? I need to add up the total hours of sleep from January 1st through today's date. We can accomplish that with SUM. Then we'll need to take that number and divide it by the number of days from January 1st to today, with DATEDIF. I'll create a seven day sample:



Add those numbers in your calculator, and divide by seven. Did you get the average we see in the spreadsheet? Yup!

The actual formula to create this is SUM(B2:B9)/(DATEDIF(DATE(2015,1,5), DATE(2015,1,12), "D")).

This takes the sum of cells B2 through B9 and divides them by the number of days between January 5th through January 12th. If you know the hours you've slept total this year, you can use the following equation to find your average per night:

=TOTALHOURS/(DATEDIF(DATE(2015,1,5), TODAY(), "D"))

And if you want to find the average hours of sleep you get each week, then you'll first need to find out how many weeks have passed so far this year. How would you express this? You take the number of days this year, and divide that by seven! So if there's been 54 days this year, just divide that by seven and you'll get a real number of 7.71 - the number of weeks to this day. Of course, we'll be using a real number, so that we don't make "bad math." Here's what this looks like in a spreadsheet:

=TOTALHOURS/((DATEDIF(DATE(2015,1,5), TODAY(), "D"))/7)

Hopefully this helps you see the relationship between cells in a spreadsheet and variables in programming. Being able to automate things may seem to take longer in such a small context, but when you're working on things in a much more massive scale, then dynamic variables save much time and money!