Google Sheet to XML

[christo1993]

Hi there,
I have a little question for you. Hopefully you can help me with my issue.
I have a Google Sheet witch looks like the one in the attached pictures

I would like to convert the Sheet to an XML file and upload it on OneDrive.
Of course no problem with the XML Converter. But unfortunately the Converter gives me 23 single files (because of the 23 rows). Is it possible to get all entries in one single XML file?

Looking forward for your help.

Best Christoph

[Donald_Mitchell]

Hello Christoph,

The aggregator is what you need to combine bundles.

After getting the rows from Google Sheets, you could use Aggregate to JSON, followed by Convert JSON to XML. Both of these modules are JSON modules.

[Darzk777]

Hi @christo1993,

The PDFco API can help you convert a Google Sheet or Excel file into XML format. This conversion tool allows you to transfer your data from the spreadsheet to XML. If you need more guidance on how to use this tool, you can check out the full tutorial at this link: https://pdf.co/convert-google-sheet-to-xml-and-upload-to-onedrive-make

If you have any questions or need assistance, feel free to contact our support team at support@bytescout.com. We’re eager to help you with the conversion process.

Have a great day!

[christo1993]

Hi Donald,
thanks for your answer and sorry for the late response. So I tried it. The result looks much better but was not working.

So here is the case. I have a MySQL Database an my website is getting an XML File from here https://termine-die-unfassbaren.showhypnotiseur.eu

THe website is reading the XML file and placing it in the calendar. This you can find here. https://die-unfassbaren.de/termine.html

So every single event is in the XML a “record”. Inside of the record are all informations like date, place, time.
That means every linke in my sheet document should be a single “record” so that my calendar can read.

Do you know if that is possible?

Best
Christoph

[Donald_Mitchell]

Sorry I don’t think I quite understand.
Sounds like you want to convert the information from the Google Sheet into an XML formatted like the file located at https://termine-die-unfassbaren.showhypnotiseur.eu/ ?

If that’s the case, it doesn’t look like every field from the Google Sheet directly matches up to fields in each of the XML, but a few fields do match.

You’ll have at least three modules then.

1. Google Sheets Get Range Values to read the data
2. Array Aggregator to combine A, B, C, D, E, F from Google Sheets into an array.
3. An XML Create XML module. Build your XML’s data structure in this module. Once this module is connected, go back in the Array Aggregator module and select the data structure you created in XML module as the Target Structure Type. Then, in the XML module, select “Map” for your “record” array and you’ll map in the array that was created in the second module.

[christo1993]

Hi Donald,
yes you are right. And know I am deffinitley closer to my goal. But there are stil somme problems.
I created the the following modul order.

To make it easier I choosed only the A and E in the aggregator. And it looks really good. So I am getting the results.

The problem is still the CREATE. XML Module. It gives me only one set of data.
Maybe you have an Idea what i did wrong…

[Donald_Mitchell]

When you build your XML data structure, use the Generate option.
If you create it based on the XML you provided before, you should end up with a structure in which Record is nested under another collection. Then you can map the array from the Array Aggregator the record field in the XML module.

Something like this…

[christo1993]

Fantastic. Now it is working and I understand the process.
Thank you so much!

[christo1993]

I have on more question to this topic. @Donald_Mitchell maybe you have again an Idea.

The problem is: I am using this whole process for a calandar on my Webseite. If I am adding an appointment Google sheet is adding a new row at the end. Sometimes this new entry is not the latest day.

What happens is, that the dates are not in cronological order in my calendar. Is there an opputunity the sort the files or the whole google sheet before MAKE is running the XML module? Do you have an idea?

[Donald_Mitchell]

You could likely use the Google Sheets Make an API call to run a sort function on the sheet. I’ve never done it, but I have to assume it’s possible, just need to find the right doc.

[christo1993]

Hi Donald,
This is a really extensive topic… However I found a trick. I am using a QUERY comand.

I have created a new tab and all entries are automatically sorted here.

=query(Termine!A1:J201; "where B is not null order by B asc")

The XML module now simply pulls the data from the new tab and it works.

[Donald_Mitchell]

Great!

That’s probably a better solution in your case anyway since the sheet is always sorted and you don’t have to repeatedly call a sort function.

For reference, the doc for the sort function is here: Operazioni sui dati  |  Google Sheets  |  Google for Developers