【Publisher】Query to YouTube API

This time, let's create a report that displays a YouTube playlist with [Sharperlight](https://www.sharperlight.com/) Publisher.
It is assumed that you have already obtained a Google API Key. And keep this API enabled.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/05908e0d-a1b5-ec8a-254d-a11dc3770274.png)
## Query Details ##
There is a query that was created in advance, that is, a query that retrieves playlist details from the YouTube API, so I will explain it.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/fc3587bd-bded-81b4-1c31-29f2fa8dd4ad.png)
### Filters ###
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7e6c6f7b-c51e-5084-3c83-b10359df0225.png)
**1 Product：** Use `System`. You can use a variety of Sharperlight built-in functionalities with this.
**2 Table：** Choose `Web Page Table, CSV, JSON, XML or OData`
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/61caf8a2-66c1-0194-eece-eb14f23a67fc.png)
**3 Google API KEY：** Add as a prompt filter. (A prompt filter is a filter that doesn't go to the WHERE clause of a query, and can be used for passing values, etc.)
**4 Playlist ID：** The ID of the YouTube playlist you want to query for information. Add this as a prompt filter as well.
**5 URL Link：** Specify the API endpoint URL for getting playlist information.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/29ccd646-b5dd-b4f2-52a3-1f0e0775e1fb.png)
```
https://www.googleapis.com/youtube/v3/playlistItems?part=snippet&maxResults=50&playlistId={@PlayListID}&key={@APIKEY}
```
**6 Start Position：** Specify record array name of JSON data returned from API.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/eb29c39a-a5df-8e3e-f543-b53c166cfd02.png)
### Outputs ###
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e263981e-366d-958b-25c9-346ec6738403.png)
**1 Title：** An expression. Extract the `title` value from the JSON data `snippet`.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6ee8bb30-7f6c-b77a-c581-f3132304b9ab.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b1b7fb23-9de3-318b-523b-6b3215fabd97.png)
**2 Item No：** An expression. Take the value of `position` from the JSON data `snippet` and add 1.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d158c222-8936-f8be-82db-d9b829059b41.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3b6b6457-5c3d-5575-805a-eee156e70d62.png)
**3 Description：** An expression. Extract the `description` value from the JSON data `snippet`.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f7987a09-f4de-8faa-60b1-4cdefec0e10a.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/af1611e8-797e-d987-ff57-3b873d1d1638.png)
**4 Video URL：** An expression. Hidden. Create a URL to watch each video.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/54fa3160-260c-6a60-77ef-35bf7fefdef5.png)
**5 Image URL：** An expression. Hidden. Create a URL to get thumbnail for each video.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/08b7b02f-19fc-1e0e-b9bf-3328a77b156a.png)
**6 Video：** An expression. Create HTML links to each video. Use the URL created in **4** and **5** above. This is the reason why `Vedio URL` and `Image URL` are hidden.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5f2c92a9-9a2a-1a14-2a4f-5bbea2fbff55.png)
**7 Posted Date：** An expression. Shows the date and time each video was published. Converting to Date type.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0c6e8dbc-a3a0-9074-f6ef-fca4d89881a3.png)
**8 snippet：** The target part of the returned JSON data to be read. There is no reason to be visible so it should be hidden.
## Executing Query (web report) ##
Save the query and fill in the Web report group name, code and report title etc. and save.
After confirming that the Sharperlight service has started, view the report in a browser by selecting `View Report` from the right-click menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/57b0e100-9ddd-3a04-eb43-5f8c61cbeec1.png)
You will see a report like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/791c57a1-7e70-9321-4a09-2b5a34cd8fb1.png)
You can browse YouTube videos by clicking on the video thumbnails.

Thank you for reading this article.