Download Exact Online into Google Sheets

Wiki (en)
, ,
1

Update 2023

Currently it may be possible to use OData v4 download using Google Sheets with the steps described on Use data from an OData feed - AppSheet Help.

Update 2021

The current (2021) approach to download Exact Online into Google Sheets is described at Hoe kan ik openstaande posten en bankmutaties downloaden in Google Sheets vanuit Exact Online?.

Google Sheets

Within the creative industry we see a lot of people using Google Sheets within a browser as an alternative for Microsoft Excel on a PC. Google Sheets provides from the foundation up both platform independence and online collaboration and runs on many platforms, including MacOS.

However, it is somewhat more limited than the Office apps from Microsoft which have been around for over twenty years and matured well. Downloading data from a cloud solution such as Exact Online into Google Sheets may seem daunting. This guide explains how to real-time download data from Exact Online into Google Sheets.

Although not described here, you can use the same Invantive technologies to upload data from Google Sheets into Exact Online.

The following approaches are to available to import data from Exact into Google spreadsheets:

Access OData and Invantive Cloud are free to use up to 5 Exact Online companies, group revenue below 3 million Euros and 100.000 rows downloaded per day.

Each of these approaches works on any operating system with network connectivity. The approaches are sorted in descending order of time for first success.

Each of these approaches can retrieve Exact data from one or hundreds of companies in one go.

Import Exact Online into Google Sheets using Get My Report

Get My Report is a very simple web application. Just click on the link, choose the country, choose the module and retrieve the data in Excel format.

Download from Exact Online with Get My Report
Download from Exact Online with Get My Report916×536 27.6 KB

Access OData and Invantive Cloud

Invantive Cloud primarily focuses on Power BI and Power Query users, but it also offers downloads in OData, CSV and Excel using Access OData and even through the integrated SQL editor.

After following the setup steps you can log on using https://access-odata.com:

Access OData
Access OData950×567 31.4 KB

Then choose the database:

Choose Cloud database

And download:

Choose Exact Online table
Choose Exact Online table673×510 31.4 KB

Exact Online into Google Sheets using Data Access Point

Base of this integration is the use of the real-time online variant of Invantive SQL. Invantive SQL provides a super-set of most commercially used SQL dialects, but relies for data storage on standard database platforms and cloud applications accessible through a API.

The real-time online variant of Invantive SQL is Invantive Data Access Point, located at https://data-access-point.com. It is free for incidental and non-commercial use. Dedicated instances with additional security features are available for commercial and/or regular use.

As with all Invantive SQL variants, it supports cross-company queries as well as cross-country queries on Exact Online.

The loading of data from Exact Online into Google Sheets will be demonstrated using a Google Sheets function named ‘Importdata’.

Steps to Load Data

Execute the following steps to retrieve your company’s data into a Google Sheets workbook:

  • Make sure your Exact Online user account allows access to the needed data through the screen.
  • Open Google Sheets.
  • Create a blank workbook.
  • Click on “Tools” in the menu and then “Script Editor” to add a function to escape URLs in another browser window.
  • Paste the following code in the script editor window:
function encode(value) {
  return encodeURIComponent(value);
}

  • Save the script, using a name such as ‘encode’.

  • Now continue in the original browser window and create a spreadsheet like this:

    google-sheet-exact-online-retrieve-data
    google-sheet-exact-online-retrieve-data1176×756 52.1 KB

  • The URL to retrieve the data from Data Access Point is constructed in cell B10 using the formula shown in cell B11.

  • The formula in B10 creates an Exact Online country specific connection as given in cell B7, combining those with specific user credentials found in cells B5 and B6.

  • The query in this case retrieves a list of projects from the company with number 868056. When you remove the use statement, data will be retrieved from the last used company by the user in Exact Online.

  • Actual loading of the data takes place through the Google Sheets formula importdata in cell A15:
    =importdata(B10)

  • In this sample, the credentials are not secured. Remember to secure the credentials using one of the possibilities such as IP-address authentication, pre-encrypted passwords and tokens for pre-authentication generated for a specific client ID.

Invantive and Exact Online Documentation

To more easily develop queries, you might want to use the Online SQL Editor for Exact Online or Invantive Query Tool.

The full Invantive SQL syntax is available as described in the grammar and concepts.

A graphical representation of the Exact Online data model as made available in Invantive SQL using XML and REST APIs is also available.

Also, all tables and views of the Exact Online data model are available on documentation.invantive.com.