Help with Adding Results to Column in Excel

[nickidb87]

Hi, 

I've only been using Power Automate for a week now. I am working on a process but am stuck figuring the last piece out. My organization needs the ability for people to submit approval requests for events and then, once the event occurs, submit results of the event. So far, I have the following flow: 

1. Submit Form

2. Approval Sent

3. User emailed about approval status

4. Upon approval, form data is added to excel

After that, I would like for the submitter to receive a reminder on the event date to submit results. My thought was that they could receive an email and reply to the email with the results and those results be added to the appropriate event row in excel. Or, if email isn't an option, then another approval request is generated and sent to them and they put the results in the comments section. I haven't been able to figure out how to schedule the approval or email to be sent on the event date or how to get the results into excel. 

Any ideas would be greatly appreciated! 

[Rhiassuring]

Hi there, 

My first question is: why Excel? I'd recommend a nice list for this in SharePoint, for tracking approvals / reminders. Has a few benefits, but, I'll answer with Excel in mind. (But let me know if you want to know why I'd go SharePoint lists instead of Excel for this.) 

So, you're saying that 1 - 4 above,  you've completed - and now you want:

A) A reminder on the event date.

B) A way for the user to submit event results.

C) The results to be added to the Events spreadsheet.

This can be done in the following way (and likely other ways as well): 

⭐Step 1 - Create a scheduled flow to run once a day (probably in early morning) that looks at your Excel sheet's Event Date column, and compares to today.  That's not as easy a task as you might think, since it has to be translated for Excel to understand (serial). The formula for getting today's date using what Excel uses looks like this:

add(div(sub(ticks(utcNow()),ticks('1900-01-01T00:00:00Z')),864000000000),2)

I got this specific formula from Manuel T Gomes' blog if you want the breakdown of why it's like that, but also note I had to use a "2" instead of a "1" for Today's Date, likely because of my position relative to UTC.

Our flow so far will look like this:

In my example, "Approvals" is my Event tracking spreadsheet, and in my filter I am saying, "Where EventDate is equal to the output from our composed serial date".

⭐Step 2 - Let's email our EventOwnerEmail. Add another action, "Send an Email" - if you click on "Add Dynamic Content" just to the bottom right of the To field, you should see your email Row there. This will turn into an "Apply to each" - which is expected. This is in case we've retrieved multiple events for today. It will select the appropriate row's EventOwnerEmail per cycle.

⭐Step 3 - Getting the results... well, this one depends. What kind of results are we talking about? Is this 3 or 4 values of like, "Attendance: 20 people" "Actual Run Time: 35 minutes" "Fun Rating: 6" - or is this just a paragraph?  Here are some ideas: 

💡Best option (most user friendly, but also most work-intensive): A PowerApp where the user selects their event from a dropdown and then fills out the "Results information". (And, really, a link could be created to send with the email to pre-populate their event in the PowerApp.)

💡A Microsoft Form where the user types in their event title and date and then submits result data along with that. This is prone to error since Microsoft Forms won't be able to validate against your events list.

💡If your required results are very very basic, you could use a "Send Email with Options" - of course, this means your options would be something like "1 - 5 people" "6 - 10 people" "10 - 50 people", etc.

💡An email response - but this would need to be just a paragraph of text and very simplistic.

⭐Step 4 - Adding the results to Excel -- it won't be that difficult, I promise, but it will vastly depend on the way your results are coming back!

Which makes the most sense for your scenario? What do your results look like? 

Cheers,

Rhia

[nickidb87]

Thanks for the response! So I am a little familiar with SharePoint, but the people using this are not - they are used to excel and want to use that. I like the idea of scheduling it to look for the event date - I hadn't thought of that. Out of the options you presented, I believe the email would be the easiest for my staff to use. I tried a form but the form has free form text and I don't think people would ensure it matched the event name exactly. I am curious about the PowerApp option - but, honestly, I am all new to this and have no clue what that is. 

[nickidb87]

Forgot to mention what our results would look like. It can vary by event, but would most likely be a couple sentences to a short paragraph. 

[Rhiassuring]

Oh just a couple of sentences! All good then, okay. 

And yeah, I understand the staff technology gap can be a bit rough - which always makes me a bit sad, it's some training and then things would be so much easier for them...  but I know how it is! I empathize.

Alright then, let me think about the best way to do this.

I think it would be best for us to have the things we need in our Subject Title so when they reply, we can grab info from there. For example:

Where "EVENT NAME" and "DATE" are variables based on that previous output (where you got the EventOwnerEmail, for example. Works the same way.) 

After those emails are sent, that would terminate our first flow.

Our second flow, the flow to check for results, would now be a flow that watches for incoming emails that fit our subject line.  Am I right in assuming this is your email address or a service account address that this will be "From"?

For cleanliness stake, I would probably take the following steps:

🔸Set-up a rule in the "From" inbox to automatically move anything with "Reminder: Event results for" in the subject into a folder called "Event Results"

🔸Set up a new Flow triggered on "When a new email arrived (V3)" and set the Folder to the "Event Results" folder.

🔸Use a "Compose" action to split() on the " (notice how our subject header had "s around our important info!)

split(triggerOutputs()?['body/subject'],'"')

🔸Use an "Initialize Variable" action and initialize an Array. Plop your output from the Compose here. I've called mine arrSubject. 

🔸To make this a bit easier to understand going forward, I'm going to put these into variables, but you could also forego variables and just use the format -- I'm going to initialize 2 more variables - txtDate and txtEvent, and I'm going to use a reference to the array item (as they will always be the same number index) to set them:

My array looks like this:

[

  "Re: Reminder: Event results for ",

  "Jackbox Trivia Night",

  " on ",

  "04/12/2022",

  " needed!"

]

 And I only care about the event name and date, so I need #1 and #3. (Arrays start at 0! Weird, right?)  Therefore, in initializing my txtEvent variable to store the event name looks like this:  

variables('arrSubject')[1]  - I enter this into the expression input, if I just click on a variable and type [1] after it, it won't work.

🔸Okay, cool, we have our Event Name & Date. Now we just need the body of that email! First, I'm going to use the "HTML to text" action and drop the "Body" from our email trigger into it.

🔸Next, since we only want the response - not our request of the response - I'm going to initialize a variable called txtBody, and I'm going to stick this into the "Expression":  

trim(first(split(outputs('Html_to_text')?['body'],'--------------')))

💡 All this is doing is saying "hey, in replies, O365 adds this huge ----- - so, split on that. Every time we split, it turns into an array, so next we're saying "i only want the first result in this array". Finally, we're trimming all the ugly whitespace that appears at the end of it.

🔸Now all we need to do is use our date & event name to update the Excel sheet. I'm only going to use event name for right now, as you learned how to get an Excel date in the previous post. I choose the "Update a Row" action, point it at my spreadsheet, choose the Key Column of "EventTitle" and use our "txtEvent" as the Key Value.  From here, it will show you all the items you can add - you'll find your "Results" column (let's pretend for me that's Dept1) and put in the txtBody. 

You could also use the date instead, if that's going to be unique - but I'm hoping your event name will be unique. If it's not, it would likely be easier overall for you to assign Event IDs in some form of a schema, even if it's just a number. 

That's a lot. Let me know if you have any questions.

Cheers,

Rhia

[Rhiassuring]

Hey there @nickidb87 , just checking in to see if this worked for you?

Cheers,

Rhia