# Self-Updating WaniKani Spreadsheet to Track Progress for API 2.0 **URL:** \ **Category:** API And Third-Party Apps\ **Created:** [November 22, 2020, 2:59am UTC](https://community.wanikani.com/t/self-updating-wanikani-spreadsheet-to-track-progress-for-api-20/47292 "2020-11-22T02:59:45Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![kusuri](https://sea1.discourse-cdn.com/wanikanicommunity/user_avatar/community.wanikani.com/kusuri/32/468788_2.png) [@kusuri](https://community.wanikani.com/u/kusuri)\ **Post date:** [November 22, 2020, 2:59am UTC](https://community.wanikani.com/t/self-updating-wanikani-spreadsheet-to-track-progress-for-api-20/47292/1 "2020-11-22T02:59:46Z")
Hi all - Since the new API 2.0 update, my old self-updating spreadsheet stopped working. I believe it was inspired from this [thread](https://community.wanikani.com/t/self-updating-wanikani-statistics-data-spreadsheet/28598), or a similar thread. Using help from @viet and @anon87959063 on this [thread](https://community.wanikani.com/t/wanikani-google-sheet%E2%80%94pull-data-from-api-version-2-to-sheet/44844), 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. ![image](https://global.discourse-cdn.com/wanikanicommunity/original/4X/5/0/2/5022191f26a03afc8d454fb74615348d1d0ac838.png) ![image](https://global.discourse-cdn.com/wanikanicommunity/original/4X/7/3/c/73cf472451e12112792b778921e37d9b532f0ee2.png) ![image](https://global.discourse-cdn.com/wanikanicommunity/original/4X/c/6/0/c60ea26b939ef2e8bb51dbfc0ed8907121e31f81.png) ![image](https://global.discourse-cdn.com/wanikanicommunity/original/4X/f/6/0/f607fc6033a1e4e8e5dc3eb5c71372bf96d6415a.png) **Instructions** 1. Get a Google account, and then go to this spreadsheet. [WaniKani Progress 2.0 - Public](https://docs.google.com/spreadsheets/d/1z5WqDoYDy6VUPdy16rq7E4Qk0z9_ehYmoHgS90EWeGY/edit?usp=sharing) 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](https://www.wanikani.com/settings/personal_access_tokens). 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. 🙂 ---
**Author:** ![oreodisaster](https://sea1.discourse-cdn.com/wanikanicommunity/user_avatar/community.wanikani.com/oreodisaster/32/349427_2.png) [@oreodisaster](https://community.wanikani.com/u/oreodisaster)\ **Post date:** [November 22, 2020, 3:57am UTC](https://community.wanikani.com/t/self-updating-wanikani-spreadsheet-to-track-progress-for-api-20/47292/2 "2020-11-22T03:57:48Z")
Trying this out right now. Guess I just need to wait until midnight! ---
**Author:** ![ekg](https://sea1.discourse-cdn.com/wanikanicommunity/user_avatar/community.wanikani.com/ekg/32/680984_2.png) [@ekg](https://community.wanikani.com/u/ekg)\ **Post date:** [November 22, 2020, 4:17am UTC](https://community.wanikani.com/t/self-updating-wanikani-spreadsheet-to-track-progress-for-api-20/47292/3 "2020-11-22T04:17:54Z")
Thanks for this! I’m definitely going to give this a go! 🙂 ---
**Author:** ![Belthazar](https://sea1.discourse-cdn.com/wanikanicommunity/user_avatar/community.wanikani.com/belthazar/32/4469_2.png) [@Belthazar](https://community.wanikani.com/u/Belthazar)\ **Post date:** [November 22, 2020, 6:19am UTC](https://community.wanikani.com/t/self-updating-wanikani-spreadsheet-to-track-progress-for-api-20/47292/4 "2020-11-22T06:19:26Z")
> [@oreodisaster](#): > > Guess I just need to wait until midnight! For the first tick, you could always just set the timer to trigger at the next upcoming hour. 🙂 ---
**Author:** ![kusuri](https://sea1.discourse-cdn.com/wanikanicommunity/user_avatar/community.wanikani.com/kusuri/32/468788_2.png) [@kusuri](https://community.wanikani.com/u/kusuri)\ **Post date:** [November 22, 2020, 6:21am UTC](https://community.wanikani.com/t/self-updating-wanikani-spreadsheet-to-track-progress-for-api-20/47292/5 "2020-11-22T06:21:30Z")
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. 🙂 ---
**Author:** ![Belthazar](https://sea1.discourse-cdn.com/wanikanicommunity/user_avatar/community.wanikani.com/belthazar/32/4469_2.png) [@Belthazar](https://community.wanikani.com/u/Belthazar)\ **Post date:** [November 22, 2020, 6:24am UTC](https://community.wanikani.com/t/self-updating-wanikani-spreadsheet-to-track-progress-for-api-20/47292/6 "2020-11-22T06:24:01Z")
Well, that works too. Guess I should actually do some reviews if I want to get any useful data out of it… ---
**Author:** ![kusuri](https://sea1.discourse-cdn.com/wanikanicommunity/user_avatar/community.wanikani.com/kusuri/32/468788_2.png) [@kusuri](https://community.wanikani.com/u/kusuri)\ **Post date:** [November 22, 2020, 6:25am UTC](https://community.wanikani.com/t/self-updating-wanikani-spreadsheet-to-track-progress-for-api-20/47292/7 "2020-11-22T06:25:30Z")
Yes now that I have spent several hours coding I guess I should probably do some of my reviews too… ---
**Author:** ![oreodisaster](https://sea1.discourse-cdn.com/wanikanicommunity/user_avatar/community.wanikani.com/oreodisaster/32/349427_2.png) [@oreodisaster](https://community.wanikani.com/u/oreodisaster)\ **Post date:** [November 22, 2020, 11:58am UTC](https://community.wanikani.com/t/self-updating-wanikani-spreadsheet-to-track-progress-for-api-20/47292/8 "2020-11-22T11:58:52Z")
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](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](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… ---
**Author:** ![raesh](https://cdck-file-uploads-global.s3.dualstack.us-west-2.amazonaws.com/wanikanicommunity/original/3X/f/d/fd4c154120954695f788402f3bcf4e616499bc2d.png) [@raesh](https://community.wanikani.com/u/raesh)\ **Post date:** [November 22, 2020, 1:18pm UTC](https://community.wanikani.com/t/self-updating-wanikani-spreadsheet-to-track-progress-for-api-20/47292/9 "2020-11-22T13:18:33Z")
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)? ---
**Author:** ![raesh](https://cdck-file-uploads-global.s3.dualstack.us-west-2.amazonaws.com/wanikanicommunity/original/3X/f/d/fd4c154120954695f788402f3bcf4e616499bc2d.png) [@raesh](https://community.wanikani.com/u/raesh)\ **Post date:** [November 22, 2020, 1:22pm UTC](https://community.wanikani.com/t/self-updating-wanikani-spreadsheet-to-track-progress-for-api-20/47292/10 "2020-11-22T13:22:49Z")
heyo, when you enter your api key in line 1, make sure to remove the [] brackets as well! ---
**Author:** ![oreodisaster](https://sea1.discourse-cdn.com/wanikanicommunity/user_avatar/community.wanikani.com/oreodisaster/32/349427_2.png) [@oreodisaster](https://community.wanikani.com/u/oreodisaster)\ **Post date:** [November 22, 2020, 2:44pm UTC](https://community.wanikani.com/t/self-updating-wanikani-spreadsheet-to-track-progress-for-api-20/47292/11 "2020-11-22T14:44:45Z")
Seems to have fixed the issue lol. I can see the new data in the spreadsheet now. Thanks! ---
**Author:** ![kusuri](https://sea1.discourse-cdn.com/wanikanicommunity/user_avatar/community.wanikani.com/kusuri/32/468788_2.png) [@kusuri](https://community.wanikani.com/u/kusuri)\ **Post date:** [November 22, 2020, 4:25pm UTC](https://community.wanikani.com/t/self-updating-wanikani-spreadsheet-to-track-progress-for-api-20/47292/12 "2020-11-22T16:25:38Z")
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. ---
**Author:** ![kusuri](https://sea1.discourse-cdn.com/wanikanicommunity/user_avatar/community.wanikani.com/kusuri/32/468788_2.png) [@kusuri](https://community.wanikani.com/u/kusuri)\ **Post date:** [November 22, 2020, 4:25pm UTC](https://community.wanikani.com/t/self-updating-wanikani-spreadsheet-to-track-progress-for-api-20/47292/13 "2020-11-22T16:25:49Z")
Yay, glad it worked for you! 🙂 I updated the post to tell people to remove the brackets. ---
**Author:** ![raesh](https://cdck-file-uploads-global.s3.dualstack.us-west-2.amazonaws.com/wanikanicommunity/original/3X/f/d/fd4c154120954695f788402f3bcf4e616499bc2d.png) [@raesh](https://community.wanikani.com/u/raesh)\ **Post date:** [November 22, 2020, 4:46pm UTC](https://community.wanikani.com/t/self-updating-wanikani-spreadsheet-to-track-progress-for-api-20/47292/14 "2020-11-22T16:46:47Z")
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 ---
**Author:** ![kusuri](https://sea1.discourse-cdn.com/wanikanicommunity/user_avatar/community.wanikani.com/kusuri/32/468788_2.png) [@kusuri](https://community.wanikani.com/u/kusuri)\ **Post date:** [November 22, 2020, 4:55pm UTC](https://community.wanikani.com/t/self-updating-wanikani-spreadsheet-to-track-progress-for-api-20/47292/15 "2020-11-22T16:55:38Z")
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. ---
**Author:** ![raesh](https://cdck-file-uploads-global.s3.dualstack.us-west-2.amazonaws.com/wanikanicommunity/original/3X/f/d/fd4c154120954695f788402f3bcf4e616499bc2d.png) [@raesh](https://community.wanikani.com/u/raesh)\ **Post date:** [November 22, 2020, 5:09pm UTC](https://community.wanikani.com/t/self-updating-wanikani-spreadsheet-to-track-progress-for-api-20/47292/16 "2020-11-22T17:09:28Z")
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 =) ![Screenshot 2020-11-22 180148](https://global.discourse-cdn.com/wanikanicommunity/original/4X/c/a/8/ca8317d3f0e288e5ffe02b01b58dbfba4d207488.png) ---
**Author:** ![kusuri](https://sea1.discourse-cdn.com/wanikanicommunity/user_avatar/community.wanikani.com/kusuri/32/468788_2.png) [@kusuri](https://community.wanikani.com/u/kusuri)\ **Post date:** [November 22, 2020, 5:11pm UTC](https://community.wanikani.com/t/self-updating-wanikani-spreadsheet-to-track-progress-for-api-20/47292/17 "2020-11-22T17:11:53Z")
Well that’s a beautiful plot if I’ve ever seen one. I’m impressed with your progress! ---
**Author:** ![raesh](https://cdck-file-uploads-global.s3.dualstack.us-west-2.amazonaws.com/wanikanicommunity/original/3X/f/d/fd4c154120954695f788402f3bcf4e616499bc2d.png) [@raesh](https://community.wanikani.com/u/raesh)\ **Post date:** [November 22, 2020, 5:45pm UTC](https://community.wanikani.com/t/self-updating-wanikani-spreadsheet-to-track-progress-for-api-20/47292/18 "2020-11-22T17:45:02Z")
Thank you! 🙃 I’m giving my best! ---
**Author:** ![whinette](https://sea1.discourse-cdn.com/wanikanicommunity/user_avatar/community.wanikani.com/whinette/32/318098_2.png) [@whinette](https://community.wanikani.com/u/whinette)\ **Post date:** [November 22, 2020, 7:14pm UTC](https://community.wanikani.com/t/self-updating-wanikani-spreadsheet-to-track-progress-for-api-20/47292/19 "2020-11-22T19:14:27Z")
Is there any way to import historic data, like that tools do? [https://public.dasding.online/wanikanitm2/](https://public.dasding.online/wanikanitm2/) That would make that sheet perfect. 🙂 Thanks for your work ---
**Author:** ![kusuri](https://sea1.discourse-cdn.com/wanikanicommunity/user_avatar/community.wanikani.com/kusuri/32/468788_2.png) [@kusuri](https://community.wanikani.com/u/kusuri)\ **Post date:** [November 22, 2020, 8:19pm UTC](https://community.wanikani.com/t/self-updating-wanikani-spreadsheet-to-track-progress-for-api-20/47292/20 "2020-11-22T20:19:00Z")
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. 🙂 [Next page](https://community.wanikani.com/t/self-updating-wanikani-spreadsheet-to-track-progress-for-api-20/47292.md?page=2)