WaniKani Google Sheet—Pull data from API version 2 to Sheet

[viet]

This was created in response to the following:

1000×1000 11.8 KB

Thanks for your answer. In fact the categories names have very little importance for me…what I want to monitor/ keep track of is my progress… so if instead of the number of kanji at Guru level I need to check for kanji at SRS stageX this is exactly the same. What I need is to understand how to get the daily status of the SRS stage of my kanji, my vocabulary and my radical. Then based on that I will decide which one is relevant and again we have today 10 SRS stages and with the new version there…

Felt a little generous and was also curious about Google’s App Script. Never used it before… So after reading the documentation this afternoon I whipped something up for the poster above.

The relevant resources:

• Shared Google Sheet. You must be logged into your Google account. Go to File > Make a copy to create a copy to your Google Drive. You can’t do much unless you have copied the sheet.
• Github Gist of the App Script I wrote. This is also accessible via the Google Sheet once you have copied it over, under Tools > Script editor. Please excuse the code quality. I only spent a couple hours on it. It does the following:
  1. Sets up the sheet.
  2. Pulls in API version 2 data.
  3. Creates a new row with some rolled up data.

How to use

When the sheet loads you’ll see a Daily Status menu option. Click on it and click Prepare sheets. This will add two sheets:

1. Daily entries
2. API

After the sheets have been generated, add your API version 2 token.

You can now generate a new entry to the Daily Entries sheet. To do so click on Daily Status > Add new daily entry

Tada. A new entry has been made to the sheet. I recommend leaving the Daily Entries alone and use a separate sheet to do roll-ups and data manipulation.

---

What each columns mean:

• Date: When the entry was generated.

• Apprentice radicals: Count of published, not hidden, apprentice (srs stages 1, 2, 3, 4) radicals.

• Apprentice kanji: Count of published, not hidden, apprentice (srs stages 1, 2, 3, 4) kanji.

• Apprentice vocabulary: Count of published, not hidden, apprentice (srs stages 1, 2, 3, 4) vocabulary.

• Guru radicals: Count of published, not hidden, guru (srs stages 5, 6) radicals.

• Guru kanji: Count of published, not hidden, guru (srs stages 5, 6) kanji.

• Guru vocabulary: Count of published, not hidden, guru (srs stages 5, 6) vocabulary.

• Master radicals: Count of published, not hidden, master (srs stages 7) radicals.

• Master kanji: Count of published, not hidden, master (srs stages 7) kanji.

• Master vocabulary: Count of published, not hidden, master (srs stages 7) vocabulary.

• Enlightened radicals: Count of published, not hidden, enlightened (srs stages 8) radicals.

• Enlightened kanji: Count of published, not hidden, enlightened (srs stages 8) kanji.

• Enlightened vocabulary: Count of published, not hidden, enlightened (srs stages 8) vocabulary.

• Burned radicals: Count of published, not hidden, enlightened (srs stages 9) radicals.

• Burned kanji: Count of published, not hidden, enlightened (srs stages 9) kanji.

• Burned vocabulary: Count of published, not hidden, enlightened (srs stages 9) vocabulary.

• Unlocked radicals: Count of published, not hidden, unlocked radicals.

• Unlocked kanji: Count of published, not hidden, unlocked kanji.

• Unlocked vocabulary: Count of published, not hidden, unlocked vocabulary.

• Started radicals: Count of published, not hidden, started radicals.

• Started kanji: Count of published, not hidden, started kanji.

• Started vocabulary: Count of published, not hidden, started vocabulary.

• Passed radicals: Count of published, not hidden, passed radicals.

• Passed kanji: Count of published, not hidden, passed kanji.

• Passed vocabulary: Count of published, not hidden, passed vocabulary.

• Burned (event) radicals: Count of published, not hidden, burned radicals. Note these are burned, but not necessarily currently burned. For example this can include resurrected subjects.

• Burned (event) kanji: Count of published, not hidden, burned kanji. Note these are burned, but not necessarily currently burned. For example this can include resurrected subjects.

• Burned (event) vocabulary: Count of published, not hidden, burned vocabulary. Note these are burned, but not necessarily currently burned. For example this can include resurrected subjects.

• Resurrected radicals: Count of published, not hidden, resurrected radicals.

• Resurrected kanji: Count of published, not hidden, resurrected kanji.

• Resurrected vocabulary: Count of published, not hidden, resurrected vocabulary.

• Lesson radicals: Count of published, not hidden, radicals currently in lessons.

