Some of you might remember @Raywes88’s WaniKani API + Google Docs Awesomeness spreadsheet from 2015. I’ve been using the spreadsheet from about that time, but I improved it over time and it can track a looooooot of data nowadays.
ETA2 19/3/19: Updated the spreadsheet and the formulas, added extra graphs sheets, and a special statistics sheet.
ETA2 26/6/19: Updated the script with @ulissesmelo’s changes, and changed the instructions accordingly.
What does it do?
Easy. The spreadsheet pulls a lot of your data from the WK API and then calculates it and stores the results in the spreadsheet.
It will calculate the items your SRS levels (App, Guru, Master, Enlighten, Burned), your accuracy percentages, how many items you have total and how many are active, how many reviews you have for tomorrow, how much lessons you have left, and what you have left to guru until you reach the next level … and many many more.
Also, there are graphs. Here’s one of mine:
The more data your spreadsheet collects, the more you’ll have in your graphs.
Here’s what it looks like with my stats from level 18 (green is level-up, blue is a Sunday):
Where to get & How to set it up:
First, you need a Google account. Then, go here:
Click on ‘File’ and then on ‘Make a copy’:
Save the document under “My Drive”.
Next, in the copied doc, go to Tools and then click on “Script editor…”.
You’ll see this:
First, input your version 1 API key from WK. If you don’t have one, generate one. Then put it between the underlined apostrophes. Then click on ‘Run’ above the clock thingie.
Next, click on the clock thingie that I marked in green, and then you’ll see this:
Click on “Add Trigger”, and you’ll get a popup pop up. Make sure the end result looks like this:
Non-automated parts - deprecated!
When you look at the spreadsheet, you’ll notice some columns with a yellow-ish background (columns B-D and AM). Those are the ones you need to fill on your own.
- Column AM: Write down when you level up, if anything significant comes up, etc.
- Columns B-D: calculate whether you went on WK, and how many lessons you did & how many items you reviewed the day before.
The spreadsheet is empty except for 1 line that you’ll need to delete after the scripts have run at least once. It’s there so you can copy/paste the formulas for calculating whether you did WK, how many lessons you did, and how many items you reviewed (columns B, C, D).
While you’ll have to manually tell the sheet to perform the calculations (I never figured out how to make Google Sheets do it automatically), you don’t have to do it daily.
In case you accidentally delete the formulas, they’re:
- Column B:
- Column C:
- Column D:
=(((Y2-Y3-((Y2-Y3-W2+W3)*Q2/100))*((N2+P2)/2/100)) + (((W2-W3)/(2-V2))))/2 + (Y2-Y3-W2+W3)*Q2/100
The updated formulas are also found in the script editor (where you input your API key). Don’t worry about these not matching.
Make sure to go to File -> Spreadsheet Settings and update your location and timezone.
After doing all of this, that’s it, you’re done! Your spreadsheet is all set up and you can see the change in your patterns across the entire time since setting it up.
Let me know if you have any questions or suggestions for improvement.