Form Responses not recording in excel on a consistent basis.

Copper Contributor

I have created a Form for Excel in OneDrive and used the resulting tables to preform a multitude of other actions via Power Automate (it is NOT the excel document that is generated from the response page of the form).

 

It is intermittently (and seemingly for no reasons) failing to collect responses from the form (see pic):

Jeff_Hunt_0-1690988298383.png

In this example - I know for certain that #'s 69, 71, 73 & 74 were properly entered.  Resynching to a new worksheet is not an option as there is a great deal of automation attached to this sheet.  Any idea why it would randomly skip entries?  Creating quite the mess 

13 Replies

@Jeff_Hunt 

Could you please clarify a bit

- you have Excel file which is automatically updates with Forms responses

- in the table of this file you have no missed responses, i.e. all from 67to 75

- with Power Automate you call the table from that file and something is missed

 

or

- you have Excel file on OneDrive

- Power Automate which is triggered on each response updates the table in that file

- and here some responses are missed

Neither one of those are quite right... Let me phrase it like this:
-I have an excel file (table) which is NOT updating with form responses consistently.
-I DO have missed responses in the table.
-The only pattern I can discern is that it is always the first entry of the day - but it also recurs later

-My mention of the Power Automate process is to point out that the table of responses is used to create several secondary tables and perform a variety of tasks based on these responses (I only included this as I believe it might preclude some of the typical fixes to form functionality).

Hope this helps narrow it down (you were MOST helpful with a script problem I had)
It is intermittently (and seemingly for no reasons)
table of responses is used to create several secondary tables
Because of intermittent happened,How about not create secondary tables?
I guess it maybe time out on server side.

I'm not sure why the secondary table creation would prevent the initial population of information from the form. The population of table 1 happens (or should happen) outside of power automate. Here is a snip of the start of the process:

 

Jeff_Hunt_0-1691588735783.png

I don't know why the server would take less than a second to retrieve the information for power automate but time out prior to submission on table 1??

 

Any help would be appreciated.

I have no experience on power automate.
According to the information from you provided and Internet, It may not handle too many steps which consume long time.
If the business logical too complex,I guess you may find another ways(like PHP/java) to achieve your goal.

But the error is happening BEFORE the Power Automate process starts. The form is simply (and intermittently) not populating on the table.

Power Automate is essentially monitoring (and showing that data is present - somewhere...).

I am not sure but I believe Power Automate is actually happening to populate your excel file.
I understand you didn't create a Form and use Power Automate to copy the data from the Form Response sheet to the Excel File but instead created the Form for Excel directly from the Excel menu, but I THINK that is just a shortcut to create a form and perform that automation in the background.

Maybe you could initiate your custom automation on New Line added to Spreadsheet trigger instead of New Response to make sure the background automation from form to spreadsheet happens first?  

 

"...I THINK that is just a shortcut to create a form and perform that automation in the background."

I believe you are 100% correct in that supposition. That "automation in the background" is where the error is happening (but where??).

 

I can't use the addition of a line in the spreadsheet as a trigger (PA does not have excel triggers to initiate ).

I could use PA to add the responses from the form directly into the spreadsheet, but that would create other problems (namely - it adds " " to answers that I need to somehow remove, and does not add a separator (";") between multiple answers that I need for functionality.....

 

Finding out where this automation in the background is happening is the answser I believe....still looking for solutions....

unfortunately I don't know.

btw are you seeing those missing responses in the secondary tables?

What if you go to the form itself and check responses on the responses tab and click either view results (green button) or 'more details' (link on each question); are the missing responses there?

mtarler_0-1691678213047.png

 

Responses are "showing" in the pop up 

Jeff_Hunt_0-1691680681726.png

....but they do not show when I open the existing workbook.......

Jeff_Hunt_1-1691680874249.png

I have tried deleting all responses and starting again, but the problem returns (Clearly - I do not want to sync data to a new workbook as I believe I will lose all the subsequent automation steps... :(

I don't believe you will lose those other automation steps as those steps are linked to the form not the excel sheet. maybe whip up a quick test by copying the workflow and link the new copy to a new test form/copy and do it. And even if the automation 'breaks' it should only be a matter of fixing that initial reference link. That all said those are all my assumptions and reality has a way of humbling me. lol
UPDATE 8-23-22
This post has 265 views and I have received a couple of personal messages asking if there was a resolution (one individual has PM disabled so I cannot answer)...sorry to report that this does not have a resolution yet, though it is clearly generating interest - anyone....anyone...?
Hate to say it - but I did loose my info.... :(
I am re-building everything in the hopes that a new table (on the server) will be error free - but I still not have answer and am getting PM's from individuals wanting to know... :(