cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Iillminetor
Helper I
Helper I

Extracting data from body mail to Google Sheet

Hello,

 

I'm a supplier to companies who send me orders by email, I want to create an automated flow that can automatically save the customer details into google sheet so we can follow up with him regarding his order, the format we getting as the below :-

Booking ID: xxx
PickupLocation: xxx
Drop Off Location: xxx

Pickup Date: xxx

Pickup Time: xx

Customer Name: xxxx

Customer Phone Number: xxxx

 

We want to auto save the customer name, phone number and date fields automatically to Google Sheet so we can follow up with him.

 

I also got 2 additional questions

1) I'm I able to extract the OLD data since 2017? or just the new ones?

2) Is there is anyway to integrate with WhatsApp to automatically send new customers WhatsApp messages?

4 REPLIES 4
chrissahagun
Resolver II
Resolver II

Here's a framework of the logic I'm thinking...

chrissahagun_0-1616230938322.png

 

  • the split() expression on xBodyLines split the email body into array.  Each item delimited by a new line (\n)
  • the trim(substring()) expression on xInfoType extracts a line's possible column (Booking ID, Pickup location, etc..  It does this by looking for the colon character (indexOf())
  • the trim(substring()) expression on each column variable setting extracts the column value without the column name (stuff to the right of the colon)

Put this logic inside a bigger loop if you want to process multiple messages in one flow run.

 

1. You can extract old emails with Get Emails, but do you really need to? Ask your colleagues / customers.

2. Not sure about WhatsApp.  Check if there are connectors for WhatsApp.

 

Iillminetor
Helper I
Helper I

I'm stuck in the cases part, which variable did you use in Booking ID and other cases? also can you write down the codes so I can be sure that It has no errors?

 

Screenshot_13.png

chrissahagun
Resolver II
Resolver II

Hey there...

 

My intent when I was writing my post was to provide guidance / a framework, not the actual solution, but I came pretty close to giving it away...

 

You might have noticed that my screenshot did not initialize the booking id variable, pickup location variable, etc.  Those are not in the screenshot but you have to initialize them too.

 

As for the codes, unfortunately, the moment I pasted the screenshot part of my response to this thread, I already worked on another question, and so I deleted the model for the solution.  The codes are already in the screenshot, which i made sure were not blurry.  Please have patience to examine and analyze and type them manually. 

chrissahagun
Resolver II
Resolver II

Also, please consider transitioning your order taking process from Email to MS Forms.  that way, your data will be straight away exportable to an Excel file, rather than having a flow to process highly variable incoming emails.. 

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Kudoed Authors
Users online (3,868)