Hey Google, track a bunch of baby stuff for me!
2020.04.18
6 minutes read

My daughter was born last september. It’s a big life event with many changes, but one (of many) things that caught me by surprise was the amount of data collection that came along with a newborn. Besides the doctors and nurses measuring her weight and length over those first months, there’s also an expectation that the parents track all kinds of things: how often is the baby feeding and for how long, how long is the baby sleeping and when, how many times is the baby pooping and peeing…

All these new data collection tasks come right when you’re at your least capable of taking on a new project like that. You’re overwhelmed with new skills or concerns, and braindead from sleep deprivation.

To make things as easy as possible for us, I decided I wanted it so that we could just tell Google Home that, “The baby fell asleep,” and that information and a timestamp would be logged in a spreadsheet for later analysis.

Using IFTTT, it was pretty easy, but there were a couple little “gotchas” – if you happen to be a sleep addled new parent reading this, I’ve decided to document my baby-related IFTTT recipes to make your life as easy as possible. Really, they could be adapted to anything you wanted to log.

IFTTT (“If THIS then THAT”) is kind of like digital glue. It lets you stick services to other services. Companies pay to have their services integrated, and then capable of talking to (and being talked to by) a wide range of other services. In my case, I wanted Google Assistant to trigger IFTTT to enter some information into a particular Google Sheet. Crucially, unlike glue, IFTTT is a one-way proposition. Information flows through it, but never back out. So it can write a new row on a spreadsheet, but will not be able to read you back the most recent row that was written. This presented a problem for tracking how long since the last breastfeed, which I got around with a fairly hacky solution.

The baby is asleep / the baby is awake

The baby is asleep

The Google Assistant side of this recipe is pretty straightforward. When we say, “OK Google, baby fell asleep _______” the IFTTT script is triggered. The blank is so that we can add some context, like, “baby fell asleep in her crib” or “baby fell asleep after crying for 37 hours straight”.

Google Assistant Command

The Google Sheets side of things is slightly more complicated. The Sheets integration with IFTTT lets you add a new row to a google sheet, and lets you access two variables Created At and TextField. TextField is the “blank” (represented with a $ in IFTTT) from before. CreatedAt is the date, in long form, like “January 28, 2020 at 06:05PM”.

Google Sheets Command

Let me break down what I’m doing with this:
=REGEXREPLACE("{{CreatedAt}}";" at .*$"; " ")|||=REGEXREPLACE("{{CreatedAt}}";"^.* at";" ")|||ASLEEP|||{{TextField}}
Each ||| separates two cells in the row we’re adding to the spreadsheet. So here we’re adding a row with the first four cells filled in.

The first two cells are reformatting the CreatedAt variable from long form into a date (in the first cell) and a time (in the second). =REGEXREPLACE() is a google sheets function that does regular expression replacement. Basically, we’re saying in the first cell, remove everything in CreatedAt after the date. And in the second cell, remove everything before the time.

The third cell simply says that the baby is ASLEEP at this time. This is useful because we also have an IFTTT set up for adding to this same spreadsheet when the baby wakes up.

Lastly, the fourth and final field includes whatever comment the speaker appended to their command. It’s a comments column, essentially.

The baby is awake

It is also good to know when the baby wakes up and, crucially, how long the baby actually slept for. The Google Assistant side of this recipe is even simpler than the “asleep” one, because we don’t have any text field for additional comments:

baby awake

The spreadsheet side of things is a little bit more complicated, however, because I wanted it to automatically calculate the duration of the sleep.

baby awake 2

Let’s break this one down cell by cell: =REGEXREPLACE("{{CreatedAt}}";" at .*$"; " ") is just extracting the date, as before. Similarly, =REGEXREPLACE("{{CreatedAt}}";"^.* at";" ") is pulling out the time, and AWAKE is just telling us that the baby is awake.

Then there’s an empty cell for the column where comments for the falling asleep IFTTT are placed. Finally, we have this mess, which is just calculating how long the baby slept for:
=IF(INDIRECT(ADDRESS(ROW()-1,COLUMN()-2))="ASLEEP", TEXT(TIMEVALUE(INDIRECT(ADDRESS(ROW(),COLUMN()-3)))-TIMEVALUE(INDIRECT(ADDRESS(ROW()-1,COLUMN()-3))), "hh:mm"), "")
What it does is check if the cell one row above and two columns over says “ASLEEP” (to make sure the last entry was an “ASLEEP” and not an “AWAKE”… sometimes you forget!), and if it is, it calculates the difference between the time of the previous row and the current time. Because IFTTT doesn’t necessarily know where in the spreadsheet it is, you have to use these awkward INDIRECT(ADDRESS(row, column)) statements to select other cells in a relative position.

What I get in the end looks something like this:
sheet

Everything works pretty well …except for Google’s voice recognition. I assure you, I never let my enemies hold our baby.

Breastfeeding

Another thing you’re often asked about, especially in the first months, is the frequency of feeding. Additionally, (and I realize this differs from region to region) we were told that we should be offering a newborn some milk every three hours. Just logging when the baby is fed is straightforward enough, and I made a script very much like the previous two:

feed1
feed2

…but what about when you’re a tired parent and you can’t remember when you last fed the baby, and want to know if it’s been three hours or not? Well, it turns out that this is where things get tricky, because IFTTT travels in one direction. You can add rows to a spreadsheet just fine, and you can even calculate elapsed time (like the previous example), but there’s no way to have IFTTT read that back to you.

Ultimately, I landed on a pretty hack-y solution, but it works. The Google Home app (that links with a Google Home device) allows you to make things called routines. These are basically meta-commands that you can define that act like you’ve said a multiple things to Google Home. Google Home also happens to have a stopwatch function which we don’t otherwise use.

So I defined two routines, one that looks like this:

When
I say “feeding baby”
Assistant will
feed baby routine
clear and start stopwatch

And another:

When
I say “last feed”
Assistant will
time on stopwatch

The first routine will log the feed in the spreadsheet using the IFTTT recipe above, and then start the in-built stopwatch. Meanwhile, the second routine is just an alias for asking how much time on the stopwatch.

It’s imperfect and a bit goofy, but it works. Unfortunately, the stopwatch is per-device, so if you have multiple homes or home minis they are unaware of the status of each other’s stopwatches. Also annoying is that the assistant implementation on the phone doesn’t seem to be capable of running a stopwatch for some reason. All the other commands work equally well on a phone as on the Google Home itself.

So there you have it. How to log baby things and whatever really using Google Home + IFTTT. Track to your heart’s content!


tags:  automation  baby  google  voice assistant  sleep deprivation  pee and poop