Creating daily logs of Forms responses in Office 365

Posted on by

Problem:

You have a Microsoft Forms form that you want to have a daily output on. Basically, every day, you want to pull all the data from an MS Forms form and output to a unique file with the date/timestamp on it.

Solution:

I’m going to break this down into its elements. This should be done by the author of the Forms form

Forms

  1. Load up your form
  2. Click on Responses
  3. Click on Open in Excel
  4. Save to a unique location in OneDrive (and name accordingly)

Collecting all the data into the above created Excel file

  1. Go into Power Automate
  2. Create a new flow
  3. Set it to type “When a new response is submitted”
  4. If Form Id does not show up with your MS Forms
    1. Go back into your MS Forms form
    2. Click on Share
    3. Under anyone can respond, you’ll have a long URL
    4. Copy the URL (into notepad)
    5. Strip out everything before id= (including id=) so you end up with a long string of characters
    6. Paste it into the Form Id field
  5. Click on + New Step
  6. Select Get Response Details
    1. Paste the same id as in 4F above
    2. Under response ID, select Response ID
  7. Click on + New Step
  8. Search for Add a row into a table
    1. Location: OneDrive for Business
    2. Document Library: OneDrive
    3. File: Select the file you created originally
    4. Table: usually it’s Table1 but whatever the table name is
    5. Rest of info:
      1. ID: Response ID
      2. Map all the other fields you need to track
  9. Save
  10. Test
    1. You should see output from the Forms form appear in the Excel file

Creating the daily Output – this will daily copy the file to a new file name that has the day’s date on it as the file name

  1. Create a new Flow
  2. Set it as type Recurrence and set the schedule
    1. Note that you should set it at a time when you know that all the data should have been collected by
    2. Maximize Recurrence
    3. Click on Edit to the right of the scheduled days
    4. Click on Show advanced options
    5. At these hours: select the Hour
    6. At these minutes: select the Minutes
  3. Click on + New Step
  4. Set it as Copy file (OneDrive for Business)
    1. File: Select the Excel file from earlier
    2. Destination File Path:
      1. Click on the field
      2. Select Expression
      3. Search for utcNow()
      4. The format that I used was utcNow(‘dd-MM-yyyy’)
      5. Copy the destination from the File field
      6. Append .xlsx at the end of the file
  5. Click on + New Step
  6. Set as List rows present in a table
    1. Location: OneDrive for Business
    2. Document Library: OneDrive
    3. File: Select the Excel File
    4. Table: Table1
  7. Click on + New Step
    1. Search for Apply to each
    2. Select an output from previous steps: value
    3. Add an Action
    4. Select Delete a row
      1. Location: OneDrive for Business
      2. Document Library: OneDrive
      3. Select the Excel file
      4. Table: Table1
      5. Key Column: ID
      6. Key Value: ID
  8. Save

What’s effectively happening here, is all the data gets collected over a period of time. Every day, that Excel file gets copied to a new file that gets renamed with the day’s date. Then the system goes in and deletes all the rows of the main Excel file

To test, add data to the form and run the test function