Saving data from Microsoft Forms into Excel using Power Automate (Flow)

In another post I reviewed how we can integrate a Form with Excel, letting all of that beautiful data be saved directly into an Excel file. This solution works really well if you are creating a new form. What if we have an existing form and we want it to write to Excel?

Unfortunately, there is no apparent method to integrate an existing form with Excel, natively in Forms. Fortunately, we have Power Automate available.

For this post, I’ll be using a survey I used to poll the new user group I’ve started up (learn more about Boston DXPT here).

Is my Form already saving to Excel?

Before we embark, maybe your form is already writing to Excel, maybe you forgot, it happens, I’m getting old too :D. Go to your form, go to the Responses tab, and look at the Open in Excel button:

In my example above, that icon indicates I’m not using Excel, the data is just in Forms. If it was integrated with Excel, I would see an icon like:

Note the subtle little cloud on the icon. Yea, that’s the only way I can see to determine if a form is writing to Excel already.

Additionally, you can check if you click the icon and open the Excel file:

  • Although my example above says Open in Excel, all it’s doing is loading the dataset into Excel. When it downloads, you’ll get an Excel file with the name of the survey and the number of responses in the title, i.e. New England CMS Meetup Survey(1-16). Also, note that it downloaded, NOT stored in SharePoint somewhere.
  • When you click on the clouded icon, it will open the actual Excel file that is linked to the form, in the actual location where the Excel file is stored. There’s no download, just opens it in SharePoint.

Okay, we determined this form is not going to an Excel file, so let’s fix that!

Get your existing entries first

Before we can start storing new entries into an Excel file, I suggest getting all of the existing entries into an Excel file, first. You’ll want to have a complete set of data available, right? Even if you have no existing results, do this step. By doing this step your Excel file will have all of the necessary formatting and columns setup for your form.

While editing your form, go to the Responses tab, click Open in Excel on your form to download the Excel file. Save it, naming it anything you’d like. I suggest removing the (1-XX) portion of the file name.

Save the Excel file somewhere in SharePoint Online. I recommend a library in a Teams site. There might be some limitations on where you can save this, based on connector options and licensing. SharePoint library should be fine.

Once saved up there, open the Excel file and rename the table for the sake of ease. It defaults to table1, I suggest a more meaningful name. To rename it, select a cell in the table, then go to the Table tab. You should see the Table Name on the far left:

This is on a Mac, I think Windows will be very similar, might be a Table Design tab

Rename it, I called mine results. Save it, but keep Excel open. It’ll be fun to see stuff fill in.

Now Power Automate it! Let’s make a Flow.

Go to https://powerautomate.com and log in. Click Create + on the left, and then select Automated Flow.

In the new dialog window, name your flow, like New CMS Survey Submission. Then under the trigger, search for Forms and select When a new response is submitted. Click Create.

Configure the Trigger

Select your form from the pick list. If, for some reason, you don’t see it listed, check out this post When Power Automate can’t find your Form.

Action: Forms – Get response details

Next, add an action. Click + Next Step, search for forms and select Get response details.

Side note: This is a little weird. The trigger only reports the ID of the response of the form that was submitted. You then have to get the response data separately. Not entirely sure why it’s designed like this, but it’s necessary.

Select the same form that you select in the trigger, then in the Response Id field, select List of response notifications Response Id from the dynamic content picker.

After you configure it, rename the action to something a little more meaningful. I renamed my to form response. See this post for more on renaming actions.

Action: Excel – Add a row into a table

Add another action, search for Excel and then select Add a row into a table.

Fill out the action for where your file is stored:

Once you select the table, the action will blow up and show all of your fields. You can then start filling it out, using the dynamic field picker.

Some caveats:

  • ID: I suggest using the ID from the trigger.
  • Start time: Forms captures when users start and complete the form process. Unfortunately that data does not come through to forms. You can skip this field
  • Completion time: I suggest putting Submission time into this field.
  • Email and Name: When the form is a secure form, requiring login, Forms automatically sets these as well. Unfortunately we don’t get both of these back in the flow. We do get Responder’s email and you could add another action before this Excel action to lookup the user by email, and then grab their name from there. If this is an anonymous form, like mine, you won’t have an email or name, so you can skip these if that’s the case. Look at your existing Excel data, if the form is anonymous, you’ll see the existing entries are all anonymous.

