Convert WebAPI in JSON format to Excel format ★

Converts response data from websites that return JSON format into Excel format (plain text) and returns it.
This allows various WebAPI to be used with WEBSERVICE functions.

URL

https://api.excelapi.org/convert/json2plain

Configuration

Configuration	Required	Description
url	✔	Specify the url to access. It must be a url that returns a response in json format.
target	✔	Specifies which part of the responded json is to be returned. Please refer to the following for details as it is a proprietary format.
cache		Specifies whether cache is used or not. When using cache, data is stored on this site and the cache is returned the second time and thereafter. This is an overload countermeasure to the provider site. When use: yes (default) When not used: no
timeout		Specify the expiration time in seconds when the cache is used. If not specified, it is 86400 seconds (1 day).

How to specify target

JSON is a format in which data is represented by "key : value.
The common way to specify data is to separate them with brackets ("["name"]"). When specifying target with this function, key is separated by ". (period)" to specify the target.
As a result, the value corresponding to the key can be obtained.

The following is an example of specifying target.

Sample 1

JSON:

{
    "id": 1,
    "name": "tanaka",
    "attribute": {
        "gender": "male",
        "phone_number": "xxxxxxxxxxx",
        "birth": "1991/01/01"
    }
}

Example1: Simple
First, the simplest way to specify a target is to specify a name.
Specify a name, and the corresponding tanaka will be returned.

name　　　→  tanaka

Example2： Object
Then the next object {...} in the hierarchy then the next key in the hierarchy.

attribute.gender　　　→  male

Sample2

JSON：

{ status: 'OK',
  data:
   [ { id: '01100', name: '札幌市' },
     { id: '01101', name: '中央区' },
     { id: '01102', name: '北区' },
     ....
   ]
}

Example3: Array
If value for key is array[...] element number is specified.
In this case, specify 0 to extract the first element "{ id: '01100', name: 'Sapporo city' }".

data.0　　　→  { id: '01100', name: '札幌市' }

Example4: Value in array
You can get "Sapporo City" by specifying name for the first array.

data.0.name　　　→  札幌市

tip

If you remember that brackets [] specify numbers (e.g., 0) and curly braces {} specify characters (e.g., name), it will be easier to deal with.

Example

A2:

https://www.land.mlit.go.jp/webland/api/CitySearch?area=01

※ The URL entered in column A is the "API for obtaining a list of cities, wards, towns, and villages within a prefecture" provided by the Ministry of Land, Infrastructure, Transport and Tourism.
https://www.land.mlit.go.jp/webland/api.html

C2:

=WEBSERVICE("https://api.excelapi.org/convert/json2plain?url="&ENCODEURL($A2)&"&target="&ENCODEURL($B2))

caution

This function allows this site to retrieve data once from the provider site, edit the data, and return it to the user.
Therefore, since all communications are concentrated on this site, this site may be subject to communication restrictions from the provider site in the event of a large volume of accesses.
Since this site cannot cancel the communication restrictions of the provider site, please adjust the amount of access in accordance with the rules of the provider site.