• Lesson kanji: Count of published, not hidden, kanji currently in lessons.

• Lesson vocabulary: Count of published, not hidden, vocabulary currently in lessons.

• Review radicals: Count of published, not hidden, radicals currently in reviews (does not mean they can be reviewed NOW, but they are reviewable).

• Review kanji: Count of published, not hidden, kanji currently in reviews (does not mean they can be reviewed NOW, but they are reviewable).

• Review vocabulary: Count of published, not hidden, vocabulary currently in reviews (does not mean they can be reviewed NOW, but they are reviewable).

• Total available radicals: Count of published, not hidden, radicals available in WaniKani.

• Total available kanji: Count of published, not hidden, kanji available in WaniKani.

• Total available vocabulary: Count of published, not hidden, vocabulary available in WaniKani.

• Current level: The user’s current level.

• Days on current level: Number of days the user has been on the current level.

[prouleau]

This is cool. There will be many uses for this. Is this in the New and Improved List of API and Third Party Applications? If not I will add it.

Edit: I have added it.

[philippe-akir]

THANKS A LOT! I really appreciate this is a great help for me.
have a nice day,
Best regards
Philippe

[philippe-akir]

Hello, I know you will think I am totally hopeless (and I am) but how can I generate the daily update automatically everyday (without having to manually run the daily status command ?
Thanks a lot in advance
Philippe

[UInt2048]

@viet

Apprentice

Will there be any sort of categories for the main page after dropping them from the API such as Started, Passed, and Burned?

If so, may I suggest we keep the Apprentice name for “Started” and maybe call “Passed” Guru-Master or something?

[viet]

There is something called installable triggers

Installable Triggers  |  Apps Script  |  Google Developers

Doesn’t look too difficult to implement. I’ll leave it up to the user to have it configured.

[UInt2048]

The relevant example code from that link is here:

/**
 * Creates two time-driven triggers.
 */
function createTimeDrivenTriggers() {
  // Trigger every 6 hours.
  ScriptApp.newTrigger('myFunction')
      .timeBased()
      .everyHours(6)
      .create();

  // Trigger every Monday at 09:00.
  ScriptApp.newTrigger('myFunction')
      .timeBased()
      .onWeekDay(ScriptApp.WeekDay.MONDAY)
      .atHour(9)
      .create();
}

[wanikani235]

This is perfect… just what I needed. I think I can also use something like this addon to connect the API? Has anyone used it?

[UInt2048]

There’s no reason to - the script already uses the API. Is this advertising, or am I missing something?

[samwill]

Thank you for this! I hadn’t realized my data spreadsheet was still on V1 until I got the failure emails from the last few days… Now to figure out how to map your columns to my columns

[Joeni]

This is great work, really helps if I ever forget to pull those numbers off and enter them in to my study log!

[kusuri]

Thanks for putting this together! It’s very helpful. I’m currently trying to modify my old code so that it can work like this one. My old code was written by a couple of people on this lovely forum, and I wish I could find them. I think they’ve stopped using WaniKani by now. The reason I want to use their code is because of the formatting. I read through Viet’s code, and it doesn’t seem easy to format it similarly to my Google sheet. The full code is at the bottom of this message.

Alright, let’s dive into the details. I’m currently getting this error when I run it:

{"code":410,"message":"This endpoint has been removed. Please use our latest API. Documentation can be found at [https://docs.api.wanikani](https://docs.api.wanikani/)."} (use muteHttpExceptions option to examine full response) (line 4, file "fetchData")

My old code would fill in a new line each day automatically, and use the below format. It’s beautiful, and it’s been ongoing since 2017! I’m trying to learn from Viet’s code now to see if I can just modify my current code and keep using it. Both codes are written in Javascript, but they seem very different. For example, the old code would do a call to get data and then everything would be in the variable info.

var raw = UrlFetchApp.fetch(
    'https://www.wanikani.com/api/user/' + apiKey + '/srs-distribution',
    {'Cache-Control': 'max-age=0, must-revalidate'}
  ).getContentText();
  var parsed = JSON.parse(raw);
  var currentLevel = parsed.user_information.level;
  var info = parsed.requested_information;

I can’t seem to find a similar thing in Viet’s code. I did find this code below, but there’s not obvious variable like info, where I can pull things from.

  const fetchData_ = (apiEndpoint) => {
    const response = UrlFetchApp.fetch(apiEndpoint, {
      method: 'GET',
      headers: {
        'Authorization': `Bearer ${getApiToken_()}`,
        'Content-Type': 'application/json',
      },
    })

    return JSON.parse(response.getContentText())
  }

And when Viet appends to his excel sheet, the code is in a very different format, using functions I believe. That looks like the code below, but it’s hard to decipher.

...Object.keys(srsStagesByStageName).map((srsStageName) => {
      return Object.keys(counts).map((subjectType) => totalSrsStagesCountBySubjectType_(counts, srsStagesByStageName[srsStageName], [subjectType]))
    }).flat(),
    ...['unlocked', 'started', 'passed', 'burned', 'resurrected', 'lessons', 'reviews', 'totalAvailable'].map((countType) => {
      return Object.keys(counts).map((subjectType) => counts[subjectType][countType])
    }).flat()

Can anyone help me with this a bit? I’d be happy to post the final code once it’s done, in case anyone wants to keep using it.

Here’s what the excel format looks like:

And one of the plots (Please ignore the long periods of inactivity haha):

My full old code is here (not written by myself, if anyone knows the author I’m happy to give credit):

var apiKey = "<Insert API key here>";

function fetchData() {
  var raw = UrlFetchApp.fetch(
    'https://www.wanikani.com/api/user/' + apiKey + '/srs-distribution',
    {'Cache-Control': 'max-age=0, must-revalidate'}
  ).getContentText();
  var parsed = JSON.parse(raw);
  var currentLevel = parsed.user_information.level;
  var info = parsed.requested_information;
 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Raw data');
 
  var previousLevelText = sheet.getRange('B3:B')
    .getValues()
    .filter(function (x) { return x != '' })
    .slice(-1)[0];
  var levelText = 'Level ' + currentLevel;
  if (levelText == previousLevelText) {
    levelText = '';
  }
 
  var cell = sheet.getRange(sheet.getLastRow(), 1);
  var col = 0;
  function appendCell(data) {
    cell.offset(1, col++).setValue(data);
  }
   
  appendCell(new Date());
 
  appendCell(levelText);
 
  appendCell(info.apprentice.total);
  appendCell(info.guru.total);
  appendCell(info.master.total);
  appendCell(info.enlighten.total);
  appendCell(info.burned.total);
 
  appendCell(info.apprentice.radicals);
  appendCell(info.guru.radicals);
  appendCell(info.master.radicals);
  appendCell(info.enlighten.radicals);
  appendCell(info.burned.radicals);
 
  appendCell(info.apprentice.kanji);
  appendCell(info.guru.kanji);
  appendCell(info.master.kanji);
  appendCell(info.enlighten.kanji);
  appendCell(info.burned.kanji);
 
  appendCell(info.apprentice.vocabulary);
  appendCell(info.guru.vocabulary);
  appendCell(info.master.vocabulary);
  appendCell(info.enlighten.vocabulary);
  appendCell(info.burned.vocabulary);
 
  appendCell(
    info.apprentice.radicals +
    info.guru.radicals +
    info.master.radicals +
    info.enlighten.radicals +
    info.burned.radicals);
 
  appendCell(
    info.apprentice.kanji +
    info.guru.kanji +
    info.master.kanji +
    info.enlighten.kanji +
    info.burned.kanji);
 
  appendCell(
    info.apprentice.vocabulary +
    info.guru.vocabulary +
    info.master.vocabulary +
    info.enlighten.vocabulary +
    info.burned.vocabulary);
   
  appendCell(
    info.apprentice.total +
    info.guru.total +
    info.master.total +
    info.enlighten.total +
    info.burned.total);
}

[prouleau]

There has been a change in the WK API as the error message indicates. The two API are very different. You have to.recode everything. The documentation. Is at the link in the error message.

[BIsTheAnswer]

You’ll want to use the newer API.

Basically, all of the new api endpoints are located under https://api.wanikani.com/v2/, and require you to send your API token with you in a HTTP header. I think maybe an example would be the easiest way to demonstrate:

Say you want to fetch all your reviews, to do so, you’ll have to make use of the reviews endpoint. To do so, you’ll have to send a GET request to this endpoint providing your API key and setting the API version header. So in your case, using the same UrlFetchApp you have been using, the call would look something similar to:

UrlFetchApp.fetch('https://api.wanikani.com/v2/reviews', {
    method: 'GET',
    headers: {
        'Wanikani-Revision': '20170710',
        'Authorization': 'Bearer ' + apiKey
    }
})

This would fetch the first set of reviews from the endpoint and return it as a JSON object. Note that if you want to loop through all reviews you’ll have to make use of pagination, or if you want to query some other data you might want to use another endpoint, more information can be found in the documentation.

I think the best starting point would probably be to read the documentation for the new API and try it out first, before writing the rest of the app. You still seem to be using the old API, which is no longer supported.

[kusuri]

Hi! Thank you for your reply. Super helpful.

Yes, I’ve read a bit of the new API documentation, and I see that we have a new API. I tried to use their example for authorization, to start off but I kept getting a header error. Your example seems really helpful. Thank you! I’ll start there and see how far I get.

[kusuri]

Hi again -

So when you say it fetches the “first set of reviews”, what does that mean? What is a review in this context? I’m going through the API documentation and the example it lists for a review is below. It makes it seem like when I’m getting a first set of reviews, I’m getting the actual kanji characters and statistics for that.

It’s a bit confusing. Reviews doesn’t seem like what I’m looking for. But when I go to the documentation on statistics, it talks about % answered correct, which is also not what I’m looking for.

Edit - what do the devs call the number of radical/kanji/vocab in Apprentice, in Guru, etc levels? Statistics or reviews or something else?

Edit 2 - Also is it a collection or is it a report? It’s unclear in the documentation what those mean to me.

Edit 3 - I think it may be under the Staged Repetition Systems section of the documentation. But I’m still having a hard time understanding how to just figure out how to get something as simple as how many radicals I have in Guru level right now…

Any tips?

[BIsTheAnswer]

The example I gave was more about how to use the new API than it was a concrete implementation of the exact routine you’re trying to write.

@eainge

What is a review in this context?

A review in the API is a combination of a meaning and reading review on WaniKani. So if you answer both the meaning and reading for an item, it’ll show up in this set.

@eainge

what does that mean?

There are a lot of reviews, and the API doesn’t return all of them in one go (that would be one enormous reply to send). Instead it returns a number of them at a time, and you can use the pagination options (e.g using the next_url field present in the returned object) to get the next part of the set if there is more data availlable.

@eainge

what do the devs call the number of radical/kanji/vocab in Apprentice, in Guru, etc levels?

They don’t, for as far as I know, you have to accumulate these yourself if you want to find out the numbers. You can either use the assignment endpoint for the current state or the reviews endpoint to compute the history over time. They both have srs stage-related fields giving you this information.

@eainge

I think it may be under the Staged Repetition Systems section of the documentation.

This one seems to me like it was designed to report the SRS intervals, not the items itself.

@eainge

how to just figure out how to get something as simple as how many radicals I have in Guru level right now…

For the current state, you can use the assignments endpoint and look at the srs_stage field, which gives you information on which stage the items are currently in. You can also use this as a query parameter to filter out just the Guru items. It even has a subject_types filter which you can use to only get radicals. Combining the two should give you what you want.

[kusuri]

Thank you for your in depth reply. I’m getting closer. I really do appreciate your patience. I’m not a Javascript coder, although I do code in Matlab and used to in Java. But I would like to learn.

Thanks for pointing me in the right direction. So if I wanted to filter out and only grab assignments that are radicals and at the Guru level, my Get call would look like below? Does that seem right?

var raw = UrlFetchApp.fetch('https://api.wanikani.com/v2/assignments?srs_stages=5&srs_stages=6&subject_types=radical', {
    method: 'GET',
    headers: {
        'Wanikani-Revision': '20170710',
        'Authorization': 'Bearer ' + apiKey
    }
})

If yes, could you point me towards how to get info from the raw variable I have now? If I just want to know how many returned, is that something like this?

var radGuru = raw.totalcount

Thanks again!

[BIsTheAnswer]

No need to specify srs_stages twice, you can just give multiple values in one go by separating them by commas, thus srs_stages=5,6.

After that, you’ll need to actually make the request and parse the result as a JSON object like you did in your original code before you can actually use it.

[kusuri]

Alright! I made perhaps the worst Javascript code of all time, but I did it! I had to do a lot of Get calls since I had to filter for Radical/Apprentice, Radical/Guru, etc, all the way to Vocab/Burned. But I was able to run it and get my code to export to my spreadsheet. Lovely!

However, there seem to be some inaccuracies. I’m not sure why, but it returns numbers that are off by +/- 1. Here are the results below. It compares what the code outputs to what I manually see. You can see that Guru is good, but Apprentice, Master, Enlightenment, and Burned are off. For example, it says I have 331 apprentice items, but Wanikani.com shows 330 apprentice items.

Spreadsheet snapshot:

Wanikani.com snapshot:

Any ideas of why it might be off? Happy to share my (terrible) code if that’s helpful.