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 runs 'createNoteInProductboard' 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();

    // Column 3 holds the Requestor email (the first question on the form).
    // "Collect email addresses" must stay ON so columns line up: 1 = timestamp, 2 = submitter email, 3 = requestor email.
    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 by combining questions and answers
    const content = createNoteContent(questions, answers);

    // 1. Create the note FIRST, without the customer. This guarantees the note is always saved,
    //    even if the customer can't be linked (in which case the note simply appears as anonymous).
    const notePayload = {
      data: {
        type: "textNote",
        fields: {
          name: "Feedback from Google Forms",
          content: content
        }
      }
    };
    const noteResponse = sendPostRequest("https://api.productboard.com/v2/notes", notePayload, productboardToken);
    const note = JSON.parse(noteResponse.getContentText()).data;
    Logger.log(`Productboard note created: ${note.links.html}`);

    // 2. Attempt to attach the customer. In v2 the customer is a RELATIONSHIP that links to an existing
    //    user by email, so we create the user first (if needed), then link them to the note.
    //    If anything here fails, the note stays in place and is simply left anonymous.
    if (requestorEmail) {
      try {
        ensureCustomerExists(requestorEmail, productboardToken);
        linkCustomerToNote(note.id, requestorEmail, productboardToken);
        Logger.log(`Linked customer ${requestorEmail} to the note.`);
      } catch (error) {
        Logger.log(`Could not link customer ${requestorEmail}; the note will remain anonymous. ${error}`);
      }
    }
  } catch (error) {
    throw new Error(`Could not create a Productboard note. Full error response: ${error}. See the Notes API v2 reference at https://developer.productboard.com for more information.`);
  }
}

// Creates the customer (a "user" entity) if they don't already exist.
// A 409 response means the user already exists, which is expected and fine.
function ensureCustomerExists(email, token) {
  const payload = {
    data: {
      type: "user",
      fields: {
        email: email
        // If your workspace requires more than email to create a user, add those fields here.
        // Check GET https://api.productboard.com/v2/entities/configurations/user for required fields.
      }
    }
  };
  const options = {
    method: "POST",
    contentType: "application/json",
    headers: { Authorization: `Bearer ${token}` },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true // tolerate the 409 (already exists) instead of throwing
  };
  const response = UrlFetchApp.fetch("https://api.productboard.com/v2/entities", options);
  const code = response.getResponseCode();
  if (code === 201) {
    Logger.log(`Customer created for ${email}.`);
  } else if (code === 409) {
    Logger.log(`Customer ${email} already exists; continuing.`);
  } else {
    Logger.log(`Could not create customer ${email} (HTTP ${code}): ${response.getContentText()}`);
  }
}

// Links a customer (user) to a note by email via the note's relationships endpoint.
function linkCustomerToNote(noteId, email, token) {
  const payload = {
    data: {
      type: "customer",
      target: {
        type: "user",
        email: email
      }
    }
  };
  return sendPostRequest(`https://api.productboard.com/v2/notes/${noteId}/relationships`, payload, token);
}

// 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 the 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);
}