Training guidance tools using the API

The formula uses isoweeknum, which is the first Thursday of the year. I was meant to provide a fix for that, and forgot about it.

I forgot about the Kg/Pounds conversion, but did have a look at it.

The dropdown list shows ā€œKilograms (Kg)ā€, but the formula shows ā€œKilogramsā€ only.
If you add " (Kg)" it will be correct; note there is a space between Kilograms and (Kg).

Before:

After:

There is a hidden tab (called lookup), that does a lookup on the data from Activities and Wellness, to then summarise the weekly Load, Fitness, Fatigue, Ramp Rate and Hours, effective every Monday. The date might show 2022-52 but it is ā€œtaken care ofā€ in the lookup tab.

1 Like

Do I understand correctly that only Power Query is supported on Mac? If yes, Iā€™m outta luck 'cause I have v16.16 and v16.57 or later is needed.

Importing a text file just throws the data into new columns and nothing else populates.

I installed the trial version of Excel 365 and now have Get Data (Power Query). However, no option window opens when clicking Data Source Settings.

Trying the Google Sheets versionā€¦ Is there a ReadMe for that version? I canā€™t figure out how to make it work either.

Thanks for your work guys looks great.
Iā€™m usually on a Mac so had held off until something like the sheet @Gerald produced a couple of weeks ago arrived.
I already have a simple shell script that pulls activities via API calls for me and my ā€œcoachedā€ athletes (the family) and then opens an Excel file that pulls in the data.

So making this work for activities was a simple matter of editing the Data Source, all done and working. :slight_smile:
I hoped wellness and getting HRV graph would be as easy. API call was straightforward, file downloads, looks OK, but returns an Expression.Error on Run_eftp whenever I try to refresh, has anyone else seen this, or have any suggestions?

image

Easiest workaround is to add a fake FTP to your settings for running in intervals.
This has been corrected in the latest Win version.DonĀ“t know if it is in latest Mac Version from @Gerald .

A shortcut is to enter the run eFTP in the following two places:

  1. Settings (set FTP under run settings)
  2. Activities (set eFTP for Run)


I havenā€™t updated it since version 1.0
Iā€™ll try spend some time updating it based on the the latest changes required on the windows version.

Open the Power Query Editor advanced editor and copy paste the highlighted section from the latest Win version.
Donā€™t change the source setting and you should be good.

1 Like

Thanks @MedTechCD and @Gerald, I have an FTP set (for run and bike) and eFTP (at least relatively recently), Iā€™ve tried adding in the eFTP as suggested, and only getting wellness data from last year or so (where the data is ā€œgoodā€) and ran in to same problem.

In the end I skipped the import and did an old school copy and paste from csv open as separate Excel sheet, and found that thereā€™s an extra column in mine. Unfortunately with many columns in the middle blank I donā€™t know where the misalignment has occurred yet (somewhere between sleep quality and Vo2max is all I can tell so far), but can see a whole pile of things working nicely so many thanks guys!

1 Like

I am getting an error too from the Wellness tab. As the MacOS version is a manual download and not API linked, it shouldnā€™t give errors like on the Windows version.

Iā€™ll take a look later today, or over the weekend.

Depends on how you get the manual download in the sheet.
If you use the Get Data from File (csv, txt) as the source, everything else can be re-used. The functionality to adapt to regional settings will remain intact.
If you copy/paste, thatā€™s another storyā€¦
Are you downloading the Wellness and Activity csv from the intervals website or are you using a curl command to get them?
If downloaded from the website, a whole lot off columns need to be checked for existence because that download contains only what you configure as visible. Best thing to do in that case, is remove all entities from the Query command that are not in your download, likeā€™ {ā€œkcalConsumedā€, type number}ā€™ for example. Fastest is using the Advanced editor and when done, save the query as you will use it.

I use curl ā€œcurl -u API_KEY:AKeyWasHere https://intervals.icu/api/v1/athlete/iXXXX/wellness.csv > stu_wellness.csvā€

When that failed to refresh as a data source I eventually thought to copy and paste into Excel (from csv opened in Excel), and thatā€™s how I noticed the column mismatch reported with the curl and Excel data refresh way.

To try to work out which column I input some wellness data today thatā€™s not normally present, to try to narrow down which field was extra/missing somewhere, and the results (below) were more confusing.
I input motivation, and blood pressure data, those three are 1 column to the left of where they ā€œshouldā€ be, but by Vo2max everything is 2 field to the left.

injury motivation spO2 systolic diastolic bloodGlucose lactate hydration hydrationVolume bodyFat abdomen comments Vo2max ctl atl rampRate
3 132 82 42.580322 36.908264
42.88345 37.96913
45 43.916737 43.799862

