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

(0) Share

Report

Posted on by XI-19080853-0

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. excel 1.jpg

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. excel 1.jpg excel 2.jpg excel 3.jpg

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!

All responses (14)

Answers (1)

TRINITY123 2 on at

Like (0)

Report

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

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
Community Power Pla... on at

Like (0)

Report

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

Thanks a lot, I had the same issue, followed your instruction and DONE!
livfrias 2 on at

Like (0)

Report

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

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!
nihalwani_raj 8 on at

Like (0)

Report

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

can u please explain the step, how did you get the TOTAL value.
XI-19080853-0 17 on at

Like (0)

Report

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

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...
LeeHarris 1,026 on at

Like (0)

Report

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

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.
XI-19080853-0 17 on at

Like (0)

Report

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

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!!
Verified answer

LeeHarris 1,026 on at

Like (2)

Report

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

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?
XI-19080853-0 17 on at

Like (0)

Report

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

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 1,026 on at

Like (0)

Report

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

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.