Want to get more info about your users?

If you’re using a secure form, and you have email addresses of the people submitting, you can get their info from O365 pretty easily. Add an Office 365 Users Get user profile (V2) action. In the User (UPN) field, select the Responder’s Email.

Now you can use the properties from the user’s O365 account when saving to Excel.

Other than that, the rest of your fields should match one for one.

That should do it!

Now let’s see if it works!

Make sure to save your flow then back out of it (arrow at the top left) and return to the flow dashboard screen.

Go fill out your survey and flip back to flow. You should see that it ran successfully.

Let’s check out Excel and confirm the data is there.

I can see my test row in there! It worked!

That’s it! Pretty simple approach.

But you can do so much more!

You have great flexibility in Power Automate:

  • you could add additional logic in here, if they answered a certain way then manipulate the data (do some basic calculus) or…
  • get data from other data sources and include it in the Excel file like do a SQL lookup or pull from a SaaS app, for example…
  • query Salesforce for the email they shared and pull their account rep’s name and send them an email saying this form was submitted…
  • then post to a Teams channel sharing a new submission was received.
  • You can even skip Excel all together, just write it directly to your data source of choice!

The sky’s the limit! Power Automate affords you to do virtually anything with this data coming from Forms, and use it however you want for your needs. Enjoy!

16 thoughts on “Saving data from Microsoft Forms into Excel using Power Automate (Flow)

Add yours

  1. Hi David! This is really helpful! Do you know if there is a way for it to check if a “row” already exists, and if it does just to update that row, and then if it doesn’t add a new one?

    For example, if an employee’s name already exists in the table, it would only update the information that has changed in the selected columns. If the employee’s name ISN’T already in the table, then it would add a whole new row.

    Thanks for any insight you might have.

    Like

  2. Hey, I followed the instructions exactly, but I’m getting an error 409 “This won’t work because it would move cells in a table on your worksheet.” Apparently this is due to a write conflict on the file but I don’t see how it is possible… Any idea? I don’t think it is open anywhere else and there is definitely not another flow working on it.

    Like

  3. Is it possible to make answers fill different tabs eg response 1 goes into tab 1, response 2 goes into tab 2 etc or would it need to be separate flows to do this? What I want is to create an action log for each submitted answer, with the response auto-filling and then other predefined columns to be manually completed as the task is completed. Maybe I’m overthinking and there is a more simple way? My form has 20 questions each creating a separate task to be actioned. Thank you.

    Like

  4. Thank you so much for these instructions. I don’t have the form buttons in Excel yet (supposed to switch from an e3 to e5 license soon) so this was a lifesaver!!!

    Like

  5. Thanks David, nice and easy to understand.
    Do you know if it’s possible to, instead of updating a table in an existing excel sheet, Power Automate can create a duplicate of that excel sheet and put the form data there? I’d like to be able to make a unique file for each response to a form but drawing a bit of a blank.
    Thank you so much for any ideas or advice you can suggest

    Like

    1. Yes, what you could do is use a SharePoint action, Copy file. First, create an Excel file to be your template. Copy that somewhere, then put the data into that Excel file. Should be rather painless, however, sometimes the Excel Actions get finicky about using dynamic values for opening files. Let me know how it goes.

      Like

      1. Hi David, got it working thanks! Used the Get Content, Create File, Get Tables connectors, and put in the ID of the table as a custom value. Could then use a Update a Row connector with JSON array to populate the row of the table. Used the ID of the Create File throughout.

        Liked by 1 person

  6. David, can you share the steps on how to get the user’s name from O365 using their email. I have been trying to figure it out for a while, with no luck.

    Like

  7. You said “If you’d like to see how to get the user’s name from O365 using their email, let me know, I can share that.”

    I assume that goes with the comment above that that says “We do get Responder’s email and you could add another action before this Excel action to lookup the user by email, and then grab their name from there.”

    Yes, I would like to know how to do this!

    Like

  8. hi david can you share this step. love your articles and really helpful..

    “If you’d like to see how to get the user’s name from O365 using their email, let me know, I can share that.””

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a free website or blog at WordPress.com.

Up ↑

%d bloggers like this: