Connect Google Forms to Productboard

You can integrate Google Forms with Productboard by using Productboard’s Notes API. Once you configure the API connection with a Google form, whenever a form is submitted, it will be pushed into your Insights board as a Note.

By using Google Forms for collecting user insights, you can

  • Bring structure to your customer feedback.
  • Let the note owner initially assess the priority.
  • Always capture important information like pain points, use cases, and benefits.

If additional customization is needed, anyone with knowledge of JavaScript should be able to make adjustments to the provided script. You can also email your main Productboard contact for support.

In this article:

Copying the form and script

  1. Make a copy of this form.
    Note: Before continuing onwards, it is recommended to make your changes to the form’s questions and options. If you do make changes, make note of how many questions the form contains as that will be needed later in the script. Also, if you would like to associate your customers with your Productboard notes, the first question (i.e. Requestor email) is required. Otherwise, the Productboard notes will appear anonymous.
  2. Go into the settings in the form and make sure you toggle on Collect email addresses.
    Screen_Shot_2022-07-27_at_12.39.33_PM.png
  3. Click on Responses and then click on the Spreadsheets logo.
    Response.png
  4. Select Create a new spreadsheet and click on Create. Renaming your new spreadsheet is optional.
    create_new_spreadsheet.png
  5. Once you create a new spreadsheet, you will get the message Spreadsheet linked. Click Open and go to the new spreadsheet. This spreadsheet will now have columns that are linked to the questions in your form.
  6. In the spreadsheet, click on Extensions and then Apps Script.
    Apps_Script.png
  7. Click on the Code.gs file and delete all the lines before pasting the code below.
    code.gs.png
  8. Copy and paste the code below into the editor for the Code.gs file:
const display_url = "<Insert Spreadsheet URL here>"; // This URL will be added to the title of the notes in Productboard
const productboardBearerToken = "<Insert Bearer Token here>"; // This API token will authenticate your POST requests from this script

function Initialize() {
  try {
    const triggers = ScriptApp.getProjectTriggers();
    for (const i in triggers) ScriptApp.deleteTrigger(triggers[i]);
    // Trigger to run function below when a new form is submitted
    ScriptApp.newTrigger("SubmitGoogleFormData")
      .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
      .onFormSubmit()
      .create();
  } catch (error) {
    throw new Error("Error when running SubmitGoogleFormData");
  }
}

