26 April 2018

How to Automatically Log Your Daily Life to Google Sheets


Life can blind us with its routine. Let’s turn to Google and open up new opportunities to improve your life? The simple act of life logging with Google Sheets can uncover details which often go unnoticed. You don’t have to sweat because you can do it automatically. There’s no special knowledge required.

In this article, we’re going to cover five specific examples of how a daily log of your life activities can be used to identify opportunities. These might uncover opportunities to improve your health, saving money, or even your relationships.

The examples used here require only simple Google Script code to create the automated logging and notifications. If you want you can copy the scripts right out of this article and into your own Google Sheet project.

Get Weight Log Reminders

When you’re done setting up this automation, all you’ll have to do is remember to step on your smart scale every day.

This will submit a new entry to your Google Sheet. However, if you go more than a week without getting on the scale, you’ll receive an email reminding you to weigh yourself.

Smart Scale

The important thing for this automation to work is that you purchase a smart scale that provides a way to log your weight to Google Sheets automatically. There are usually multiple ways to accomplish this depending on the manufacturer.

  • The scale may let you send an email notification, which you can use to trigger an IFTTT action that writes the weight to a Google Sheet.
  • The scale may be compatible with IFTTT, so you only need to select it in IFTT as a trigger, and choose Google Sheets as the action.
  • Direct Google Sheets integration may be available.

Make sure to do your homework before you buy the scale so you know it will let you log your weight.

In my case, I failed to do my homework. The Weight Gurus smart scale does not provide an automated output to email or IFTTT, but the app does allows me to export all weight recordings to a CSV file, which I export into Google Sheets every day.

logging your daily life to google sheets automatically

This isn’t ideal, and hopefully, you’ve purchased a smart scale that will automatically log using one of the three solutions above.

Once the scale is sending new data to Google Sheets, the next step is to create a Google script that will check every day whether you’ve weighed yourself.

To create this new script, open that Google Sheet where your weight is getting logged and select Tools from the menu, then Script editor.

logging your daily life to google sheets automatically

In the script editor, paste the following code and save it as a new project.

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var lastRow = CountColA();
  var lastDate = new Date(sheet.getRange(lastRow,1).getValues());
  var thisDate = new Date();
  var DateDiff = (thisDate.getTime() - lastDate.getTime())/1000/60/60/24;

  if (DateDiff > 7) {
    MailApp.sendEmail("xxxxxx@gmail.com", "Weight Log Reminder", "You haven't weighed yourself in " + DateDiff.toString() + " days!");
  } 
}

function CountColA() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  for(var i = data.length-1 ; i >=0 ; i--){
    if (data[i][0] != null && data[i][0] != ''){
      return i+1 ;
    }
  }
}

The way this code works is as follows.

  1. Uses the CountColA function to identify the last row of data in the sheet.
  2. Pulls the date value out of the last row, and subtracts it from today’s date.
  3. Converts the date difference from miliseconds to days.
  4. If more than seven days have passed since the last entry, the script will send a reminder email.

If you’re curious how sending email via Google Scripts works, we’ve covered the details in another article. Finally, you just need to configure the script to run every day. To do this, in the script editor, click on Edit, and then Current project’s triggers.

logging your daily life to google sheets automatically

Set your new function to trigger as a Time-driven event using the Day timer, and set when you want the script to run every day.

logging your daily life to google sheets automatically

I’ve set my script to run every morning before my alarm goes off, so that I’ll see my email reminder first thing in the morning.

logging your daily life to google sheets automatically

It’s a really useful way to never forget to weigh yourself. By weighing yourself often, you’ll always be aware of where you stand health-wise.

See Who’s Wasting Energy at Home

There is nothing more frustrating for a homeowner than seeing your heating or cooling bill climbing month after month, and not having any clue as to what’s causing it.

There are a lot of ways to track your home’s energy use. One great way is to track where you may be losing air (hot or cold) to the outside is by monitoring how often people open and close the front door of your house. A great gadget for this is an indoor or outdoor wireless camera pointed at the entryway.

Nearly any wireless camera you buy these days allows you to send motion notifications via email. For example, I use the ReoLink Argus 2 Indoor/Outdoor Camera, and it allows for up to three email accounts to receive notifications.

logging your daily life to google sheets automatically

These notifications arrive in the following format.

logging your daily life to google sheets automatically

You’ll need to determine either the incoming email address or the subject line that should be used when the motion detection alerts come in.

Then you can use this in IFTTT when you configure the trigger for your applet.

logging your daily life to google sheets automatically

Then, set up the action in IFTTT to write a line of data that includes the date and time of the alert.

logging your daily life to google sheets automatically

Once this is set up, you’ll have a full log of all of the days and times the front door of your house was opened.

logging your daily life to google sheets automatically

In this format, it’s easy enough see if there are any days where the front door is opening and closing excessively.

Even better, if your wireless camera is configured to send a snapshot whenever there’s motion, you could check your incoming email during those times and see who the culprit is!

Automated Gym Reminders

Another useful thing to log using Google Sheets is visits to the gym. It’s very easy to forget to go to the gym, or come up with excuses not to go. Sometimes all it takes is a little bit of encouragement.

By tracking how often you go, you’ll provide yourself with a record of your accomplishments. Go often and watch thet sheet fill up.

The best way to do this is using IFTTT. Specifically the Location trigger. Just type the address of your gym into the address field.

logging your daily life to google sheets automatically

Create the trigger, and then choose Google Sheets as the action. The important data point is just the date, so make sure to leave at least the OccurredAt ingredient.

logging your daily life to google sheets automatically

Now you’ll have a record of the day and time you’ve visited the gym each day.

A visual record of your success in going to the gym is great. But what if you just decide not to go and don’t even check the spreadsheet?

A solution for this is to trigger an automated reminder email using the same script as the weight logging one in the first section of this article.

You’ll just need to change the IF statement so the outgoing email looks something like this:

if (DateDiff>2) {
  MailApp.sendEmail("xxxxxx@gmail.com", "Exercise Reminder", "It's been " + DateDiff.toString() + " days since you've worked out. Get to the gym you sloth!");
}

Now you’ll get a gentle reminder if you go more than two days without going to the gym!

Get Better Sleep

Whether you own a Fitbit, Apple Watch, or a Samsung Smart Watch, you have a very easy eay to log how well you’re sleeping every night.

Keeping a history of that data can really help you understand different things that are hurting or helping your sleep patterns.

Not all of the solutions out there provide a way to log information to a spreadsheet. For example, Samsung Gear will log your sleep patterns to the Samsung Health app.

logging your daily life to google sheets automatically

You can scroll back and forth in time in this app to see what your sleep patterns were like over time, but there’s no easy way to do a data analysis on the numbers.

On the other hand, if you have a Fitbit, it’s a different story.

Fitbit is integrated with IFTTT, which means you can collecte data to a Google Sheet every time your Fitbit logs a new sleep event.

logging your daily life to google sheets automatically

You don’t have to own a Fitbit to get great sleep tracking. Khamosh liked the Apple Watch’s sleep tracking, and you can integrate Samsung Health with an app like Sleep as Android to achieve the same thing.

logging your daily life to google sheets automatically

You actually don’t need to use Samsung Health at all, since Sleep as Android is capatible directly with your Samsung Gear smart watch (both S2 and S3).

Sleep as Android is compatible with IFTTT, but you need to set it up via a Webhook using the instructions in the user guide.

Once you’re logging how many hours a night you’re sleeping to Google Sheets, you can start trying to correlate that data with other events that were going on in your life during that time.

Some examples of what you can learn from this data:

  • Is your sleep quality better when you go to bed early or late?
  • Do you sleep better during certain seasons?
  • Are you sleeping better on certain days of the week?
  • Do your workout days (from your earlier log) affect your sleep quality?

By understanding what patterns lead to better sleep, you’ll have the knowledge you need to improve your odds of waking up much better-rested in the morning.

Trend SMS Activity With Spouse

Has your spouse ever claimed that you don’t give them enough attention?

Maybe you’ve been really busy lately and forgot to text them during the day like you usually do?

You can actually create a very quick Google Sheets log that’ll remind you if you’ve gone too long since sending a text to your spouse.

All you have to do is create an IFTTT recipe with Android SMS as the trigger.

logging your daily life to google sheets automatically

Fill in your spouse’s phone number as the “send to” phone number. This will trigger the applet every time you text your spouse.

For the trigger action, choose Google Sheets Add Row to Spreadsheet.

logging your daily life to google sheets automatically

Now all you have to do is go into the Google Sheet, and create a Google Script just like in the first section of this article.

Modify the number of days when you want to alert, and edit the email send section of the code to something like this:

if (DateDiff>4) {
  MailApp.sendEmail("xxxxxx@gmail.com", "Spousal Reminder", "It's been " + DateDiff.toString() + " days since you've texted your Spouse. You're being neglectful!");
}

Yes, it’s probably sad that you need an automatic reminder to reach out to your spouse and say I love you, but it’s better than forgetting and not doing it at all, right?

Google Sheets Helps You Track Your Life

These are just a few examples of the ways you can take advantage of Google Sheets to improve your life.

It’s so easy to collect data from your life to Google Sheets. In nearly every area of your life, there’s probably a way to track and even create notifications that can help you improve in some way.

If you like what we’ve done here with scripting in Google Sheets, you’ll enjoy our more advanced article on Google Scripts that make Google Sheets more powerful.


Read Full Article

No comments:

Post a Comment