Google Sheets
Use Google Sheets to import actuals from non-supported systems.
Overview
The Google Sheets integration enables you to import data from virtually any business tool into Francis for financial planning. By pairing Google Sheets with third-party integrations, you can connect CRMs, ERPs, data warehouses, or create custom API connections. Use this integration as a flexible option for importing data that is not already supported by Francis’ native accounting or currency integrations.
Configuration
To set up the Google Sheets integration, go to Settings > Integrations > Google Sheets and connect your Google account. Francis will then create a new Google Sheets file called Francis Data Source in your Google Drive. Use this template to input your data, and when you sync the connection again, Francis will automatically import any new or updated information.
To share this sheet with team members, update its permissions in Google Sheets or move it from your personal drive to a shared drive.
When you configure this integration, Francis requests permission to access your Google account. Francis can view, edit, create, and delete only the specific Google Drive files it creates. Francis does not request or gain access to any of your other Google Drive files.
Data formats
Before setting up a Google Sheets connection, you must choose one of two templates — monthly totals or detailed entries — based on how your data is organized. Select the template that best matches your existing format and reporting needs.
Select monthly totals if your data is already aggregated by month and does not require drilling down into individual records. This format is best suited for data that is summarized on a monthly basis.
Google Sheets for Microsoft users and organizations
If you’re using Microsoft 365, you can still connect Google Sheets to Francis by creating a Google account that uses your Microsoft email address. This approach lets you add a Google Sheets connection to your organization within Francis.
We plan to introduce Excel Online support in 2025 and are currently assessing the technical requirements. If you have questions about this feature, please reach out to support@francis.app.
Importing accounting data
Google Sheets makes it easy to import data from accounting systems that aren’t supported by our native integrations. Follow the steps below to ensure Francis accurately interprets your accounting data and applies the correct transformations.
Format your data
After establishing a connection to Google Sheets, start by uploading your data. Since you’re working with accounting data, it’s important to format it correctly to ensure proper transformation once it’s in Francis. Follow these guidelines for consistent results:
-
Income accounts: Input negative (-) values.
-
Expense accounts: Input positive (+) values.
-
Asset accounts: Input positive (+) values.
-
Liability accounts: Input negative (-) values.
-
Equity accounts: Input negative (-) values.
Balance sheet accounts (assets, liabilities, and equity) are automatically accumulated at import. To accommodate this, provide delta values rather than cumulative totals.
Treat as entity
Once your data is uploaded, switch on the Treat as entity setting for your Google Sheets connection. This indicates to Francis that the uploaded data represents accounting information. In response, Francis applies a series of transformations to present your data in a compliant format.
Categorize accounts
After enabling Treat as entity, Francis will prompt you to categorize each account as income, expense, asset, liability, or equity. This step ensures that Francis can automatically accumulate balance sheet accounts and correctly adjust values based on standard accounting conventions. Once categorized, Francis converts your data as follows:
-
Income accounts: Converted to positive (+) values.
-
Expense accounts: Converted to negative (-) values.
-
Asset accounts: Converted to positive (+) values.
-
Liability accounts: Converted to positive (+) values.
-
Equity accounts: Converted to positive (+) values.
Adjust consolidation setup (Optional)
If you’re already consolidating actuals across entities in Francis and want to incorporate accounting data imported from Google Sheets, you’ll need to adjust your existing setup to include this new data source.
Open your consolidation template and select Adjust split from the action menu. Then, choose Add new sheet and drag the chip representing your newly added accounting data from the Not yet assigned group to the new sheet. Click Apply to confirm your changes. Your consolidation will now include an additional instance, reflecting the newly integrated Google Sheets data.
Syncing
Changes made in Google Sheets do not automatically sync to Francis. To reflect the latest updates in Francis, you must manually initiate a sync. If your data is not formatted correctly and does not meet the required data structure, the sync will fail, prompting you to correct the source data in Google Sheets before attempting again.