Download Exact Online into Google Sheets

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.

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.

Exact Online into Google Sheets

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:

  • 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.