Google Sheets™ Destination

Intro

Finicom's Google Sheets™ destination syncs your financial transactions to a spreadsheet within your Google drive account or Workspace, so anyone in your workspace can create financial reports directly in Google Sheets™!

Creating a Google Sheets™ Destination

1. Go to your organization's destination page
2. Click "Add Destination"
3. Click "Google Sheets™"
4. Make sure you have a Google Account connected to Finicom. If not, select the "Google Account" dropdown and select "Connect to Google"
   • Complete Google's authorization flow to connect your Google account to Finicom.
5. Select the Google account you'd like to use for this Desitination
6. Select which Spreadsheet you'd like to sync transactions to. Finicom will open a selector that allows searching and selecting any sheet in your drive.
7. Last, optionally select which sheet within your spreadsheet you'd like transactions to sync into.
   • If no sheet is explicitly selected, Finicom will automatically sync transactions into the first sheet of the spreadsheet. When this happens and a different sheet moves into the first position, transactions will begin to sync to the new first sheet.

Details

• New transactions are added as a new row into the last open row without any data inside it.
• Modified transactions (moving from pending to posted status) will update the transaction's row within your spreadhseet to be correct & current data.
• Removed transactions (voided) will clear the transaction's row from your spreadsheet.
• To filter and only sync transactions to your spreadsheet for a specific transaction status (pending or posted) use filter to filter for only the status you'd like.
• Re-ordering rows is safe. When Finicom creates a new row of data, updates will apply to that grouping of data, wherever it is within that same sheet.
• Finicom may not sync transactions in the order they appear on your financial account. We recommend applying a sort order within your spreadsheet to achieve a desired ordering.
• Re-ordering columns is not safe, but hiding columns is. Finicom will always sync data across columns within a single row in this order:
  • Transaction creation date (Date & time)
  • Merchant the Transaction With (text)
  • Transaction description (text)
  • Transaction amount (number)
  • Transaction status (pending, posted or void)
  • Institution Name (text)
  • Source Account Name (text)
  • Transaction Category (text or empty)
  • Currency code (ISO 4217)
  • Unique transaction identifier (text)
  • Unique financial account identifier (text)
• Finicom always tries to maintain an accurate sync with your financial account. Because of this, if a transaction moves from pending to posted and that transaction is not present in your sheet, it will be added.
• Modifying data within a row may be overwritten or deleted if the transaction is not already in a posted (final) state, however, adding more data into columns after transaction data will not be modified

Template

Don't have an existing Google Sheet? Create one in exactly the correct format from our template here.

Keeping a Google Sheet Sorted

Oftentimes, your transactions will be listed with the most recent dates at the top. Finicom will always add transactions to first open row from the top.

If you wish to manually apply a sort to your transactions data, you can safely do so.

If you wish to automatically keep sort order after Finicom updates your sheet, you can achieve this through Google Apps Script by:

1. Open Google Sheets™: Open the Google Sheets™ document where you want to apply the automatic sorting.
2. Open Script Editor: Click on Extensions > Apps Script to open the script editor.
3. Enter the Script: Add code in the script editor with the following code:

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var sheetName = sheet.getName(); // Get the active sheet's name
 
  // Specify the sheet name where the sorting should apply
  var targetSheetName = "Sheet1"; // Replace "Sheet1" with the name of your sheet
 
  // Check if the edit is in the correct sheet and in Column A
  if (sheetName === targetSheetName && e.range.getColumn() == 1) {
    // Sort the entire Column A (from A2 to the last row)
    var lastRow = sheet.getLastRow();
    var rangeToSort = sheet.getRange(2, 1, lastRow - 1, 1); // Adjust range from A2 to the last row
 
    // Perform the sort with earliest date first
    rangeToSort.sort({ column: 1, ascending: false });
  }
}

4. Save the Script: Save the script by clicking on the floppy disk icon.
5. Authorize the Script: The first time you run this script, you'll need to authorize it. Follow the prompts to give the script the necessary permissions.
6. Close the Script Editor: After saving and authorizing, you can close the script editor.

Limitations

Google Sheets™ has a limit on the amount of data you can store in each individual Sheet. Right now, that limit is around 800 transactions per Sheet. However, the good news is that there’s no limit to how many total transactions you can store across multiple Sheets in the same Spreadsheet.

What to do if you hit the limit:

If you run into this limit, we suggest creating a new Sheet in the same Spreadsheet. Then, update your sync settings so that new data goes to this new Sheet. A common approach is to split Sheets by calendar year or month to keep things organized.

Controlling which transactions are sent:

You can manage which transactions are sent to a destination using our filtering feature. This allows you to send transactions based on the date they were created, so you have more control over what data gets synced.