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

It’s hard to tell just from this. If the code is correct there shouldn’t be a difference.

Is there any reason you’re writing a script for this? I’m pretty sure there are already a number of scripts available that can just export this information as a csv, which would probably be a lot easier. @prouleau, the Item Inspector can export as a csv, right? I think that would be exactly the functionality you’re looking for.

The script is automatic though. It runs on it’s own. That’s why it’s so useful to me. It auto-fills the excel each day, in the exact format I want.

Here’s the full code, terribleness and all, if that helps. You can see that I ask to print to the log the total radicals that I’ve burned. In the log it says 135 radicals, even though my Wanikani website says 134. So whatever’s happening is happening right after I fetch it. Maybe I can try to find out exactly what it’s exporting and why there are 135 in the total count, while WaniKani says there should be 134 radicals But any insight is helpful!

var apiKey = '[My API Code]';

function fetchData2() {

  //Radicals  
  var raw = UrlFetchApp.fetch('https://api.wanikani.com/v2/assignments?srs_stages=1,2,3,4&subject_types=radical', {
      method: 'GET',
      headers: {
          'Wanikani-Revision': '20170710',
          'Authorization': 'Bearer ' + apiKey
      }
  })
    var parsed = JSON.parse(raw);
    var radA = parsed.total_count;
  
  var raw = UrlFetchApp.fetch('https://api.wanikani.com/v2/assignments?srs_stages=5,6&subject_types=radical', {
      method: 'GET',
      headers: {
          'Wanikani-Revision': '20170710',
          'Authorization': 'Bearer ' + apiKey
      }
  })
  var parsed = JSON.parse(raw);
  var radG = parsed.total_count;
  
  var raw = UrlFetchApp.fetch('https://api.wanikani.com/v2/assignments?srs_stages=7&subject_types=radical', {
    method: 'GET',
    headers: {
      'Wanikani-Revision': '20170710',
      'Authorization': 'Bearer ' + apiKey
    }
  })
  var parsed = JSON.parse(raw);
  var radM = parsed.total_count;
  
  var raw = UrlFetchApp.fetch('https://api.wanikani.com/v2/assignments?srs_stages=8&subject_types=radical', {
    method: 'GET',
    headers: {
      'Wanikani-Revision': '20170710',
      'Authorization': 'Bearer ' + apiKey
    }
  })
  var parsed = JSON.parse(raw);
  var radE = parsed.total_count;
  
  var raw = UrlFetchApp.fetch('https://api.wanikani.com/v2/assignments?srs_stages=9&subject_types=radical', {
    method: 'GET',
    headers: {
      'Wanikani-Revision': '20170710',
      'Authorization': 'Bearer ' + apiKey
    }
  })
  var parsed = JSON.parse(raw);
  var radB = parsed.total_count;
  console.log(radB);
  
  
  //Kanji 
  var raw = UrlFetchApp.fetch('https://api.wanikani.com/v2/assignments?srs_stages=1,2,3,4&subject_types=kanji', {
      method: 'GET',
      headers: {
          'Wanikani-Revision': '20170710',
          'Authorization': 'Bearer ' + apiKey
      }
  })
    var parsed = JSON.parse(raw);
    var kanjiA = parsed.total_count;
  
  var raw = UrlFetchApp.fetch('https://api.wanikani.com/v2/assignments?srs_stages=5,6&subject_types=kanji', {
      method: 'GET',
      headers: {
          'Wanikani-Revision': '20170710',
          'Authorization': 'Bearer ' + apiKey
      }
  })
  var parsed = JSON.parse(raw);
  var kanjiG = parsed.total_count;
  
  var raw = UrlFetchApp.fetch('https://api.wanikani.com/v2/assignments?srs_stages=7&subject_types=kanji', {
    method: 'GET',
    headers: {
      'Wanikani-Revision': '20170710',
      'Authorization': 'Bearer ' + apiKey
    }
  })
  var parsed = JSON.parse(raw);
  var kanjiM = parsed.total_count;
  
  var raw = UrlFetchApp.fetch('https://api.wanikani.com/v2/assignments?srs_stages=8&subject_types=kanji', {
    method: 'GET',
    headers: {
      'Wanikani-Revision': '20170710',
      'Authorization': 'Bearer ' + apiKey
    }
  })
  var parsed = JSON.parse(raw);
  var kanjiE = parsed.total_count;
  
  var raw = UrlFetchApp.fetch('https://api.wanikani.com/v2/assignments?srs_stages=9&subject_types=kanji', {
    method: 'GET',
    headers: {
      'Wanikani-Revision': '20170710',
      'Authorization': 'Bearer ' + apiKey
    }
  })
  var parsed = JSON.parse(raw);
  var kanjiB = parsed.total_count;
  
  //Vocab
  var raw = UrlFetchApp.fetch('https://api.wanikani.com/v2/assignments?srs_stages=1,2,3,4&subject_types=vocabulary', {
      method: 'GET',
      headers: {
          'Wanikani-Revision': '20170710',
          'Authorization': 'Bearer ' + apiKey
      }
  })
    var parsed = JSON.parse(raw);
    var vocabA = parsed.total_count;
  
  var raw = UrlFetchApp.fetch('https://api.wanikani.com/v2/assignments?srs_stages=5,6&subject_types=vocabulary', {
      method: 'GET',
      headers: {
          'Wanikani-Revision': '20170710',
          'Authorization': 'Bearer ' + apiKey
      }
  })
  var parsed = JSON.parse(raw);
  var vocabG = parsed.total_count;
  
  var raw = UrlFetchApp.fetch('https://api.wanikani.com/v2/assignments?srs_stages=7&subject_types=vocabulary', {
    method: 'GET',
    headers: {
      'Wanikani-Revision': '20170710',
      'Authorization': 'Bearer ' + apiKey
    }
  })
  var parsed = JSON.parse(raw);
  var vocabM = parsed.total_count;
  
  var raw = UrlFetchApp.fetch('https://api.wanikani.com/v2/assignments?srs_stages=8&subject_types=vocabulary', {
    method: 'GET',
    headers: {
      'Wanikani-Revision': '20170710',
      'Authorization': 'Bearer ' + apiKey
    }
  })
  var parsed = JSON.parse(raw);
  var vocabE = parsed.total_count;
  
  var raw = UrlFetchApp.fetch('https://api.wanikani.com/v2/assignments?srs_stages=9&subject_types=vocabulary', {
    method: 'GET',
    headers: {
      'Wanikani-Revision': '20170710',
      'Authorization': 'Bearer ' + apiKey
    }
  })
  var parsed = JSON.parse(raw);
  var vocabB = parsed.total_count;

 var totalA = radA+kanjiA+vocabA;
 var totalG = radG+kanjiG+vocabG;
 var totalM = radM+kanjiM+vocabM;
 var totalE = radE+kanjiE+vocabE;
 var totalB = radB+kanjiB+vocabB;

 //Write to Sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Raw data');


  var cell = sheet.getRange(sheet.getLastRow(), 1);
  var col = 0;
  function appendCell(data) {
    cell.offset(1, col++).setValue(data);
  }
    
  appendCell(new Date());
  
  appendCell('Blank');
  
  appendCell(totalA);
  appendCell(totalG);
  appendCell(totalM);
  appendCell(totalE);
  appendCell(totalB);
  
  appendCell(radA);
  appendCell(radG);
  appendCell(radM);
  appendCell(radE);
  appendCell(radB);
  
  appendCell(kanjiA);
  appendCell(kanjiG);
  appendCell(kanjiM);
  appendCell(kanjiE);
  appendCell(kanjiB);
  
  appendCell(vocabA);
  appendCell(vocabG);
  appendCell(vocabM);
  appendCell(vocabE);
  appendCell(vocabB);
  
  appendCell(radA + radG + radM + radE + radB);
  
  appendCell(kanjiA + kanjiG + kanjiM + kanjiE + kanjiB);
  
  appendCell(vocabA + vocabG + vocabM + vocabE + vocabB);
    
  appendCell(totalA + totalG + totalM + totalE + totalB);

}


