In this #PowerShot, I will show you how to create partially filled Google Forms and send those to users using Power Automate. We will also look at a setup to receive the new/ edited responses from the users and update them in the data source.
Let's Get Started
One of the limitations we have come across setting Power Platform solutions is to send partially filled forms and get responses on those and further update the database where all the information is stored. In this post, we will look at one such example where the requirement is for sending information to a user that already exists in the system and asking them to update in case there are any changes. We will use an excel file where we have a table with the email, name and phone number of a person. The email column is being used as the identifier and the person will be sent an email with the link to the prefilled form on the email ID that already exists in the system. The user will then review the information on the form by clinking the link and make any changes and finally submit. We will setup another flow that receives this information and updates the master data.
PART 1 (Setting up Google Forms and Trigger)
Step 1: Create a form in Google Forms. For the purpose of this tutorial, I have created a sample form as in the screenshot. (DataUpdateForm)
Step 2: Once the form is created, logon to https://script.google.com/ and create a new project. Here, we will add the code snippet to collect the form response and send it over to a flow in Power Automate.
Step 3: On https://script.google.com/ editor copy the below snippet and paste there. Ensure that you fill up the form ID from the URL in the commented out area in the code snippet and also update the Flow trigger URL in the UrlFetchApp function.
function setUpTrigger(){
ScriptApp.newTrigger('sendPostRequest')
.forForm('yourGoogleFormIDHere') //can be found in the form URL
.onFormSubmit();
}
function sendPostRequest(){
var form = FormApp.openById('yourGoogleFormIDHere')
var responses = form.getResponses()
var formDataLast=responses[responses.length - 1]
var formData=formDataLast.getItemResponses()
var sendData =[]
var emailUser = {"email":formDataLast.getRespondentEmail()}
sendData.push(emailUser)
for(var k=0; k<formData.length;k++){
var newTemp = formData[k]
var key = newTemp.getItem().getTitle().toString()
var val =newTemp.getResponse()
var keyVal = {[key]:val}
sendData.push(keyVal)
}
Logger.log(JSON.stringify(sendData))
var options = {
'method' : 'post',
'contentType': 'application/json',
'payload' : JSON.stringify(sendData)
};
UrlFetchApp.fetch('urlOfTheSecondFlowHTTPTriggerHere', options); //Second flow in this blog
}
Step 4: Save and test the function and ensure that the HTTP triggered flow triggers and gets the data appropriately.
You can also check out these references for more details and information:
PART 2 (Power Automate)
Flow 1: To send partially filled form
In this flow we will first get data from our data source and then create the partially filled form link and send it over to the users. The form will be prefilled with data mapped to the user in the data source.
Step 1: Trigger: Manual (this can be updated/ changed based on the use case)
Step 2: Action: List rows present in a table from Excel- to get the data rows from excel for the user and construct the partially filled form URL.
Step 3: Control: Apply to Each- to iterate over each row in the excel table.
//Loop Starts
Step 4: Action: Compose- to construct the partially filled form URL.
//First get a sample prefilled form URL. Instructions here.
Step 5: Action: Send an Email using Outlook- to send the email to the user with the link to the prefilled form based on the data that is mapped to this user previously.
//Loop Ends
Flow 2: To receive the form and update data
In this flow, we will receive the updated data and then update our data source based on that.
Step 1: Trigger: When an HTTP request is received- To trigger the flow when the form is submitted on Google Forms.
Step 2: Action: Update a row in an Excel table- To update the row corresponding to the user data. The identifier being used here is the form. The data from the form is being received in an array and so we are using the first() function to access the specific keys from the first item in the array. (Note that the array will have one item only). There can be further logic added here to validate the data and also have flag columns set in the table to mark that the row was updated.
Step 3: Action: Response- to send the status code back.
Setup in Action
In this post, we saw how to setup partially filled forms with Google Forms and automate sending the form and receiving responses from Power Automate. This is a cool hack and a quick work around when you have a use case to get certain information updated from the users that are outside of the organization.
I hope you found this interesting and it helped you. Thank you for reading!