Having issues getting names/times/answers formatted correctly in the Excel sheet.

(0) Share

Report

Posted on by CU16110116-0

I have been tasked with making a form for my coworkers (30 of us) to fill out, and I must submit the results to my supervisor.

Questions look mostly like: "Were you able to log into [System 1]?" with possible answers "Yes" or "No" radio buttons.

I've finished the Microsoft Form and have the Excel sheet linked, so when I submit a form it goes directly into the Excel sheet.

I am struggling with some features I want to use via Power Automate, though. This is also my first time using Power Automate.

Here are my goals:

1) Instead of users manually entering their first and last name into the Form, I want their names to be automatically pulled due to them being logged into their Microsoft account.

Then, when their name goes into the Excel sheet, I want it to be formatted as [Last Name, First Name] within a single cell. Currently, no matter what I do (I've been going back and forth with ChatGPT), it either submits the name as First Name Last Name, or one time it did have Last Name, First Name, but it had the wrong time format and also it made a duplicate row.

2) For the Time Submitted column, I want it in Eastern Standard Time. Like the name format, I was successful once but had issues with unwanted duplicate rows.

3) When the Yes/No answers reach the Excel sheet, I want them to be formatted as "y" and "n". The reason is because my supervisor will be editing these fields as people report back to her down the road that they were successful with logging into certain systems, and it just makes my supervisor's job a little easier. I do know how to use Conditional Formatting in Excel. All "y" answers will be highlighted green (and "n" remains white). Just a simple feature I think would visually help my supervisor looking at over 300 data points at once, and help reduce typing a bit.

If anyone could assist with these goals I would be truly grateful!

Categories:

Building flows

I have the same question (0)

All responses (2)

Answers (0)

Sort by

creativeopinion 10,508 Moderator on at

Like (0)

Report
Copy link

Link copied!

An Excel sheet is automatically generated with the response answers from MS Forms. You can't manipulate this data before it's added to the Excel sheet.

However, you can do one of the following:

1) Add your data to another Excel sheet (which is redundant and not recommended)

2) Add your data to a SP List

3) Run a flow that goes through your Excel sheet at a specific time of day to manipulate the data

What is your preference? In the meantime you might be interested in this YT Tutorial:

How to Get a Microsoft Form RSVP Response into a SharePoint List

In this Microsoft Power Automate tutorial, I’ll show you how to get your Microsoft Form responses into SharePoint. I’ll cover how to build a Microsoft form with conditional fields by using branching. You’ll learn how to format your multiple choice responses for a SharePoint multi-choice column as well as converting it to text. I’ll also show you how to take a text response and convert it to a number with a switch action and with an expression.

After adding the response to SharePoint, we’ll send the user an email confirmation with all the details of their form submission.

At the end of the video I’ll show you a few tips and tricks on working with a SharePoint list.

IN THIS VIDEO:
✓ How to get a Microsoft Form Response into SharePoint

✓ How to get a Microsoft Form ID

✓ How to get a Microsoft Form response

✓ How to Build a Microsoft Form with Conditional Fields

✓ How to used Branching in Microsoft Forms

✓ How to format Microsoft Form multiple choice responses for a SharePoint multi-choice column

✓ How to parse a Microsoft Form multiple choice response as string of text

✓ How to convert a text response to a number

✓ How to use the Switch action

✓ How to create a custom email confirmation for a Microsoft Form submission

✓ How to Create a Custom View in a SharePoint list

✓ How to use the Compose Action

✓ How to write Power Automate Expressions

Hope this helps!

Consider giving me a ❤️ if you liked my response!

👉 Level up your Power Automate skills by checking out my tutorials on YouTube
👉 Tips and Tricks on TikTok and Instagram

Was this reply helpful? Yes No
Suggested answer

Mark Nanneman 993 Moderator on at

Like (0)

Report
Copy link

Link copied!

You could create a SharePoint list formated as you want it for your supervisor, and populate the SharePoint list automatically with each submission of the form.

Just use the "When a new response is submitted" trigger, and the "Get response details" action. Then map the data points to where you want them to go in a Create Item (Sharepoint) action.

You can use power automate expressions or actions to convert the timezone or time format.

You can also use conditional logic to change the Y/N or Yes/No to whatever you want.

Then, you can just give your supervisor a link to the sharepoint list.

- Pre-fill names:
a. You might not need to even have a name field--the responder's email will come through to power automate if it's an internal form submission.

b. If you want to pre fill name fields you can use pre-fill URLs. You need to have a power automate flow or some other system (like a power app or liquid code on a portal page that can auto place the user's name in the appropriate place on the URL to the form)

Just find the "Get Pre-Filled URL" option when editting your form. Put some placeholder text in the fields you want to preload, then find them in the URL and come up with a method to dynamicly load them for each respondant.

If this helped you, please click "Does this answer your question" and give it a like to help others in the community (+ close the ticket)!

Power Platform Developer | LinkedIn: Mark Nanneman | Blog: Power Stuff | YouTube: Mark's Power Stuff |

Was this reply helpful? Yes No