Home > Team Server REST API – Import into Google Sheets

Team Server REST API – Import into Google Sheets

Team Server provides a REST based API (REpresentational State Transfer; Application Programming Interface) to interact with it.

We can work with our EntitiesAttributesTablesViews, etc, as well as manage (Create, Read, Update, Delete) our Glossaries and Business Terms, …

In my previous post, we have seen how to obtain the authorization to use the API.

In this one,  we’ll create a new blank spreadsheet in Google Sheets which will use the Team Server REST API to directly import and refresh the objects we want.

System requirements

Using Google Sheets

We start by creating a new blank spreadsheet and we rename the sheet Settings and we add the Team Server root  URL [Settings!$B$1] and access token [Settings! $B$2] in the sheet:

Settings

Then we add a new Sheet that we rename Glossaries and we can create a formula to generate the URL to use the API [Glossaries!$B$1]:

=Settings!$B$1 & "/api/v1/businessglossaries?access_token=" & Settings!$B$2

Glossaries URL
Google Sheets provides several functions to directly import data in different formats such as CSV, XML, HTML but it doesn’t provide any to import a JSON structure. However, the Team Server REST API returns JSON.

There are many ways to import a JSON file in Google Sheets. We can use an add-on available on the Google Workspace Marketplace:

Google Workspace Marketplace

We can also add some custom scripts. We will use a library shared on GitHub: import_json_appsscript.js and add it to the Apps Script:

From Google Sheets, click the menu Extensions/Apps Script

Apps Script

In the new opened window, replace the existing code with the code from the library and save the project:

Save project

Go back to our spreadsheet and use this new formula:

[Glossaries!$A$3]: =ImportJSON($B$1)

Import JSON

If the Team Server can be reached, we should get our business glossaries with a header and values similar to the screenshot above.

We can filter the JSON items with a path and options (check the source code we pasted earlier for more details):

[Glossaries!$A$3]: =ImportJSON($B$1, "/businessglossaries", "noTruncate")

We can filter specific columns using another function. For example:

[Glossaries!$A$3]: =QUERY(ImportJSON($B$1, "/businessglossaries", "noTruncate"), "SELECT Col7, Col3, Col6")

Query

Another example with the Terms:

API URL [Terms!$B$1]: =Settings!$B$1 & "/api/v1/businessterms?access_token=" & Settings!$B$2 & "&offset=" & $E$1 & "&limit=" & $G$1 
Import [Terms!$A$2]: =QUERY(ImportJSON($B$1, "/businessterms", "noTruncate"), "select Col12, Col9, Col5, Col4, Col11")

Terms

In addition, we can easily convert the date numbers [Terms!$F] to human readable values:

[Terms!$G]: =IF($F3="", "", EPOCHTODATE(VALUE($F3)))

EPOCHTODATE

Summary

In this blog post, we used:

  • the Team Server REST API to get the resources we need
  • Google Sheets to import and manipulate the resources

Bonus

I shared a video (8′) to support this blog post! 👍

ER/Studio Logo
Aligning complex data environments with business goals for over 30 years.
Copyright © 2024 Idera, Inc.