People frequently ask us here at Root how to integrate their bank account with Google Sheets - a straightforward, yet super convenient integration that allows you to automatically get your bank transactions into a spreadsheet for analysis and budgeting.

Our mission is to open up financial services to software developers in a secure, yet simplified way.

Since many of our private beta users find integrating with Sheets a great life hack, I thought a cheatsheet ought to be useful. This post covers a few approaches to getting all your transactions into Google Sheets. From there, it’s up to you to craft the perfect analysis.

To achieve this, we have three options:

• Push: build a webhook in RootCode to post to a Google App Script web endpoint inserting the transaction into the Google Sheet.
• Pull: set up a scheduled job in Google App Script to fetch all transactions in our account and write it to our Google Sheet.
• Both: set up a scheduled job to update it periodically, and set up a push webhook to also run the update script.

We have not yet released webhooks as a feature for Root accounts, which, when launched, would be an easier option to implement.

I’m a fan of option 2, pulling, as this makes sure we capture all transactions into the sheet, not only card transactions. For the sake of completeness, I’ll go through all three.

Option 1: Post transactions to Google Sheets from RootCode

RootCode allows you to write any rules or functionality to enforce during a card transaction. Your RootCode script has full access to your Root bank account and can also make HTTPS requests to the outside world.

In the beforeTransaction function you can decline transactions, and in the afterTransaction function, you can do interesting things like sending yourself an SMS with your remaining budget for the week, categorise the transaction in your way or send a payload to Google Sheets. We’ll be using afterTransaction, as we don’t want to interfere with the limited time available during a card transaction (typically max 4 seconds execution time available for beforeTransaction).

Setting up the Google App Script

Firstly, create a new Google Sheet and give it a name. In our new Google Sheet, go to Tools > Script editor. This will open the Google App Script linked to our current sheet.

Here we want to create a doPost(e) function. doPost is Google App Script’s default handler for inbound POST requests. Our function receives an inbound post request, parses the payload into a row, and inserts this row into our sheet. We’ll be sending the transaction as the payload. Note that our sheet is still named the default: “Sheet1”.

function doPost(e) {
  // Get the transaction from the request
  const transaction = JSON.parse(e.postData.contents);
  
  // Compile an array representation of the new row
  const row = [
    new Date(transaction.created_at), // parse date
    transaction.transaction_id,
    transaction.card_id,
    parseInt(transaction.amount) / 100, // cents to rand
    transaction.method,
    transaction.type,
    transaction.description,
    transaction.category_id,
    transaction.contact_id
  ];
  
  if (transaction.merchant) {
    row.push(transaction.merchant.category);
    row.push(transaction.merchant.category_code);
    row.push(transaction.merchant.name);
    row.push(transaction.merchant.location);
    row.push(transaction.merchant.country);
  }
  
  // Now insert our new row at the bottom of the sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  sheet.appendRow(row);
  
  // Complete the request
  return ContentService.createTextOutput("Received, thank you!");
}

Next, we need to expose this script to the outside world; we need to activate it. In the toolbar, navigate to Publish > Deploy as web app… When the modal has loaded, change “Who has access to the app” to “Anyone, even anonymous”. Tap Deploy, then Review permissions and give it access. It will generate a web app URL. Save this somewhere for the next step.

Note: if you’re getting a 404 when posting to this URL, see this answer on Stack Overflow.

Setting up RootCode to post to your sheet

Now that we’ve set up the sheet and have our web app URL ready, we can start sending transactions to it. Open RootCode on the card that you’d like to track and send the transaction to the Google App Script using a simple POST request.

function afterTransaction(transaction) {
  var options = {
    url: 'YOUR_APP_SCRIPT_WEB_APP_URL_HERE',
    json: transaction
  };
  try {
    root.post(options);
  } catch (e) {}
}

You can also react to events such as declines, reversals, and adjustments using afterDecline, afterReversal, and afterAdjustment.

Boom! We have now configured a card to send its transactions in real-time to a Google Sheet.

Option 2: Periodically fetch transactions from Root

The second option to building an auto-updated Google Sheets for your bank account is having the sheet fetch the transactions from Root on a predefined interval.

This option is different from the previous in the sense that we’ll fetch all our transactions, instead of just a specific card’s transactions. We’ll use Google App Script’s Installable Triggers to run the scheduled job.

Fetching transactions from your Root bank account

First, as per the previous option, create a new Google Sheet and open the app script. The first function we want to create is one that fetches all your banking transactions. Create a read-only API Key in your account settings to use here.

function fetchTransactions(e) {
  // Copy a read-only API Key from your Root account
  const api_key = "production_eyJ0eXA...";
  
  // Fetch the transactions
  const headers = {
    "Authorization": "Basic " + Utilities.base64Encode(api_key + ':')
  };
  const params = {
    "method": "GET",
    "headers": headers
  };
  const result = UrlFetchApp.fetch("https://api.rootplatform.com/v1/transactions", params);
  
  // Parse and return the result
  const transactions = JSON.parse(result);
  return transactions;
}

When you run this script, it will ask you for permission to access outside services – go ahead and allow it.

Clearing and inserting transactions

This script is taking a very basic approach: clearing the sheet and fetching all transactions again, as opposed to retrieving only new transactions. I’ll leave that challenge up to you to figure out.

After we’ve fetched our transactions from Root, we need to clear the sheet first. We’re still using the default “Sheet1”.

function clearTransactions() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  var range = sheet.getRange("A1:N");
  range.clear();
}

Now that the sheet is cleared out let’s take that transactions array, map it to rows, and insert the entire range at once.

function insertTransactions(transactions) {
  // Create rows from the transactions
  const rows = transactions.map(transactionToRow);
  // Now insert the rows
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Sheet1");
  const range = sheet.getRange(1, 1, rows.length, 14);
  range.setValues(rows);
}

function transactionToRow(transaction) {
  const row = [
    new Date(transaction.created_at), // parse date
    transaction.transaction_id,
    transaction.card_id,
    parseInt(transaction.amount) / 100, // cents to rand
    transaction.method,
    transaction.type,
    transaction.description,
    transaction.category_id,
    transaction.contact_id
  ];
  if (transaction.merchant) {
    row.push(transaction.merchant.category);
    row.push(transaction.merchant.category_code);
    row.push(transaction.merchant.name);
    row.push(transaction.merchant.location);
    row.push(transaction.merchant.country);
  } else {
    row.push('','','','','');
  }
  return row;
}

Creating the scheduled job

We can now fetch transactions, clear the sheet, and insert the new payload, let’s create a scheduled function to handle these steps.

function scheduledJob() {
  const transactions = fetchTransactions();
  clearTransactions();
  insertTransactions(transactions);
}

The final step is to configure a Trigger to run this every hour. Go to Edit > Current project’s triggers… and set a trigger to run scheduledJob every hour.

We’re done! You now have a script that fetches all your Root account transactions every hour and keeps your Google Sheet updated.

Option 3: Combining push and pull

For those who are interested in having their sheet updated more frequently, a simple solution exists: run the scheduled job after a card transaction too.

To make this happen, we can simply create a doPost(e) function as before, and let this trigger scheduledJob().

function doPost(e) {
  scheduledJob();
  
  // Complete the request
  return ContentService.createTextOutput("Received, thank you!");
}

There you have some of the methods to get your Root bank account transactions into a Google Sheet, from where you can create exciting budgeting calculations, tracking, and graphs. The hard part lies ahead!

Let us know if there are any other examples you’d like us to share. Happy hacking!

By the way, if you’re interested in building great tools for developers: we’re hiring! Take a look at our careers page to see what’s going on at Root! :)