Automatically download YNAB account balance to Google Sheets (Guide)

In order to track my ongoing Net Worth I have a Google Spreadsheet that tracks my mortgage as well as Shares. In YNAB I track all current bank accounts and wanted a way to automatically pull this data into the Google Sheet each month for accurate reporting. As such I wrote a Google Script that will retrieve your YNAB accounts value and log them in 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
  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. Note down the account Id
    • The alphanumeric string that comes after /accounts/ as shown in the image below (the area inside the red box).

    • Copy the YNAB account Id
    • You will need to copy the account Id per account you wish to track

Setup Google Sheets

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

  2. Format column B and C as currency (dollar amounts)

  3. Select Tools and then Script Editor

  4. Select Tools and then Script Editor

  5. The Script Editor page should open with a single file called Code.gs

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 in the YNAB_Monthly_Data sheet. The current date will be in Column A, the downloaded balance of YNAB accounts will be in Column B and C (2 accounts in this example).


function YNAB_Data_Downloader() {

var ss = SpreadsheetApp.getActive();
var sheet = ss.getSheetByName(‘YNAB_Monthly_Data’);
var lastRow = sheet.getLastRow()+1;
var date = new Date();

var token = YNAB_Token_Here; //enter your YNAB token (from Step 2) inside the single quotation marks
var budgetId = YNAB_BudgetId_Here; //enter your YNAB budget Id (from step 4) inside the single quotation marks

// enter the account id (from step 5) insde the single quotes below (repeat for each account you wish to download)

var account01 = YNAB_AccountId01_Here;
var account02 = YNAB_AccountId02_Here;

//Below commands are setting up the API call Endpoint as well as the parameters requried (e.g. Headers)

var baseurl = https://api.youneedabudget.com/v1/budgets/'+budgetId+’/accounts/’;

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

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

//Below command performs the API call and stores the response data in a variable (repeat for each account)

var account01Response = UrlFetchApp.fetch(baseurl+account01,params).getContentText();
var account02Response = UrlFetchApp.fetch(baseurl+account02,params).getContentText();

//The API response contains a lot of data. The below command pulls just the cleared balance out of the response and stores it in a variable (repeat for each account)

var account01Balance = account01Response.substring(account01Response.lastIndexOf(’“balance”’)+ 10,account01Response.lastIndexOf(’,“cleared_balance”’));
var account02Balance = account02Response.substring(account02Response.lastIndexOf(’“balance”’)+ 10,account02Response.lastIndexOf(’,“cleared_balance”’));

//The balance is passed by YNAB in Miliunits format, so we need to multiply it to receive standard amount ($00.00) format (repeat for each account)

var account01Balance = account01Balance * 0.001;
var account02Balance = account02Balance * 0.001;

//Place the Date and the balance of account01 into the sheet YNAB_Monthly_Data

sheet.getRange(“A”+lastRow).setValue(date);
sheet.getRange(“B”+lastRow).setValue(account01Balance);
sheet.getRange(“C”+lastRow).setValue(account02Balance);

} //Ensure you copy right to the end and include this bracket

  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_Data_Downloader

  6. Select Tools, Macros, YNAB_Data_Downloader

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

  8. The script will run and data will be enterd into your YNAB_Monthly_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_Data_Downloader
    • Head
    • Time-driven
    • Month timer
    • 1st
    • 3am to 4am
    • Notify me immediately
    • Save
Select YNAB_Data_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.