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…
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.
The full featured file just got updated to version 1.5.
Changes:
1.5 ----- Accepts blank values for RHR and/or HRV while keeping correct averages. If too much data is missing, a watermark is indicated over the chart. That’s if more then 2 days out of the last 7 are missing or more then 9 out of the last 28. You will still get results but be notified by the watermark.
Just headup. I downloaded the file today , follow the instructions and looks good so far.
O little bit confunsing , abour workbook and updated workbook, which one i need to do the changes , but i think i do the right way.
Thanks so much
PS. if was possible how i can import just the sheet of IiThlete HRV , to another workbook and make work when “Refresh All” , without need to import others sheets ( i already have a bulk worbook, with a Dashboard , and graphs with my HRV , Tonnage , Volume , Food etc… )
Stumbled across and edge case today (and running the MacOS version, which I haven’t updated in a while so it may be fixed or irrelevant to others), which may be less relevant in the Northern Hemisphere winter/off-season.
The activities for 1Jan23, were being processed as 2023-01, as the logic in they YEAR() and ISOWEEK() functions are based on different things, found a good explanation about ISOWEEK()s first Thursday of the year start, and how to code for year here excel - week number and year combination - Stack Overflow
And the resulting code if anyone else needs/want it is swapping Activities “A”
is swapping
IF(B2="","",IF(ISOWEEKNUM(C2)<10,YEAR(C2)&"-0"&ISOWEEKNUM(C2),YEAR(C2)&"-"&ISOWEEKNUM(C2)))
with
=IF(B2="","",IF(ISOWEEKNUM(C2)<10,YEAR(C2+3-WEEKDAY(C2,3))&"-0"&ISOWEEKNUM(C2),YEAR(C2+3-WEEKDAY(C2,3))&"-"&ISOWEEKNUM(C2)))
2023-04
I really love the way WeekNum is used in this simplifies date/week handling so much, but the edge case caught me out.
Next fun, this year is one of the rarities that has 53 (iso)weeks…
Thanks for that. The weeknum function is my contribution to some of the work done on the various Excel files. My workaround (on my version) uses the IF function; when the week number is greater than 52, the new year and week 01 is then used.
As the HRV data is now managed using the app through MATLAB, the Excel file support has all but ended.
I still use the Excel file, but as mine works, I haven’t been look to improve on it.
Sorry, I typo’d my original.
The IF idea failed for me because 1Jan2023, showed up as 2023-52 originally, it’s in the second half of the 52nd week, but of 2022 calendar, so since week and year come from different maths, edge cases are tricky…
Just putting it here for reference, in case anyone else comes across similar.
The formula I use, is as follows:
=IF(G25="","",IF(YEAR(A25)<>YEAR(A26),YEAR(A26)&"-"&ISOWEEKNUM(activities[@[start_date_local]]),IFERROR(IF(activities[@[start_date_local]]="","",IF(C25<10,YEAR(activities[@[start_date_local]])&"-0"&C25,YEAR(activities[@[start_date_local]])&"-"&C25)),"")))
I need to give a public shout-out to @William_Barnes for the work he has put into the spreadsheet
“[Next Activity_icu v1 (shared)”. I’ve been using it for a month or so and going by its recommendations for “Next Activity” and I’ve been able to set many personal record wattage versus time numbers. The Next Activity workouts give me a choice on what I want to do the next day e.g.: FTP TTE, or CP TTE, or VO2Max, or just Z2.
Since I don’t think it’s earlier in this thread, here’s a stable link to the folder where the latest version (v2.3 currently) can be copied from for anyone else interested.
Copied the file in the link from (Training guidance tools using the API - #22 by Gerald) in google sheets, entered the info, and ran the import data script, but getting an error message saying " Exception: Request failed for https://intervals.icu returned code 403. Truncated server response: {“status”:403,“error”:“Access denied”}"
Hi all,
I’ve been using Excel version of Intervals_API_Tools_Office365_v1.6 but the data stopped refreshing at the end of March 2024. Excel throws [Expression error] The column “rampRate” of the table wasn’t found. Has anybody had the same issue? How can it be fixed?
thanks for this! I did search for the issue on the forum but didn’t find the thread you referred to. I can confirm that simply removing "Columns=38, " from the source query step in v1.6 solves the issue.