Interestingly enough, when I run Viet’s code from above, it also says I have 135 burned radicals, just like my code does. But the WaniKani website says 134 burned radicals.

So it’s not my code, it seems to be a general error. @viet - Maybe you can shed some light here?

Viet’s Code:
image

My Code:
image

WaniKani Website:
image

I’ve put my detective hat on.

So Viet’s code, my code, and also Wkstats.com says that I have 135 radicals burned. The Wanikani website says that I have 134 burned radicals. Where’s this mystery radical coming from?

On wkstats, I can see the discrepancy. It says I have one radical burned on Level 29, the radical Hills. Since I’m level 13 right now, it seems unlikely that I should have this burned. The WaniKani website agrees. Seems like this is a bigger bug than just my code. Any idea how to report this?

image

The likely source of the count discrepancy is the idea of hidden subjects. We “hide” subjects when we no longer want to expose it to the user. Assignments have a “hidden” field to help identify these. The API returns both hidden and not hidden data by default.

If you wish to match the counts on the WaniKani app then you’ll need to filter out the hidden assignments. The assignment endpoint has a query filter parameter for this, which is outlined in the documentation.


Reading your last post more carefully…

The subjects can be moved around to different levels if our content team decides it makes sense to do so. We don’t reset user progress for the subject when this happens.

Here is the log where hills radical was moved from level 8 to 29

For this case, if you want the counts to match with the app, then you’ll need to filter out assignments where their associated subject’s level is greater than your user level.

The assignments endpoint has a query filter for levels, which can help you accomplish the goal. For the levels filter pass in a comma delimited list of every level up to your current user level.

3 Likes

Yes Item Inspector export to spreadsheet. But I wonder how OP autofill works. There are security in a browser that prevent writing into a user file.

Edit: Now I see. The data goes to Googlesheet which is web based. This give ideas for Item Inspector.

1 Like

Hi Viet -

Thanks for your reply. I will look into filtering out the hidden items.

As for the Hills radical, even though the Wanikani website does not show it as burned in my list of burned radicals, if I go directly to the Hills radical page, it shows it as burned. That’s a bit strange. I also don’t remember hills, so I figured I would resurrect it. However that button is missing from the Hills radical page. Interesting.

I was able to get my code updated and I wrote a summary topic here. Thank you all so much for your help with this code. It was a doozy, but it was fun to learn. :slight_smile: