This post follows on from two previous posts:
One of the problems with the Power Query data connector I built for strava was it would run and keep returning an error 429 Too many requests. This happens because Strava API rate limits are 300 every 15 minutes and 3000 a day.
A request is needed for each activity and each activity with a comment. So if you ran 5 times a week for a year, that’s already 260 and if 40 had comments you’re already at the limit.
I spent quite a long time trying to figure out a way to get around this. First trying to keep a track of how many times a request had been made and then sleeping the program. This proved quite tricky. In the end I found out about a helper function Value.WaitFor() but before I explain that, it’s important to understand a crucial bit about how Web.Contents() works.
The Web.Contents function has some built-in functionality for dealing with certain HTTP status codes. Web.Contents() by default, will automatically retry (up to 3 times) when it receives a 429 response from the server, before failing.
This default behavior can be overridden in our extension by using the ManualStatusHandling field in the options record. This is where the Value.WaitFor() helper function comes in.
“.. . a standard helper function that can usually be used with no modification. It works by building a List of retry attempts.”
Value.WaitFor = (producer as function, interval as function, optional count as number) as any =>
let
list = List.Generate(
() => {0, null},
(state) => state{0} <> null and (count = null or state{0} < count),
(state) => if state{1} <> null then {null, state{1}} else {1 + state{0}, Function.InvokeAfter(() => producer(state{0}), interval(state{0}))},
(state) => state{1})
in
List.Last(list);
I used the wait-retry pattern to modify the existing StravaData.Page() function (the code which runs Web.Contents). The change can be seen below, which is mainly around the assignment of the content variable.
This first section is unchanged
StravaData.Page = (url as text, optional page as number, optional contentType as text, optional StartDate as number , optional EndDate as number, optional cursor as text)
as nullable table =>
let
queryOptions = if contentType = "Activities" then
[page = Number.ToText(page),per_page = Text.From(per_page)]
else if contentType = "ClubActivities" then
[page = Number.ToText(page),per_page = Text.From(per_page)]
else if contentType = "ActivityComments" and cursor <> null then
[after_cursor = cursor,page_size = Text.From(per_page)]
else if contentType = "ActivityComments" and cursor = null then
[page_size = Text.From(per_page)]
else
null,
This is where we use the new helper function
content = ValueWaitFor(
// Start of 1st argument - producer as function (The Task to be (possibly) retried.)
(iteration) =>
let
result =
if contentType = "Activities" or contentType = "ActivityComments" or contentType = "ClubActivities" then
Web.Contents(url, [
Query = queryOptions
, ManualStatusHandling = {429} // Tell Web.Contents() we will manually handle 429 errors
, IsRetry = iteration > 0
])
else
Web.Contents(url, [
ManualStatusHandling = {429}
, IsRetry = iteration > 0
]),
status = Value.Metadata(result)[Response.Status],
actualResult = if status = 429 then null else result
in
actualResult
// End of 1st argument)
// 2nd argument - interval as function (Wait time between retries doubles with each try)
,(iteration) => if iteration = 0 then #duration(0, 0, 0, 0) else #duration(0, 0, 0, Number.Power(2, iteration)) ,
// 3rd argument in ValueWaitFor() Count - Maximum number of retries (10)
10)
in
if content = null then
error "Value.WaitFor() Failed after multiple retry attempts"
else
The rest of the function remains the same as before
let
json = Json.Document(content),
result =
// List of records
if (json is list) then
if (List.IsEmpty(json)) then
null
else if contentType = "Activities" then
Table.StravaData.Activities(json) meta [NextPage = page + 1]
else if contentType = "ClubActivities" then
StravaData.ClubActivities(json) meta [NextPage = page + 1]
else if contentType = "Clubs" then
StravaData.Clubs(json) meta [NextPage =null]
else if contentType = "DetailedActivity" then
StravaData.DetailedActivity(json) meta [NextPage =null]
else if contentType = "ActivityComments" then // we need to GET the last curstor from the reord here
StravaData.ActivityComments(json) // do not set metatype as irrelevant for this type
else if contentType = "Athlete" then
StravaData.Athlete(json) meta [NextPage =null]
else
null
// single record
else
Table.FromRecords({json}) meta [NextPage = null] // turn single record into one item list
in
result;
Finally I suggest scheduling this to run around 11pm as the chances are it will run for a couple of hours and you’ll get two days worth of requests (12,000).
Updated source code available here