4 ms·

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 repeat

by Shorel 2y ago

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 =)

actionfromafar 2y ago
How about Excel as an API server, too?
- jodrellblank 2y ago
  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 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-server-side-automation-of-office-48bcfe93-8a89-47f1-0bce-017433ad79e2 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-development/walkthrough-developing-a-custom-application-using-excel-web-services?redirectedfrom=MSDN https://learn.microsoft.com/en-us/sharepoint/dev/general-dev...
  - WorldMaker 2y ago
    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?view=graph-rest-1.0 https://learn.microsoft.com/en-us/graph/api/resources/excel?...
- kevin_thibedeau 2y ago
  MS has had ODBC integration for ages. That is a more rational way to connect Excel with data sources.
- nhatcher 2y ago
  I'm getting closer! https://github.com/ironcalc/IronCalc https://github.com/ironcalc/IronCalc
xnx 2y ago
Can you do this with the =WEBSERVICE() function? https://support.microsoft.com/en-us/office/webservice-function-0546a35a-ecc6-4739-aed7-c0b7ce1562c4 https://support.microsoft.com/en-us/office/webservice-functi...
- deleted 2y ago
  [deleted]
- ComodoHacker 2y ago
  Can I parse response (JSON, XML, whatever) into cells without VBA?
  - xnx 2y ago
    With some effort, probably: =FILTERXML() https://support.microsoft.com/en-us/office/filterxml-function-4df72efc-11ec-4951-86f5-c1374812f5b7 https://support.microsoft.com/en-us/office/filterxml-functio... Could also do some crude/fragile parsing with aforementioned regex functions.
  - deleted 2y ago
    [deleted]
jmkni 2y ago
Knock yourself out! https://stackoverflow.com/a/158657/969613 https://stackoverflow.com/a/158657/969613
voidUpdate 2y ago
Until they add python into excel, then there will be python jobs again :P https://support.microsoft.com/en-gb/office/get-started-with-python-in-excel-a33fbcbe-065b-41d3-82cf-23d05397f53d https://support.microsoft.com/en-gb/office/get-started-with-...
- personalityson 2y ago
  Every self-respecting corporate org will block execution in the cloud + no IDE (you edit the code inside the cells), its dead on arrival
deleted 2y ago
[deleted]
deleted 2y ago
[deleted]
drivers99 2y ago
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
deathanatos 2y ago
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.

by Shorel 2y ago

actionfromafar 2y ago

How about Excel as an API server, too?

jodrellblank 2y ago
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 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-server-side-automation-of-office-48bcfe93-8a89-47f1-0bce-017433ad79e2 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-development/walkthrough-developing-a-custom-application-using-excel-web-services?redirectedfrom=MSDN https://learn.microsoft.com/en-us/sharepoint/dev/general-dev...
- WorldMaker 2y ago
  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?view=graph-rest-1.0 https://learn.microsoft.com/en-us/graph/api/resources/excel?...
kevin_thibedeau 2y ago
MS has had ODBC integration for ages. That is a more rational way to connect Excel with data sources.
nhatcher 2y ago
I'm getting closer! https://github.com/ironcalc/IronCalc https://github.com/ironcalc/IronCalc

xnx 2y ago

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

deleted 2y ago
[deleted]
ComodoHacker 2y ago
Can I parse response (JSON, XML, whatever) into cells without VBA?
- xnx 2y ago
  With some effort, probably: =FILTERXML() https://support.microsoft.com/en-us/office/filterxml-function-4df72efc-11ec-4951-86f5-c1374812f5b7 https://support.microsoft.com/en-us/office/filterxml-functio... Could also do some crude/fragile parsing with aforementioned regex functions.
- deleted 2y ago
  [deleted]

jmkni 2y ago

Knock yourself out! https://stackoverflow.com/a/158657/969613 https://stackoverflow.com/a/158657/969613

voidUpdate 2y ago

Until they add python into excel, then there will be python jobs again :P https://support.microsoft.com/en-gb/office/get-started-with-python-in-excel-a33fbcbe-065b-41d3-82cf-23d05397f53d https://support.microsoft.com/en-gb/office/get-started-with-...

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

deleted 2y ago

[deleted]

deleted 2y ago

[deleted]

drivers99 2y ago

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

deathanatos 2y ago

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.