Triggering App Integration from Google Forms - A detailed guide

[carloscabral]

Integrating Google Forms with Google Cloud Pub/Sub and App Integration via Apps Script: A Detailed Guide

Esse artigo está disponível em Português aqui. 

In this article, I'll talk very little about Application Integration! Just know that the goal here is to trigger an integration in App Integration when a Google Form is submitted (taking, of course, the Form's content as input for the integration). The bridge here will be made via Pub/Sub. In other words, the objective is to deliver the submitted Form's content to a Pub/Sub topic. Once that's done, you just need to create an integration that uses the PubSub Trigger. Let's get started!

Google Forms is a fantastic tool for collecting data, but what if you need to process these responses in real-time or integrate them with more complex backend systems? A powerful solution is to send the data from each response to a Google Cloud Pub/Sub topic as soon as the form is submitted. This opens doors for real-time data processing, analysis, triggering App Integration, and much more.

In this post, we will detail how to set up a script in Google Apps Script to automatically publish data from a Google Form to a Pub/Sub topic, with a special focus on permission details and environment configuration.

Prerequisites:

• Google Account: Needed to create the Google Form and the Google Cloud project.
• Google Cloud Platform (GCP) Project: An active project where you will create the Pub/Sub topic and enable the necessary APIs.
• Google Form: The form you want to integrate.

Step 1: Google Cloud Project (GCP) Configuration

Before touching the script, we need to prepare the environment in GCP.

1. Select or Create a GCP Project: Access the Google Cloud Console and select the project you want to use or create a new one. Note the Project ID, as you will need it in the script.
2. Enable the Pub/Sub, Google Forms, and AppsScript APIs:
   • In the navigation menu, go to "APIs & Services" -> "Library".
   • Search for "Cloud Pub/Sub API" and click "Enable". If it's already enabled, great.
   • Do the same for "Google Forms API" and "Google Apps Script API".
3. Create a Pub/Sub Topic:
   • In the navigation menu, go to "Pub/Sub" -> "Topics".
   • Click "Create Topic".
   • Give your topic a name (e.g., form-submission). Note this Topic ID.
   • Keep the other default settings for now and click "Create".

Step 2: Google Apps Script Configuration

Now, let's connect our Google Form to the script and our GCP project.

