Sync updated Form responses with Excel workbook automatically

Question

I'm using Power Automate to read an Excel file that is linked to a Form.

What I'm trying to find out is if there is any way at all to sync updated Form responses without having to manually open the Excel file. As in, the Form allows users to update their responses, and right now the only way I can find to sync the updated responses is to open the Excel file.

I understand that I can use a Power Automate flow to capture new responses in a separate spreadsheet without opening the Excel file because you can trigger Power Automate flows using new Form responses (I'm already using this workaround for new responses). But Form response updates do not trigger new responses. There is no Power Automate trigger for response updates.

I have tried using Power Automate to run a scheduled Office Script to refresh the Excel file, but this neither syncs new responses, nor does it sync updated responses. This is the Office Script I run with Power Automate just to show what I've already tried:

{"version":"0.3.0","body":"function main(workbook: ExcelScript.Workbook) {\n  // Calculate the entire workbook\n  workbook.getApplication().calculate(ExcelScript.CalculationType.full);\n\n  // Attempt to reapply filters on all tables, which can refresh the views\n  const sheets = workbook.getWorksheets();\n  for (let sheet of sheets) {\n    const tables = sheet.getTables();\n    for (let table of tables) {\n      const filters = table.getAutoFilter();\n      // Check if filters are applied\n      if (filters) {\n        // Reapply filters\n        filters.reapply();\n      }\n    }\n  }\n}","description":"","copilotMetadata":null,"parameterInfo":"{"version":1,"originalParameterOrder":[],"parameterSchema":{"type":"object","default":{},"x-ms-visibility":"internal"},"returnSchema":{"type":"object","properties":{}},"signature":{"comment":"","parameters":[{"name":"workbook","comment":""}]}}","apiInfo":"{"variant":"synchronous","variantVersion":2}"}

I have also tried using Power Automate to make an HTTP request using the GET method to the Excel file on Sharepoint, but that hasn't worked either.

I have tried creating a new Form from an Excel file by opening a new Excel file then going to the Insert tab to add a new Form. This doesn't change anything about syncing behavior.

I have tried moving the Form to a group using the "Move to Group" option, and I have also tried sharing the Excel file with a Group. Neither of these approaches changes the behavior.

I have tried using Power Automate to just list the rows in the Excel file and run my Office Script, and I can see in OneDrive that this updates the Excel file's "Modified" time, but this still does not sync Form responses or Form response updates.

Is there anything I haven't tried? I don't understand how there isn't an option to just schedule the syncing behavior or a less convoluted work around. Having to manually open the file to sync really limits the utility of syncing form responses in the first place.

My goal is to ensure that the Form responses and Form response updates sync to the Excel file before a series of Power Automate flows run. Can anyone fill me in on how to do this?

Answer 1

Ok so, seeing as there is no longer support to use the HTTP request to SharePoint connector with endpoints like Forms, and there is no Forms connector for Forms response updates (which is what my original question was about), are there any other options available to us?

It is really baffling to me that there's no way to just schedule the syncing behavior between Forms surveys and their linked spreadsheets. My work around right now is to locally run a shell script on a cron job to load the browser version of the spreadsheet on a daily basis to make the sync happen, which is ridiculous. I've thought about abandoning Forms all together over this but transitioning platforms would be a pain and I was holding out hope that it wouldn't be necessary...

Answer 2

I thought I would have a resolution to post today after a meeting with Microsoft Support, which was scheduled for this morning. Unfortunately, the meeting was cancelled by Microsoft. Hopefully it will be rescheduled soon. All my affected flows are still not working.

Answer 3

Dear Jawad,

As you are experiencing another issue, I suggest you post a new thread on our forum for further help.

Your effort and time are greatly appreciated!

Sincerely

Cliff | Microsoft Community Moderator

Answer 4

The Forms API is no longer working with "Send an HTTP request to SharePoint" connector.

{ "status": 401, "message": "{"error":{"code":"701","message":"Required user login."}", "source": "https://forms.office.com/formapi/DownloadExcelFile.ashx?formid=XXXXXXX", "errors": [] }

Answer 5

Dear Drb,

You can create a scheduled flow to add a new response and updated the existing responses automatically in each specific time.

You can add the ID column in Excel for the flow to find the rows to update the values.

Form:

You can create the flow as follows.

Use the Send an HTTP request to SharePoint action to get all the latest responses.

https://forms.office.com/formapi/api/forms('formid')/responses

For your reference: Power Automate: MS Forms responses to Sharepoint - Microsoft Community

Get each question field's value and append them to an array.

Compose: outputs('Send_an_HTTP_request_to_SharePoint')?['body']?['value']

Compose2: items('Apply_to_each')?['answers']

Compose3: json(item()['answers'])[0]['answer1']

Compose4: json(item()['answers'])[1]['answer1']

Compose5: items('Apply_to_each')?['id']

Append to array:

{
"QuestionA":"@{outputs('Compose_3')}",
"QuestionB":"@{outputs('Compose_4')}",
"ID":"@{outputs('Compose_5')}"
}

Get all rows in the Excel file and get the rows existing in the HTTP response but not in the Excel table.

@not(contains(body('Select'), item()))

Then get the number of the rows in the Excel table.

 Compose6: length(outputs('List_rows_present_in_a_table_2')?['body/value'])

If the conditions that number of the items from the HTTP response is not equal to the number of the rows in the Excel table and the values of the List rows action is not empty are true, add the new rows in the Excel table.

Condition:

empty(outputs('List_rows_present_in_a_table')?['body/value']) is not equal to true and int(length(variables('Response'))) is equal to int(Outputs)

get each value of the new rows which needs to be added to the Excel table.

Compose7: items('Apply_to_each_2')?['QuestionA']

Compose8: items('Apply_to_each_2')?['QuestionB']

Compose9: items('Apply_to_each_2')?['id']

Add the new rows to the Excel table.

If the conditions are false, update each row.

Key Value: items('Apply_to_each_3')?['ID']

QuestionA: items('Apply_to_each_3')?['QuestionA']

QuestionB: items('Apply_to_each_3')?['QuestionB']

Compose9: length(outputs('List_rows_present_in_a_table_2')?['body/value'])

If the conditions that number of the items from the HTTP response is equal to the number of the rows in the Excel table and the values of the List rows action is not empty are true, update the rows in the Excel table with the items' values from the HTTP response.

Condition:

empty(outputs('List_rows_present_in_a_table')?['body/value']) is not equal to true and length(variables('Response')) is equal to Outputs

Update a row:

Key Value: items('Apply_to_each_3')?['ID']

QuestionA: items('Apply_to_each_3')?['QuestionA']

QuestionB: items('Apply_to_each_3')?['QuestionB']

Welcome to share any updates at your convenience.

Your effort and time are greatly appreciated!

Sincerely

Cliff | Microsoft Community Moderator