When the query editor opens, we can see that Excel has recognized the output as JSON and applied the JSON.Document parsing to the result. We can go with the default settings in the next pop up so just press the Connect button. We only need to use the Basic query so you can pop your URL into the field and press the OK button. This can also be found under Get Data in the From Other Sources menu. Go to the Data tab in the ribbon and select From Web under the Get & Transform Data section. Now that we’ve seen the API in use from the browser and have created a fancier URL to call, let’s use a From Web power query to call it in Excel. limit=5 This parameter will limit the results to the 5 nearest.Ĭombining all these parameters into the proper API request URL format results in the above URL.country=CA This means the station is in Canada.fuel_type=ELEC This means the station provides electric fuel.access=public This means the station is open to the public.status=E This means the station is open and carries alternative fuel.location=Toronto+ON This means station is located near Toronto Ontario.For this, I’ll use the following parameters and values. Just for fun, I’m going to get a bit fancy with some of the parameters available in the API to find 5 electric fuel stations that are currently open to the public and nearest to Toronto, Ontario in Canada. We can easily turn something like this into a more familiar and readable table in just a few clicks, so we won’t need to try and decipher our API’s output on our own. The cool thing is Power Query has JSON parsing functionality built in.
Excel for mac web query returned no data code#
The address contains a street name, number and postal code and the email contains a work and personal email. The address and email objects themselves are also arrays of objects. This object is actually an array of 3 objects (name, address and email). "Work": is a simple example of a JSON data object for a person’s contact information. JSON stands for JavaScript Object Notation and it’s a syntax for storing and exchanging hierarchical data. The output of this API is in a JSON format. The output might not be that readable to you if you’ve never seen JSON before.
We’ll try some of these out later.Īfter submitting this request URL it will return a whole bunch of data. All the possible parameters are nicely documented on the government website. In fact, you can pass many other parameters to the API by appending them to the URL in a similar fashion. This means the API will return a list of stations near Denver Colorado. Notice the &location=Denver+CO at the end of the URL. Copy this URL from the email and paste it into the address bar of any browser (I’m using Chrome) then press enter. Let’s try out our example web request from the email with our API key.
0 kommentar(er)
