top of page

Generate OpenAPI (Swagger) Specification Automatically with AlchemyJ

In this tutorial, I'm going to show you how you can enable OpenAPI (Swagger) from API generated by AJ

For this tutorial, we are going to use the model we created from our previous post (How to write your first API with AlchemyJ).

Open the file you saved previously and if you follow the tutorial exactly, the file name should be SayHello.xlsm. Before we start to change the content, let's save the file under a new name.

Go to File -> Save As -> OpenAPI Swagger.xlsm

Please ensure the "Excel Macro-Enabled Worksheet" is selected as the file type when you save.

Let's go to the BizLogic tab and have a quick review of what we did previously. In this model, we take the input from B1 and generate output on A6 based on the formula in B2.

Now let's add another function to the model that can show today's date.

I've prepared something that looks like below.

Let's prepare a formula that able to produce results as below.

Hello, raymond today's date is 29/04/2020

*assuming the input in B13 is RAYMOND

Give it a go before you compare your formula with mine. All this can be achieved with standard excel formula.

Once you've tried, compare yours with mine.

="Hello " & LOWER(B13) & ", today's date is " & TEXT(TODAY(),"dd/mm/yyyy")

Now we have the formula ready, let's configure it to only run when the function is called. To do that, we will change the formula to look like below.

=IF(AlchemyJ_FunctionPoint="Date Today", "Hello " & LOWER(B13) & ", today's date is " & TEXT(TODAY(),"dd/mm/yyyy"), "")

*the function "=IF(AlchemyJ_FunctionPoint="Date Today"," will ensure that this formula will run only when Date Today is triggered

While we are at it, we will change the formula in B2 to something like below.

= IF(AlchemyJ_FunctionPoint="Time Now","Hello " & PROPER(B1) & ", now is " & TEXT(NOW(), "hh:mm:ss"), "")

Now we configure the output table to show the end result of our second formula. I've prepared a table that looks like below.

In cell A18, we will put the formula to refer to cell B14.

*the formula is "=B14"

Next, we select the cell A20 and go to the AlchemyJ ribbon. There we select Insert Snippet then the Database Relationship Schema.

On the cell B22, type in DateOutput then on cell C22 type in the formula "=ajGetAddress(" and select A17:A18 then press Enter. Your formula should look like this "=ajGetAddress(A17:A18)"

Now that we've completed the logic, let's move to the ##RestApi tab. We will change the API name from MyAPI to OpenAPI_Swagger.

For Package name, let's change it from my_api to openapi_swagger.

You should have something like below.

While we are here, let's configure the OpenAPI (Swagger) Specification.

Go to cell B94 and change it to Yes, this will enable the specification to be generated automatically.

On cell B95, we will type in OpenAPI Swagger for Document Title, and on cell B96 we will type in OpenAPI Swagger documentation for Document Description.

*you can type your own title and description and it'll show on the specification page

We will leave others as default for now.

Next, we go to the ##RestEndpointGroup tab. First, let's change some value in the Basic Settings table. I've changed mine as below.

*the Endpoint group path is the first part of the API address

Then we configure the Function Points table as below.

*the Path is the second part of the API Address

*for Response Data Address use the "=ajGetAddress(" formula and select A9:K10 for cell D13 and A21:K22 for cell D14.

Now let's configure the Input Parameters as below.

*for Data Address use the "=ajGetAddress(" formula and select B1 for cell E27 and B13 for cell E28.

Now that we've done configuring, it's time to test our model on Excel. Go to the AlchemyJ ribbon, select the Run Function Point. Select "Time Now" and you should be able to see your output on A6 showing the result.

Anything you type in cell B1 will be taken as input and generate as output.

Now let's run the second function, select the Run Function Point again. Select "Date Today" and you will see your output on B14.

For this function, the cell B13 is the input cell.

Now it's time for us to generate the API but before that, let's validate if everything is fine with our model. We should get the same warning as our previous model.

We will ignore this message and proceed with the generation of the API, so select the Generate API and select Yes when prompt to save the workbook.

Your API will be generated in less than a minute. Once you see the below screen, your API is ready for testing.

Now that your API is generated, let's test it. Instead of typing the address manually like our previous test, just type the below to your browser address and press Enter.


You should land on a page similar to the below

This is the OpenAPI (Swagger) Specification page. Here you can test your API easily. Let's run some tests here.

First, we select the GET for Date Today.

*the blue GET button

Once you select the GET, it'll expand and you will see more options here. Select Try it out, then type in anything you want in the Input as below. Now select Execute.

After it's successfully executed, it'll show the results under Responses.

The Request URL is how we used to call the API manually previously.

And the Response body is how your response will look like.

You can proceed to test the other API we generated as well or use different inputs to generate different responses.

That's all for this tutorial, let me know what you think in the comment section below.


Recent Posts

See All


bottom of page