Sleep Diary Core Library

Documentation and code for manipulating sleep diaries

Project maintained by sleepdiary Hosted on GitHub Pages — Theme by mattgraham

Spreadsheet Table format

There is no formal definition for a spreadsheet table, but people often independently create sleep diaries containing a table of values. This format attempts to parse those tables based on common features.

In this directory

You may find the following useful:

Creation process

Spreadsheet tables are usually created in Microsoft Excel or LibreOffice Calc. The user puts headings in the first row, then adds a new row each day.

Export format

The most common raw format for spreadsheets is Office Open XML, followed by OpenDocument. These formats have been published as international standards, and developers are encouraged to use a library to manipulate them.

Spreadsheet tables generally have the following properties:

the first row contains a set of column names
one column indicates the time when each record starts
one column indicates the time when each record ends
there may be a column that indicates the status associated with each record
if there is no status column, all records indicate times the user was asleep
there may be a column that contains comments

Detecting columns

Here is a process to detect which column contains the start time:

if any header cell matches the regular expression /sleep|start|begin/i, the first matching column indicates the start time
otherwise, the leftmost column that does not match /wake|stop|end/i indicates the start time

Here is a process to detect which column contains the end time:

if any header cell matches the regular expression /wake|stop|end/i, the first matching column indicates the end time
otherwise, the leftmost column that does not match /sleep|start|begin/i indicates the end time

Here is a process to detect which column contains the status:

if any header cell matches the regular expression /event|activity|stat(e|us)/i, the first matching column indicates the status
otherwise, the status is always asleep

Here is a process to detect which columns contains comments:

if any header cell matches the regular expression /comment|note/i, the all matching columns indicate the comment
any columns with blank headers indicate comments

The precise process used by the JavaScript code is more complex than the above. For example, it handles the case where a user specifies the full date in some columns, but only the time in others.

Parsing statuses

Here is a process to convert status strings to values:

values that match /sleep/i indicate the user is asleep
values that match /wake/i indicate the user is awake
values that match /snack/i indicate the user is eating a snack
values that match /meal|eat/i but not /snack/i indicate the user is eating a meal
values that match /alco/i indicate the user is drinking an alcoholic drink
values that match /caffeine|coffee|tea|cola/i indicate the user is drinking a caffeinated drink
values that match both /choc/i and /drink/i indicate the user is drinking a chocolate drink
values that match /drink/i but none of the above indicate the user is drinking something that is neither alcoholic, caffeniated nor chocolate
values that match /pill|tranq/i indicate the user is taking a sleeping pill or tranquiliser
values that match /exercise/i indicate the user is exercising
values that match /toilet|bathroom|loo/i indicate the user is using the toilet
values that match /noise/i indicate the user’s sleep has been disturbed by noise
values that match /alarm/i indicate an alarm is occurring
values that match /down|(in|to).*bed/i indicate the user is going to bed
values that match /up|out.*bed/ indicate the user is getting out of bed