Visualizing data from ThingSpeak REST API

In this example, you’ll learn how to visualize data from ThingSpeak API.

For demo purposes, the traffic monitor public channel (Channel ID : 38629) is being used. ThingSpeak APIs are available in different formats such as JSON, CSV, and XML, and we are going to see how to use all of those different API formats.

JSON Example

As you can see in the picture below, we’re using the JSON feed URL of the channel. According to this JSON schema, the data points are under the node called feeds, so we’ll specify the same as our Rows/Root selector. Then for the columns, we need to first specify our time column which is created_at field in our JSON feed. Then, we can specify one or more metrics (number) fields as shown in the picture below.

CSV Example

If you prefer CSV format over JSON, here is the example settings of the same channel. We are using the CSV feed URL of the channel. In the query, choose CSV as your type, specify the CSV feed URL and then, the fields you needed along with time field.

XML Example

You are not limited to CSV or JSON. You can also use XML format as well in your query if your prefer that. We are using the XML feed URL of the channel. The query setup is almost same as for CSV/JSON. Notable configurations are your rows/root field and timestamp field. As shown in the picture below, we are using channel.feeds[0].feed as root/rows selector and then for selecting timestamp, we use created-at[0]._

Using Grafana’s time filter

After you set up the query, you may find that your are always querying recent data in the graphs, regardless of the time range chosen in Grafana. To solve this issue, you need to pass the Grafana’s dynamic time range in the URL. As per the ThingSpeak API specification, you should to pass the start and end time to the URL as query string params. To do that, append ?start=${__from:date:YYYY-MM-DD HH:NN:SS}&end=${__to:date:YYYY-MM-DD HH:NN:SS} to the URL. You can use any variable as specified in the grafana doc

The full URL now becomes https://thingspeak.com/channels/38629/feed.json?start=${__from:date:YYYY-MM-DD HH:NN:SS}&end=${__to:date:YYYY-MM-DD HH:NN:SS}.

Long time range

Typically you can retrieve all the metrics without any aggregation for up to 2 hours. If you are looking for a longer time range, you may need to pass an aggregation filter to your URL. Here in the below example, we are looking for data of the last 180 days, so we are pulling the aggregated data using the URL https://thingspeak.com/channels/38629/feed.json?start=${__from:date:YYYY-MM-DD HH:NN:SS}&end=${__to:date:YYYY-MM-DD HH:NN:SS}&average=1440. An important thing to note here is average=1440, where it specifies to take average of each 1440 minutes/1day. You can specify more granular time aggregations. As per the document, 10, 15, 20, 30, 60, 240, 720, 1440 are the valid time range in minutes. Also, instead average you can use sum/timescale/median functions.