Connect Google Forms to Productboard

// Get the Productboard token from Script Properties
const scriptProperties = PropertiesService.getScriptProperties();
const productboardToken = scriptProperties.getProperty('PB_API_TOKEN');

// Adds a trigger that runs a function when a Google Form is submitted. Triggers are managed in the left hand menu.
function addTrigger() {
 if (!productboardToken) {
 throw new Error ("Please add your Productboard API token in the Project settings in the left hand menu. More info here: https://support.productboard.com/hc/en-us/articles/8200971722387-Connect-Google-Forms-to-Productboard")
 }
 try {
 const triggers = ScriptApp.getProjectTriggers();
 triggers.forEach(trigger => ScriptApp.deleteTrigger(trigger));
 // Create a new trigger that will run the 'submitGoogleFormData' function when the form is submitted
 ScriptApp.newTrigger("createNoteInProductboard")
 .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
 .onFormSubmit()
 .create();
 Logger.log("Trigger successfully added. A Productboard note will be created upon a Google form submission.");
 } catch (error) {
 throw new Error("Error when adding trigger: " + error.toString());
 }
}

// Function called when a form is submitted
function createNoteInProductboard(e) {
 // Check if the function is called manually and throw an error if true
 if (!e) {
 throw new Error("This function cannot be run manually. Please run the 'addTrigger' function from the Run menu and submit a form to test.");
 }
 try {
 // Get the active sheet and the last row and column with data
 const activeSheet = SpreadsheetApp.getActiveSheet();
 const lastRow = activeSheet.getLastRow();
 const numOfColumns = activeSheet.getLastColumn();
 const timestamp = getCellValue(activeSheet, lastRow, 1);
 const submitterEmail = getCellValue(activeSheet, lastRow, 2);
 let requestorEmail = getCellValue(activeSheet, lastRow, 3);
 requestorEmail = validateEmail(requestorEmail) ? requestorEmail : null;
 // Get questions from the first row/headers and answers from the last row
 const questions = getRowValues(activeSheet, 1, numOfColumns);
 const answers = getRowValues(activeSheet, lastRow, numOfColumns);
 // Create note content for the Productboard note by combining questions and answers
 const content = createNoteContent(questions, answers);
 // Prepare the payload for the Productboard API
 const payload = {
 title: "Feedback from Google Forms",
 content: content,
 user: {
 email: requestorEmail
 }
 };
 // Make the API request to create a note in Productboard
 const url = "https://api.productboard.com/notes";
 const response = sendPostRequest(url, payload, productboardToken);
 if (response) {
 const parsedResponse = JSON.parse(response)
 Logger.log(`Productboard note created: ${parsedResponse.links.html}`);
 try {
 const noteId = parsedResponse.data.id;
 const addFollowerUrl = `https://api.productboard.com/notes/${noteId}/user-followers`
 const addFollowerPayload = [
 {
 "email": submitterEmail
 }
 ]
 sendPostRequest(addFollowerUrl, addFollowerPayload, productboardToken);
 console.log(`${submitterEmail} was added as a follower to the note.`)
 }
 catch (error) {
 console.log(`Attempted to add the submitter as a follower to the note but no matching email was found. `)
 }
 }
 } catch (error) {
 throw new Error(`Could not create a Productboard note. Full error response: ${error}. Check https://developer.productboard.com/reference/create_note-1 for more information.`);
 }
}

// Helper function to get a cell value
function getCellValue(sheet, row, column) {
 return sheet.getRange(row, column).getValue();
}

// Helper function to get values of a row
function getRowValues(sheet, row, numOfColumns) {
 return sheet.getRange(row, 1, 1, numOfColumns).getValues()[0];
}

// Helper function to validate email format
function validateEmail(email) {
 const emailRegex = /[^@\s]+@[^@\s]+/;
 return emailRegex.test(email);
}

// Helper function to create content for Productboard note
function createNoteContent(questions, answers) {
 let content = "";
 const length = questions.length;
 for (let i = 1; i < length; i++) {
 content += `<strong>${questions[i]}:</strong> ${answers[i]}<br><br>`;
 }
 return content;
}

// Helper function to send a POST request to the Productboard API
function sendPostRequest(url, payload, token) {
 const options = {
 method: "POST",
 contentType: "application/json",
 headers: {
 Authorization: `Bearer ${token}`,
 },
 payload: JSON.stringify(payload),
 muteHttpExceptions: false,
 };
 return UrlFetchApp.fetch(url, options);
}