Sleep Data Automation

The problem

I attended a presentation by Dr. Helen Burgess's team at the Sleep and Circadian Research Laboratory that walked through how they cleaned Fitbit sleep data by hand, night by night, and I built this automation for the lab in response. The underlying problem is that a Fitbit often counts time spent lying awake in bed, before falling asleep or after waking, as sleep. Researchers correct for that with sleep diaries, in which participants report when they tried to sleep and when they got up. Merging the diary entries with the Fitabase exports was slow manual work, with many small judgment calls for every participant and every night.

What the automation does

• Parses sleep diary messages, sent by text message to a shared mailbox, into a table.
• Walks the 30-second sleep stages to find the real sleep onset and the final wake time, and assigns each episode to the right sleep day even when it starts after midnight.
• Identifies the main sleep episode and filters out naps, using rules the team can configure, such as the longest or the latest episode.
• Computes total sleep time, time awake, time in bed, and related measures.
• Computes heart rate variability averages (RMSSD, HF, and LF) only from the five-minute readings inside the corrected sleep window.
• Optionally adjusts the times when the device and the diary disagree by more than a set number of minutes.
• Applies manual overrides from a separate override file, so every correction is logged.
• Writes one row per participant per day per sleep episode, plus a weekly summary with participant and week selectors.

The output table, with the Power Query editor open on one of its steps. Each step is named so a researcher can follow what happens to the data.

The weekly summary. A study coordinator picks a participant and a week and sees each night's sleep window, sleep time, and heart rate variability.

Design decisions

I built the automation in Excel and Power Query so that any research team can run it without a server or a programming environment. Most teams already have Excel, and refreshing the workbook is one click. Every transformation is a named step in Power Query, so the method is visible and reproducible rather than hidden in a script.

Sleep diaries are collected by text message. Participants text "sleep" or "wake" with their study ID to a shared mailbox, and a Power Automate flow parses each message into a row in a spreadsheet. That removes paper diaries and the transcription that went with them, and the messages carry their own timestamps.

Manual overrides live in a separate spreadsheet. When the team decides that a night needs a correction, they record the new times and the reason there, and the workbook recalculates every measure for that night on the next refresh. Corrections are logged instead of typed over the results, and nothing has to be recalculated by hand.

Who did what

I designed and wrote the entire automation. The sleep-window rules it applies come from Dr. Helen Burgess's Sleep and Circadian Research Laboratory, where they were developed for the IBD-Sleep pilot study. Drs. Helen Burgess and Cathy Goldstein proposed the enhancements that detect naps and adjust the sleep window from the diary. Moony Rizvydeen and Zainab Fayyaz developed the manual override practice that the override spreadsheet automates.

Published results

Once every night in the study was recalculated the same way, the team could measure how often, and by how much, the device's sleep window differed from what the participant reported. That evidence became a 2025 letter in the journal SLEEP, which I co-authored with the study team and which points readers to this code.

In the study's first 100-plus nights across 15 participants, the device marked sleep onset earlier than the participant's actual attempt to sleep on 11.0% of nights, by about 26 minutes on average, and marked the final wake later on 5.5% of nights, by about 20 minutes on average. This usually happened when the participant was reading or using a phone in bed, lying still enough to look asleep; the lab calls this a "BOGUS" onset, for Bogus Onset Generated by User Stillness. The team now recalculates sleep parameters for every night as a matter of course. Read the letter, Lessons learned on the road to improve sleep data extracted from a Fitbit device, or go to DOI 10.1093/sleep/zsae290.

One night's log. The device, the diary, and the sleep stages each give a different sleep onset, three minutes apart. The automation settles the difference the same way every time.

Adoption

The automation is used by more than 15 research studies at multiple universities.