Track Oura recovery next to Strava training in one sheet

Every morning at 7am, append yesterday's recovery and training numbers into a single Google Sheets tracker, so that over time I can see how training load and recovery move together. Use a cron trigger set to 7am daily.

First work out yesterday's date in my local timezone and format it as YYYY-MM-DD. Use that same value as both the start date and the end date on every Oura call, since Oura date parameters expect YYYY-MM-DD.

Pull the recovery side from Oura. Use List Daily Sleep for sleep score, total sleep duration and sleep efficiency. Use List Daily Readiness for readiness score, HRV balance and resting heart rate. Use List Daily Activity for steps and active calories. Use List Workouts to count any workouts Oura recorded that day.

Pull the training side from Strava with List Athlete Activities, filtered to yesterday only. Strava filters activities by epoch seconds, so convert yesterday's local start and end of day into epoch timestamps for the before and after parameters, then discard anything that falls outside yesterday. From each activity capture sport type, moving time, distance and average heart rate.

If there is more than one Strava activity on the day, combine them into the one daily row rather than writing multiple rows: join the distinct sport types with a comma, sum moving time, sum distance, and take the average heart rate weighted by each activity's moving time. Ignore activities with no heart rate data when computing that weighted average.

Write exactly one row per day using Append Values, with the Oura recovery columns and the Strava training columns side by side so a single row tells me what I did and how my body responded. Use this column order: Date, Sleep Score, Total Sleep (hours), Sleep Efficiency (%), Readiness Score, HRV Balance, Resting Heart Rate, Steps, Active Calories, Oura Workout Count, Strava Sport Types, Strava Moving Time (minutes), Strava Distance (km), Strava Average Heart Rate. Convert Oura sleep duration from seconds to hours and Strava distance from meters to kilometers, and round each to one decimal place.

Never skip a date. If Oura returns no data for the day, still write the row with the date filled in and the recovery metric cells left blank rather than omitting the date entirely. Do the same on rest days, where the Strava training cells are blank. The date column has to stay continuous or the charts built on this sheet break.

Two things to handle carefully. Oura collection responses paginate, returning a next_token field, so keep requesting with that token until it comes back null before deciding a day has no data. And Gen3 and Ring 4 users need an active Oura Membership or the API simply returns empty data, so treat an empty Oura response as a legitimate blank row rather than an error, and do not fail the run over it.

If the target sheet is empty, write the header row first using the column names above, then append the day's data row beneath it.