Download YNAB Category Balance into Google Sheets (Guide)

This guide details how to download the totals of your YNAB categories into Google sheets automatically.
It follows on from the earlier guide where we downlaoded account balances once per month automatically which can be found here: Automatically download YNAB account balance into Google Sheets

Setup YNAB

In order to pull data from YNAB we need to setup an Access token so that we can perform API calls.
  1. Go to your YNAB settings and select Developer Settings

  2. Select Developer Settings

  3. Select New Token, enter your Password and click Generate
    • Store the Token in a memorable location
    • This will be referred to as your Personal Access Token (PAT)
  4. Go to your YNAB budget and select an account

  5. Note down the budget Id
    • The alphanumeric string that comes after app.youneedabudget.com/ and before /accounts as shown in the image below (the area inside the red box).

    • Copy the YNAB budget Id
    • The budget Id will not change

  6. Go to https://api.youneedabudget.com/v1#/ and select Authorize in the top right corner

  7. Select Authorize

  8. Enter your Personal Access token and Select Authorize

  9. Select Authorize

  10. Close the box and then scroll down to the Categories section.

  11. Select List Categories, then enter your budget Id and select Execute (leave the other field blank)

  12. Enter Budget Id and select Execute

  13. Scroll down to the categories you would like to track in Google Sheets, and note down the Id for each
    • In this example I am using 3 categories
      • Beauty (1111-1111)
      • Clothing (2222-2222)
      • Haircut (3333-3333)

  14. Note down the Id for each category

Setup Google Sheets

  1. Create a Google Sheet and rename the first sheet to YNAB_Category_Data

  2. Format all rows except Row 1 as currency (dollar amounts)

  3. If you are following along with this guide, to see an accurate example set up the first page like so:
    Note: It is important not to add the months for the current or future months, only previous.

  4. Setup the Google Sheet

  5. Select Tools and then Script Editor

  6. The Script Editor page should open with a single file called Code.gs
    Clear the file so that it is blank in preparation for the Google Script below.

The Google Script

Note: For this section, anything that comes after // is a comment, and wont be performed by the script

This script will put data into the YNAB_Category_Data sheet. The current month will be in Row 1, the downloaded balance of YNAB categories will be in Rows 3, 4, and 5 (for this example).

Please make sure you replace the items highlighted in yellow with your own data.

function YNAB_Category_Downloader() {
var ss = SpreadsheetApp.getActive();
var sheet = ss.getSheetByName(’YNAB_Category_Data’);

var PAT = ‘personal_access_token_here’; //Personal Access Token
var budgetID = ‘budget_Id_here’; //Budget Id

var baseurl = ‘https://api.youneedabudget.com/v1/budgets/’+budgetID+’/months/current/categories/’;

//repeat the below for however many categories you wish to download

var beauty = ‘category_id_01_here’; //Beauty Category Id
var clothing = ‘category_id_02_here’; //Clothing Category Id
var haircut = ‘category_id_03_here’; //Haircut Category Id

var headers = {
Authorization’ : ‘Bearer ‘+PAT
};

var params = {
method’ : ‘GET’,
headers’ : headers,
contentType’ : ‘application/json
};

//repeat the below for however many categories you wish to download

var beautyResponse = UrlFetchApp.fetch(baseurl+beauty,params).getContentText();
var clothingResponse = UrlFetchApp.fetch(baseurl+beauty,params).getContentText();
var haircutResponse = UrlFetchApp.fetch(baseurl+beauty,params).getContentText();

//repeat the below for however many categories you wish to download

var beautyAmount = (beautyResponse.substring(beautyResponse.lastIndexOf(’“balance”’) + 10,beautyResponse.lastIndexOf(’,“goal_type”’))) * 0.001;
var clothingAmount = (clothingResponse.substring(clothingResponse.lastIndexOf(’“balance”’) + 10,clothingResponse.lastIndexOf(’,“goal_type”’))) * 0.001;
var haircutAmount = (haircutResponse.substring(haircutResponse.lastIndexOf(’“balance”’) + 10,haircutResponse.lastIndexOf(’,“goal_type”’))) * 0.001;

//add any additional categories here to get the total amount

var total = beautyAmount + clothingAmount + haircutAmount;

var lastMonthColumn = sheet.getLastColumn()+1;
var columnLetter = column_to_letter(lastMonthColumn);

var date = new Date();
var month = Utilities.formatDate(date, Session.getScriptTimeZone(), “MMMM”);

//repeat the below for however many categories you wish to download

sheet.getRange(columnLetter+“1”).setValue(month);
sheet.getRange(columnLetter+2”).setValue(total);
sheet.getRange(columnLetter+“3”).setValue(beautyAmount);
sheet.getRange(columnLetter+“4”).setValue(clothingAmount);
sheet.getRange(columnLetter+“5”).setValue(haircutAmount);
}

function column_to_letter(column){
var temp, columnLetter = “;
while (column > 0)
{
temp = (column - 1) % 26;
columnLetter = String.fromCharCode(temp + 65) + columnLetter;
column = (column - temp - 1) / 26;
}
return columnLetter;
}

//ensure you copy all the way to here!


  1. Go back to your Google Sheet and select:
    • Tools
    • Macros
    • Import

  2. Select Tools, Macros, Import

  3. Select Add Function

  4. Select Add Function

  5. Back on your Google Sheet select:
    • Tools
    • Macros
    • YNAB_Category_Downloader

  6. Select Tools, Macros, YNAB_Category_Downloader

  7. The script will now run (you should see it say Running script) and you should see values be entered into your YNAB_Category_Data sheet like so:

  8. The script will run and data will be enterd into your YNAB_Category_Data sheet

Automate the script

  1. In the Script Editor page, select the Triggers button

  2. Select the triggers button in the Script Editor

  3. Script Triggers page will open, select Add Trigger in the bottom right corner

  4. In the window that opens, select the following:
    • YNAB_Category_Downloader
    • Head
    • Time-driven
    • Month timer
    • 1st
    • 3am to 4am
    • Notify me immediately
    • Save
Select YNAB_Category_Downloader, Head, Time-driven, Month timer, 1st, 3am to 4am, Notify me immediately, Save

The script will now download your data and place it in the Google Sheet on the 1st of every month.

Following on from this guide you can also have the Google Script alert you on your mobile once it has completed, further information on this can be found in our guide Send notification from Google Sheets to Mobile using Pushover.