Get Form responses, use the form responders' email to lookup the matching file name in sharepoint folder then add the form response to that files table using OfficeScript and JSON

[AliceN]

Good morning, If anyone has a better idea to handle this issue I would love to hear it!

The use case and goal of this project is for each of my call center sales agents to have their own automated excel workbook that tracks the information they fill out on a form. To accomplish this all sales agents use the same form to answer questions about the sales calls they received. The form responders' email address from the form response is then matched to an excel file by the same name in a specified sharepoint folder and the form responses for that agent are then added to the table within that folder.

The current flow steps I have in place appear to work UNTIL I get to the apply to each where it appears to become "stuck" and never completes the last step but continues processing before I finally cancel it after 30+ minutes.

Because form responses will be coming in continually for approx 25 sales agents with every sales call they receive over an 8 hour period which averages about 100 plus responses a day, process time is a factor.

Side Note on the use of script- we didn't have a better idea to get the form response per agent into their own file without the use of the script. Any better ideas would be appreciated!

The flow steps are as follows:

1. When a new response is submitted 

2. Get Response Details: Response ID

3. Get files (properties only) 

3. Filter Array: value - Name ( File name of the item in the document library, display name of the item in lists) eg. the name format of the file is the responders' email address.xlsx CONTAINS  Responders' Email

4. Apply to each: Body

5. Apply to each 2 : Value - Run script from SharePoint Library

The script is a simple one:

function convertToRow(formData: object😞 (number|string)[] 

{  return [

    formData["Completion time"],

    formData["Email"],

    formData["Call Type"],

    formData["Customer Phone Number"],

    formData["New Customer Outcome"]

];  }

function main(workbook: ExcelScript.Workbook, formData: object) {

    workbook.getTable("Table_Name").addRow(-1, convertToRow(formData))}

_____________________________________________________________________________________

DURING PROCESSING: 

While it's running it looks like this; passes quickly through until it reaches the Apply to each. 

----------------------------------------------------------------------------------------------------

THE FLOW STEPS:  The last step of the apply to each formData (which is what is used in the script) is a simple JSON with no errors. 

[RobElliott]

@AliceN is there any particular reason you are using a script? Why not just use the add row into a table action? You could choose the table via a Switch control.

[AliceN]

@RobElliott the script was an attempt to work around the "Row"  not populating when trying to use the add row to table with the 'Name' for the file variable and a custom table name entry.  Do you have an example per chance of the add row into a table action and then choose the table via a Switch control that you are describing by chance? YouTube isn't exactly coming up with the best solutions at the moment. (Really appreciate the idea by the way) Also good to note, the file name isn't an exact match because it has an xlsx extension ergo the Case Equals is a concern as well.

[Andrew_J]

@AliceN 

I think you have an overly complicated flow and or process but I will try and propose something mroe streamlined.

I am assuming you have a SharePoint site, SPS, called SALES as start.  Your sales agents are all internal members of staff.  So in the SPS you have a SharePoint list, SPL, called SALESCALLS.  This SPL has a form that your sales agents can fill in to record the data in the list.  By thre fact that your sales agents are logged into your sytem it takes thair user details and adds that to the SPL.  You now have a SPL with the raw data in.

You mention wanting to store data in an individual folder per sales agent.  I am not why you want to do thisother than for their records and yours. Maybe you could look at doing several things at once on a daily basis that all run at midnight when I hope the sales team are not working:

• run a report per sales agent, store it in their folder
• undertake the above but send it the sales agent via email
• run a daily report for the mangers of any seperate team and store in their folder
• undertake the above but send it the mangers via email
• do both of the same for the sales director

All the above could be in the form of a HTML table.  You could get this all done in one flow, if you have 25 sales agents and two mananger plus a sales director.  That would equate to 28 paralle branches.  Get one branch working the rest should follow easily.  Imagien being able process all the dta in less than 1m for all the calls made the day before whilst you are out of the office.

Look at the below for the SPL form issue.

https://www.youtube.com/watch?v=H4HyWJ9Seps&t

https://www.youtube.com/watch?v=H4HyWJ9Seps&list=PLTyFh-qDKAiE7C_2lVNAPzgHXsBykG0Tf

Please note I am on leave for sometime so may not respond to the above.

Regards,

Andrew

[AliceN]

@Andrew_J 

Incidentally, I do however have a an automation in place that does indeed drop the form data into a SPL already so I now have 2 possible solutions to chase. (Thanks to you @Andrew_J  & @RobElliott for these) 

As a side note: We have to use the M365 Forms as a standalone to prevent unscrupulous shenanigans from the more non tenured sales agents, so giving them the ability to view the SPL is a no-go unfortunately. 

[Andrew_J]

@AliceN 

Glad you found the above useful.

You can lock down access to the SPL to certain members of the team to say only SALES AGENTS, managers and sales director have access.  The standard data entry screen for SPL can aslo be configured I think be a complete web page once you have styled it better to fit the whole screen.   So using the MS forms should be the second choice I think.

Look at the folder permissions, split the sales team from the mannger and the same with the director, i.e. those lower down should not see the higer ups reports. 

Andrew

[Andrew_J]

@AliceN 

I have just read your last post, you should be able to control access of guest users (external sales agents) in a similar manner to the internal ones.  Although it maybe better to give the external sales agents a user account at your organisation.

As for permsions to the site you can deal with that quite easily in the Active Directory.  Then setting all the persmisons within the SPS, SharePoint Document Library and Lists using Flow see below:

https://www.youtube.com/watch?v=m1wYjAyJ_YU

https://www.youtube.com/watch?v=_Qnz4YeHses

You can even clsoe down the item level in the list

https://www.youtube.com/watch?v=vGSNrj82JcI&t

Although the above maybe too much.

With regard the data entry form in the SPList

https://www.youtube.com/watch?v=y_bdjMRHVmY&t

https://www.youtube.com/watch?v=H4HyWJ9Seps

I am going away for few weeks so I hope you are able to resolve your issue.

Andrew

[AliceN]

FOLLOW UP 

Thank you everyone for your support on this. You gave me some breadcrumbs to follow and it led me to an "almost" solution which works up until "Add a row into the table". I've read others' posts with similar issues to no resolve. 

I will repost the newest flow screens below.

NOTE: The additional compose actions are used to check output only.

FLOW RESPONSE ERROR:

The Flow up to this point succeeds:

Yes Branching..... (If No -N/A)