WaniKani Data to Google Sheet Apps Script. More info: https://community.wanikani.com/t/wanikani-google-sheet—pull-data-from-api-version-2-to-sheet/44844

wanikani-data-to-google-sheet-apps-script.js

const onOpen = () => {
  const spreadsheet = SpreadsheetApp.getActive()
  const menuItems = [
    { name: 'Prepare sheets...', functionName: 'prepareSheets_' },
    { name: 'Add new daily entry...', functionName: 'generateNewDailyEntry_' }
  ]
  spreadsheet.addMenu('Daily Status', menuItems)
}

const dailyEntriesSheetDetails = {
  name: 'Daily Entries',
  headers: [
    'Date (YYYY-MM-DD)',
    'Apprentice radicals',
    'Apprentice kanji',
    'Apprentice vocabulary',
    'Guru radicals',
    'Guru kanji',
    'Guru vocabulary',
    'Master radicals',
    'Master kanji',
    'Master vocabulary',
    'Enlightened radicals',
    'Enlightened kanji',
    'Enlightened vocabularies',
    'Burned radicals',
    'Burned kanji',
    'Burned vocabularies',
    'Unlocked radicals',
    'Unlocked kanji',
    'Unlocked vocabulary',
    'Started radicals',
    'Started kanji',
    'Started vocabulary',
    'Passed radicals',
    'Passed above kanji',
    'Passed above vocabulary',
    'Burned (event) radicals',
    'Burned (event) kanji',
    'Burned (event) vocabulary',
    'Resurrected radicals',
    'Resurrected kanji',
    'Resurrected vocabulary',
    'Lesson radicals',
    'Lesson kanji',
    'Lesson vocabulary',
    'Review radicals',
    'Review kanji',
    'Review vocabulary',
    'Total available radicals',
    'Total available kanji',
    'Total available vocabulary',
    'Current level',
    'Days on current level',
  ],
}

const apiSheetDetails = {
  name: 'API',
  headers: [
    'API token (version 2)'
  ],
}

const prepareSheets_ = () => {
  prepareSheet_(dailyEntriesSheetDetails)
  prepareSheet_(apiSheetDetails)
  getSheetByName_(apiSheetDetails.name).getRange('A2').setValue('(Replace this cell with your API token)')
}

const prepareSheet_ = ({name, headers}) => {
  const sheet = insertSheet_().setName(name)
  
  if (sheet.getMaxColumns() < headers.length) {
    sheet.insertColumnsAfter(sheet.getMaxColumns() - 1, headers.length - sheet.getMaxColumns())
  }
  
  if (sheet.getMaxColumns() > headers.length) {
    sheet.deleteColumns(headers.length, sheet.getMaxColumns() - headers.length)
  }
  
  sheet.deleteRows(2, sheet.getMaxRows() - 2)
  sheet.getRange("A1:1").setValues([headers]).setFontWeight('bold')
  sheet.setFrozenRows(1)
  sheet.autoResizeColumns(1, headers.length)
}

