Skip to main content
Google Sheets, meet my bank account

Google Sheets, meet my bank account

Root Team  •  20 October 2018

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.

Deploying the script

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.

Setting up a trigger

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! :)


Author

Root Team

A collaborative article

Root is an end-to-end digital insurance platform that enables you to launch new products and digital engagement channels fast. We package all the compliance, regulatory and reporting complexities behind easy-to-use APIs, allowing your team to focus on building great customer experiences.

Are you interested in taking your insurance business to the next level today? Email us at [email protected] or get in touch here to hear more.

Schedule a chat

Stay in touch

Receive updates and the latest news from Root.

Thank you

Your email has been added to our mailing list.

By subscribing you agree to our Ts & Cs and our Privacy Policy, including our use of cookies.

We use cookies to ensure that we give you the best experience on our website, as described in our Privacy Policy.
If you continue to use this site we will assume that you are happy with it. Change options.
Accept

Statistic cookies help us to understand how visitors interact with our website by collecting and reporting information anonymously. Google Analytics cookies are anonymized.