Does working out every day feel tiring and boring? What if your goal of exercising for 30 minutes or an hour every day could be shown on a fun and interactive dashboard? Imagine looking back at your past workouts to help you get motivated for the next ones.
By dedicating just 5 minutes each morning or evening, you can start gathering valuable data from your daily activities. Create a Google Sheets document to input details like the time spent reading, writing, or engaging in any other targeted hobby. The magic happens when you connect this data to a Grafana dashboard. In no time, you’ll have a visually appealing and informative dashboard that reflects your progress. It’s a small investment of time that can lead to big changes in achieving your daily goals.
By the end of this article, you will :
- understand the benefits of tracking your daily activities for habit formation
- gain practical insights into setting up a simple data model in Google Sheets.
- learn how to opening a free account on Grafana Cloud, and connect your Google Sheet to Grafana,
- create visually appealing dashboards without writing any line of code to effortlessly monitor and track your progress over time.
The tools used in this article work online, so you don’t need to download and install any software. To use them, you just need an email address and an internet connection. And the free version of Grafana Cloud is enough to do everything you need for this project, so you don’t need to pay for a subscription.
The Benefits of Tracking Daily Activities for Habit Formation
Observing your daily activities can significantly impact the formation of habits. A consensus among various self-discipline resources, including books and articles, supports the idea that making small, consistent efforts can bring substantial and enduring changes in your daily routines.
As James Clear puts it in “Atomic Habits”: “You should be far more concerned with your current trajectory than with your current results”. Monitoring your daily activities allows you to adjust your trajectory, paving the way for sustained habit formation and long-term success.
Consider the daily habit of reading a book. Your current result might be reading a few pages each day, but as James Clear emphasizes in the quote above, your focus should extend beyond immediate outcomes. Instead, concentrate on your trajectory, for instance the consistent effort you invest in reading daily. Over time, this trajectory can lead to completing numerous books in a year.
When you are defining an objective to track, focus on daily, time-based objectives that you can directly influence, like exercising for 30 minutes each day. These leading metrics contribute to achieving lagging metrics, such as monthly weight loss. Prioritize your daily, consistent efforts and trajectory rather than fixating on longer-term outcomes.
By using the data pipeline presented in this article, you gain the ability to accurately monitor the trajectory of your daily habit. Simply adding your habit over time in a Google data Sheet, and using Grafana time-series visualization allows you to track the consistent effort invested in improving each day. The visualization of the historical data not only ensures the alignment of your daily actions with your long-term goal you set for yourself but empowers you to adapt and refine your routine.
How to Set Up a Simple Data Model in Google Sheets for Each Daily Activity
In the initial stages here, we will start by capturing a minimal subset of metrics that you can easily fill in your sheet in less than 5 minutes every day.
Let’s explore three common objectives you can set for yourself:
- Exercise: Engage in at least 30 minutes of physical exercise daily.
- Reading: Dedicate a minimum of 30 minutes per day to reading a book.
- Hobby: Generally allocate 1 hour each day to work on a hobby or a side hustle.
The data pipeline aims to visualize your consistent daily effort to build a routine, so start with a minimal set of data to spend less time entering them in your data sheet at the end of the day.
This article focuses on visualizing and analyzing your daily commitments using Grafana. However, it doesn’t explain how to collect data from your daily activities. There are many productivity apps, like Todoist or TickTick, that can help you track your recurring tasks and reminders. We’ll explore how to automatically import data from such apps into your Google Sheet in a future article. For now, we’ll assume you can manually input your daily data into your Google Sheet.
We choose Google Sheets for its simplicity and online accessibility as the foundation for our data input and storage. If you have a Gmail account (which is free for everyone), you already have access to Google Sheets. Simply log in to your Gmail account and navigate to Google Drive.
From Google Drive, you can click on the New button and select Google Sheets from the dropdown menu. This will open a new, blank spreadsheet. A spreadsheet is a collection of many worksheets. By default three worksheets are created. We will be using a separate worksheet inside the document to track the progress of each new activity. For our example above, we have a sheet for ‘Exercise’, another one for ‘Reading’ sheet and a last one for ‘Hobby’ sheet. Strictly speaking, you could group all your activities in a single sheet but we assume here for simplicity that every single goal that you set for yourself can be easily monitored in a separate sheet.
For this tutorial, we provide a public template file which you can use as a basis to create your own. Click on the link to open the file directly in your Google sheet, then click the “File” Menu and choose “Create a copy”. Choose a meaningful name then click “Create a Copy”. The file is now available in your Google drive ready to record your daily feats. The example dashboards given in the next sections rely on the data provided in the template file. A screenshot of the “Exercise” daily activity is given below.
Take a moment to go through the columns in each sheet. Customize the columns to fit your specific tracking needs, but ensure to retain at least the date and duration columns, as they form the foundation for visualizing our daily progress on Grafana dashboards.
Create a Shareable Link to Access your document from Grafana
- Launch Google Sheets and locate the document you want to share.
- Click the “Share” button in the top right corner of the Google Sheet.
- In the sharing window, select “Restricted” from the sharing options. Then, choose “Anyone with the link” to make your sheet publicly accessible.
- Click the “Done” button to apply the sharing settings. Keep the Google Sheet window open.
- Extract the unique identifier: Look at the address bar of your web browser. The unique identifier for your Google Sheet is the string of text between the “/d/” and “/edit#”. Your identifier should be similar to 1wy9196yvqQ0G1I6V7H69bhdJYQOZXKd9lTvS8ILy0wU
- Save the identifier: Copy this identifier and paste it into a safe location. You’ll need this identifier to connect Grafana to your Google Sheet later.
Security Note: The result of the previous step will make your data visible to anyone who has the link to your spreadsheet. Grafana or anyone with the link will not be able to change the data. This method is generally safe for most use cases. However, if you are storing sensitive data, you should keep your spreadsheet private and use more secure methods, such as Google’s Service Account authentication.
Opening a Free Account on Grafana Cloud
Grafana is a widely used open-source data visualization platform that enables you to collect, analyze, and visualize data from various sources. It was primarily designed for DevOps and IT operations teams. This is due to its ability to effectively monitor and analyze metrics from applications, infrastructure, and IoT devices. However, Grafana has reached other fields, including business intelligence and analytics, data science and machine learning, banking and financial services.
Creating a free Grafana account is open to everyone. Here’s how:
- Initiate Sign-up: Visit the Grafana Cloud signup page.
- Federation Option: You can either federate your Grafana account with your existing Google or Microsoft account. This will allow you to sign in to Grafana and access your dashboard using your existing credentials from any device.
- Direct Sign-up: If you prefer not to federate, enter your email address, choose a strong password, and agree to the terms of service.
- Verify Email: Check your email inbox for a verification email from Grafana. Click on the link provided to confirm your email address.
- Select Deployment Region: Review the URL displayed on the next page and choose a deployment region closest to your geographical location. This will optimize performance and reduce latency for accessing your Grafana dashboard.
- Launch Instance: Click on the “Finish Setup” button to initiate the creation of your Grafana Cloud instance.
- Instance Deployment: Allow for some time as the instance is being deployed and prepared. Once complete, you should see a confirmation message.
- Access Grafana Cloud: You will be directed to your Grafana Cloud instance. Navigate to your designated URL and enter your chosen credentials to log in.
- Launch Grafana Dashboard: Once logged in, locate the “My Account” option in the top right corner and click on it. Scroll down to the “Manage your Grafana Cloud Stack” section.
- Access Grafana: Under the “Grafana” section, click on the “Launch” button. This will open a new page where you can create and manage your Grafana dashboards.
Remember to keep your Grafana Cloud instance URL and credentials safe as you’ll need them to access and manage your dashboards.
Establishing a Connection from Grafana to Google Sheets
In this step, you’ll establish a connection between your Grafana instance and your Google Sheet, allowing you to visualize and analyze your data in a table view. A data source serves as a bridge to a data storage repository where the actual data you want to visualize resides. Each data source has its unique configuration requirements.
Install Google Sheets Plugin
Grafana supports a diverse range of data sources through a plugin mechanism, based on various data storage technologies, including relational databases, time-series databases, cloud data warehouses, and more. Common aspects of a data source include connection details, data formats, data query and transformation methods.
- Using the provided URL from the previous section, launch your cloud Grafana instance.
- At the bottom left pane, navigate to the “Connections” menu and select “Data Sources.”
- Click on the “Add New Data Source” button.
- Scroll down the page and click on “Find more data source plugins.”
- Install the Google Sheets Plugin: Start typing “Google Sheets” in the search bar and select the corresponding plugin from the list below.
- Click on “Install via grafana.com” in the top right corner.
- A new tab will open in your browser. Scroll down and select “Install plugin.”
- Navigate back to your Grafana instance and refresh the page; The text link is now “Uninstall via grafana.com”
Add Google Sheet As New Data Source
- Click on the “Data Sources” page on the left panel again.
- A new data source called “grafana-googlesheets-datasource” should appear. Select it to configure authentication with Google.
- The data source requires an API key generated in the Google API console to authenticate and query data from your Google Sheet document. The procedure is explained in the plugin’s “Settings” page, as shown in the screenshot.
- Follow the instructions to access the Credentials page in the Google API Console. If you haven’t used the Google Cloud console before, you’ll need to create a Google Cloud project. Make sure to follow the link to the Credentials page in the Google API Console.
- Choose either an existing project or create a new one. Give your project a meaningful name that reflects its purpose.
- Click on “Create credentials” and select “API key.” This will generate a unique API key for your project.
- Copy the generated API key, Navigate back to the settings page in Grafana paste it into Grafana’s data source settings.
- In case you encounter a “Permission check failed” error, navigate to the Google Sheets API in the Google Cloud Console Marketplace. Click “Enable” and ensure the project is selected in the dropdown menu.
- Navigate back to the Grafana instance and click “Save & test” in the data source settings. If you still encounter the “Permission check failed” error, wait a few minutes. Google documentation suggests it may take up to five minutes for settings to take effect.
- If everything is configured correctly, you’ll see a message indicating successful authentication.
- Now you can connect to your Google Sheet and create dashboards to visualize your daily commitments.
Creating Dashboards with Grafana and Google Sheets Data
Creating a well-designed dashboard can help you visually track your daily commitments, spot patterns in your activities, and monitor your progress over time. For instance, you can build a dashboard that displays your daily exercise minutes, missed exercise days, and overall activity trends.
Grafana’s User Interface: A Quick Overview
To create our dashboard, we’ll use basic Grafana panels and customizations. For more advanced features, numerous tutorials are available on Grafana’s website and online. Additionally, Grafana offers a helpful search assistant where you can ask any question.
Building a Dashboard with Grafana
A typical Grafana dashboard typically consists of various visualization panels, including line charts, bar charts, scatter plots, and more. Since your Google Sheet data will include a date column, you can navigate through time and visualize changes in your collected data over time. Grafana provides a top menu bar with a date/time picker to help you focus on specific time periods, such as the current week, month, or year.
In the following sections, we’ll guide you through the steps of creating a dashboard similar to the following example. The code source is available on GitHub. Feel free to import it into Grafana as you build your dashboard and customize it to meet your specific needs.
Adding Visual Panels to Your Dashboard
- Navigate to the Dashboard tab: In the left menu, select “Dashboard” and click on “New.” This will open a new window where you can start creating panels for your dashboard.
- Import an existing dashboard (optional): If you prefer, you can import the dashboard provided in the GitHub repository. This will give you a starting point to customize based on your data sheet.
- Click on the “Add visualization” button in the top right corner.
- Select “Google Sheets” as your data source.
- Paste the spreadsheet ID you obtained earlier from your shared Google Sheet document.
- Choose the appropriate data range within your Google Sheet document. This typically includes the columns containing the data you want to visualize.
- Select the type of chart you want to use to represent your data. Common options include line charts, bar charts, pie charts, and histograms.
- Adjust the panel’s settings to your liking. You can modify the chart type, title, labels, and other elements.
Adding a Text Panel to highlight the daily objectives
- Click on the “Add visualization” button and select “Text” from the left panel.
- In the “Panel Options” section, remove the default title and content.
- In the “Text” section, replace the default content with the following message:
# <p style="text-align: center">Tracking My Everyday Fitness Goal!</p>- Click on the “Apply” button to save your customizations.
Saving the Dashboard
- Click on the “Save” button located in the top menu bar.
- Enter a descriptive name for your dashboard in the “Dashboard Name” field.
- If desired, select a specific folder from the “Save in folder” dropdown menu. The dashboard will be saved to the “General” folder by default.
- To finalize the dashboard and save your changes.
Tracking Your Latest Exercise Session
This panel will display the date of your most recent exercise session in your “Exercise” worksheet of your Google Sheet. The date will always reflect the selected time frame on the top menu bar.
- Click the “Add” button on the top menu bar and select “Visualization.”
- Instead of the default Time Series visualization, select “Stat.”
- In the left sidebar, under “Panel options,” add a title to your panel, such as “Last Exercise.”
- Under “Query,” choose “Google Sheets” as the data source.
- Paste the spreadsheet ID you obtained from your shared Google Sheet document.
- Add the “Range”, something like Exercise!A:D if you are using the provided template file.
- Toggle on “Use Time Filter” to limit the data to the selected time frame.
- Use the “Table view” option at the top of the page to confirm the data is correctly retrieved from your Google Sheet document.
- You can select the “Query inspector” inside the “Query” option to refresh the query and inspect the retrieved data.
- Under “Value Options” on the left sidebar, identify the “Calculation” field and select “Last*” to retrieve the latest non-null values. Select “Date” on the “Fields” field.
- In the left sidebar, under “Standard options” and the “Unit” field, select “Date&Time/From Now.”
- Under “Standard options” and the “color scheme” field, change to “Single color” and select a color of your choice.
- Click the “Apply” button to apply the customizations.
- You can resize and move the newly created panel to your desired position on the dashboard.
Analyzing Average Exercise Duration
This panel will display the average duration of all your physical exercises performed within the selected time frame.
- Follow the previous steps to create a new “Stat” visualization.
- In the “Query” section, specify “Google Sheets” as the data source and paste the spreadsheet ID you obtained from your shared Google Sheet document. Set the “Range”: Exercise!A:D and enable “Use Time Filter.”
- Calculate Average Duration: Under “Value Options,” select “Mean” in the “Calculation” field and choose “Exercise Duration” as the field to be included in the panel.
- In the “Standard options” section, select “Time/minutes(m)” in the “Unit” field.
- Under “Color scheme,” select “Red-Yellow-Green (by value)” to represent the average duration using an intuitive color gradient.
- Click “Apply” to save the customizations. Resize and move the panel to your desired position on the dashboard.
- Regularly save your dashboard to ensure your changes are preserved.
Follow the same steps to add panels for:
- Number of Exercise Sessions: Calculate the total number of exercise sessions within the selected time frame.
- Total Exercise Time: Calculate the total duration of all exercises performed within the selected time frame.
- Last Time for Side Activity: Retrieve the date of your most recent side activity based on data from the “Hobby” worksheet
Monitoring Exercise Progress over Time
Utilize Grafana’s ability to visualize temporal changes by creating a panel to track your exercise progress. This will help you stay motivated and assess your progress towards your fitness goals. It also allows you to answer questions like: How often do I exercise? How many times have I met my daily exercise goal?
- Follow the previous steps to create a new “Time Series” visualization.
- Assign a descriptive title to your panel, specify the “Google Sheets” data source, and paste the spreadsheet ID. Set the “Range”: Exercise!A:D and enable “Use Time Filter.”
- Under “Thresholds,” define your exercise goals by setting minimum and recommended durations. For example, if you aim for at least 30 minutes and ideally 60 minutes daily, set the thresholds accordingly.
- Choose “As filled regions” in the “Show thresholds” field to visually represent your goals.
- If your Google Sheet contains multiple exercise entries per day, add a transformation to aggregate them.
- Click “Transform Data” and “Add Transformation” near the “Query” entry.
- Apply Group By Transformation: Select “Group By” and configure as follows: Date: Group By / Exercise Type: Ignored / Exercise Duration: Calculate ⇒ Total
- Apply Transformation and Customize: Click “Apply” to apply the transformation and customize the panel’s appearance. Resize and move it to your desired location on the dashboard.
Similar to exercise tracking, you can create a panel dedicated to monitoring your side activity time. This will help you stay on track with your side project goals.
Grafana’s Gauge and Table Visualizations for Side Activities
- Grafana’s gauge view offers a rapid and straightforward way to visualize the total time spent on various side activities. This functionality enables you to readily spot on which hobby you allocate much of your time.
- Additionally, Grafana’s table view offers a detailed breakdown of your recent side activities. Each row provides a comprehensive overview of your time investments, including duration, date, and personal notes.
For simplicity, this article does not provide detailed instructions on setting up these visualization panels. However, you can easily import the dashboard from GitHub and explore the detailed configuration of each panel.
Dashboard Interactions
Grafana provides various tools to visualize, update panels with fresh data, and share your dashboard once you’re satisfied:
- Selecting Time Frame: As highlighted earlier, you can select the time frame from the top menu bar. You can select for instance “This month so far” to highlight your achievements for the ongoing month.
- Refreshing Your Data: You can keep your dashboard up-to-date by clicking the refresh icon on the top menu bar. Note that fetching data for multiple panels may take some time.
- Adjusting Refresh Settings: By default, data refreshes on demand and when opening the dashboard. If your data source needs frequent updates, customize the parameter to automatically refresh the dashboard from every 5 seconds up to once a day.
- Enabling Kiosk mode: You can display your dashboard in a full-screen view. In this view, menu bars and panel menus are removed to provide a very clean view. This mode is useful if you are displaying your dashboard on your home TV.
- Sharing your dashboard: Once you are satisfied, you can click on the ‘Share’ button on the button on the top menu bar to create a snapshot of your achievements and share the link around, or export the source code of the dashboard. For example, this link offers a snapshot featuring sample data in the template file.
Beyond The Basic Pipeline: Introducing a Data Orchestration Pipeline.
The goal of this article was to provide the reader with accessible tools to collect data and visualize them to track daily progress on personal objectives.
Before you embark on your data-tracking journey, it’s essential to acknowledge the limitations of the tools we’ve explored:
- Grafana is very good at visualizing extensive data from diverse sources and setting up alerts and other automations. However, when it comes to specific data manipulation and transformations, Google Sheets functions can offer a more straightforward solution within the data source. Nevertheless, It’s worth noting that Grafana provides more and more transformation functions for those seeking an alternative method directly within the platform.
- The method outlined in this article involves the users to collect data in a Google Sheet on a daily basis. While this is suitable for data with low velocity and small variety, a more streamlined approach involves setting up workflow automation to minimize manual input. Leveraging tools such as Zapier, n8n, or even Google Apps Script can enable a seamless connection to a fitness app or cloud logs from connected devices, to automatically populate Google Sheet documents at regular intervals.
With the basic data collection and visualization workflow established here, you’re already well on your way to unlocking valuable insights that optimize your time management. As you delve deeper into data analytics, be sure to explore additional Grafana features and charting options to enhance your visualizations. And remember, if you need further assistance, feel free to reach out to me for personalized assistance and explore more data-driven opportunities.
Sections of this article have been enhanced using Bard, powered by PaLM2, Google’s large language model, to enhance clarity and improve English expressions.