Feb 04 2020
- last edited on
Aug 06 2023
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?
Feb 04 2020 10:20 PM
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.
Feb 06 2020 01:48 AM
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:
Feb 06 2020 09:50 PM
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