How to Setup Webhook in Google Form?

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

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.

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

• Create new key as JSON file.

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

Setup Google Form

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

Setup Server

• Create a web server using Express.

  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}`);
  });
  

  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 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.

• Use the AppScript provided below.

// 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.

• 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.

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

• Deploy the AppScript

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

Testing

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

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

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

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.