External data from dynamic URL?

bwv999

New Member
Hi everyone!

I've been trying to establish a data connection to an online JSON file. This seems to work just fine, but part of the URL is a date, like so:

https://www.sampleurl.com/api/file.json?id=25&start=2019-01-01&limit=2500

I'd like to be able to have that date automatically be today's date (so as a variable), so that all the data that is loaded in is relevant to me. Anything in the past is not...

I've been trying to get this to work using parameters, but so far no luck as I'm pretty new to working with them. Is this even possible? All the information I've found so far have been using other Excel versions that don't seem to match with mine, I'm using Office 365 ProPlus (version 1903). If at all possible I'd like to avoid using VBA or add-ins.

Thanks in advance for any help, I appreciate it!
 

CorporateTramp

New Member
Url = "https://www.sampleurl.com/api/file.json?id=25&start=" & Year(Now()) & "-" & Right("0" & Month(Now()), 2) & "-" & Day(Now()) & "&limit=2500"
 

bwv999

New Member
Url = "https://www.sampleurl.com/api/file.json?id=25&start=" & TEXT(Now(),"yyyy-mm-dd") & "&limit=2500"
Thanks! This seems to be a good way to go about it! However, I'm still running into a bit of an issue... When I use this formula to retrieve the data, it gives me records from a couple of years ago. But when I manually enter the current date, I do get the correct data. Any idea what might cause this? Technically the url that's being called should be exactly the same, right? My computer's date and time are also set correctly.
 

Some videos you may like

This Week's Hot Topics

Top