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:

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.

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!