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
- 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.
- Go into the settings in the form and make sure you toggle on Collect email addresses.
- Click on Responses and then click on the Spreadsheets logo.
- Select Create a new spreadsheet and click on Create. Renaming your new spreadsheet is optional.
- 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.
- In the spreadsheet, click on Extensions and then Apps Script.
- Click on the Code.gs file and delete all the lines before pasting the code below.
- 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
- 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.
After being replaced, the display_url variable should look similar to this: - We will now need an API token to insert on the third line. Follow these steps to create one in your Productboard workspace.
- 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:
- Click on Save and then Run.
- You may need to Review permissions.
- 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.
- 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.
If you have any feedback on this guide, feel free to email alex.degregori@productboard.com.
school Productboard Academy Quick Tip
Take a look at our quick tip academy course about connecting Google Forms to Productboard.
By using Google Forms to collect 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.
Note: Productboard's Academy content is available to trial users and paid customers only.
Comments
Article is closed for comments.