1. Open the Script Editor: Open your Google Form, click the three vertical dots (More) in the top right corner, and select "Script editor".
2. Associate the Script with the GCP Project: This is a crucial step for the script to use GCP services and obtain the correct authentication token.
   • In the script editor, click the gear icon ("Project Settings") in the left sidebar.
   • Check the box "Show "appsscript.json" manifest file in editor".
   • Scroll down to the "Google Cloud Platform (GCP) Project" section.
   • Click "Change Project".
   • Paste the GCP Project Number (you can find this on your project's dashboard in the Cloud Console, usually below the Project ID) and click "Set Project".
   • Confirm the change. This ensures that ScriptApp.getOAuthToken() will request a token with permission to interact with your configured GCP project.

Step 3: The Apps Script Code

Copy and paste the following code into the script editor (usually in the Code.gs file).

JavaScript

/**
 * Configuration: Replace with your values!
 */
const GcpProjectId = 'YOUR_GCP_PROJECT_ID'; // <-- Replace with your GCP project ID
const PubSubTopicName = 'YOUR_PUBSUB_TOPIC_ID'; // <-- Replace with your Pub/Sub topic ID

/**
 * This function is triggered when a form is submitted.
 * @param {GoogleAppsScript.Events.FormsOnSubmit} e The form submission event object.
 */
function onFormSubmit(e) {
  try {
    // Get the form response from the event object
    const formResponse = e.response;
    if (!formResponse) {
      Logger.log('Event object does not contain a form response.');
      return;
    }

    // Get all item responses from the submission
    const itemResponses = formResponse.getItemResponses();

    // Create a data object to store the form values
    const formData = {
      timestamp: formResponse.getTimestamp().toISOString(), // Submission timestamp in ISO format
      respondentEmail: formResponse.getRespondentEmail() || 'anonymous', // Respondent's email (if collected and allowed)
      responses: {} // Object to map questions and answers
    };

    // Map form questions to answers
    itemResponses.forEach(itemResponse => {
      const question = itemResponse.getItem().getTitle(); // Get the question text
      const answer = itemResponse.getResponse(); // Get the answer
      formData.responses[question] = answer; // Add to the responses object
    });

    // Send to Pub/Sub using direct HTTP call with OAuth token
    const result = publishToPubSub(formData);
    Logger.log('Message published to Pub/Sub: ' + result);

  } catch (error) {
    // Log any errors that occur during the process
    Logger.log('Error in onFormSubmit: ' + error.toString() + '\nStack: ' + error.stack);
  }
}

/**
 * Publishes a message to Pub/Sub using the Apps Script OAuth token.
 * @param {Object} data The data object to be published.
 * @return {string} The response body from the Pub/Sub API on success.
 * @throws {Error} If the publication fails.
 */
function publishToPubSub(data) {
  // Basic validation of constants
  if (!GcpProjectId || GcpProjectId === 'YOUR_GCP_PROJECT_ID') {
      throw new Error('GcpProjectId is not configured. Edit the script and replace the placeholder.');
  }
  if (!PubSubTopicName || PubSubTopicName === 'YOUR_PUBSUB_TOPIC_ID') {
      throw new Error('PubSubTopicName is not configured. Edit the script and replace the placeholder.');
  }

  // Prepare the message for the Pub/Sub API
  const pubsubData = {
    messages: [
      {
        // Data needs to be Base64 encoded
        data: Utilities.base64Encode(JSON.stringify(data))
      }
    ]
  };

  // Get the OAuth token from the Apps Script environment.
  // THIS IS CRUCIAL: The token belongs to the USER authorizing the script.
  // This user MUST have permission to publish to the specified Pub/Sub topic
  // in the GCP project associated with the script.
  // The required IAM permission is typically 'roles/pubsub.publisher'.
  const token = ScriptApp.getOAuthToken();

  // Build the Pub/Sub API endpoint URL
  const pubsubUrl = `https://pubsub.googleapis.com/v1/projects/${GcpProjectId}/topics/${PubSubTopicName}:publish`;

  // Configure the HTTP request options
  const options = {
    method: 'post', // HTTP POST method
    contentType: 'application/json', // Payload content type
    // Payload must be a JSON string
    payload: JSON.stringify(pubsubData),
    headers: {
      // Authentication using the obtained OAuth token
      Authorization: 'Bearer ' + token
    },
    muteHttpExceptions: true // Important: Catches HTTP errors for manual handling
  };

  // Make the call to the Pub/Sub API using UrlFetchApp
  const response = UrlFetchApp.fetch(pubsubUrl, options);

  // Check the HTTP response status code
  const responseCode = response.getResponseCode();
  const responseText = response.getContentText();

  // If the response is successful (2xx codes)
  if (responseCode >= 200 && responseCode < 300) {
    Logger.log('Pub/Sub API Response (Success): ' + responseText);
    return responseText; // Return the response body (usually contains messageIds)
  } else {
    // If there's an error, log details and throw an exception
    Logger.log(`Error publishing to Pub/Sub. Code: ${responseCode}. Response: ${responseText}`);
    throw new Error(`Failed to publish to Pub/Sub: ${responseText} (Code: ${responseCode})`);
  }
}

/**
 * Sets up the 'onFormSubmit' trigger for the active form.
 * This function must be run manually ONCE from the script editor.
 */
function createFormSubmitTrigger() {
  // Remove old triggers to avoid duplicates (optional, but recommended)
  const currentTriggers = ScriptApp.getProjectTriggers();
  currentTriggers.forEach(trigger => {
    if (trigger.getHandlerFunction() === 'onFormSubmit' &&
        trigger.getEventType() === ScriptApp.EventType.ON_FORM_SUBMIT) {
      ScriptApp.deleteTrigger(trigger);
      Logger.log('Existing onFormSubmit trigger removed.');
    }
  });

  // Get the form the script is bound to
  const form = FormApp.getActiveForm();

  // Create a new trigger that calls 'onFormSubmit' when the form is submitted
  ScriptApp.newTrigger('onFormSubmit')
    .forForm(form)
    .onFormSubmit()
    .create();

  Logger.log('onFormSubmit trigger created successfully.');
}

Detailed Code Explanation:

• Global Constants (GcpProjectId, PubSubTopicName): It is essential that you replace the placeholder values 'YOUR_GCP_PROJECT_ID' and 'YOUR_PUBSUB_TOPIC_ID' with your actual IDs obtained in Step 1.
• onFormSubmit(e):
  • This function is the trigger. It is automatically executed by Google Apps Script whenever the linked form receives a new submission.
  • e: The event object containing information about the submission, including e.response.
  • formResponse.getItemResponses(): Gets an array containing each question and its corresponding answer.
  • formData: We create a structured JSON object to send to Pub/Sub. It includes:
    • timestamp: Submission date and time in standard ISO 8601 format.
    • respondentEmail: The email of the user who responded (if email collection is enabled in the form and the user allows it). Otherwise, 'anonymous'.
    • responses: An object where each key is the question title and the value is the given answer.
  • The forEach loop iterates over the responses, extracting the question title (item.getTitle()) and the answer (itemResponse.getResponse()) and populating the formData.responses object.
  • Calls the publishToPubSub function to send the collected data.
  • try...catch: Captures and logs any errors that might occur during response processing.
• publishToPubSub(data):
  • This function is responsible for direct communication with the Pub/Sub API.
  • Prepares the payload (pubsubData) in the format expected by the Pub/Sub API: an object with a messages key, which is an array containing message objects. Each message has a data key whose value must be the actual payload encoded in Base64. We use Utilities.base64Encode(JSON.stringify(data)) for this.
  • ScriptApp.getOAuthToken(): This is the key point for authentication. It returns an OAuth 2.0 token representing the user who authorized the script. When you (or whoever sets up the trigger) run createFormSubmitTrigger for the first time, Google will request permission for the script to access external services (like Pub/Sub) on behalf of that user. Therefore, the user authorizing the script MUST have the necessary IAM permissions in the associated GCP project to publish messages to the specified Pub/Sub topic (typically, the roles/pubsub.publisher role or a broader role that includes it, like roles/editor or roles/owner).
  • UrlFetchApp.fetch(): Makes the HTTP POST request to the Pub/Sub API endpoint.
    • The URL is built using your GcpProjectId and PubSubTopicName.
    • headers: Includes the Authorization: Bearer <token> header to authenticate the call using the obtained token.
    • payload: The Pub/Sub message JSON.
    • muteHttpExceptions: true: Prevents HTTP errors (like 403 Forbidden, 404 Not Found) from stopping script execution immediately. This allows us to check response.getResponseCode() manually.
  • Response Check: Checks if the HTTP status code (responseCode) is in the success range (200-299). If so, logs and returns the API response. If not, logs the error and throws an exception to indicate failure, which will be caught by the try...catch in onFormSubmit.
• createFormSubmitTrigger():
  • This is a helper function and needs to be run manually ONCE from the script editor.
  • It first removes old onFormSubmit triggers to prevent duplicate executions if you run it more than once.
  • Then, it programmatically creates the trigger that links the onFormSubmit event of the active form to the execution of the onFormSubmit function in your script.
  • When running this function for the first time, Google will request the necessary authorizations.

Step 4: Authorization and Testing

1. Save the Script: Click the floppy disk icon ("Save project").
2. Run createFormSubmitTrigger:
   • In the dropdown menu above the code (where onFormSubmit is likely selected), choose createFormSubmitTrigger.
   • Click the "Run" button (play icon).
3. Authorization:
   • An "Authorization required" window will appear. Click "Review Permissions".
   • Choose the Google account you are using (the same one with access to the form and GCP project).
   • You'll see a "Google hasn't verified this app" screen. This is normal for personal scripts. Click "Advanced" and then "Go to [Your project name] (unsafe)".
   • Review the permissions the script is requesting (manage forms, connect to external services) and click "Allow".
   • The function will run, and you should see "onFormSubmit trigger created successfully." in the logs (View -> Logs).
4. Test the Form: Open your Google Form (in view mode, not editing mode) and submit a test response.
5. Check the Logs: Go back to the script editor and go to "View" -> "Executions". Look for the most recent execution of onFormSubmit. Click on it to see the logs (Logger.log). You should see the message "Message published to Pub/Sub: ..." if everything went well, or error messages otherwise.
6. Check Pub/Sub:
   • In the Cloud Console, go to "Pub/Sub" -> "Subscriptions".
   • Create a new subscription ("Create Subscription") for your topic (e.g., form-submission). Give it a name (e.g., form-submission-test-sub), select the correct topic, and keep the rest as default (Delivery type: Pull).
   • After creating the subscription, click on its name and go to the "Messages" tab.
   • Click "Pull". It might take a few seconds, but you should see the message corresponding to your form response (the content will be Base64 encoded in the "Data" field, but the console often provides an option to view it decoded).

Important Considerations

• IAM Permissions: Emphasizing: The user who authorizes the script (usually the form/script creator) needs to have the roles/pubsub.publisher role (or equivalent) in the associated GCP project for the publishToPubSub call to work. If you receive 403 Forbidden errors, check this user's IAM permissions in GCP.
• Error Handling: The code includes basic error handling (try...catch and HTTP response code checking). For production, you might want to add more robust handling, such as failure notifications (e.g., via email or another alert system).
• Quotas and Limits: Be aware of Google Apps Script quotas (e.g., script runtime, UrlFetchApp calls per day) and Pub/Sub quotas (e.g., throughput, message size). For very high-volume forms, you might need to consider other architectures (e.g., using a Cloud Function triggered directly by the Form, though this requires more setup).
• Security: If the form collects sensitive data, ensure that your Pub/Sub topic and the systems consuming from it (like your App Integration flow or other backend services) are adequately secured and follow data privacy best practices.

Conclusion

Integrating Google Forms with Pub/Sub using Apps Script opens up a range of possibilities for automating workflows and processing data in real-time. By following the GCP setup steps, correctly associating the Apps Script project with GCP, understanding the authentication flow via ScriptApp.getOAuthToken(), and implementing the provided code, you can get this integration working quickly. Always remember to check permissions and test the complete flow!