Tracking your mood using Google Sheets

In this article, I will teach you how to build a simple Mood Tracking app using Google Sheets and Apps Script. I recently came across this discussion on Hacker News that introduced me to the idea of tracking my mood. I had never considered tracking my mood, although, I sort of knew that I usually felt bad if I hadn't slept enough the previous night or if I hadn't exercised that morning.

I wanted to give a few mood tracking apps a try but I really did not want to install an app for this simple use case. In addition to privacy concerns, I was also not convinced that a one-size fits all app would work for me. For example, I currently believe that sleep and exercise have a huge impact on my mood during the day but perhaps there are other factors that matter more (such as my diet). For apps like this, I prefer starting simple and then making changes over time. The various off-the-shelf apps were either too simple and lacked customization options or were too complex with many knobs and dials.

I realized that I could easily build a simple app using Google Sheets and Apps Script that would address most of my requirements. In the rest of this article, I'll describe how I built the app so you can build one for yourself.

Product requirements

As a product manager and an entrepreneur, I LOVE the process of designing and building products. In the beginning, when thinking about a new product to build, I like to start with defining the problem I'm trying to solve. If I get the problem definition right, the odds of building a good solution also become higher. Once I've defined the problem, I like to jot down the key product requirements that the solution must satisfy in order to solve the problem well.

Problem definition

In this case the problem definition is easy since I am going to be the only user of this product. I want an easy way to log my mood everyday along with whether I ran that morning and how much sleep I got the previous night. I also want this data stored such that I can analyze trends or generate reports.

[note]

In general, it is important to get customer input when defining the problem. A good problem definition must go beyond what the code should do. A good problem definition will try to get to the heart of the user pain point.

[/note]

Key requirements

• Ability to log my mood everyday along with answers to a few questions.

• A daily reminder to log my mood.

• My responses should be stored such that I can analyze this data.

• My responses should remain private.

• The app doesn't have to work for anyone else. I will be its only user.

The next step is to design the solution such that the above requirements are met.

Why Google Sheets and Apps Script?

When I was researching the mood tracking apps that were available on the various app stores, I realized that that they're just glorified forms with bells and whistles.

There is usually some way for you to enter how you're feeling along with additional information about that day or the previous night. This info is converted into charts to help you track your mood over time and to identify correlations between certain types of events and your mood. That's basically it.

I realized that I could build this using a Google Form for entering data, a Google Sheet for storing and analyzing the data and an Apps Script for sending a daily reminder to fill the form.

How I built the Mood Tracker app using Apps Script

I began by creating a form where I will enter information every morning and a spreadsheet to store my responses. Then, I wrote an Apps Script to send me an email reminder. Finally, I created an Apps Script Trigger to send me the email reminder every morning.

Step 1. Creating the spreadsheet and the form

I created a new spreadsheet and renamed it to “Mood Tracker”.

Next, I inserted a Form into the sheet to collect responses. I seelectd Insert from the menu bar and then Form.

The form builder opened in a new window.

I added four questions to the form. First I added a question to track how I'm feeling. I made this a multiple choice question with just two choices: Good or Bad.

Some mood trackers use a linear scale where they ask you to rate your mood on a scale of 1 to 10 but I wasn't sure that I could tell the difference between a 6 and a 7 or an 8 and a 9.

Next, I added a question to track whether I ran that morning. I usually run every morning and I think my mood is better on days when I run. I will see if this hypothesis holds up.

I also added a question to track the number of hours of sleep I got the previous night. Google Forms was smart enough to automatically add a validation rule to ensure only numbers can be entered.

Finally, I added an optional question for me to enter additional notes (if any).

[note]

You should customize the form to suit your needs

I created the form based on what I thought my mood depended on. You should add whatever questions you think will help you track your mood better. Please feel free to use the above questions as a starting point but remember to personalize the questions to suit your needs.

[/note]

A new sheet called “Form Responses 1” was automatically created in the spreadsheet. This sheet will store my responses. This sheet had five columns. The first one called “Timestamp” will record the time at which a response is submitted. The others will capture my responses to the questions on the form.

Next, I tested and confirmed that the form was working. I previewed the form (by clicking the "eye" icon) and submitted a response.

I submitted the following responses.

Once I clicked submit, I saw a confirmation message and also saw my response recorded in the sheet.

So far, I've built the following:

• A form to enter information.

• A sheet to store responses.

[note]

My data remains private to me

Since my responses are captured in a sheet that I own, they’ll remain private unless I decide to share the sheet with someone else. This is unlike most mood tracking apps out there where I cannot control who gets access to my data.

[/note]

Step 2. Creating an Apps Script to send an email reminder

The next step is to build the daily email reminder. Google Sheets does not have in-built support to send reminders. Therefore, I built this feature myself using Apps Script. This is really where Apps Script shines. It enables you to build simple enhancements on top of other G Suite apps like Sheets.

The first step is to create a new script (select Extensions > Apps Script).

[note]

Creating your first Apps Script?

I've written a detailed article that walks you through the process of creating an Apps Script.

[/note]

I named my project to Mood Tracker (yes, I know I'm super creative at naming 😃). I also renamed the function in the script from [code]myFunction[/code] to [code]sendEmail[/code].

Next, I wrote the following Apps Script code to send an email reminder. The function [code]MailApp.sendEmail()[/code] takes the email's recipient, subject and body as input and sends the email.

function sendEmail() {
  var recipient = "myemail@example.com";
  var subject = "How are you feeling today?";
  var body = "Don't forget to enter your mood at:\n" +
    "https://docs.google.com/forms/d/e/ENTER_YOUR_FORM_ID_HERE/viewform";
  MailApp.sendEmail(recipient, subject, body);
}

[note]

Remember to change the recipient and the form URL

Don't forget to replace [code]myemail@example.com[/code] with your email address. You also need to replace the highlighted portion of the code with a link to your form. To get the link, first click the SEND button on the form editor.

Then click the link icon to get the URL. Copy this URL and replace the one in the code with this URL.

[/note]

After I finished making edits, my code looked like this. You'll notice how my code is just a few lines long, but it does something very useful - send me an email reminder.

I tested the code by running it (you can run the code by selecting the function to run from the dropdown menu and clicking the play icon).

[note]

Authenticating and authorizing your script

When you run the script for the first time, Apps Script will ask you to authorize it since it will be sending an email on your behalf. If you've never done this before, I've written an article on how to authorize an Apps Script that walks you through this process.

[/note]

When I ran the script, I received an email reminder like the one below. Hooray!

Step 3. Creating an Apps Script Trigger to send the email reminder every morning

The final step is to make this code run every morning. The way to do that in Apps Script is by setting up a trigger.

A trigger automatically runs a function for you based on a schedule that you specify. In this case, I want the email reminder every morning at 7AM. It's pretty simple to set up a trigger to do this.

First, click the clock icon to get to the triggers page.

A new window will open that displays the triggers in the project (there are obviously none yet). So I clicked the + Add Trigger button to create a trigger.

I chose the following settings to configure the trigger:

• Which function to run: sendEmail

• Which deployment to run: Head (I’ll explain what this means in a later post but for now just choose Head)

• Event source: Time-driven (since I want the trigger to be time based)

• Type of time based trigger: Day timer (since I want the script to run at a specific time)

• Time of day: I want an email reminder around 7AM so I chose 6AM to 7AM

• Failure notification settings: I want to get notified daily if the trigger fails so I chose daily

Then I clicked Save and the trigger was created. Now, the [code]sendEmail[/code] function will be run daily between 6AM and 7AM automatically! How cool is that?

That's it. I now have a private and customizable Mood Tracker app that I can use to track my mood over time.

Summary

In this article, I showed you how I built a Mood Tracker application using Google Forms, Google Sheets and Apps Script. Now it is time for you to build one for yourself. I'm excited to see what you'll build.

Ideas for future enhancements

Here are some enhancements that I plan to make in the future.

• Creating a visual dashboard in Sheets to show my how my mood is trending

• Embedding the Google Form directly in the email so you can fill it out from the email itself.

• Sending a weekly email summarizing how you felt that week.

Hope this article was useful and thanks for reading!