Is there a way of making microsoft form that grabs specific data from excel spreedsheet

%3CLINGO-SUB%20id%3D%22lingo-sub-1151633%22%20slang%3D%22en-US%22%3EIs%20there%20a%20way%20of%20making%20microsoft%20form%20that%20grabs%20specific%20data%20from%20excel%20spreedsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1151633%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EIs%20there%20a%20way%20of%20making%20a%20form%20that%20grabs%20specific%20data%20from%20excel%20spreedsheet%20after%20user%20enters%20a%20id%20%23%20would%20grab%20all%20data%20on%20that%20row%20and%20update%20the%20form%20baseed%20on%20whats%20missing%3F%20and%20then%20a%20user%20can%20add%20Missing%26nbsp%3B%20info%20only%20to%20form.%20then%20that%20would%20update%20the%20google%20sheets%20with%20new%20info%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1151633%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EForms%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMicrosoft%20Forms%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1151799%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20there%20a%20way%20of%20making%20microsoft%20form%20that%20grabs%20specific%20data%20from%20excel%20spreedsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1151799%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F545349%22%20target%3D%22_blank%22%3E%40rbadtke%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Enope%20...%20not%20really.%20Forms%20are%20just%20for%20capture%20...%20query%20or%20update.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20that%20said%20..%20people%20are%20really%20starting%20to%20push%20the%20boundary%20of%20what%20can%20be%20done%20with%20FormsPro.%20There%20might%20be%20some%20crazy%20backdoor%20someone%20has%20found%20that%20uses%20variables%20and%20branching%20rules%20to%20get%20close.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20maybe%20that's%20just%20wishful%20thinking%20on%20my%20part.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1155041%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20there%20a%20way%20of%20making%20microsoft%20form%20that%20grabs%20specific%20data%20from%20excel%20spreedsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1155041%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F545349%22%20target%3D%22_blank%22%3E%40rbadtke%3C%2FA%3E%26nbsp%3B-%20potentially%2C%20although%20as%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F484537%22%20target%3D%22_blank%22%3E%40CYappert%3C%2FA%3E%26nbsp%3Bsays%2C%20it's%20not%20designed%20to.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou've%20mentioned%20Excel%20AND%20Google%20Sheets%20-%20and%20Forms.%20So%2C%20for%20this%20example%2C%20I%20am%20going%20with%20Google%20Sheets%20and%20Forms%20Pro%20to%20explain%20what%20you%20COULD%20potentially%20do%2C%20but%20it's%20messy%20and%20not%20ideal.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20would%20need%20to%20set%20up%20a%20Forms%20Pro%20survey%20with%20variables.%20Challenge%20is%2C%20you%20can%20only%20have%2015%20and%20two%20of%20them%20are%20already%20there%20by%20default%20which%20you%20can't%20remove%20(First%20Name%2C%20Last%20Name).%20Your%20other%20variables%20would%20be%20the%20items%20that%20you%20need%20the%20user%20to%20fill%20out%20and%20the%20default%20would%20say%20something%20like%20'Missing%20Data'.%20You%20would%20ad%20the%20variables%20to%20the%20questions.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20Power%20Automate%2C%20you%20would%20need%20to%20determine%20your%20trigger%20for%20when%20the%20survey%20invitation%20should%20be%20sent%20to%20someone%2C%20then%20use%20an%20action%20to%20get%20your%20Google%20Sheets.%20Then%20use%20an%20action%20to%20create%20the%20Send%20Survey%20or%20Create%20an%20Invitation%20step%20from%20the%20Forms%20Pro%20connector.%20Then%20you%20would%20be%20able%20to%20populate%20all%20of%20the%20variables%20from%20your%20various%20fields%20in%20the%20Google%20Sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20the%20user%20gets%20the%20link%20for%20the%20survey%20and%20opens%20it%2C%20if%20there%20was%20data%20in%20the%20field%2C%20it%20would%20show%2C%20if%20not%2C%20it%20would%20show%20the%20default%20of%20'Missing%20Data'.%20Your%20questions%20can%20then%20be%2C%20Is%20there%20Missing%20Data%3F%20If%20yes%2C%20display%20another%20text%20field%20for%20them%20to%20fill%20out.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EAgain%2C%20it's%20messy%20and%20I%20don't%20think%20it's%20ideal%2C%20but%20that%20is%20a%20way%20around%20what%20you%20want%20to%20do.%20Check%20out%20this%20post%20for%20information%20on%20the%20variables%20if%20you%20are%20not%20sure%20what%20I%20mean%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fmeganvwalker.com%2Fextracting-survey-variables-from-forms-pro-responses-without-access-to-cds%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fmeganvwalker.com%2Fextracting-survey-variables-from-forms-pro-responses-without-access-to-cds%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1157612%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20there%20a%20way%20of%20making%20microsoft%20form%20that%20grabs%20specific%20data%20from%20excel%20spreedsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1157612%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F370753%22%20target%3D%22_blank%22%3E%40Megan_V_Walker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20..%20very%20creative%20..%20I%20can%20see%20how%20I%20might%20use%20this%20in%20a%20create%2Fupdate%20cycle%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20see%20a%20version%20of%20the%20Survey%20that%20had%20all%20of%20the%20Required%20questions%20that%20you%20use%20for%20the%20initial%20capture%20of%20the%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20have%20a%20second%20version%20of%20the%20form%20(using%20the%20variable%20approach%20you%20describe)%20where%20each%20question%20provided%20the%20past%20answer%20and%20the%20same%20questions%20(configured%20as%20not%20required)%20with%20a%20variable%20driven%20label%20or%20subtitle%20that%20invites%20the%20user%20to%20%22Change%22%20the%20response.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20using%20PowerAutomate%20...%20Merge%20the%20two%20data%20sets%20based%20on%20actual%20changes%20and%20push%20the%20data%20into%20whatever%20repository%20you%20need.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20bit%20of%20work%20..%20but%20it%20could%20be%20templated%20for%20a%20general%20case%20using%20some%20of%20the%20examples%20you%20have%20put%20together%20Megan.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBoth%20forms%20would%20have%20to%20be%20simple%20and%20limited%20in%20question%20count%20but%20it%20might%20work%20for%20simple%20data%20capture.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOh%20this%20is%20fun%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Is there a way of making a form that grabs specific data from excel spreedsheet after user enters a id # would grab all data on that row and update the form baseed on whats missing? and then a user can add Missing  info only to form. then that would update the google sheets with new info?

