Hacker News Reader

Comment by Shorel | Hacker News Reader

I am waiting for the day I can use Excel as a REST API client, so I write the ID of some record in a cell, and Excel does all the work of calling the API repeatedly and fills the rows and sheets with the required information.

This will kill a few Python jobs and make it a very popular REST client =)

View on HN

Can you do this with the =WEBSERVICE() function? https://support.microsoft.com/en-us/office/webservice-functi...

Can I parse response (JSON, XML, whatever) into cells without VBA?

With some effort, probably: =FILTERXML() https://support.microsoft.com/en-us/office/filterxml-functio...

Could also do some crude/fragile parsing with aforementioned regex functions.

I've actually done that. I needed to create a lot of json documents and POST them somewhere (it was metadata for some Chef cookbooks), and a lot of the information was similar with a few variations. Excel lets me create what I call a "conductor's score" of that information. (In music, the conductor's score has all the instruments on one page. Each row is a different instrument, and each column is the same measure [point in time] in the music.) In other words, I can easily look for commonalities, differences, and presence of the data as I update it.

I was just going to copy/paste the fields into JSON which is repetitive and error prone, and realized I could automate it. So I wrote VBA to output a json version of that. (The annoying part of writing that in VBA is that JSON data needed a lot of double-quotes, but then you have to use escape sequences for every double-quote.)

Once I had the JSON data generated, I was going to post each one manually, so again, I looked and realized you can POST the data directly from VBA. Added a button to the page and you could update the data in Excel and click a button to POST it.

Of course, once I turned it over to someone else, they were like "what the hell" and started doing it a different way. lol

Knock yourself out!

https://stackoverflow.com/a/158657/969613

I've done this in Google Sheets, with "Google Apps Script", the most horridly named thing I think I've ever worked with.

It's just (utterly ancient) Javascript (I think ECMAScript, technically, since there's no DOM, no browser, etc.), bolted on. I wrote in modern JS and used Babel to transpile the source back into the stone age.

… I'd much rather write Python.

Until they add python into excel, then there will be python jobs again :P

https://support.microsoft.com/en-gb/office/get-started-with-...

Every self-respecting corporate org will block execution in the cloud + no IDE (you edit the code inside the cells), its dead on arrival

How about Excel as an API server, too?

You've been able to do that for ages, a client automating Excel through COM and listening for events in Excel from script: https://news.ycombinator.com/item?id=37229850

Microsoft's advice page for wrapping Office in a web application fronted by ASP/ASP.Net: https://support.microsoft.com/en-us/topic/considerations-for...

And that has links to documentation about Excel Web Services of old (on-premises SharePoint days, not sure if it translates to today's cloud), which is SOAP/HTTP: https://learn.microsoft.com/en-us/sharepoint/dev/general-dev...

The "new hotness" for today's cloud are all the Excel APIs in the Microsoft Graph:

https://learn.microsoft.com/en-us/graph/api/resources/excel?...

MS has had ODBC integration for ages. That is a more rational way to connect Excel with data sources.

I'm getting closer! https://github.com/ironcalc/IronCalc

Reply on news.ycombinator.com