Skip to main content
Question

Webhook not passing data to Google apps script

  • November 12, 2024
  • 0 replies
  • 20 views

I have a zap that takes a form entry from a Cognito form and transfers the data to a Google sheet. There is one cell on the Google sheet that I want to update based on a lookup to another sheet. I have the code correct in Google apps script, but the webhook is not working correctly and I am at my wit’s end trying to figure it out.

Here is the code:

function doPost(e) {
  try {

    var data = JSON.parse(e.postData.contents);

    var sheetName = data.sheetName;
    var editedRow = data.row;
    var studentID = data.studentID;

    // Log the incoming data for debugging
    Logger.log("Received data: " + JSON.stringify(data));
    Logger.log("Sheet name: " + sheetName);
    Logger.log("Row: " + editedRow);
    Logger.log("Student ID: " + studentID);

    // Open the spreadsheet and find the target sheet by name
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName(sheetName);

    if (!sheet) {
      Logger.log("Error: Sheet not found - " + sheetName);
      return ContentService.createTextOutput("Sheet not found").setMimeType(ContentService.MimeType.TEXT);
    }

    // Validate row number
    if (editedRow < 1 || editedRow > sheet.getMaxRows()) {
      Logger.log("Error: Invalid row number - " + editedRow);
      return ContentService.createTextOutput("Invalid row number").setMimeType(ContentService.MimeType.TEXT);
    }

    // Proceed with modifying data
    var statusCell = sheet.getRange(editedRow, 16);  // Column P (16th column) for status

    var currentStatus = statusCell.getValue();
    if (currentStatus === "") {
      statusCell.setValue("Processed");  // Change to whatever value you want
      Logger.log("Student " + studentID + " processed and status updated.");
    }

    var response = ContentService.createTextOutput("Processed successfully");
    response.setMimeType(ContentService.MimeType.TEXT);

    return response;

  } catch (err) {
    Logger.log("Error processing POST request: " + err.message);
    return ContentService.createTextOutput("Error: " + err.message).setMimeType(ContentService.MimeType.TEXT);
  }
}

// Function to get the student status from enrollments sheet
function getStudentStatus(studentID, enrollmentsSheet) {
  var enrollmentsData = enrollmentsSheet.getDataRange().getValues();

  // Loop through the "2024-25 Enrollments" sheet to find the student ID and return the status
  for (var i = 1; i < enrollmentsData.length; i++) {
    if (enrollmentsData[i][4] === studentID) { // Student ID is in column E (5th column)
      return enrollmentsData[i][15]; // Return the status (New or Returning) from column P (16th column)
    }
  }

  // If no match is found in the 2024-25 sheet, search in the "2025-26 Enrollments" sheet
  var enrollmentsSheet2025 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("2025-26 enrollments");
  var enrollmentsData2025 = enrollmentsSheet2025.getDataRange().getValues();

  // Loop through the "2025-26 Enrollments" sheet to find the student ID and return the status
  for (var i = 1; i < enrollmentsData2025.length; i++) {
    if (enrollmentsData2025[i][4] === studentID) { // Student ID is in column E (5th column)
      return enrollmentsData2025[i][15]; // Return the status (New or Returning) from column P (16th column)
    }
  }

  // If no match is found in either sheet, return "New"
  return "New";
}

In the webhooks task, sheetName is hard coded as the sheet name; row is passed from the row number of the Google sheet in that step of the zap; and studentID is passed from the data entered in the Cognito form which triggers the zap in the first place.

The only thing that outputs is “Processed successfully” - so the script is running, but the data is not passing through the webhook correctly. What am I missing?

Did this topic help you find an answer to your question?
This post has been closed for comments. Please create a new post if you need help or have a question about this topic.