3 Replies

@rbadtke 

 

nope ... not really. Forms are just for capture ... query or update.

 

With that said .. people are really starting to push the boundary of what can be done with FormsPro. There might be some crazy backdoor someone has found that uses variables and branching rules to get close.

 

But maybe that's just wishful thinking on my part.

@rbadtke - potentially, although as @CYappert says, it's not designed to. 

 

You've mentioned Excel AND Google Sheets - and Forms. So, for this example, I am going with Google Sheets and Forms Pro to explain what you COULD potentially do, but it's messy and not ideal.

 

You would need to set up a Forms Pro survey with variables. Challenge is, you can only have 15 and two of them are already there by default which you can't remove (First Name, Last Name). Your other variables would be the items that you need the user to fill out and the default would say something like 'Missing Data'. You would ad the variables to the questions. 

 

Using Power Automate, you would need to determine your trigger for when the survey invitation should be sent to someone, then use an action to get your Google Sheets. Then use an action to create the Send Survey or Create an Invitation step from the Forms Pro connector. Then you would be able to populate all of the variables from your various fields in the Google Sheet.

 

When the user gets the link for the survey and opens it, if there was data in the field, it would show, if not, it would show the default of 'Missing Data'. Your questions can then be, Is there Missing Data? If yes, display another text field for them to fill out. 


Again, it's messy and I don't think it's ideal, but that is a way around what you want to do. Check out this post for information on the variables if you are not sure what I mean:

https://meganvwalker.com/extracting-survey-variables-from-forms-pro-responses-without-access-to-cds/

 

 

@Megan_V_Walker 

 

See .. very creative .. I can see how I might use this in a create/update cycle

 

I can see a version of the Survey that had all of the Required questions that you use for the initial capture of the data.

 

Then have a second version of the form (using the variable approach you describe) where each question provided the past answer and the same questions (configured as not required) with a variable driven label or subtitle that invites the user to "Change" the response.

 

Then using PowerAutomate ... Merge the two data sets based on actual changes and push the data into whatever repository you need.

 

A bit of work .. but it could be templated for a general case using some of the examples you have put together Megan.

 

Both forms would have to be simple and limited in question count but it might work for simple data capture.

 

Oh this is fun