Save Google Form Data to SharePoint List

Introduction

Google Forms is a popular tool for gathering data through customizable surveys and forms, while SharePoint provides a robust platform for centralized data storage, collaboration, and advanced data management. By integrating Google Form data with a SharePoint list, you can automate the transfer of form responses into a structured, accessible format within SharePoint.

This integration allows organizations to capture valuable input through Google Forms and systematically store it in SharePoint lists. This not only centralizes data for better visibility and management but also enables efficient collaboration and reporting within SharePoint.

In this process, you’ll utilize Power Automate to bridge the gap between Google Forms and SharePoint, creating an automated workflow that ensures data is accurately and promptly transferred. This approach minimizes manual effort, reduces errors, and enhances the overall efficiency of data handling.

By connecting Google Forms with SharePoint lists, you harness the power of both platforms to improve data accuracy, streamline operations, and facilitate better decision-making.

1. Create a Google form
   
2. From the Top right corner, click on “Get pre-filled link”.
   
3. Fill in all the required data and click on the “Get link” button at the bottom.
   
4. You will get a pop-up at the left bottom corner. Click on the “COPY LINK” button, link will be copied in the clipboard.
   
5. Now create a new flow for saving form data to the SP list with the “HTTP request received” trigger. Save a flow and it will generate POST Url.
   
6. Copy that POST URL and replace it with the form code. Go to the Google form and from the upper right corner select script editor, it will open the code page. Add the following function to it. Replace the form ID at yellow color places and flow Post Url( which is copied in the last step) to blue color text.

   function setUpTrigger() {
     ScriptApp.newTrigger('sendPostRequest')
       .forForm('Google Form ID') // can be found in the form URL
       .onFormSubmit();
   }
   
   function sendPostRequest() {
     var form = FormApp.openById('Google Form ID');
     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('Flow Trigger URL', options); // Second flow in this blog
   }

7. For deployment of the form, on the same script editor page, from the top right corner select New Deployment.
   
8. Select the web app and if you want you can change the settings from the drop-down and then click on the Deploy button.
   
9. You can configure the web app on the next page. Like deployment type and triggering automatically and schedule of running the web app. From the left side, pannel select Trigger.
   
10. From the bottom right corner, select Add Trigger, it will open a pop-up to configure the trigger.
    
11. Now, In your SharePoint site, Create a SharePoint list with only one field Email.
    
12. Now Go to the power Automate flow and add Compose Action to get the details of form from the trigger body. To parse the data add Parse JSON action and add Schema.
    
13. Add Compose action to extract Email and Attachment from Parse JSON action output. E.g. body('Parse_JSON')[1]?['Email'] and body('Parse_JSON')[2]?['Attachment']. Add Create item action to create a list item as per the form data.
    
14. Add a Condition to check if the attachment is not null, and then add the attachment to the list item created in the previous item.