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!

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

Add yours

  1. Hi David! This is awesome – thank you! Do you know if there is a limitation to the number of rows Power Automate can add to Excel? I have linked Power Automate to a survey in MS Forms. Thank you!

    1. I’m not sure about a limit, I’ve handled some pretty large data sets in flow (well, medium size, several hundred items) across different systems and data sources, although not directly into Excel. If you find a limit, make sure to share! :D. thanks

      1. Thank you for letting me know. For sure, will keep you posted if I find out there’s a limit! Thanks again.

  2. Thank you! This worked a treat! Now I don’t have to remake my graphs every time I get new responses

  3. Hi David, I have used power automate to log the responses from several of our forms within the company and it was working fabulously until this one form. We pushed out a poll to our users and this time power automate logged a ton of duplicates. The only difference with this form was that we had a ton of people all submitting their responses at once. Have you had any issues with this in the past?

    1. I haven’t come across this, but I might know what is going on. When you create a flow on a form, the object you get back is an array, a collection, of response ids. Then you have to get the form details using the id (notice how when you use the id Flow puts a for each loop in for you). I’d guess that Forms collects multiple responses and submits them at once, no idea what the frequency is as I haven’t actually run into this before. The fix might be to check if the form exists first in your target location, then write it if its new.

  4. This is a good summary of this process. I have used this in the past. One question: What if the same form is used to collect the information but some data goes to one workbook and other data goes to another notebook, and still other data goes to another workbook? 1 Form to multiple workbooks?

    1. absolutely, you have that power in your Flow, simply add new Excel actions in the same manner, connected to different workbooks. Going to a different worksheet in the same workbook will require another action, the same action, and just select the other table name.

      1. Thank you. I actually think I need a condition. The different data will be sorted to different workbooks based on email address or username.

  5. Hi David, i stumbled across this amazing article when trying to get a form to update a spreadsheet. I’ve got it all working up until the point in your picture “Other than that, the rest of your fields should match one for one”. it gives me no options to populate the cells/rows with any data from the form. I can get it to notify me and other flows but it does not fill in the spreadsheet :( starting to regret going down this rabbit hole.

    1. Hmmm… few questions. Are you able to see the fields from Excel in the action? Or are you not seeing fields from the Form? OR is it mapped and it’s just not writing to excel?

      1. Hi, flow is showing me the fields in the spreadsheet created from the form by opening in excel but it’s not giving me any options to map them. IE in the form there is a field for a contact number for example, the spreadsheet created the corresponding column but flow offers me the cell/column but no option to map it. the best I’ve got it to do so far is “create new row” but it won’t populate. It’s just blank :(

        1. Weird. In the Excel file, are the fields in a named table? I believe it’s the Data or Table tab that should show if it’s in a table. If it’s not, select all the cells and insert a new table. Flow likes tables in Excel a lot.

          1. Hi, i’ve got the table named (it’s in the design tab weirdly) and flow lets me select the names table but still doesn’t let me map any of the questions from forms to that table :(

              1. Thanks, i’ve just sent you an email with a Loom com video. thought that might explain it a bit better.

  6. 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.

  7. 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.

  8. 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.

  9. 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!!!

  10. 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

    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.

      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.

  11. 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.

  12. 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!

  13. 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.””

Leave a Reply

Powered by WordPress.com.

Up ↑

%d bloggers like this: