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:
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.
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
).
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.
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.
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.
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.
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;
}
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.
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! :)
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.