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 numOfQuestions = 7; // Seven questions by default. Change if questions are added or removed.
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 SubmitGoogleFormData(e) {
if (!e) {
throw new Error("Please go the Run menu and choose Initialize");
}
try {
// This is the beginning of the POST request
// Grab the columns from the Spreadsheet to put into the payload
const activeSheet = SpreadsheetApp.getActiveSheet(), // Get the active sheet
lastRow = activeSheet.getLastRow(), // Get the last row
timestamp = activeSheet.getRange(lastRow, 1, 1, 1).getValue(), // column 1 - Timestamp
submitEmail = activeSheet.getRange(lastRow, 2, 1, 1).getValue(), // column 2 - Submitter Email
requestorEmail = activeSheet.getRange(lastRow, 3, 1, 1).getValue(); // column 3 - Requestor email
// Consolidating questions and answers from form responses
const contentQuestions = numOfQuestions - 1;
const topRange = activeSheet.getRange(1, 4, 1, contentQuestions);
const topValues = topRange.getValues();
let questions = [];
// Looping through top row to place values into array
for (const element of topValues) {
questions = element;
}
const bottomRange = activeSheet.getRange(lastRow, 4, 1, contentQuestions);
const bottomValues = bottomRange.getValues();
let answers = [];
// Looping through bottom row to place values into array
for (const element of bottomValues) {
answers = element;
}
// Combining questions and answers into single array to loop into the body/content of Productbaord 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?
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 Form",
record_id: "Time: " + timestamp + " - Submitted By: " + submitEmail,
},
};
//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) {
Logger.log(error.toString());
}
}

Updating the variables in the script

  1. Go to the first line of the script and adjust the numOfQuestions variable if you made any changes to the number of questions in the Google form. There are 7 by default.
    Screen_Shot_2022-07-27_at_2.16.38_PM.png
  2. 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.
    step_2.png
    After being replaced, the display_url variable should look similar to this:
    Screen_Shot_2022-07-27_at_2.17.30_PM.png
  3. We will now need an API token to insert on the third line. Follow these steps to create one in your Productboard workspace.
  4. 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-07-27_at_2.18.07_PM.png
  5. Click on the Save button.
    Screen_Shot_2022-07-27_at_2.19.12_PM.png
  6. Click on the Run button.
    Screen_Shot_2022-07-27_at_2.18.51_PM.png
  7. You may need to Review permissions.
    Screen_Shot_2022-07-27_at_1.27.58_PM.png
  8. 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
  9. 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 certain questions/answers are missing within your Productboard note or they are extra colons at the end, make sure your numOfQuestions variable is accurate in the first line of the script.

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

Was this article helpful?
0 out of 0 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 and learn Product Excellence with a global community