Automatically Push Your Oura Ring Data to Google Sheets with Python

https://github.com/samchaaa/oura_to_sheets

Repo here: https://github.com/samchaaa/oura_to_sheets

Here are some quick code snippets to get you started pulling your Oura ring data, transforming it in python, and pushing to Google Sheets.

This assumes familiarity with python, Google Sheets, and Google Cloud. If not, there are links for further instructions.

First the setup and code, then commentary.

The code

This code uses the oura-ring wrapper for Oura API requests, and gspread for Google Sheets.

Here are all the steps to setting up the system:
1. git clone https://github.com/samchaaa/oura_to_sheets

2. Get your Oura API personal access token, paste in oura_key.txt

3. File > Make a copy, this Google Sheet, copy the sheet id to config.py

4. Set up Google Cloud service account, download .json key and paste in svc_acct.json

5. Share the Google Sheet with the service account

All of this info is kept in config.py, which is pulled into the python script or notebook, whichever you want to use.

For further instructions on setting up gspread and the Google service account, see the gspread docs for the best explanation.

Running the code

python update_sheets.py to run the update.

There are two ways to run the code: the notebook (notebook.ipynb), or the python script (update_sheets.py). Both are the same end result.

The notebook notebook.ipynb is available for digging into the data, creating visualizations, and developing new transformations of the different Oura endpoints. At the end, it still pushes to Google Sheets.

The script update_sheets.py is for quick updates. This is also useful if you want to schedule and run this task in the cloud — for example, using PythonAnywhere (shameless affiliate link).

Commentary

How the sheet works

The script pushes data to the “data” tab on the sheet:

These columns then get pulled into the main sheet (“202212”) via VLOOKUP:
=ifna(vlookup(A2, data!A:C, 2, false), “-”)

Note the VLOOKUP is dependent on the formats of the date in “202212” col A:A and “data” col A:A. If you change either one, make sure either they both match or the code is updated accordingly.

Explanation of transformations

The Oura API does a pretty good job of cleaning and consolidating your data. Most top-level metrics (by day) you may probably just pull right into the sheet.

That being said, if you are doing shift work or traveling, there are a few tweaks you may make to clean up your data.

Converting timezone (heart rate data)

# set timezone to local
hr = client.get_heart_rate(ydy, tdy)
hr = pd.DataFrame(hr).set_index('timestamp').drop(columns=['source'])
hr.index = [pd.to_datetime(_.replace('+00:00', '')) for _ in hr.index]
hr.index = hr.index.tz_localize('UTC')
hr.index = hr.index.tz_convert('Asia/Seoul')
# hr.plot(figsize=(13,5));

By default the heart rate data is in UTC time. (I don’t know if this is from the Oura API, or the oura-ring wrapper.) This simply transforms into a timezone of your choice.

Aggregating sleep by day

If you have fragmented sleep, or nap around the clock, this is useful for getting your total sleep by day.

# filter sleep to what we need and engineer duration
s = [
  {
    x: _[x] for x in _
      if x in [
        'day',
        'bedtime_end',
        'bedtime_start',
        'total_sleep_duration',
      ]
  } for _ in sleep
]
for _ in s:
  _['dur'] = (
    pd.to_datetime(_['bedtime_end']) - 
    pd.to_datetime(_['bedtime_start'])
  )
# convert seconds to hours
total_sleep = pd.DataFrame(s)[
  ['day', 'total_sleep_duration']
].groupby('day').sum() / 60 / 60

This simply uses pd.to_datetime() to convert whatever datetime format into a python datetime object. By default, it is in seconds. Then we groupby on date, sum up all your sleep sessions, and convert into hours.

Converting date to excel date

This makes it easier to have the VLOOKUP formula in the sheet work.

def excel_date(date1):
  """
  converts datetime date to excel int date
  https://stackoverflow.com/a/9574948
  """
  temp = datetime.datetime(1899, 12, 30) # Note, not 31st Dec but 30th!
  delta = date1 - temp
  return float(delta.days) + (float(delta.seconds) / 86400)

This just converts your python datetime date to an excel-style integer date.

Further steps

Here are some other steps I’d take to streamline these updates or expand further.

Put it in Google Apps Script

If you want to cut out a few steps from this process, you could pull the data directly into Google Apps Script and transform there. I just find python easier.

Automate the app update

One issue with the Oura update flow is that you need to open the app in order to push your data to from the ring, to the phone, to the cloud.

Here are some ways to automate this push, from users on r/ouraring.

Engineer other metrics

There are tons of other endpoints to use, via the oura-ring API.

get_daily_activity(): aggregate calories burnt

get_workouts(): get in-depth heartrate, HRV, and calories from recorded workouts

get_tags(): get tag frequencies

get_sleep_periods(): aggregate time in REM, deep sleep, sleep latency, etc.

Put it in the cloud

PythonAnywhere (shameless affiliate link)

Feedback and ideas for improvement welcome! Find me via GitHub: https://github.com/samchaaa/