How to Connect a Google Form to a Google Sheet with Google Apps Script

Google Forms and Google Sheets are two powerful tools that can help you streamline your work and organize your data. Google Apps Script is a scripting language developed by Google that can help you automate tasks and create custom tools within your Google Workspace environment. In this article, we will discuss how to connect a Google Form to a Google Sheet using Google Apps Script.

Step 1: Create a Google Form

The first step is to create a Google Form. To do this, open Google Drive, click on the “New” button, and select “Google Forms” from the drop-down menu. You can then customize your form by adding questions, images, and other elements.

Step 2: Create a Google Sheet

Once you have created your Google Form, the next step is to create a Google Sheet to store the responses. To do this, click on the “Responses” tab in your form and then click on the “Create Spreadsheet” button. This will create a new Google Sheet with columns corresponding to each question in your form.

Step 3: Open the Script Editor

The next step is to open the Script Editor in Google Sheets. To do this, click on the “Tools” menu and select “Script Editor.” This will open a new window where you can write your script.

Step 4: Write the Script

The following script will automatically add new form responses to your Google Sheet:

function onFormSubmit(e) {
    var sheet = SpreadsheetApp.getActiveSheet();
    var rowData = [];
    for (var i = 0; i < e.values.length; i++) {
        rowData.push(e.values[i]);
    }
    sheet.appendRow(rowData);
}

This script uses the `onFormSubmit` trigger to run every time a new form response is submitted. It then retrieves the active sheet in the Google Sheet and pushes the values from the form response into a new row in the sheet using the `appendRow` method.

Step 5: Save and Test the Script

Once you have written the script, save it by clicking on the “Save” button. You can then test the script by submitting a test form response. To do this, click on the “Run” button in the Script Editor and select “onFormSubmit.” You should see a new row added to your Google Sheet with the test form response.

Step 6: Add Extra Functionality

The script we wrote above is a basic example of how to connect a Google Form to a Google Sheet, but you can add extra functionality to your script depending on your specific needs. For example, you can add conditional formatting to your Google Sheet or send email notifications when new form responses are submitted.

In conclusion, connecting a Google Form to a Google Sheet using Google Apps Script is a powerful way to streamline your work and organize your data. With just a few lines of code, you can automate the process of adding new form responses to your sheet, which can save you time and eliminate the risk of human error.