Need4Script - Zapier Integration with XML data

[kngzeng]

Hi everyone,

 

I'm having trouble parsing data from XML files. My objective is to extract data in a usable way for Zapier to work with. I need to find a way to automate the extraction of the data of the XML file and import it to Google Spreadsheet. 

 

The easiest method of doing this is using the function "=IMPORTXML" that is integrated with Spreadsheet, however the site I'm extracting the data from is user and password protected, so Spreadsheet can't extract the data because it sees nothing, just a message error indicating that a username and password is needed.

 

Image 1. Error message when no username and password is provided...

 

So what I'm doing right now for parsing the data, is:

1. Manually download the XML file from the website
2. Manually upload the XML file to a XML to Excel online converter
3. Manually open the Excel file
4. Manually copy the data from the Excel to Google Spreadsheet
5. Activate the Zap using Zap's Push Button chrome extension.

I want to find a way to elimate the "Manually" part of the process, because when doing this with 20-30 files daily is very tedious and a waste of time...

 

So I need help finding the perfect workflow for this, some ideas I have in mind are to a) host the XML file in a personal web server (don't know how to do this, I just know that it could be done), b) learn how to send the data either via Zapier's API, webhook, etc... c) upload the files to a FTP server and configure Spreadsheet to access the file from the FTP server (I tried but it doesn't work...) d) create a little script of program that could automatically extract the data (again, dont know how to do this) aaand... that's it, I have no other idea to do this...

 

Any help is greatly appreciated  I'm attaching the screenshot of an example XML file, if needed I can upload the example XML file.

 

-----------------------------------------------------------------------------------------------------------