How to Use IMPORTRANGE to Sync Spreadsheet Transactions
What if you wanted to categorize your transactions in one spreadsheet and then share those transactions and categories with a different spreadsheet? That’s easy with the IMPORTRANGE formula built into Google Sheets.
We’ve explored the use of Google Sheets’ built in IMPORTRANGE formula for sharing your transactions between Google Sheets. In this post we’ll dig deeper into the capabilities of this built in power feature for using multiple spreadsheets while only categorizing a transaction one time.
Don’t be scared off by the term “power feature.” Anyone can run through these steps to maximize your flexibility while still getting the benefits of an automated feed of your bank data into a Google Sheet.
See a video tutorial of these steps at the bottom of this post.
Let’s say I want to use the Tiller Budget template and the Weekly Expense Tracker to track my spending for the same accounts. I like the reporting available in the Weekly Expense Tracker, but I don’t want to have to categorize the spending in both of them, thus doubling the work. IMPORTRANGE makes this a breeze.
Choosing the master financial spreadsheet
The first step is to choose a spreadsheet to use for your master data. This will be the Google Sheet that’s fed by Tiller each day with your latest transactions and bank balances. It’s also the only one you’ll really want linked to your Tiller Console, and it’ll be the place where you do the categorizing.
In our example let’s say we want our Tiller data fed into the Tiller Budget spreadsheet where we’ll categorize everything. Then we want those transactions and categories fed into a different spreadsheet, our Weekly Expense Tracker spreadsheet. It’s pretty straightforward to get this set up.
Prepping your sheets
From the Tiller Console make sure you have two spreadsheets created. In our example we’ll set up the Weekly Expense Tracker to receive the transactions, categories, and balances from the Tiller Budget template. If you’re creating new spreadsheets from templates, be sure to wait a few minutes for the data to populate into the Transactions sheet.
Open the Weekly Expense Tracker and then unlink it from your Tiller Console. (Click the sheet name and then choose “unlink this sheet”). We don’t want this sheet actively connected to Tiller because we’ll be using a shared data feed from the Tiller Budget sheet instead. Be sure to bookmark the URL for this unlinked sheet or organize it in your Google Drive after you unlink it since it will no longer appear in your Tiller Console.
In the Weekly Expense Tracker spreadsheet, create a new sheet using the plus (+) sign in the lower left corner of the Google Sheet and title it “Config” in this example. Next, paste the URL of your Tiller Budget sheet into cell A1. The rest of the formulas we’ll be using reference this Config sheet.
Importing your transactions
Open the Transactions sheet in the Weekly Expense Tracker and select all the data in that sheet (there’s a square in the upper left corner that will do this for you with one click) and then delete the data.
Select column C by clicking the column header letter and then open the “Data” menu and choose “Data Validation.” Next, choose to “remove validation” to ensure that when you import your categorized transactions you don’t get validation errors.
Place your cursor in cell A1 and paste in this formula:
=IMPORTRANGE(Config!A1, "Transactions!A:L")
The “Config!A1” portion of the formula is looking at cell A1 in the Config sheet we created previously, which is the URL of your Tiller Budget sheet. “Transactions!A:L” is pulling in all data from columns A through L on the Transactions sheet in the linked Tiller Budget Google Sheet
You’ll need to give it permission to connect to Tiller Budget so that it can import the data. Click on the #REF error (a popup requesting access should automatically appear) and then choose to “allow access.” You should then see your transaction data from the Tiller Budget populate into the Weekly Expense Tracker Transactions sheet.
Importing your categories
Next, you’ll want to share your categories between spreadsheets so that when you add new categories to your Tiller Budget, they’re reflected in the Weekly Expense Tracker. We can add an IMPORTRANGE formula to the Category Setup sheet to accomplish this.
On the Category Setup sheet select column A by clicking the column letter and then delete the data. Then place your cursor in cell A1 (the column header) and paste in this formula.
=IMPORTRANGE(Config!A1, "Categories!B:B")
Once again the config parameter is looking at the Config sheet we created and is using the URL for our Tiller Budget spreadsheet. Then we’re looking for the Categories sheet in the Tiller Budget template and pulling in only column B, the categories column.
You should see that your categories from the Tiller Budget are now pulled into the Category Setup sheet here in the Weekly Expense Tracker.
Importing your Balance History
=IMPORTRANGE(Config!A1, "Balance History!A:L")
We’re using the Config sheet again to access the Tiller Budget spreadsheet and then we’re pulling in columns A through L from the Balance History sheet. Now we’ll see the balances update for our linked accounts.
Voila! Now you can categorize everything in the Tiller Budget template and then see those updates reflected in your Weekly Expense Tracker, which you can always access from your Google Drive. You can use the built in category reports and charts in the Weekly Expense Tracker and monitor your progress in the Tiller Budget spreadsheet with a single data set.
A note about manual accounts
Not that these IMPORTRANGE formulas copy all of the data that Tiller feeds into your sheet. If you also entered manual accounts and balances on the Balances sheet in the Tiller Budget, you’ll need to separately add those to the Weekly Expense Tracker.
Video Demonstration
