Self-updating WaniKani Statistics & Data Spreadsheet

WaniKani API And Third-Party Apps
1

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.

[ETA: the version that I used was the one made by @Maxinoume here.]

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:

image
image913×382 73 KB

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):

image
image1660×435 62.7 KB

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’:

image
image406×578 15.9 KB

Save the document under “My Drive”.

Next, in the copied doc, go to Tools and then click on “Script editor…”.
image

You’ll see this:

image
image1162×239 26.1 KB

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:

image
image887×551 28 KB

Click on “Add Trigger”, and you’ll get a popup pop up. Make sure the end result looks like this:

image
image719×776 40.6 KB

Deprecated section

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).
image

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: =IF(AND(AA2=AA3,L2=L3),".","Yes")
  • Column C: =SUM(L2-L3)
  • 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.

image
image831×141 17.6 KB

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.

Enjoy!
Let me know if you have any questions or suggestions for improvement. :slight_smile:

20 Likes
2

It looks to me like there’s an error in the “total review number.” That number is rising on my sheet by about twice the number of reviews I do each day. Yesterday I did 207 reviews according to column D, but column Z rose by 407. It seems pretty consistent that the rise in Z is close to twice the number of reviews done.

Oh, wait—I get it. Column Z counts the number of questions answered—both meaning and reading. So “reviews” in column D is counting something different from “reviews” in column Z. Z is close to twice D, but not exactly because radicals only have one question associated with them. I may re-name column Z to reflect that, on my own sheet.

3

Is it just me or there’s constantly an error preventing me from saving the project triggers?

1 Like
4

It happened to me too. I couldn’t save any trigger at all. Perhaps Punmaster @konekush could help us?

5

Hit Run before trying to add the triggers. This should bring up a window to give the script permissions to access your account. It will complain about the script not being verified. Continue despite this. Once permissions are given, you can add the triggers and they will save.

6

Thank you! That worked!
Now, I’m not sure I understand what I’m supposed to do with those 3 cells that were highlighted in the screenshot. Think you could help me here as well?

7

Each time the scripts run and a day entry is added, those rows will not have the formulas for those three cells. You’ll need to copy the formulas from older entries up to the new ones. The “easy” way to do this is to select the three cells in the older entry, then click that little blue box on the bottom right corner of the selection and drag it up to the new entry cells.

8

It looks like this after I dragged it up :confused:
I’m sorry I’m terrible at excel…

9

…
While the method was correct, you’re supposed to do this only AFTER you have entries added with actual data pulled from the scripts running over time. You’ve just copied over the column headings right now.

10

I can just see you facepalming every time I reply. So sorry :see_no_evil:

I did have this after the script ran for the first time

Am I supposed to copy the three cells and paste onto the first entry like this and then delete the first line? If so, I’m a little confused about the numbers. I assume they are very high because I didn’t start using this from level 1?

11

Well, in the version of the document I’m using, the “formula” is up in row 1, the headers are row 2, and the first date entries are row 3. Anyway, that’s just a stylistic thing to not have to lose them, I guess.

What you’ve done seems fine. Yes, there are very large numbers for the first entry due to not starting it from level 1. It’s the total lessons and reviews you’ve done to “now.” Every new entry added after this should have the accurate lesson and review counts for each day once you copy the formulas up for each one.

1 Like
12

@konekush
I can’t figure out why these numbers don’t match. Please help, thank you!

@OtakuShowboat Thank you so much for your help!

13

You likely did lessons after the initial running of the script (50, it seems). They should be reflected in a new entry the next time the scripts trigger.

Edit: Misinterpreted the columns. See below.

14

I haven’t done any today though :thinking:
I’ll see if the numbers match tomorrow

15

Note that it is including everything left for the current level in those columns, perhaps including what’s still locked.

Edit: Aha, I see. It’s what’s left to guru.

16

Ah, so it’s including those I haven’t guru’d yet! I’ve unlocked and did lessons for all kanjis already, so that would be the right answer!

AK and AL don’t make much sense to me though. Like what’s the difference between the two? AK is what’s left to guru? and the other? including the unlocked?

17

It’s the same as the kanji and radicals, telling what you have left to guru for vocab (current level) and all vocab you’ve yet to guru. As in, when you level up, you’ll have a bunch of vocab from the previous level to still do as well as new vocab with this new level. I’ll need to level up to really test if it’s taking a count of what’s locked, too.

18

Aaaaargh, the numbers don’t add up though :c
If AL is all vocab I’ve yet to guru (all levels up until now), I have technically over 236+ rather than 196 that the table is telling me.

Unless it’s vocab I haven’t learned yet, which would remove a chunk of items already in apprentice.

I wish the values were better labeled for dummies like me T-T

19

Well, bear in mind, too, that some of those from previous levels that are apprentice now may have gone back from being guru at some point. Still counts as completed.

Anyway, both AK and AL are the same value for me (78), which is exactly how many I have left available in lessons and currently locked, and I know I’ve got a bunch not guru’d yet this level. So, perhaps those columns are just different. Perhaps they are just totals of what you haven’t done lessons of yet, including locked, AK for just this level and AL the sum of AK and every other vocab lesson you haven’t done yet from previous levels.

So, it seems to be consistent with how WK treats Radicals, Kanji, and Vocab in that Radicals and Kanji Left columns count how many are Guru as “left” and the vocab ones just that you’ve seen them.

1 Like
20

I think I’ll grind everything down to 0/0 and then check the numbers to make sure I’m getting everything right! Thanks for helping me, I really appreciate it!