How to Setup Webhook in Google Form?
Tags: googlesheet, googleform, webhook, appscript
Published: 2024-05-01 16:49:42

If you aim to implement custom logic when a user submits a **Google Form**, this workaround is for you.


![1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tnuspqb2x2tfq8ejpcdm.png)

Our goal is to automate the two steps outlined in the previous diagram using Google Workspace APIs. In order to access these APIs, it is necessary to create a service account and enable both the **Google Forms** API and **Google Drive** API.

### Create Service Account & Enable APIs

- Log in to Google Cloud Console and create a project.
- Go to **IAM & Admin** > **Service Account** and create the service account.


![2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/60whp4fl5xnmyxdra0ib.png)

Assign Editor access to the service account from the basic roles.

- Create new key as JSON file.

![3](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/10pyi39jybo32446zsa9.png)

- Go to **APIs & Services** > **Enabled APIs Services** and enable Google Cloud API and Google Form API.


![4](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/n2lqzaqvaln0mjqakx7h.png)


![5](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/laxh4uwq0cfuzqjiqqit.png)

### Setup Google Form

- Create a **Google Form** and add the service account email as a collaborator ( edit access ).


![6](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fq4zz0a2wwrzu30v0498.png)

### Setup Server

- Create a web server using Express.
    
    ```jsx
    const express = require('express');
    const cors = require('cors');
    const { fetchForm, addFormToSheet, authenticateForm } = require('./utils');
    const app = express();
    const port = 3000;
    
    authenticateForm();
    
    app.use(cors());
    app.use(express.json({
        type: ["application/json", "text/plain"]
    }));
    
    // To upload Google Form ID to link to a Google Sheet
    app.post('/api/v1/form', async (req, res) => {
        const { formId } = req.body;
        console.log(formId);
        const data = await fetchForm(formId);
        const formName = data?.info?.title || "undefined";
        await addFormToSheet(formId, formName);
        res.status(200).json({
            message: "Form added to sheet",
        });
    });
    
    // Webhook url to get response when a user submit a form. We have to add this in AppScript
    app.post('/api/v1/webhook', async (req, res) => {
        console.log(req.body);
        res.status(200).json({
            message: "Webhook received",
        });
    });
    
    app.listen(port, () => {
        console.log(`Server is running on port ${port}`);
    });
    ```
    
    ```jsx
    const { google } = require("googleapis");
    const path = require("path");
    const axios = require("axios");
    const FormData = require("form-data");
    const {config} = require("dotenv");
    
    config();
    
    let forms;
    const authenticateForm = async () => {
      const auth = new google.auth.GoogleAuth({
        keyFile: "./key.json", // service account key that we downloaded from Google Cloud Console
        scopes: [
          "https://www.googleapis.com/auth/forms.responses.readonly",
          "https://www.googleapis.com/auth/forms.body.readonly",
          "https://www.googleapis.com/auth/forms.body",
          "https://www.googleapis.com/auth/drive",
        ],
      });
    
      const client = await auth.getClient();
      forms = google.forms({ version: "v1", auth: client });
      console.log("google form authenticated");
    };
    
    const fetchForm = async (formId) => {
      const data = await forms.forms.get({
        formId,
      });
      return data.data;
    };
    
    const addFormToSheet = async (formId, formName) => {
      const data = new FormData();
      data.append("formId", formId);
      data.append("formName", formName);
      // APP_SCRIPT_URL from the ENV file added in the same folder.
      // We will get this URL after the deploy the AppScript
      const response = await axios.post(process.env.APP_SCRIPT_URL, data, {
        headers: {
          "Content-Type": "multipart/form-data",
        },
      });
    
      console.log(response.data);
    };
    
    module.exports = {
      authenticateForm,
      fetchForm,
      addFormToSheet,
    };
    
    ```
    
