Email HTML to Excel

[archiejg]

Hi,

You'll have to bare with me - I'm experimenting with Power Automate at the moment as it's fairly new to me.

I'm trying to automate a process where I receive a email receipt and I'd like certain information from the email to be inserted into a new row in Excel.

I've been following some guidance from a YouTube video but it seems to rely on there being some form of delimiter in the email - unfortunately when the HTML is converted to Text, there is no delimiter I can use.

Currently, the flow will trigger when a new email arrives, no issues there and then the HTML is converted to text. No problems there.

The next steps in the flow are to split that text into lines, using a filter array to eliminate any blank rows (length greater than 0)

Here is an example of the array it produced

[
"Company",
"[https://url]",
"Thank you for booking with our company, please find your receipt below.",
"Please call us if you need anything",
"Telephone Number",
"Booking Reference73784709A",
"CustomerJoe BloggsPassenger Phone00000000000000AccountJoe Bloggs",
"PickupFake Address, Fake Street, Fake Town, SW1A4AADestinationFake Address,",
"Fake Street, Fake Town, SW1A4AADCO2 Emission43gTrip Distance0.35MI",
"Driver Number017VehicleAudi A6, License: 999",
"Pickup DateSun 15th Jan 2023 06:00 Your driver arrived 06:00 Journey",
"Start06:03Journey End06:16",
"Payment GBPAccount:",
"ABC123Price£3.00Tip£0.00Tolls£0.00Extras£0.00Waiting£0.00Service Charge£0.00Total",
"£3.00  Please note: This booking has not been verified by our accounts",
"department and may be subject to change.",
"Your booking route is available below",
"[cid:map]",
"Fake Address, Fake Street, Fake Town, SW1A4AA"
]

The first piece of information I wanted to try and split was the booking reference, the tutorial used the equivalent of:

split(body('Filter_array')[6], 'Booking Reference')[1]

However when testing the flow at this stage, it failed and gave this response:

Error
Action 'Compose' failed
Error Details
Unable to process template language expressions in action 'Compose' inputs at line '0' and column '0': 'The template language expression 'split(body('Filter_array')[6], 'Booking Reference')[1]' cannot be evaluated because array index '1' is outside bounds (0, 0) of array. Please see https://aka.ms/logicexpressions for usage details.'.

This is the layout of my flow so far:

I would really appreciate any direction, it's very possible I'm going about this totally the wrong way or it may just be something really simple.

I got to about 5mins in following this video: https://www.youtube.com/watch?v=7tZ6bRtco3Y

Many thanks.