// Function that is called after each form submission
function SubmitGoogleFormData(e) {
  if (!e) {
    throw new Error("Please go the Run menu in the Menu bar and choose Initialize");
  }
  try {
    // This is the beginning of the POST request
    // Below are the variables we associate to the columns from the Spreadsheet to put into the payload
    const activeSheet = SpreadsheetApp.getActiveSheet() // Get the active sheet 
    const lastRow = activeSheet.getLastRow() // Get the last row of sheet
    const numOfColumns = activeSheet.getLastColumn() // Get the last column
    const timestamp = activeSheet.getRange(lastRow, 1, 1, 1).getValue() // column 1 - Timestamp of submission
    const submitterEmail = activeSheet.getRange(lastRow, 2, 1, 1).getValue() // column 2 - Submitter email
    let requestorEmail = activeSheet.getRange(lastRow, 3, 1, 1).getValue() // column 3 - Requestor email - validated in the validateEmail function
    // Regex to confirm the email is valid to be sent to Productboard
    const emailRegex = (email) => {
      return email.match(/[^@\s]+@[^@\s]+/);
    };
    // Function to validate email against regex
    const validateEmail = (email) => {
      if (emailRegex(email)) {
        requestorEmail = requestorEmail
      } else {
        requestorEmail = null    
      }
    };
    // Function called to validate email and replaced with null if not valid
    validateEmail(requestorEmail)
    // Consolidating questions and answers from form responses
    // 🛑 If question/answers are missing from your Productboard note, remove the "- 2" from the questionRange variable
    const questionRange = numOfColumns - 2;
    const questionRow = activeSheet.getRange(1, 3, 1, questionRange);
    const questionValues = questionRow.getValues();
    let questions = [];
    // Looping through top row to place questions into array
    for (const element of questionValues) {
      questions = element;
    }
    const bottomRange = activeSheet.getRange(lastRow, 3, 1, questionRange);
    const bottomValues = bottomRange.getValues();
    let answers = [];
    // Looping through bottom row to place answers into array
    for (const element of bottomValues) {
      answers = element;
    }
    // Combining questions and answers into single array to loop into the body/content of Productboard note
    let questionsAndAnswers = questions.concat(answers);
    let content = "";
    const questionsAndAnswersLength = questionsAndAnswers.length;
    for (let i = 0; i < questionsAndAnswersLength / 2; i++) {
      content += `<strong>${questionsAndAnswers[i]}:</strong> ${
        questionsAndAnswers[i + questionsAndAnswersLength / 2]
      }<br><br>`;
    }
    // Put the columns from the spreadsheet (questions & answers from the form) into the API payload
    const payload = {
      // What is the feature request called?
      title: "Feedback from Google Forms",
      //Include feature request details/fields in the content body of the note
      content: content,
      // Who is the feature request coming from? Only added if the requestorEmail question is added in Column C
      user: {
        email: requestorEmail,
      },
      // Link to the forms sheet for easy access
      display_url: display_url,
      // Track where it came from and who submitted
      source: {
        origin: "Google Forms",
        record_id: `Time: ${timestamp} + Submitted By: ${submitterEmail}`,
      },
    };
    //Set up the authorization below. The Bearer token can be created in the Productboard Integrations section.
    const headers = {
      Authorization: `Bearer ${productboardBearerToken}`,
    };
    //Build up options for the request type
    const options = {
      method: "post",
      contentType: "application/json",
      headers: headers,
      payload: JSON.stringify(payload),
      muteHttpExceptions: false,
    };
    // Set API URL endpoint
    const url = "https://api.productboard.com/notes";
    // Make the call
    const response = UrlFetchApp.fetch(url, options);
    // Log the response (useful for debugging)
    Logger.log(JSON.stringify(response));
  } catch (error) {
    // Log the error (useful for debugging)
    Logger.log(error.toString());
  }
}

Updating the variables in the script

  1. Add the URL of your spreadsheet (only the part before /edit#gid) where it says <Insert Spreadsheet URL here> in the display_url variable in the second line of the script.
    image__45_.png
    After being replaced, the display_url variable should look similar to this:
    Screen_Shot_2022-09-29_at_8.54.45_AM.png
  2. We will now need an API token to insert on the third line. Follow these steps to create one in your Productboard workspace.
  3. Go back to Code.gs file and paste the key into the productboardBearerToken variable, replacing <Insert Bearer Token here>. After you paste the token it should look similar to this:
    Screen_Shot_2022-09-29_at_8.55.58_AM.png
  4. Click on Save and then Run.
    Screen_Shot_2022-09-29_at_8.57.25_AM.png
  5. You may need to Review permissions.
    Screen_Shot_2022-07-27_at_1.27.58_PM.png
  6. After clicking on Review permissions and then Allow in the next window, you should see these notes in the Execution log at the bottom of the page.
    Screen_Shot_2022-07-27_at_1.29.25_PM.png
  7. If you see the Execution log messages above, it means that you have successfully configured the connection. You may test the connection by submitting a test form.

Troubleshooting and debugging

If nothing appears in your Productboard Insights board after your first test, start by looking in the Executions menu of Apps Script. This will show the last functions that were run and the possible errors.
Screen_Shot_2022-07-27_at_2.00.03_PM.png

If you have any feedback on this guide, feel free to email alex.degregori@productboard.com.

Was this article helpful?
2 out of 2 found this helpful

Comments

0 comments

Article is closed for comments.

Articles in this section

See more
Our Support hours:
Monday to Friday from 9:00 am - 2:00 am CET. Monday to Friday from 0:00 am - 5:00 pm PST.
Productboard Academy
Become a Productboard expert with self-paced courses, quick tip videos, webinars and more.
Product Makers Community
Connect with product leaders, share and find product jobs, and learn how to approach similar challenges. Come join our Product Makers community.