Alexande I do not if you could share that solution I would be greatly appreciative.
Replying with my other account. This may not work for you as the list of Lat/Long has to sit in the Google Sheet, but I wanted to get it out there anyway. Equally, the number of calls you need to make will result in a charge (of less than a dollar) if you try this in one day (but so would a WDC). But it may be a start..
- First, you'll need an API key from Darksky. No need to enter CC info unless you need to call the API over 1,000 times a day. (Dark Sky)
- And a Google Sheets account (but who doesn't have one of these..)
- Add the ImportJSON script into your sheet (GitHub - bradjasper/ImportJSON: Import JSON into Google Sheets, this library adds various ImportJSON functions to your s…)
- Get Lat/Long for each location
- The API call is different depending if you're looking for historical/forecast and then with forecast you can choose to not see hourly/minutes/alerts and some other things. It's important to have the 'noInherit' option set for ImportJSON. If you don't then subsequent days without that metric (e.g. no rain amounts) will get data pulled through from a prior day
- For example, =importJSON('https://api.darksky.net/forecast/yourAPIkeygoeshere/26.42,-66.99',,"noInherit") will give a forecast for that location with multiple columns for a forecast for the next 60 minutes, hourly for the next 48 hours, and daily for the next 7 days. A single row can have minute, hourly, daily, or a combination in different columns. Placement and existence of columns is not guaranteed and depends on the type of weather that may be forecast for the hour/to days/week
- If you're after historical data you probably wouldn't want to use a WDC as there's no need to get the data more than once. You need the date in UNIX. I'm polling the API everyday and looking back to yesterday, so here's an equation to get yesterday's date: =86400*((today()-A2-DATE(1970,1,1)))+36000 I'm adding 10 hours to the date to make sure I don't run into time zone issues - the API documentation describes this nicely
- API call: https://api.darksky.net/forecast/yourAPIkeygoeshere/26.16,-56.28,1540461600?&exclude=currently,flags,hourly - this returns a header and single row for the daily data
- Here's the kicker - with this methodology, you cannot just have row upon row of API calls - firstly a header is returned and even if you edited the importJSON to remove it, there's no guarantee that data will be in the same column from call to call.
- To deal with this, I stagger my API calls every two rows on a staging sheet, then use another sheet with a set of hlookups to pull data into the right row based on the header (see sheet link below)
- I also have another piece of code that moves cleaned rows onto another sheet to build up a history
- Then I have a trigger that runs each morning to get yesterday's data, and another that runs afterwards to move new data to the history sheet
So, convoluted, but it works. You'll need an API key, but here's a public version of the sheet: Tableau Weather Historical - Google Sheets
EDIT: Added 'noInherit' requirement that was causing a bug for duplicated weather info for subsequent days
You will also have to be careful about exceeding the max cell count of 2m per sheet
Thanks Alex, I'll give it a shot. I'm able to do what I want in postman by having a CSV file with the field I want to pass and the url looks something like this:
Postman iterates over this fine but I need to understand how this works and apply it to Tableau WDC. I'll check your solution and see if I can get that working as well.
Thank you again.