So Iā€™ve resorted to field by field comparison in csv vs Excel and can see there is a mismatch, as far as Iā€™ve noticed a few are in different places and menstrualPhasePredicted is the only extra:
CSV via CURL
date weight restingHR hrv hrvSDNN readiness menstrualPhase menstrualPhasePredicted kcalConsumed sleepSecs sleepScore sleepQuality avgSleepingHR soreness fatigue stress mood motivation spO2 systolic diastolic hydration hydrationVolume bloodGlucose lactate bodyFat abdomen comments injury baevskySI vo2max ctl atl rampRate ctlLoad atlLoad Ride_eftp Run_eftp
vs Excel
date weight restingHR hrv hrvSDNN readiness baevskySI menstrualPhase kcalConsumed sleepSecs sleepScore sleepQuality avgSleepingHR soreness fatigue stress mood injury motivation spO2 systolic diastolic bloodGlucose lactate hydration hydrationVolume bodyFat abdomen comments Vo2max ctl atl rampRate ctlLoad atlLoad Ride_eftp Run_eftp Column1

Does anyone have any thoughts on how to match up (have already considered importing raw CSV to a sheet, and pulling data from there into ā€œWellnessā€ tab to allow for column swaps, or some *nix string hackery to rip and push the right number of fields to make it work), but would be curious to see what others are experiencing before I do something non transferable and maybe pointless).

Update:
Iā€™ve found a way to make it work (at least on macOS and presumably *nix) stopping the extra, command line is now:
curl -u API_KEY:AKeyWasHere https://intervals.icu/api/v1/athlete/iXXXX/wellness.csv > stu_wellness.tmp
cut -d , -f 1-7,9-99 stu_wellness.tmp > stu_wellness.csv

Found a separate issue where the decimal in the Run_eftp is missed by something, so 330.99518 becomes 33099518 and 331.3265 becomes 3313265, which makes the slightly higher value and order of magnitude smaller (due to less decimal places), but I donā€™t have any ideas about fixing that oneā€¦

1 Like

If I untick certain fields in the wellness table, I get an error; but this is only in the download, copy and paste option.

Your last paragraph also happened to me, where weight would be 790 instead of 79.0, I guess based on the decimal used (period VD comma). The formatting matters here, and what works for me requires something different for others.

For the MacOS version, it requires editing the source data location, so Iā€™d have to share my file with a link to the csv files in the download location. Iā€™ll work it out during this weekend.

My advice:
Donā€™t copy paste but always use the Get Data function from Excell.
If you Get Data from a csv (regardless if it comes from manual download or from a curl query) the first line of the csv states the column names. Use promote Headers to automatically set the column names from the first line and anything done afterwards is referenced to the column names. The position in the csv or the excel file doesnā€™t matter.
A second advantage of using Get Data is that you can specify the origin of the file (in this case itā€™s en-US, iso 65001 UTF8). The csv from download or curl has all data formatted as txt with decimal numbers using a dot as separator. If you copy paste a decimal number formatted as text in an Excell cell that is formatted as a decimal number, the result depends on your regional setting. If you are in a region using a decimal dot, it will be OK. If you are in a region using a comma, the dot will be interpreted as a thousand separator and if your regional setting is set to not showing the thousand separator, the dot simply is omitted and you loose the decimalsā€¦
Specifying the origin makes sure that if you change the columns format in Excel to a number or a date that the decimal dot is interpreted correctly (changed to a ā€˜,ā€™ for EUR region for example) and also dates will be displayed correctly as set in your regional settings.
Iā€™m pretty new to this stuff too but once you understand how it works, it makes it very easy to import data correctly.
The Transform script in my API_tools Excell file is now quite mature. Open a copy of it and edit the source to point to your csv file iso of the web api. Then in the Power Query advanced editor, remove any pointer to a column that isnā€™t present and it should work. It may take a bit of time before you understand what is happening, but once you get it, you will be able transform just about any file with dates and numbers from whatever region.
I should find an easy way of telling the Query editor, that it should only set column format for columns that exist. Most of the errors reported until now were caused by the script trying to set a format on a column that was not present.

I just got an idea. What if i prepared a second basic file that only gets the needed Wellness data from a local file (downloaded wellness.csv) and formats that data correctly? This file would not have the full blown functionality of the original file but only a tab for the iThlete HRV. Activity data will not be in the final file, it would only serve to show the iThlete chart.That should considerably reduce errors related to importing data and would need minimal change of configuration to use on Mac.
It would just need you to enter the file path to your downloaded file.
Did some quick testing and it shouldnā€™t be to difficult to make such a file. It will need a logic to disregard the first empty lines in the csv because the downloaded csv file has some empty projected days causing the chart to be blank for that number of days. Simple solution would be to auto populate the date field with todayā€™s date.

Hello,
Is it possible to import totals (training zones) only into excel?
I tried to find the API but didnā€™t findā€¦

Thank you very much

Found this as an endpoint in the swagger docs:

SummaryWithCats{
count	integer($int32)
time	integer($int32)
moving_time	integer($int32)
elapsed_time	integer($int32)
calories	integer($int32)
total_elevation_gain	number($float)
training_load	integer($int32)
srpe	integer($int32)
distance	number($float)
date	string
athlete_id	string
athlete_name	string
email	string
external_id	string
fitness	number($float)
fatigue	number($float)
form	number($float)
rampRate	number($float)
timeInZones	[...]
timeInZonesTot	integer($int32)
byCategory	[...]
mostRecentWellnessId	string
}

Thank you very much!
Iā€™ll let you know :pray:t3: