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.
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 cause i suck at this haha
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
//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);
}
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.
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);
}
cause i suck at this haha