Flow to calculate value from Forms input and send it by mail (using excel/sharepoint)

[Xavier_I]

Hi all,

first time posting, hope you can help me out.

I'm building a flow from a MS Forms that would calculate an estimated value from the inputs given in the form and return it by mail to the requestor.

The calculations are a bit complex, with conditionals, lookups... not simple operations (otherwise i could try directly in sharepoint).

I had the idea to conect the form to excel for the calculations and send a mail with the value when the file is modified, or update a sharepoint from excel with the calculated values, that would trigger the mail notification.

What i have done so far:

1- Created a Form online connected to an excel sheet (in onedrive for business)

2- For every new answer in forms a new row is created in excel, a set of formulas calculates then the final value on the last column of each row. Another sheet of the excel contains a matrix with the information for the calculations wich is working properly.

The sheet that gets the rows from forms and has the formulas is formatted as a Table.

Up to this point it's working properly.

Where i get problems is on the next step:

OPTIONS:

3a- Make a flow to send a mail directly with the excel values. Problem is it sends a mail for each row of the excel table instead of only the last one, so it will spam the requestor with a lot of mails.

3b- Make a flow to create new items in sharepoint while the row is also sent to excel (calculation will be done in excel). After that, update the sharepoint with the values from excel. Then send mail notification when sharepoint is updated. On this one I am unable to relate the row in sharepoint with the one in excel to update only the new one. 

Can anyone help me find a solution for either point 3a or 3b, any of them would solve the problem.

Attached some screenshots, probably i'm doing something wrong as i'm not an expert but i can't find the solution.

Thanks!

[LeeHarris]

Hi @Xavier_I

Try adding a new column to your Excel table called "ISLAST" and populate it with the following formula (where Table1 is the name of your table).

=IF(ROW()-ROWS(Table1)=1,"TRUE","FALSE")

You should then be able to remove the step in your flow that lists all table rows and just use the Get a Row action. Change your KEYCOLUMN to 'ISLAST' and the KEYVALUE to 'TRUE' in order to locate the last row each time. This should also remove some of the Apply to Each loops you currently have as they will no longer be necessary.

[v-zhos-msft]

Hi Xavier_I,

Accroding to your discription, you want to get the last Column value together. 

Then send them in one email,right?

I‘m afraid Flow can't get an entire column right now.

You can send the table via the create csv Table action.

Best Regards,

Zhongys

[Xavier_I]

Hi LeeHarris,

thanks a lot for your help.

I did some testing with your suggestion:

The formula works nice in excel but when the flow runs i get an error that no value "true" was found on the file.

If i look at the excel, ony the last row contains "true" on that column.

I tried to add more rows but still no result, then i manually modified the excel file and added a "True" value in the column created for this on the second row out of 10.

The flow worked and sent the mail with the correct cell value for row 2.

It's like the flow would not recognize the last row added to the excel table, any ideas why this might happen?

Is this because of the timing?

[Xavier_I]

Hi v-zhos-msft,

thanks for your comment, maybe my first description was a bit complex.

I don't need the entire column, what i need is one cell from one column from the last row that was modified.

[LeeHarris]

Hi @Xavier_I

Are you updating and reading the Excel in the same Flow? I have had timing issues in the past where the calculations/update of the spreadsheet don't happen quick enough and don't get picked up by the process. You could try adding in a Delay action. This would help you confirm whether or not it is due to timing.

[Xavier_I]

Hi,

the excel file is upgraded from Forms directly (created a MSform from excel online and it connected them automatically).

What should happen is:

1- New form entry--> new row in excel (calculating all values with formulas) -- Working OK

2- Flow triggers when excel file is modified--> get the value of one cell from the last row -- -Not working

3- Send that value by mail

I cleaned the flow to make it simpler, see screenshot.

I changed the value on the formula you provided from "TRUE"-->"Good_value"

The flow provides the following error: 

{

"status": 404,

"message": "No row was found with Id 'Good_value'.\r\nclientRequestId: b3cec8db-15e9-48d4-a290-2af5d8868b9f",

"source": "excelonline-wus.azconn-wus.p.azurewebsites.net"

}

When i manually change this value it works (writing manually the text "Good_value" in the proper column).

Delay did not make a difference, i tried with up to 1 minute, also if i open the excel it gets updated in like 2-3 seconds from the submission of the form.

I thought formulas were supported for Excel (business) connectors, can this be the problem?

or the formatting of the excel cells? (i'm a bit tied there as the table autoformats the new rows)

Any help or ideas will be appreciated.

[LeeHarris · Accepted solution]

Hi @Xavier_I

My guess is that the issue here is with the submission of the values from MS Forms into Excel. Perhaps this is not triggering the formula calculations correctly.

I have previously set up a similar process, but rather than link the Form direct to Excel I used the new Form submission trigger in Flow. I inserted the new Excel row using an action step in the Flow, added a delay to allow it to Save, then retrieved the row back out from Excel with the Get Row action, at which point the calculations had occured. Is this something you are able to test in your environment? 

[Xavier_I]

Hi Leeharris,

you're 100% right.

I reworked the flow capturing the values from forms with FLOW instead of creating the form from excel online and everything worked.

(Also i could clean the excel file of rows created by that "link")

Thanks a lot for you support!!

[LeeHarris]

Hi @Xavier_I

No problem, glad you got it working.

As you are now editing and reading the excel in the same Flow I would suggest changing the degree of parallelism in the trigger settings so that there can only be one running instance of the Flow at any time. This prevents issues occuring if you have multiple Form submissions within a short period of time. The Flow will run to completion before the next instance is triggered.

[Xavier_I]

That's a really nice advice!

Changed the settings already, probably the form will not get to that point as service will not be used that much but you never know...

[nihalwani_raj]

can u please explain the step, how did you get the TOTAL value.

[livfrias]

Hey @Xavier_I 

I found this discussion while looking for a way to do exactly what you've done: 1. connect forms to excel 2. calculate results in excel and 3. trigger e-mail with said results. Although I understand the general concepts, I'm finding hard to develop the step by step. I was able to connect form to excel, but I am struggling in the 2nd part. Do you know any tutorials or could you share how you made the excel calculations and the process so on?

I would be super grateful!

[Anonymous]

Thanks a lot, I had the same issue, followed your instruction and DONE! 

[TRINITY123]

Hi

Could you provide a screen shot of the full flow from start to finish please?

I'm a novice and attempting to build a similar Flow, but need my hand held!

I'm looking to pull the Forms results, convert to numerical answer for each question (each answer with a different formula) the output to total the scores and give 1 of 2 actions.  (above 20 = High risk) (below 20 = low risk).

Ideally then an email with the action/advice.

Appreciate any help you can give, meanwhile I'll keep trying 🙂

Thanks