can someone point me in the right direction?

I’ve got a basic API call going in Google Scripts. I can use it to get data, process the data, and then output it into a Google Sheets spreadsheet, scheduled with a trigger. All nice.

However, due to GDPR concerns, I need to be able to do this without involving Google, either on my machine (a Mac) or on work Sharepoint or something. (Excel Mac doesn’t have PowerQuery). I don’t know where to start, or what apps to use / look for. I’ve got Postman, although I’m not entirely sure what it’s for. I’ve heard of ‘curl’ although I’m not sure whether it’s an app, a verb, or something else.

I am a complete noob here. Any tutorials or useful sources of info would be massively appreciated, because at the moment I’m just staring at stackoverflow trying to make sense of techy jargon that is way way above my knowledge level!

Can I check I’ve understood this correctly. You’ve written a script, that uses an API to query something (an Excel spreadsheet?) that outputs the results to a Google Sheet? What are you querying that will help someone to answer.

If all you need is a (non-google) script to make an API call and then do something with the data then python is probably your best bet, but if you’ve never used it before it’ll require some reading. If I’ve understood correctly, you’ll neeed to create a python script which calls the API with the requests library (second link below), then process the data and write it to an excel spreadsheet with the pandas library (pandas is a bit of a beast, so use stackoverflow searches to find code snippets to do what you need).

https://www.python.org/about/gettingstarted/

https://www.nylas.com/blog/use-python-requests-module-rest-apis/

https://www.dataquest.io/blog/excel-and-pandas/

Postman is a tool you can use to make http (or other) requests to APIs. Mostly used for testing. CURL is a command line utility which allows you to execute requests to outside resources from within a shell script.

Another common tool/platform is node.js but that’s even more involved than python and probably not as suited to working with spreadsheets.

Can I check I’ve understood this correctly. You’ve written a script, that uses an API to query something (an Excel spreadsheet?) that outputs the results to a Google Sheet? What are you querying that will help someone to answer.

Yep. It queries the API of an email marketing service. I’m trying to make a results dashboard. So the requests are ‘get data for campaign XYZ’ and the response is like ‘total sends: 100, total opens: 34, unique opens: 23,’, etc etc etc.

So I want to put that into Excel (or similar) so we can make it nice and readable.

edit – the reason I want to stop using Google is that for more complex data, the API starts returning Personally Identifiable Information which I don’t really want or need, and doubt that our legal people would look kindly on my processing it in something like Google Apps

If all you need is a (non-google) script to make an API call and then do something with the data then python is probably your best bet, but if you’ve never used it before it’ll require some reading.

ah damnit, I’ve just spent about 3 weeks on the Codeacademy Javascript course to get to this point 😆

Power query has been rebranded as get and transform in most recent versions of excel. Do a search for that in your version. Also check the plug ins : options and make sure it is enabled.

What does the api return? JSON, XML? If the request just uses a url then you may be able to just connect it to the source under the data tab.

I would be very surprised, if you can’t connect to a api / data source in excel.

Just read your update. Download power bi desktop. Sounds like it will do what you want as well.

ah damnit, I’ve just spent about 3 weeks on the Codeacademy Javascript course to get to this point

Have a look at node.js then. I was assuming you didn’t know javascript as it can be a bit more of a learning curve than python (IMO).

https://rapidapi.com/blog/how-to-use-an-api-with-node-js/

https://www.twilio.com/blog/2017/08/http-requests-in-node-js.html

node is more focused on web apps than python but you can still use it get data from APIs and then process it. Really though if you’re not doing any web stuff python is probably best. Don’t worry about having to learn it. Once you understand how to create and execute scripts you can literally copy and paste code snippets from stackoverflow. Just be aware of the ‘pythonic’ syntax and how it’s different from javascript for simple stuff like if/else statements and looping etc.

@kellies heroes – thanks, but PowerBI desktop is Windows only, and as far as I can see, Get & Transform is only partially implemented on a Mac, so you can run existing queries but can’t write your own!

I think I’ll go for node.js (hopefully js might come in useful elsewhere in my job), export to an excel file on onedrive, and import that to PowerBI online so everyone can view it. What could go wrong?

 and import that to PowerBI online so everyone can view it

The problem with PowerBI online is that you need a licence for everyone that is going to view it which can get pricey 🙁

If you are on Office365 you might be able to do it using powerapps but I’ve never tried it.  I find it very difficult to stay up with the latest techniques for pulling data before they become obsolete.

https://powerapps.microsoft.com/hr-hr/blog/register-and-use-custom-apis-in-powerapps/

If you enjoy pain you might be able to read the API in VBA in Excel.  I haven’t done it for a while but there are a bunch of libraries in github that help with that pain

https://github.com/VBA-tools/VBA-Web

obviously it’s not as easy as python but it does mean that you keep it all in excel

There’s tons of ways to make API calls from node but this is a popular package in the web world.. https://github.com/axios/axios

Have a look at this npm package to write to excel. https://www.npmjs.com/package/xlsx

and watch this…

ah damnit, I’ve just spent about 3 weeks on the Codeacademy Javascript course to get to this point

You’ll probably find python a breeze then! Python comes ready installed on your mac, so is very easy to do this with. Your email tool may even have a python API library you can install from pip.

As an alternative:

edit – the reason I want to stop using Google is that for more complex data, the API starts returning Personally Identifiable Information which I don’t really want or need, and doubt that our legal people would look kindly on my processing it in something like Google Apps

Can the API request be configured to the response doesn’t have Personal Data?
I’m not actually sure that running the API in Google is any worse than running it on your local machine, although I realise your “legal people” are probably unaware of what you might do locally anyway!

Postman has the ability to turn the requests you make in it, in to code, python, javascript and a whole bunch of other languages.

My suggestion would be to get it to work i Postman first, then use their code to start you off on what you need to do