Google Spreadsheet OAuth

API Discussion
#1

Hello.

I am trying to maintain a gear spreadsheet for our raid roster.
This pulls data from the character API and displays it in a GoogleSheet.

I’m now getting 403 when trying to pull the data.
I suspect this is due to using the client credentials flow.

I am unable to use the authorization code flow due to everything running inside a spreadsheet. Redirecting to a Blizzard player login page is not an option, nor is hard-coding a BNET username/password inside the sheet.

I find myself stuck at this point.
Any suggestions, or is our guild’s spreadsheet dead?

#2

If you’re using the client credentials flow, then you gave Blizzard the client ID and secret and got back a token, and your spreadsheet uses that token in all its fetches. After 24 hours, your token expires and your sheet breaks.

Unofficial workaround: I’ve noticed that as long as you request a new client credentials token while an existing one is valid, you’ll get back the existing token, refreshed for another 24 hours. This does not need to be done within the spreadsheet, this can be done from a completely different system. So have something fetch a “new” token every 6 hours or so, and your spreadsheet token will never expire.

Again, this behavior is not officially documented or intended, I don’t think, just a side-effect of how Battle.net OAuth happens to be implemented right now.

2 Likes
#3

By that you mean a actual Battle.net username and password, or the client credentials key and secret ?

If you are ok with the later option you can use the GScript sample here to fetch a fresh access_token, you can even run it on a time trigger inside the spreadsheet.

If you combine Erorus reply with this you can even have a separate private Google spreadsheet just for refreshing the token on a timer so your client credentials can be kept safe.

Also note the reason for a 404/403 might be something else, sometimes when they update something internally on the API the current profile data is invalidated and will only be available after the character logs out of WoW after those changes.

1 Like
#4

Sorry for the lack of update.
I managed to sort it out myself with something very similar to the GScript example.

Thank you for the responses.