- Make the server publicly available using ngrok. Checkout the [link ](https://dev.to/amjadcp/how-web-technology-works-part-01-4ic8#server-in-internet) to setup ngrok.

### Setup Google Sheet

- Create a Google Sheet and set the AppScript logic to link Google Form to the **Google Sheet** and trigger a webhook while user submit the form.
    
    
![7](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/v4by8yef30sd0605ymfq.png)
    
    
![8](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wprr4h1uodxnpdmkpg0p.png)
    

- Use the AppScript provided below.

```jsx
// This function will trigger when you do the post request using the AppScript deploy link
function doPost(e) {
  var formId = e.parameter.formId; // Google Form ID send in POST request
  var sheetName = e.parameter.formName; // Google Form title send in POST rquest
  var form = FormApp.openById(formId);

  // Replace with your sheet ID
  var sheetId = 'sheet_id';

  // Link the form to the sheet
  form.setDestination(FormApp.DestinationType.SPREADSHEET, sheetId);

  // Get all sheets in the spreadsheet
  var spreadsheet = SpreadsheetApp.openById(sheetId);
  var sheets = spreadsheet.getSheets();

  // set the sheet name as the form title
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].getName().startsWith('Form')) {
      sheets[i].setName(sheetName);
      break;
    }
  }

  return ContentService.createTextOutput("Done");
}

// This function will trigger when a user submits any of the linked form
function onFormSubmit(e) {
  // Get the active spreadsheet and the first sheet
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // Get the headers (field names)
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];

  // Get the response values
  var values = e.values;

  // Create an object with the field names and values
  var data = {};
  for (var i = 0; i < headers.length; i++) {
    data[headers[i]] = values[i];
  }
  console.log(data);

  // Get the form URL
  var formUrl = e.range.getSheet().getFormUrl();

  // Extract the form ID from the form URL
  var formId = formUrl.split('/forms/d/')[1].split('/')[0];
  console.log('Form ID: ' + formId);

  // Add the form ID to the data
  data['formId'] = formId;

  // Prepare the options for the HTTP request
  var options = {
    method: 'post',
    headers: { 'Content-Type': 'application/json' },
    payload: JSON.stringify(data)
  };

  // Send the HTTP request. Replace the URL with your server webhook url (url from ngrok)
  UrlFetchApp.fetch('webhook_url', options);
}
```

The selected part of the **Google Sheet** URL is the sheet ID.




![9](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/na3fqbpr47d9vaku756y.png)

- After saving the AppScript go to the trigger section and create the trigger to execute the **onFormSubmit** function when a row entry happens  in sheet.


![10](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q7tm6pc9nczocdfbo5gd.png)

When you clicks the save button you have to authorize with your Google account.


![11](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jokfa417z5vpeawmz2yj.png)

- Deploy the AppScript


![12](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kcpm8kvxzlfoigqjw7vk.png)

![12](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vwhn2e22gr0vkw5xfgbo.png)

![13](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xpo9lv3bl6e4qhux4hp6.png)


After clicks the deploy button, you will get the AppScript URL and update in your server ENV file and restart the server .


![14](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ociiy6lcrq9xu4qk0pvj.png)

### Testing

- Upload Google Form ID using the API to link with Google Sheet.



![15](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/38cxskf6tgkbaapowhzy.png)

The selected part of the Google Form URL is the **Google Form** ID



![16](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ouxyfkxlcm9lwnc9yvmi.png)

- Submit a response using the form you will get the form response in your terminal.



![17](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xo6kqalslz0ocy92zau6.png)



![18](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m7fzamm44hfcnswzdkrf.png)



![19](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wwigcas0b5tuwawihr83.png)

You can link multiple forms to a sheet using the Form ID Upload API. Responses from these forms can be found using the "formId" key in the webhook.

### NOTE

- Upon calling the Google Workspace API for the first time, you may receive an unauthorized error due to a delay in API enablement or service account setup.
- If you still encounter the error, try changing the service account role to owner.