WaniKani API + Google Docs Awesomeness

[Raywes88]

I was inspired by BreadStickNinja’s chart in a recent thread and wanted to do something similar to keep track of my own progress.

Behold: https://docs.google.com/spreadsheets/d/1ksaH_F_58xCtp-coCAUC8MVx9O9VqWcBEt65p11y0uY/edit?usp=sharing

This is updated automatically every day at 5 am thanks to a short script that performs an API call and parses the data I want into the columns you see in the sheet. This is super easy (well slightly easier than dealing with EVE’s xml nonsense with their API) since WK’s API returns the data in JSON format.
Source code: http://paste.ee/p/2oNqz

As you can see it’s pretty simple; so if you have any experience with Google app scripts and want something like this for yourself it can be accomplished in about 10 minutes.

You can actually get a copy of this up and running for yourself with a little work:

First make a copy of my sheet:

This makes a copy of the sheet on your own Google drive; including the script to pull the API info.

Now go to Tools > Script Editor…

Replace my API key (highlighted) with your own:

To set it up to run automatically; click the clock icon:

Then click “Add a new trigger” and set it up like this:

Don’t forget to click save!

Also thanks to DeviMevi who originally posted screens in the thread on setting up the scheduler. I only made new ones since his were in polish

[dothack]

Very interesting, thanks for sharing.

Never used Google docs scripts but I’ll give it a go

[DeviMevi]

It works for me, thanks!

[Kaimera]

var derp
...
var derp2

You're fired.

Cool though.

[Raywes88]

Kaimera said...

var derp
...
var derp2

You're fired.

Cool though.

 haha! Yes I get real lazy when I'm writing stuff for my own use :P

[Edit] For me, this has already served as a good additional motivator; I hate seeing those chart lines do anything but go up!

[dothack]

Raywes88 said...

Kaimera said...

var derp
...
var derp2

You're fired.

Cool though.

 haha! Yes I get real lazy when I'm writing stuff for my own use :P

[Edit] For me, this has already served as a good additional motivator; I hate seeing those chart lines do anything but go up!

It works nice, but maybe now you would need to regenerate your API key, since anybody can see (edited and replaced with mine, and started fetching data). 

Is there any way to schedule daily data updating instead when the document opens? Very misinformed when it comes to Gscripts 

[ShotgunLagoon]

i have no idea how to use google scripts but i think i’ve managed to get it working…
it’s pretty cool, excited to see how my graphs will look as time goes on.

[DeviMevi]

Sorry for Polish text but You should figure it out

edit: Ok, I am bad at posting images edit2: now it works

[ShotgunLagoon]

DeviMevi said... Sorry for Polish text but You should figure it out :)
http://i61.tinypic.com/10rs2td.png

edit: Ok, I am bad at posting images :P

thank you so much!!!

[kfdm]

VERY COOL!

I might borrow this and try to do a similar graph for reviews_available/lessons_available.

https://www.wanikani.com/api/user/<APIKEY>/study-queue
{
…
  “requested_information”: {
    “lessons_available”: 20,
    “reviews_available”: 47,
    “next_review_date”: 1428359776,
    “reviews_available_next_hour”: 10,
    “reviews_available_next_day”: 67
  }
}

[Maxinoume]

Thanks a lot!!!
I was doing my stats manually every day xD

[Aleithian]

Cool! I’ll try this out. 

[Raywes88]

np guys

Ahh! I figured it may not preserve the scheduling if you made a copy for yourself. I see DeviMevi was kind enough to post a screenshot though.

Edit: I suppose I probably should regen my API. Although it’s all read only correct? I don’t think anyone could do anything bad with it.

Edit 2: I think I’ll try and condense the relevant how to info throughout the thread into the main post.

[dothack]

Thanks… I’m unsure how I missed that evident button there, maybe lack of sleep.

[DeviMevi]

いいですね Good job with tutorial!

[Raywes88]

Well I’m glad it turned out to be pretty easy to make a copy so anyone can use it! Not that it was much work

Oh; I guess it’s worth noting that there may or may not be issues when exceeding 999 rows of data. I expect all you’d have to do is update the data ranges in the charts but I’m not sure. That’s 2.75 years (assuming one per day) away so it’s a problem for “future me” :P

[marcsan]

Thanks I like this.
I was able to edit the script pretty easily to load extra IPA information like the level progression into my sheet.
But now I’ve decided I dont like the charts in google docs… they are so limited!

Has anyone tried using excel online to do this? I shall try when I have time…

EDIT: I gave up trying to do the same in excel… far too difficult! 
Instead I have left my google spreadsheet still logging the data, but then use excel on my computer to get the ‘external data from web’ from my shared google spreadsheet so I can then graph the data away in excel.

[marcsan]

Anybody know how to fix this error message I am getting most nights from google notifications? 

If I run the script in my browser it works fine, but timed I seem to get this error message every day now...

SyntaxError: Empty JSON string (line 10, file "wkapi")

cheers

[ShotgunLagoon]

marcsan said... Anybody know how to fix this error message I am getting most nights from google notifications?  If I run the script in my browser it works fine, but timed I seem to get this error message every day now...

SyntaxError: Empty JSON string (line 10, file “wkapi”) cheers

 maybe try using diff to see the difference between the original script in OP’s post and the script that you’re using?

[Amejanai]

If I’m away from the internet for the weekend, or something (camping, for example), will this update, anyway?  Or do I have to be logged into the appropriate google account?  Thanks!