const generateNewDailyEntry_ = () => {
  const level = {
    current: null,
    numberOfDaysOnCurrent: null
  }
  const counts = {
    radical: {
      lessons: 0,
      reviews: 0,
      unlocked: 0,
      started: 0,
      passed: 0,
      burned: 0,
      resurrected: 0,
      totalAvailable: 0,
      srsStages: {
        0: 0,
        1: 0,
        2: 0,
        3: 0,
        4: 0,
        5: 0,
        6: 0,
        7: 0,
        8: 0,
        9: 0, 
      }
    },
    kanji: {
      lessons: 0,
      reviews: 0,
      unlocked: 0,
      started: 0,
      passed: 0,
      burned: 0,
      resurrected: 0,
      totalAvailable: 0,
      srsStages: {
        0: 0,
        1: 0,
        2: 0,
        3: 0,
        4: 0,
        5: 0,
        6: 0,
        7: 0,
        8: 0,
        9: 0, 
      }
    },
    vocabulary: {
      lessons: 0,
      reviews: 0,
      unlocked: 0,
      started: 0,
      passed: 0,
      burned: 0,
      resurrected: 0,
      totalAvailable: 0,
      srsStages: {
        0: 0,
        1: 0,
        2: 0,
        3: 0,
        4: 0,
        5: 0,
        6: 0,
        7: 0,
        8: 0,
        9: 0, 
      }
    },
  }

  const generateDataFromAssignments = () => {
    let assignmentsEndpoint = 'https://api.wanikani.com/v2/assignments?hidden=false'
  
    while (assignmentsEndpoint) {
      const response = fetchData_(assignmentsEndpoint)
    
      response.data.forEach(({ data }) => {
        const { subject_type, srs_stage, unlocked_at, started_at, passed_at, burned_at, resurrected_at } = data
                            
        counts[subject_type]['srsStages'][srs_stage]++
        if (unlocked_at) { counts[subject_type]['unlocked']++ }    
        if (started_at) { counts[subject_type]['started']++ }
        if (passed_at) { counts[subject_type]['passed']++ }
        if (burned_at) { counts[subject_type]['burned']++ }  
        if (resurrected_at) { counts[subject_type]['resurrected']++ }
        if (unlocked_at && !started_at) { counts[subject_type]['lessons']++ }
        if (started_at && (!burned_at || resurrected_at)) { counts[subject_type]['reviews']++ }
      })
                            
      assignmentsEndpoint = response.pages.next_url
    }
  }
  
  const generateDataFromSubjects = () => {
    let subjectsEndpoint = 'https://api.wanikani.com/v2/subjects?hidden=false'

    while (subjectsEndpoint) {
      const response = fetchData_(subjectsEndpoint)
    
      response.data.forEach(({ object }) => {
        counts[object]['totalAvailable']++
      })
                            
      subjectsEndpoint = response.pages.next_url
    }
  }
        
  const generateDataFromLevelProgressions = () => {
    let levelProgressionsEndpoint = 'https://api.wanikani.com/v2/level_progressions'
    let levelProgressions = []

    while (levelProgressionsEndpoint) {
      const response = fetchData_(levelProgressionsEndpoint)
    
      response.data.forEach(({ data }) => {
        levelProgressions = [...levelProgressions, data]
      })
                            
      levelProgressionsEndpoint = response.pages.next_url
    }
                            
    const latestLevelProgression = levelProgressions[levelProgressions.length - 1]
        
    if (latestLevelProgression) {
      level.current = latestLevelProgression.level
      level.numberOfDaysOnCurrent = numberOfDaysBetweenDates(new Date(latestLevelProgression.unlocked_at), new Date())   
    }
  }

  const srsStagesByStageName = {
    apprentice: [1, 2, 3, 4],
    guru: [5, 6],
    master: [7],
    enlightened: [8],
    burned: [9],
  }
      
  const todayDate_ = () => Utilities.formatDate(new Date(), getActiveSpreadsheet_().getSpreadsheetTimeZone(), 'yyyy-MM-dd')

  const numberOfDaysBetweenDates = (earlyDate, laterDate) => (laterDate.getTime() - earlyDate.getTime()) / (1000 * 3600 * 24)
                            
  const totalSrsStagesCountBySubjectType_ = (counts, srsStages, subjectType) => {
    return srsStages.reduce((totalCount, srsStage) => {
      return totalCount + counts[subjectType]['srsStages'][srsStage]
    }, 0)
  }
                            
  const fetchData_ = (apiEndpoint) => {
    const response = UrlFetchApp.fetch(apiEndpoint, {
      method: 'GET',
      headers: {
        'Authorization': `Bearer ${getApiToken_()}`,
        'Content-Type': 'application/json',
      },
    })

    return JSON.parse(response.getContentText())
  }
        
  generateDataFromAssignments()
  generateDataFromSubjects()
  generateDataFromLevelProgressions()
                            
  getSheetByName_(dailyEntriesSheetDetails.name).appendRow([
    todayDate_(),
    ...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(),
    level.current,
    level.numberOfDaysOnCurrent,
  ])
}

const getActiveSpreadsheet_ = () => SpreadsheetApp.getActiveSpreadsheet()
const getApiToken_ = () => getSheetByName_(apiSheetDetails.name).getRange('A2').getValue()
const getSheetByName_ = (sheetName) => getActiveSpreadsheet_().getSheetByName(sheetName)
const insertSheet_ = () => getActiveSpreadsheet_().insertSheet()