Self-updating WaniKani Statistics & Data Spreadsheet

From what I understand I have to let the script run once, and then delete the word “formula” in the first line.
I assume i’m doing something wrong, it might be me, or it might be the way it has been explained but after the “arrange sheet” part of the script ran, the formula is placed above the first frozen row and it gives value errors.
I manually moved it and assumed after a second time it would run, it would sort itself out but today i got an email saying there was an issue with a script.


It also made the values in the yellow columns negative because of that.
image

Anyway, i really dont know how i can fix this but i hope someone has an idea cause this looks nice and i would love to get it to work as intended.

Thanks in advance!

EDIT: The range in the script goes from A1 to AM1000 and i only had 999 rows, so i’m hoping that was the problem?
I added a row, and manually sorted the first column, so i hope that fixes it. Any advice welcome :stuck_out_tongue: cause i suck at this haha

Glad to see this sheet/script is still being used and upgraded!

I have changed your sheet in such a way that you don’t neet to copy and paste the cells:

1 Like

Oh, that’s great! I never could figure out how to do it. ^^;

I added your changes to my spreadsheet-to-copy, and will now change the instructions.

Thank you so much!!

1 Like

sorry to necro, but will there be an update to this once the old api v1 keys will cease to work in september 2020?

Yes would also be very interested by an update or at least some explanation on how to make the below script compatible with the Api V2…

function fetchData() {
var options = {‘Cache-Control’ : ‘max-age=0, must-revalidate’}; //I’ve used this before to try and prevent google from giving me cached data when making API calls…seems to get the job done
var sheet = SpreadsheetApp.getActiveSheet(); //This script is bound to the corresponding sheet so we can use this conveinience method
var cell = sheet.getRange(sheet.getLastRow(),1); //Get a reference to current last cell in Column A

var raw_data = UrlFetchApp.fetch(“https://www.wanikani.com/api/user/d8fd194bf2f2b9f7275b795f62e4b13e/srs-distribution”, options).getContentText()
var doc = JSON.parse(raw_data);

//Total up kanji at guru and above
var derp = doc.requested_information.guru.kanji
derp += doc.requested_information.master.kanji
derp += doc.requested_information.enlighten.kanji
derp += doc.requested_information.burned.kanji

//Total up vocab at guru and above
var derp2 = doc.requested_information.guru.vocabulary
derp2 += doc.requested_information.master.vocabulary
derp2 += doc.requested_information.enlighten.vocabulary
derp2 += doc.requested_information.burned.vocabulary

//Total Burned vocab
var derp3 = doc.requested_information.burned.vocabulary

//Total Burned Kanji
var derp4 = doc.requested_information.burned.kanji

//Write new data at (last_row + 1)
cell.offset(1,0).setValue(new Date());
cell.offset(1,1).setValue(doc.requested_information.apprentice.total);
cell.offset(1,2).setValue(doc.requested_information.guru.total);
cell.offset(1,3).setValue(doc.requested_information.master.total);
cell.offset(1,4).setValue(doc.requested_information.enlighten.total);
cell.offset(1,5).setValue(doc.requested_information.burned.total);
cell.offset(1,6).setValue(doc.requested_information.apprentice.kanji);
cell.offset(1,7).setValue(derp);
cell.offset(1,8).setValue(doc.requested_information.apprentice.vocabulary);
cell.offset(1,9).setValue(derp2);
cell.offset(1,17).setValue(derp3);
cell.offset(1,18).setValue(derp4);
}

Thanks a lot in advance

I would also love help here. Trying to figure it out now.

The sheet made by Viet might be a good start…

I am glad the sheet is of use to some people and it looks like it is slowly picking up steam.

Modifying it to your liking isn’t so difficult if you have some programming knowledge or comfortable with JavaScript. The foundational stuff is already set up (API set up and data pull). All one needs to do is manipulate the data and write it to the row.

2 Likes

Hi Viet - I feel that I have some programming knowhow, but I only know Java and Matlab. I’m a bit stuck, though.

Someone wrote this old Google Apps script that pulls data on number of Apprentice, Guru, etc for Kanji, Vocab, and Radicals. It’s really very simple. However with the new API update it’s not able to pull the data anymore. I’ve read the new documentation for the updated API, but I honestly can’t seem to understand what’s wrong with my code. I also looked at your code, but even though they’re both Javascript, they seem to work very differently, so hard to know what to do here. Could you help me a bit? Is there an easy fix to the below code to be able to pull data? Thank you.

Edit: I also replied to your thread that directly talks about your script.

var apiKey = "[Enter Api Key]";

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);
}
1 Like

I was able to get my code updated and I wrote a summary topic here. Thank you everyone for your help!

1 Like