Team Server provides a REST based API (REpresentational State Transfer; Application Programming Interface) to interact with it.
We can work with our Entities, Attributes, Tables, Views, 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
- A Google account to be able to create a new spreadsheet.
- A running Team Server installation accessible from the Google’s servers (otherwise we wouldn’t be able to automatically import).
- A valid access token to use the API (you can check my previous post and use the Excel workbook to get an access token).
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:
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
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:
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
In the new opened window, replace the existing code with the code from the library and save the project:
Go back to our spreadsheet and use this new formula:
[Glossaries!$A$3]: =ImportJSON($B$1)
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")
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")
In addition, we can easily convert the date numbers [Terms!$F] to human readable values:
[Terms!$G]: =IF($F3="", "", EPOCHTODATE(VALUE($F3)))
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! 👍