Qonto

Information about your transactions is uploaded to Google Sheets automatically, so you can complete more detailed analyses of your past transactions and manage your finances as you see fit. Need help using the Google Sheets file that contains your exported Qonto transactions? This article provides you with a step-by-step guide.

How do I use the data exported to Google Sheets?

To use your exported Qonto transactions in Google Sheets, you will need to change the date of your transactions, as this is still unavailable in Google Sheets. Keep in mind that the Sync. Transactions - Do not edit tab cannot be modified directly to protect against any accidental changes. If you modify this tab, the connection will be suspended, and your transactions will no longer be imported automatically.

Step 1: Duplicate your transactions in another editable spreadsheet

Log in to your Google Drive account and open the Google Sheets file created when the integration was installed. To quickly locate the file, enter type:spreadsheet "Qonto Connect -” in your Google Drive search bar.

Create a new spreadsheet by clicking on + at the bottom left of your screen, then click on the A1 cell and enter =(importrange

Add the URL of your spreadsheets in quotation marks, for example: “<a href="https://docs.google.com/spreadsheets/d/1MJud6TQOxS5VlRe0BlXKHm5QIeLVivSDv9Ukc/edit?usp=sharing%E2%80%9D" rel="nofollow noopener noreferrer" target="_blank">https://docs.google.com/spreadsheets/d/1MJud6TQOxS5VlRe0BlXKHm5QIeLVivSDv9Ukc/edit?usp=sharing”</a>

In the same formula, add a comma, and copy-paste "'Sync. transactions - Do not edit'!A:AZ”, then click Enter.

Your imported Qonto transactions have now been duplicated in another spreadsheet.

1. Log in to your Google Drive account and open the Google Sheets file created when the integration was installed. To quickly locate the file, enter type:spreadsheet "Qonto Connect -” in your Google Drive search bar.
2. Create a new spreadsheet by clicking on + at the bottom left of your screen, then click on the A1 cell and enter =(importrange
3. Add the URL of your spreadsheets in quotation marks, for example: “<a href="https://docs.google.com/spreadsheets/d/1MJud6TQOxS5VlRe0BlXKHm5QIeLVivSDv9Ukc/edit?usp=sharing%E2%80%9D" rel="nofollow noopener noreferrer" target="_blank">https://docs.google.com/spreadsheets/d/1MJud6TQOxS5VlRe0BlXKHm5QIeLVivSDv9Ukc/edit?usp=sharing”</a>
4. In the same formula, add a comma, and copy-paste "'Sync. transactions - Do not edit'!A:AZ”, then click Enter.
5. Your imported Qonto transactions have now been duplicated in another spreadsheet.

⚠️ Keep in mind: Avoid editing columns in which data are imported via the =importrange feature, as this will cause the synching of your Qonto transactions to stop. Read <a href="https://support.google.com/docs/answer/3093340?hl=en" rel="nofollow noopener noreferrer" target="_blank">this article</a> for more information about the =importrange feature.

Step 2: Change the date of your transactions automatically

In the spreadsheet you have just created, click on the first empty cell at the right of your screen in line 1, and enter the formula =ARRAYFORMULA(split(F2:F," ")) in which the F column corresponds to the settlement_date_local column.

Click on the first empty cell at the right of your screen in line 1 and enter =ARRAYFORMULA(REPLACEB(AI2:AI,3,1,"/")), then replace AI2 and AI with the coordinates of the cell you have used to populate the previous formula (AI2 and AI in the example below).

Click on the first empty cell at the right of your screen in line 1 and enter =ARRAYFORMULA(REPLACEB(AJ2:AJ,6,1,"/")), then replace AJ2 and AJ with the coordinates of the cell you have used to populate the previous formula (AK2 and AK in the example below).

Finally, click on the first empty cell at the right of your screen in line 1 and enter =ARRAYFORMULA(RIGHT(AK2:AK, LEN(AK2:AK)-3)), then replace AK2 and AK with the coordinates of the cell you have used to populate the previous formula (AL2 and AL in the example below).

You may now use the data from this sheet to create all the graphs and dashboards you need to manage your business.

1. In the spreadsheet you have just created, click on the first empty cell at the right of your screen in line 1, and enter the formula =ARRAYFORMULA(split(F2:F," ")) in which the F column corresponds to the settlement_date_local column.
2. Click on the first empty cell at the right of your screen in line 1 and enter =ARRAYFORMULA(REPLACEB(AI2:AI,3,1,"/")), then replace AI2 and AI with the coordinates of the cell you have used to populate the previous formula (AI2 and AI in the example below).
3. Click on the first empty cell at the right of your screen in line 1 and enter =ARRAYFORMULA(REPLACEB(AJ2:AJ,6,1,"/")), then replace AJ2 and AJ with the coordinates of the cell you have used to populate the previous formula (AK2 and AK in the example below).
4. Finally, click on the first empty cell at the right of your screen in line 1 and enter =ARRAYFORMULA(RIGHT(AK2:AK, LEN(AK2:AK)-3)), then replace AK2 and AK with the coordinates of the cell you have used to populate the previous formula (AL2 and AL in the example below).
5. You may now use the data from this sheet to create all the graphs and dashboards you need to manage your business.

👉 Keep in mind: To create personalized filters in your Qonto dashboard, we recommend leaving several empty columns between the first empty cell at the right of your screen and the operations from step 2.