0

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?

SATO Yusuke
  • 1,600
  • 15
  • 39
  • Excel does not have built-in capability to extract a JSON element. There are VBA routines but that may not be appropriate for your use. You may be able to write string functions. Also, you may run into problems with `WEBSERVICE` if the length of the returned string is greater than `32,767` (maximum length of a formula). – Ron Rosenfeld Dec 11 '21 at 15:32
  • [This answer](https://stackoverflow.com/a/52903455/8112776) is also using a `GET` but it can be easily changed to `POST`, like in [this answer](https://stackoverflow.com/a/21675040/8112776). There are also several existing questions/amswers about handling JSON in VBA, such as [this](https://stackoverflow.com/q/6627652/8112776) and [this](https://stackoverflow.com/q/2782076/8112776). – ashleedawg Dec 11 '21 at 15:37
  • @ashleedawg Wouldn't that link require the XMLHTTP module? – Ron Rosenfeld Dec 11 '21 at 15:41
  • Yeah; it's built in, and a reference is easily added. I use it often. (Did I miss something?) Another option is [Power Query](https://support.microsoft.com/office/import-data-from-data-sources-power-query-be4330b3-5356-486c-a168-b68e9e616f5a) via Excel – ashleedawg Dec 11 '21 at 15:45
  • @ashleedawg The OP wrote "*I want to avoid using platform-specific functionality like a script with the XMLHTTP60 module.*" – Ron Rosenfeld Dec 12 '21 at 00:10
  • Thank you for your comments, but `MSXML2.XMLHTTP` doesn't work in the Linux+LibreOffice environment, so I am struggling to find some workaround. – SATO Yusuke Dec 12 '21 at 14:37

1 Answers1

0

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")
SATO Yusuke
  • 1,600
  • 15
  • 39