Self-Updating WaniKani Spreadsheet to Track Progress for API 2.0

Hi all -

Since the new API 2.0 update, my old self-updating spreadsheet stopped working. I believe it was inspired from this thread, or a similar thread. Using help from @viet and @BIsTheAnswer on this thread, I was able to update the old code to use the new API. I’m happy to share my new code in case it’s useful for anyone. It updates a Google docs spreadsheet that plots progress. Someone else made this lovely spreadsheet, but I can’t seem to find the thread it originated in. Whoever you are, please reply and I’ll credit you and your lovely work!

The spreadsheet looks like the following images and automatically updates every day at midnight. There are more details in the spreadsheet for how to interpret the plots.





Instructions

  1. Get a Google account, and then go to this spreadsheet. WaniKani Progress 2.0 - Public

  2. Go to File → Make a copy and save the Spreadsheet to your own drive.

  3. Open the spreadsheet and then go to Tools → Script Editor.

  4. Replace [Insert Your API Key here] with your API key from here. Make sure to remove the [ ] brackets.

  5. Click on the trigger icon (looks like a clock).

  6. Click on the Add Trigger button in the bottom right.

  7. Set the settings up to trigger every day at midnight.

  8. Go back to your spreadsheet and go to File → Spreadsheet Settings to change to your local time zone.



That’s it!

Good luck with it! Please let me know if you have any trouble, and I will do my best to help you. :slight_smile:

29 Likes

Trying this out right now. Guess I just need to wait until midnight!

Thanks for this! I’m definitely going to give this a go! :slight_smile:

For the first tick, you could always just set the timer to trigger at the next upcoming hour. :slightly_smiling_face:

1 Like

As a test, you can also press the play button to the right of the trigger button. (The one next to the bug). That’ll run the script. And if you want to start fresh at midnight you can just delete the row you added during the test. :slight_smile:

2 Likes

Well, that works too.

Guess I should actually do some reviews if I want to get any useful data out of it…

Yes now that I have spent several hours coding I guess I should probably do some of my reviews too…

3 Likes

Thanks for the tip! So I tried to run the script using @eainge’s method (thanks also) and this error message in a red box shows up:

Exception: Request failed for https://api.wanikani.com returned code 401. Truncated server response:

Opening the error box reveals this text:

Exception: Request failed for https://api.wanikani.com returned code 401. Truncated server response: {“error”:“Unauthorized. Nice try.”,“code”:401} (use muteHttpExceptions option to examine full response). (line 6, file “fetchData2”)

Any solutions to this? I tried changing the API token too, however I think it’s no use…

1 Like

wow, nice job! is there a way to have the newest entry automatically created as the first row, so the the sheet is automatically sorted by date (descending)?

heyo, when you enter your api key in line 1, make sure to remove the [ ] brackets as well!

1 Like

Seems to have fixed the issue lol. I can see the new data in the spreadsheet now. Thanks!

I’m sure there is a way to do this with the code, but I think it would take a bit of effort to figure it out. It would also break the plots, most likely. You can always manipulate the excel by creating new sheets, although they won’t be automatically updated.

Yay, glad it worked for you! :slight_smile: I updated the post to tell people to remove the brackets.

1 Like

I’ve tried adding this to the end of your code:

sheet.getRange(‘A3:Z’).sort({column: 1, ascending: false});

I “think” this works, but i have little to no experience with coding. Thing is, I am trying to migrate my data from the old self-updating spreadsheet to this new one and I dont want to start a whole new sheet after 1.5 years of studying =D

1 Like

Oh nice! This is my first time working with Javascript, so we’re learning together. Glad that it works for you. A final sort at the end seems like it’d be fine, I would think the plots will still plot based on date without issue. Nice fix.

If you find that there are issues with this change happy to try and help. If you’re just trying to use your old data, you could always manually sort your old data in your old spreadsheet (or a copy of it), using the filter option in the excel. Then you can copy it to this new spreadsheet. The code just appends to the last line of the spreadsheet.

Yep, thats what I’ve done now, pasting all my old data in the new sheet and sorting it. Although i dont have all the specific data for what SRS stage my kanji,radicals and vocab were like back then, the plots still look good i think. thanks again for your work =)

Well that’s a beautiful plot if I’ve ever seen one. I’m impressed with your progress!

1 Like

Thank you! :upside_down_face:
I’m giving my best!

1 Like

Is there any way to import historic data, like that tools do? https://public.dasding.online/wanikanitm2/
That would make that sheet perfect. :slight_smile:
Thanks for your work

3 Likes

Wow - that’s an amazing tool. I’ve never heard of it.

I’m sure there is a way, although it would take some time for me to figure out. I’m still a beginner Javascript coder. Maybe when I have some time I’ll add that in. :slight_smile:

3 Likes