Jump to content
Sign in to follow this  

Simplest way to display local IP JSON data as a spreadsheet / html page on my PC?

Recommended Posts

So some major progress - I now have 1) admin user id and password - and have been shown how to use Excel 2013 with PowerQuery add on - which reads javascript really well!  I can now query the old or classic views of my device - with the admin user id, or the new views using PowerQuery and if the URL is not protected all is fine.

My Dilemma

If in Excel I simply select Data -> From Web and supply the URL User Id and Password – it works fine – but it returns javascript which Excel simply can't natively ingest.

If in Excel I select Powerquery -> from Web, it asks basic or advanced. If I chose basic – it only asks for the url and this fails. If I chose Advanced – I don't know how to format the query to supply the user id and password. Which is annoying given Powerquery natively does support Javascript and easily converts it into readable tables!

If in Powerquery I try and reach say http://envoy:011842@ the call will fail and bring up web access – where I can choose API, Simple or web access – and for simple or advanced I can add user id and password – but the call simply hangs.

Does anyone know how to use Excel 2013 with PowerQuery on a web access call to supply the user id and password?

Many thanks, Matthew


Share this post

Link to post
Share on other sites

So I tried a new approach with a lot more more success. I created a Execl file with two worksheets.  In the first worksheet I wrote a normal Data -> web query that polls the website, successfully passes the credentials – grabs the inverter JSON results and simply writes it to a local file called inverters.json. Then in another worksheet write a power builder query to read this inverters.json file and extract the inverter data – scheduled to run every minute. This worked well!

The only challenge I have now is storing the first procedure call and setting its properties to run every minute. Excel doesn't seem to want to store this call if Excel isn't trying to do something with the results. Any suggestion guys?

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this