I'm going to show you how to implement a solution using Visual Basic code in Microsoft Excel to synchronize certain Liquid Content Types and Items using DNN Evoq's REST APIs. This blog post is going to be all meat, no potatoes. By establishing a connection via API between Excel and a destination web address, we can create and update very large batches of content in just seconds. Let's dive in.
Purpose of this demo
- Remove the dependency of Evoq Product and Persona Bar
- Avoid end-user to learn and understand JSon (Tree view), or complicated forms (Phase II will provide more features)
- An easy way for end-user to maintain the data
- A tool for QA to import/export data quickly
Use Case
The HR Department has asked IT to create a new page for a job posting, but doesn’t know exactly how many jobs and descriptions of jobs are needed at that moment. This information is still being tracked via Excel. Some HR team members don’t have login credentials to Evoq, so they can’t even create new web content themselves. By the same token, HR can’t be hindered from making progress and doesn’t want to wait for IT.
Technologies Used
- Liquid Content APIs from Evoq
- Excel VBA scripting
- JsonConverter module (3rd party)
- Dictionary.cls (3rd party)
Prerequisites
- A target website and obtain API Key for Liquid Content
- This API Key shall have permission to read and write Content Types and Content Items
- Enable Excel micro with file extension “.xlsm”
- Load JsonConverter.bas and Dictionary.cls in Excel micro
Step 1: Authentication
There are multiple methods to authenticate REST APIs from the client side, but here I'll introduce the preferred method when using Evoq: APIKey authentication.
How to get DNN APIKey
- Log in as Host User
- Go to Persona Bar > Content > Content Library > API Keys
- Create a new one or copy from existing ones.
- Make sure the APIKey has proper permission assigned.
More details: http://www.dnnsoftware.com/docs/content-managers/structured-content/create-api-key.html
Step 2: API Calls examples in Excel
Basic GET Request
Basic POST Request
The POST APIs allow us to CREATE “Content Types” and “Content Items”; while PUT calls to UPDATE; DELETE calls to remove.
Basic JSon POST String
JSON format is widely accepted and used in the context of NoSQL DB and serialization for information across internet. More knowledge about JSON, please refer to: https://www.tutorialspoint.com/json/
JSON Strings
There are lots of open-source JSON Converter Tools on the internet. In today’s demo, I am going to use VB-JSON module to handle JSON strings. Please make sure JsonConverter.bas and Dictionary.cls are imported to Excel VBA module.
Step 3: Put the code together to be executable in Excel for your uses.
Here I am using “JobPosting.xlsm” as today’s demo to use those modules and Evoq Liquid Content APIs to synchronize the data between MS Excel and one of the web pages.
After launching the “JobPosting.xlsm”, in sheet “ControlPage”, input “API Key” from Step 1, follow the steps as show in the screen-copy below to load all content items.
After step 4, it will automaticallygenerate a new sheet with couple controls dynamically generated on it.
For this example, we will try to add a new Job in Career
- Simply fill in the data in field <jobTitle> and <description>, using the same <contentTypeId>, click the button “Update to Remote >>>”
To modify a job in Career
- Change the data in <jobTitle> and <description>, Check on the <Selected>, click the button “Update to Remote >>>
Conclusion
All data will be reflected on the web page almost immediately, and viewable by all users. No need to wait for a web developer or IT to fix the page for you, all the changes could be complete in couple seconds. Even more, considering by using the Evoq Liquid Content and itsAPIs, those changes could be published to multiple channels automatically at the same time.