{"id":472,"date":"2021-06-23T14:53:14","date_gmt":"2021-06-23T14:53:14","guid":{"rendered":"http:\/\/www.torontohelpdesk.ca\/blog\/?p=472"},"modified":"2021-06-23T15:14:04","modified_gmt":"2021-06-23T15:14:04","slug":"creating-daily-logs-of-forms-responses-in-office-365","status":"publish","type":"post","link":"https:\/\/www.torontohelpdesk.ca\/blog\/creating-daily-logs-of-forms-responses-in-office-365\/","title":{"rendered":"Creating daily logs of Forms responses in Office 365"},"content":{"rendered":"\n

Problem:<\/strong><\/p>\n\n\n\n

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.<\/p>\n\n\n\n

Solution:<\/strong><\/p>\n\n\n\n

I’m going to break this down into its elements. This should be done by the author of the Forms form<\/p>\n\n\n\n

Forms<\/em><\/strong><\/p>\n\n\n\n

  1. Load up your form<\/li>
  2. Click on Responses<\/li>
  3. Click on Open in Excel<\/li>
  4. Save to a unique location in OneDrive (and name accordingly)<\/li><\/ol>\n\n\n\n

    Collecting all the data into the above created Excel file<\/em><\/strong><\/p>\n\n\n\n

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

                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<\/em><\/strong><\/p>\n\n\n\n

                \n
                <\/div>\n<\/div>\n\n\n\n
                1. Create a new Flow<\/li>
                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<\/li>
                  2. Maximize Recurrence<\/li>
                  3. Click on Edit to the right of the scheduled days<\/li>
                  4. Click on Show advanced options<\/li>
                  5. At these hours: select the Hour<\/li>
                  6. At these minutes: select the Minutes<\/li><\/ol><\/li>
                  7. Click on + New Step<\/li>
                  8. Set it as Copy file (OneDrive for Business)
                    1. File: Select the Excel file from earlier<\/li>
                    2. Destination File Path:
                      1. Click on the field<\/li>
                      2. Select Expression<\/li>
                      3. Search for utcNow()<\/li>
                      4. The format that I used was utcNow(‘dd-MM-yyyy’)<\/li>
                      5. Copy the destination from the File field<\/li>
                      6. Append .xlsx at the end of the file<\/li><\/ol><\/li><\/ol><\/li>
                      7. Click on + New Step<\/li>
                      8. Set as List rows present in a table
                        1. Location: OneDrive for Business<\/li>
                        2. Document Library: OneDrive<\/li>
                        3. File: Select the Excel File<\/li>
                        4. Table: Table1<\/li><\/ol><\/li>
                        5. Click on + New Step
                          1. Search for Apply to each<\/li>
                          2. Select an output from previous steps: value<\/li>
                          3. Add an Action<\/li>
                          4. Select Delete a row
                            1. Location: OneDrive for Business<\/li>
                            2. Document Library: OneDrive<\/li>
                            3. Select the Excel file<\/li>
                            4. Table: Table1<\/li>
                            5. Key Column: ID<\/li>
                            6. Key Value: ID<\/li><\/ol><\/li><\/ol><\/li>
                            7. Save<\/li><\/ol>\n\n\n\n

                              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<\/p>\n\n\n\n

                              To test, add data to the form and run the test function<\/p>\n\n\n\n

                              <\/p>\n\n\n\n

                              <\/p>\n\n\n\n

                              <\/p>\n","protected":false},"excerpt":{"rendered":"

                              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 Continue reading Creating daily logs of Forms responses in Office 365<\/span>→<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-472","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/www.torontohelpdesk.ca\/blog\/wp-json\/wp\/v2\/posts\/472","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.torontohelpdesk.ca\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.torontohelpdesk.ca\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.torontohelpdesk.ca\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.torontohelpdesk.ca\/blog\/wp-json\/wp\/v2\/comments?post=472"}],"version-history":[{"count":2,"href":"https:\/\/www.torontohelpdesk.ca\/blog\/wp-json\/wp\/v2\/posts\/472\/revisions"}],"predecessor-version":[{"id":478,"href":"https:\/\/www.torontohelpdesk.ca\/blog\/wp-json\/wp\/v2\/posts\/472\/revisions\/478"}],"wp:attachment":[{"href":"https:\/\/www.torontohelpdesk.ca\/blog\/wp-json\/wp\/v2\/media?parent=472"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.torontohelpdesk.ca\/blog\/wp-json\/wp\/v2\/categories?post=472"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.torontohelpdesk.ca\/blog\/wp-json\/wp\/v2\/tags?post=472"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}