Skip to main content
Question

Webhook to run Google sheet script doesn't update correctly

  • November 12, 2024
  • 2 replies
  • 89 views

I have tried three times to post my question, and every time it disappears. I am going to try to post a simple intro post, and follow up with a reply, just to see if it will stay posted.

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.

2 replies

  • Author
  • Beginner
  • 3 replies
  • November 12, 2024

OK, it looks like this time it might take, lord willing.

I have a zap that takes the output of a Cognito form and passes it over to add a new row to a Google sheet. This has worked fine for years. I am now trying to add a webhook as a new task in the zap that runs a doPost script after the row is added. Essentially, I am running a vlookup on an ID number in the row that was just added, and adding a value to a cell in that row based on the result of the vlookup.

The script is running fine; I have tested it with a helper function as well as it returning a “processed successfully” message through the logger in the zap. However, none of the other information is apparently passing through. It does not log in the logger, and it doesn’t update the cell correctly.

Here is the screenshot of the data I’m passing through to the script. Can anyone help?

 

edit: the script might help:

function doPost(e) {
  try {
    // Parse the incoming POST data
    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";
}

 


Badger
Forum|alt.badge.img+5
  • New
  • 134 replies
  • November 23, 2024

It occurred to me that you could log the trace stack for errors.

Logger.log("Error processing POST request: " + err.message + "\n" + err.stack);

Another thought is that Apps Script can encounter race conditions when multiple users or processes edit the same spreadsheet simultaneously. This is possible, if your trying to run the script immediately after adding the row, especially if there multiple rows being added consider adding a delay between adding rows and running the script. Or add locks into the script to prevent conflicts. 
 

var lock = LockService.getScriptLock();
lock.tryLock(10000); // Wait up to 10 seconds for the lock
try {
  // Perform operations on the sheet
} finally {
  lock.releaseLock();
}

[You’ll probably not need a full ten seconds though 😂]

Do let me know your thoughts and I hope this was useful.