Convert GET parameter to POST request body and extract specific JSON property

Question

Background

• I am making a spreadsheet that refers to information security license registration provided by the Japanese government (https://riss.ipa.go.jp/). The spreadsheet will be used on Microsoft Excel/LibreOffice Calc on Windows/Linux, so I want to avoid using platform-specific functionality like a script with the XMLHTTP60 module.
• The site https://riss.ipa.go.jp has a URI that can retrieve registration information with a registration number (https://riss.ipa.go.jp/ajax/findRissRequest). The URI only works with a POST request with the application/x-www-form-urlencoded style request body and doesn't work with a GET request. The response of the URI is JSON format.

Problem #1

Microsoft Excel and LibreOffice Calc have the WEBSERVICE function that can be used to send a request to a URI. This function is supported on all platforms and is suitable for my use case.

Unfortunately, the WEBSERVICE function only supports GET requests, and the URI I want to use only supports POST requests.

Problem #2

Microsoft Excel and LibreOffice Calc have the FILTERXML function that can be used to extract a specific element from XML.

Unfortunately, the URI I want to use returns response in JSON format. There are no functions to parse JSON in Microsoft Excel and LibreOffice Calc.

Question

Is there any way to convert GET request to POST request and extract a JSON property?

For example, is there any Web API like http://api.example.com/convert/get-to-post?uri=https://riss.ipa.go.jp/ajax/findRissRequest&reg_no=000006&property=result.reg_date that calls https://riss.ipa.go.jp/ajax/findRissRequest with POST request body reg_no=000006 and extract property result.reg_date from its response?

Answer 1

After all, I could not find any existing services. So I made a web API service with AWS Lambda and API Gateway.

First, I made a Lambda function like this:

import json
import urllib.request
import urllib.parse

def lambda_handler(event, context):
    queryStringParameters = event.get('params').get('querystring')
    data = urllib.parse.urlencode(queryStringParameters)
    data = data.encode('UTF-8')

    f = urllib.request.urlopen("https://riss.ipa.go.jp/ajax/findRissRequest", data)
    j = json.loads(f.read().decode('utf-8'))
    return j

Then I made a resource with a GET method in API Gateway and connect it with the Lambda function.

• In Integration Request, you have to use non-proxy integration. Also, you have to specify a mapping template for Content-Type application/json with Method Request passthrough template.
• In Integration Response, you have to specify a mapping template for Content-Type application/xml like this:

<?xml version="1.0" encoding="UTF-8" ?>
#set($root = $input.path('$.result[0]'))
<result>
#foreach($key in $root.keySet())
    <$key>$root.get($key)</$key>
#end
</result>

Then I added the HEAD and OPTIONS method for the resource. It is because the WEBSERVICE function of LibreOffice sends OPTIONS and HEAD requests before a GET request. You can use a mock in Integration Request with a mapping template for Content-Type application/json like { "statusCode": 200 }. The result of WEBSERVICE function will be #VALUE! without these methods.

Finally, I can get a property from a web service that only accepts POST requests and returns a JSON with WEBSERVICE and FILTERXML like:

=FILTERXML(WEBSERVICE("https://xxxxxxxxxx.execute-api.ap-northeast-1.amazonaws.com/prod/passthru?reg_no=000006"),"//result/reg_date")