Download Teamleader 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.

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 Teamleader into Google Sheets may seem daunting. This guide explains how to real-time download data from Teamleader into Google Sheets.

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

Teamleader 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 for incidental and non-commercial use. Dedicated instances with additional security features are available for commercial and/or regular use.

The loading of data from Teamleader 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:

  • In this sample we use the API key. An API key reverts to solely V1, but has the advantage that ALL V1 APIs can be accessed. You can also log on using OAuth; in that case make sure your Teamleader user account allows access to the needed data and that you configure the right scopes. With OAuth, you can access all V1 and V2 APIs provided by Invantive SQL.
  • 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 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:

download-teamleader-into-google-sheets
download-teamleader-into-google-sheets1081×781 95.1 KB

  • The URL to retrieve the data from Data Access Point is constructed in cell B10 using the formula:
    =B5 & "Results?user="&B7&"&connection=PUBLIC%5Ckey&format=Csv&query=" & encode(B6) & "&password="&encode(B8)
  • 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 Teamleader Documentation

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

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

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