hi
Need help with the following; it is clearly beyond me !
I have an API for openweather.org
I can Paste the API info into a Browser and get a response back that looks like this;
Webinfo
{"cod":"200","message":0.1607,"cnt":39,"list":[{"dt":1498705200,"main":{"temp":288.19,"temp_min":286.354,"temp_max":288.19,"pressure":1011.62,"sea_level":1033.45,"grnd_level":1011.62,"humidity":78,"temp_kf":1.83},"weather":[{"id":802,"main":"Clouds","description":"scattered clouds","icon":"03d"}],"clouds":{"all":32},"wind":{"speed":1.56,"deg":251.5},"rain":{},"sys":{"pod":"d"},"dt_txt":"2017-06-29 03:00:00"},{"dt":1498716000,"main":{"temp":287.01,"temp_min":285.784,"temp_max":287.01,"pressure":1010.91,"sea_level":1032.71,"grnd_level":1010.91,"humidity":80,"temp_kf":1.22},"weather":[{"id":800,"main":"Clear","description":"clear sky","icon":"01d"}],"clouds":{"all":0},"wind":{"speed":1.21,"deg":258.501},"rain":{},"sys":{"pod":"d"},"dt_txt":"2017-06-29 06:00:00"},{"dt":1498726800,"main":{"temp":283.73,"temp_min":283.123,"temp_max":283.73,"pressure":1011.99,"sea_level":1033.88,"grnd_level":1011.99,"humidity":85,"temp_kf":0.61},"weather":[{"id":800,"main":"Clear","description":"clear sky","icon":"01n"}],"clouds":{"all":0},"wind":{"speed":3.31,"deg":269.502},"rain":{},"sys":{"pod":"n"},"dt_txt":"2017-06-29 09:00:00"},{"dt":1498737600,"main": etc etc
in Excel I have B1=API Key B2=Location C4=WEBSERVICE("http://api.openweathermap.org/data/2.5/forecast?id="&LOCATION&"&APPID="&API)
Which displays the information as seen below Webinfo
I have googled and and found that I need to use the filterxml to filter what info I want to display ( which is where I have the problem )
I can not work it out.
Basics are I want to lookup a cell that contains a Date i.e Cell A10 has a date of 28/06/2017 and return the temp from the filterxml for that day.
I found that the date data is listed as " dt " which is utc time so I have worked out that to convert this to date format I needed to (( example I have 1498694400 in cell O13 ))
=(((O13/60)/60)/24)+DATE(1970,1,1) which then allows me to see the normal date format 29/06/2017
Ideally I will have a spreadsheet with information running down the page with dates I am after a way to display the temp for the given date.
HELP
Anthony
Need help with the following; it is clearly beyond me !
I have an API for openweather.org
I can Paste the API info into a Browser and get a response back that looks like this;
Webinfo
{"cod":"200","message":0.1607,"cnt":39,"list":[{"dt":1498705200,"main":{"temp":288.19,"temp_min":286.354,"temp_max":288.19,"pressure":1011.62,"sea_level":1033.45,"grnd_level":1011.62,"humidity":78,"temp_kf":1.83},"weather":[{"id":802,"main":"Clouds","description":"scattered clouds","icon":"03d"}],"clouds":{"all":32},"wind":{"speed":1.56,"deg":251.5},"rain":{},"sys":{"pod":"d"},"dt_txt":"2017-06-29 03:00:00"},{"dt":1498716000,"main":{"temp":287.01,"temp_min":285.784,"temp_max":287.01,"pressure":1010.91,"sea_level":1032.71,"grnd_level":1010.91,"humidity":80,"temp_kf":1.22},"weather":[{"id":800,"main":"Clear","description":"clear sky","icon":"01d"}],"clouds":{"all":0},"wind":{"speed":1.21,"deg":258.501},"rain":{},"sys":{"pod":"d"},"dt_txt":"2017-06-29 06:00:00"},{"dt":1498726800,"main":{"temp":283.73,"temp_min":283.123,"temp_max":283.73,"pressure":1011.99,"sea_level":1033.88,"grnd_level":1011.99,"humidity":85,"temp_kf":0.61},"weather":[{"id":800,"main":"Clear","description":"clear sky","icon":"01n"}],"clouds":{"all":0},"wind":{"speed":3.31,"deg":269.502},"rain":{},"sys":{"pod":"n"},"dt_txt":"2017-06-29 09:00:00"},{"dt":1498737600,"main": etc etc
in Excel I have B1=API Key B2=Location C4=WEBSERVICE("http://api.openweathermap.org/data/2.5/forecast?id="&LOCATION&"&APPID="&API)
Which displays the information as seen below Webinfo
I have googled and and found that I need to use the filterxml to filter what info I want to display ( which is where I have the problem )
I can not work it out.
Basics are I want to lookup a cell that contains a Date i.e Cell A10 has a date of 28/06/2017 and return the temp from the filterxml for that day.
I found that the date data is listed as " dt " which is utc time so I have worked out that to convert this to date format I needed to (( example I have 1498694400 in cell O13 ))
=(((O13/60)/60)/24)+DATE(1970,1,1) which then allows me to see the normal date format 29/06/2017
Ideally I will have a spreadsheet with information running down the page with dates I am after a way to display the temp for the given date.
HELP
Anthony
