Training guidance tools using the API

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.

Top shelf stuff.

Thanks @William_Barnes

3 Likes

Thanks @Kapooya_Bam!

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.

https://drive.google.com/drive/folders/111ji2LYKzKAPIPHKXvpdpxrLlE_f-vNI

4 Likes

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”}"

Any suggestions?

I suggest you first try a more recent version and see if you still get the error - use the link in my post just above yours.

(reposted as a direct reply)

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?

Here’s a temporary quickfix:

I’m working on it and will share new version soon.

3 Likes

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.

1 Like

I tried the xlsx sheet today (recent version 1.6). I do not get the popup to enter the API key and PW.
grafik

I followed instructions mentioned in the readme (at least I think I did). Entered my ID and so on. I always get this error:
grafik

Did the data refresh. I entered the api and the password as shown in the screenshot above. Still the same error.
Any suggestions?

1 Like

Let’s take this step by step:

  • Open a fresh copy of the sheet (don’t try to open the shared one! - download your own copy first)
  • Dismiss the two errors
  • Enter your AthleteID in the highlighted cell and quit the cell to make sure the ID is saved
  • Then goto Data menu and hit refresh
  • The Web Access dialog should now display

Are you using Microsoft Excel 2013 or newer on Windows or do you have another config? This sheet is not working on iOS/Mac.

1 Like
Summary

I am using office 365 newest version build 2405

I do not get error messages when I open the file. I get this popup about security two times:
grafik
I check the box two times and save.

Then I get this error two times:
grafik
I hit OK two times

I enter my Athlete ID (no copy paste, real typing :slight_smile:

I go to data and hit refresh, this error pops up:
grafik

No web access dialogue is showing up.

UPDATE:
OK f**k me I did it, dont mind my language :grin:. Its a simple and stupid mistake. The user name has to be “API_KEY” and NOT the api-key itself :rofl::joy::rofl:. That did the trick… Thx for replying and making me look through everything a second time :tada:

You’re not the first to make that mistake. I tried to clearly explain but it is extremely counter-intuitive.
Time to start playing now :wink:

1 Like

Thx! Worked for me as well! That is some awesome work you guys are doing! Kudos :metal:t6:

I started planning an ATP this weekend for my prep / road to LEL 2025. Everything works neatly, at least the planning ;). I found a tiny thing: the calendar weeks continue counting upwards over new years eve, instead of starting by 1 or 2 in the new year. The formula is “=Y9+1”. It should instead use the month, or the date as counting reference and not the previous number, no?

Depending on the system used, and the start day for the week (Sunday or Monday) the week number will change. I use ISOWEEKNUM instead of weeknum, then having to use 0 or 1 in the formula.

You could use the formula year and isoweeknum to ensure that you’re using the correct weeks. Here is an example of the weeks only (incl. allowing for double values in each week):

Hi,

For some strange reason, I’m not even getting this popup to input API and password →
image

I’m following the steps you laid out before:

  • Open a fresh copy of the sheet (don’t try to open the shared one! - download your own copy first) → Done, I’ve downloaded the file (1.6) from the Google Drive to a local copy, without even opening it online.

  • Dismiss the two errors → Done. I do also get another error about “Privacy levels”. I’ve read elsewhere on the forum to click “Ignore Privacy Levels check”, which I did

  • Enter your AthleteID in the highlighted cell and quit the cell to make sure the ID is saved → Done, even intermittently saved the file.

  • Then goto Data menu and hit refresh → Done

  • The Web Access dialog should now display → It does not, unfortunately. It does however display the “Microsoft.Mashup…” error again.

Running Microsoft Office 365.

Thanks in advance for any help :slight_smile:

Is your data coming from Strava or from the original device, eg. Garmin, Wahoo, etc.
Intervals is not allowed to retrieve data from the Strava API.

I’m not at my laptop, so can only check in about 2 hours.

I’m also running Office 365 and it all works.
The error message may indicate that the resource is forbidden from where you’re trying to access it??
Try this:
Data/Get Data/ Data Source Settings / Global Permissions / Clear all permissions. Then click on Refresh