Episode 8: Tracking Diet Goals and Meal Logs with Appsheet – Health DX –

Health DX Progress Confirmation

This is a continuation of our discussion about building our own employee health management system. It’s been about two weeks since we migrated from spreadsheets to Appsheet, and the data has continued to grow. As before, we’ll further review both the weight loss initiative as part of our health DX efforts and the underlying mechanisms.

• Weight: Overall decreasing trend, averaging about 1kg less.
• Body Fat Percentage: Also decreased by about 1%.
• Steps: Averaging over 9,000 steps.
• Sleep: Mostly under 5 hours, on the low side.

I feel like I’m starting to see some results, but I’ll continue working on the following: addressing operational challenges, reviewing the status of my health DX with my trainer, and making improvements.

Advantages and Challenges as a Health DX Tool

Here’s a summary of the pros and cons we experienced after implementing AppSheet in our previous initiative.

• Pros
  • Input can now be done on smartphones, making it easier to enter data anywhere.
  • Visibility improved by using graphs on the dashboard.
• Cons
  • Make it clearer by including target values to aim for.
  • Weight fluctuates a lot, so make trends easier to understand.
  • Since values are entered manually, we want to make input easier.

First, we’ll proceed by discussing the current situation with the trainer.

Current Status Review and Future Action Planning with the Trainer (ChatGPT)

We will provide the current data to the trainer to review the current figures and discuss future actions. The following are ongoing initiatives from the previous session.

Current Situation Analysis and Countermeasures

As before, we provided the data to the trainer to confirm the current status. The trainer gave us the harsh opinion that “it’s still too early to say we’re seeing a downward trend.” However, since the numbers do show some reduction, they suggested we try the following measures.

Regarding sleep, I’ll focus on effort, and for meal tracking, I’ll explore improvements within the tool itself.

Health DX Tool Improvements

We’ll address the issues mentioned at the beginning and the trainer’s feedback on meal tracking within the tool. This time, we’ll focus on the following four points:

• Measure 1: Add Target Values: Include target values to make goals clearer.
• Measure 2: Add Moving Averages: Weight fluctuates a lot, so moving averages will help show trends more clearly.
• Measure 3: Improve Input Method: Currently manual entry; make input easier.
• Measure 4: Add Meal Logging: Add a dedicated screen and data for meal records.

Measure 1: Add Target Values

While numbers are now visible on dashboards and graphs, without targets, it’s unclear how much effort is needed. We’ll set target values for each item and display them on the graphs.

1.Adding Target Values

Add a “Target Values” sheet to the spreadsheet where data is recorded and set target values for each item.

2.Link the data recording sheet with the target value sheet in Appsheet

Use the Lookup function to add a virtual column and set up cross-sheet references using the Lookup function.

Set up the target value sheet to load in Appsheet as before.

Next, we’ll add a link between the two sheets. Since the chart references data from Sheet1, open Sheet1 and add a Virtual Column (VC) with a Lookup function for the link. First, add the VC.

Click the Add Virtual Column button to add an item. Set the formula as follows. This Lookup function references another sheet and sets the value in this VC.

Adding an item will place it at the bottom of the table as shown below.

Next, configure the graph settings. Open the UX Weight section, click the Add button under Chart columns, and add the “Weight (Goal)” column you added earlier. After adding it, the display will appear as shown below. The graph colors are also set individually. The target value is uniformly displayed in red.

Adding the target weight is now complete. Similarly, add the VC and graph for each item. Once all additions are finished, it will look like this. For the target weight, while the ultimate goal is 60kg, it feels too distant, so I’ve set the immediate target to 67kg to keep it within an achievable range. Everything else remains unchanged.

This should make it easier to identify your target compared to just looking at actual values, right?

Countermeasure 2: Adding a Moving Average

The trainer said, “Since a person’s weight fluctuates by about ±0.8kg daily due to body water levels, it’s better to take a moving average over about a week to see the trend.” So, first, we’ll add a moving average to the weight section. The method for adding it is the same as above: add a VC and include the calculation formula.

Definition of this moving average: Calculate the average of the weight data for the week including the current day (from 6 days prior to the current day), and use this as the moving average value for the current day.

The VC settings are as follows:

Adding this item to the UX weight will enable the moving average.

A blue broken line has been added to the graph on the right. While the actual weight values fluctuate wildly, you can see that the moving average shows a smoother progression. It should now be easier to see that the weight has decreased slightly compared to the beginning.

Countermeasure 3: Improve Input Methods

Currently, input is limited to numerical fields and text notes, with text input or numerical adjustments via the plus/minus icons on the far right. We will make input methods more user-friendly by selecting them based on the data type.

The sleep duration input method will be changed to a slider input. To display the slider, select Range and set it to a range of 3 to 10 hours in 0.2-hour increments.

Try adjusting each setting to experiment with different options. In the next meal log section, we’ll test the button input method.

Countermeasure 4: Adding Meal Logs

Since meal preferences vary, please customize the following as a reference example for using the tool. The trainer mentioned “fried foods no more than twice a week,” so we’ll add a field for whether fried foods were consumed.

Set up the management fields as follows:

• Breakfast: Enter common breakfast items via buttons
• Breakfast Notes: Enter other items as text
• Add similar items for Lunch, Dinner, and Snacks
• Fried Foods: Indicate whether fried foods were consumed that day

First, add data columns to the spreadsheet. The newly added columns are highlighted in green for clarity. You’ll notice a column labeled S1 at the top; this is included for page control on the input screen. It might be possible to omit it, but for now, it’s included.

Below are the methods for switching input pages, entering meal buttons, and indicating fried food presence.

1.Switching Input Pages

Configure page switching using the S1 item added above. Set S1 as follows: Set Type to Show and Category to Page_Header. This will display “Next” next to the Save button on the right, and the meal item added above will appear on the next page.

When switching pages, if a message in red text appears indicating that an entry is required, uncheck the “Require?” box next to the data field in the list. Currently, it’s best to leave all boxes unchecked except for the date field.

2.Adding Meal Input Buttons

For example, if you regularly eat certain items every morning like eggs, bread, and milk, adding these as buttons allows you to input them all at once. Items you don’t eat daily should be listed in the text input field.

Set the Type to EnumList, press the Add button for Values, and enter items like eggs, bread, and milk as shown in the figure below.

Once saved, you can input items by clicking buttons as shown in the lower right screen, then add any missing items in text. If the items used change, we’ll review the button contents and customize them for easier use.

3.Whether you ate fried food

If you eat fried food with every meal, it’s better to add a fried food button to the top buttons. However, if you want to manage it only once a day or so, keep it as a separate item like this time. It might also be clearer for your trainer to see if they’re separated.

Since this item asks whether you ate fried food with a Yes or No answer, it shouldn’t allow multiple selections like the top buttons. It must be one or the other.

Simply set the Category to Yes/No. This automatically generates a button where only one option can be pressed. You can also change the button text in the settings.

Next, add lunch, dinner, and snacks in the same way to complete this improvement.

Finally

This time, we’ve further reviewed visualization and input methods, making daily use easier. Comparing against target values should also boost motivation.

However, as the trainer pointed out, it’s important not to get too caught up in daily fluctuations. It’s better to accumulate data weekly or over a period and then review it together with the trainer. Currently, we’re conducting weekly reviews.

I’d like to continue operating in this state for a while.