Using API for Team Matches.

Hey guys,

I recently made a spreadsheet for my club that plays alot of daily chess.

I have previously used importJSON on google sheets to constantly update people's bullet ratings on a sheet, but is it possible to have a constantly updating match score of a Team Match?

I can't find it under https://api.chess.com/pub/match/1034696 (https://www.chess.com/news/view/published-data-api#pubapi-endpoint-match-profile). 

Any help?

Thanks

This is a REST API so will not update 3rd party applications. The server data is updated but you need to pull it each time you need to assess it.

No. I put a script into my sheet that makes the formula importJSON work. It pulls data from chess.com api, i have used it before to get players’ bullet ratings. 

Can you just tell me what the api link is to the match score, and i’ll go from there?

There's no such thing as 'the api link to the match score', you request the entire match endpoint, in which the scores for each team are given.

Let me explain. Look at the cell H2 in https://docs.google.com/spreadsheets/d/1vdTV9ym8DYwskbwShYNsGpWWhSoQj9Wt96tJXkPRhzo/edit#gid=0.

can you see how it has 

=importJSON(("https://api.chess.com/pub/player/hikaru/stats"),"/chess_bullet/last/rating","noHeaders")?

I can obviously also get 

=IMPOrtjson("https://api.chess.com/pub/match/989500")

But how can i be more specific, so it gives me the score?

I noticed i could set out all of the boards by doing

=IMPOrtjson(("https://api.chess.com/pub/match/989500/1"),"/board_scores")

=IMPOrtjson(("https://api.chess.com/pub/match/989500/2"),"/board_scores")

=IMPOrtjson(("https://api.chess.com/pub/match/989500/3"),"/board_scores")

And so on, but this wastes alot of space because it puts out the score of each board. Isn't there an easier way?

I can't view that page directly - I've had to request access so that might take a moment...

Got no form of confirmation there, so I'm not sure what that was about.

You can't in a single request because the scores are given in different fields. Either split the score between two cells or work out a way of extracting both team scores in one endpoint request.

I'm not familiar with importing endpoint data directly into a spreadsheet but my best guess of how to derive the team1 score would be...

=importJSON(("https://api.chess.com/pub/match/989500"),"/teams/team1/score","noHeaders")?

I'm not sure what the "noHeaders" specifies?

But you might try that & see what it shows? It's not a format I've ever used but this might display both teams' scores in the same cell? ...

=importJSON(("https://api.chess.com/pub/match/989500"),"/teams/team1/score","/teams/team2/score","noHeaders")?

Like I say, I'm guessing here  😄

Hey, thanks, you were really close! I used what you put to figure it out! 

I can use =importJSON(("https://api.chess.com/pub/match/989500"),"/teams/team1/score","noheaders")

and

=importJSON(("https://api.chess.com/pub/match/989500"),"/teams/team2/score","noheaders")

For some reasons the headers "Teams Team2 Score" and "Teams Team2 Score" still come up even when i write "noheaders", but i think i'll figure it out.

Thanks @stephen_33

Glad that works 

But that leaves one small question in my mind - how often does that refresh? That's to say, how frequently does your spreadsheet make new endpoint requests to update the team score(s)?

Hey stephen,

I am not sure, I will find that out as i go along

Glad to find another importJSON user.  My problem with it is my daily limit for urlfetch in Google Sheets keeps getting maxed out.  I need to find ways to make less requests, for more data.  Similar to what Sorry discovered here

im not sure but deleting everything and undoing it forces it to rethink

"deleting everything and undoing it forces it to rethink" - what does rethink mean here?

recalculate, update

O/k, that's to be expected but it still doesn't answer the question of how frequently it refreshes? In other words, how many API requests are made per hour?

oh, i misinterpreted what you were asking. I don't know.

Is there any way you can adjust that in the settings of your software?

Adjust what?

Adjust the rate at which it refreshes, i.e. new endpoint data is requested.