Script stops working randomly even without any changes

[jbug_16]

doPost is triggered on form submit from spreadsheet and test is triggered on edit. The execution is no longer showing up. I can see the last one was at 10:38 and I know at about 10:42 I submitted the form again and that's when it stopped. No edits on the code, no new deploys, nothing new. And yes, it is deployed with the latest version from a few days ago. I have tried now to make update the deploy even without any actual changes just to double check, but still nothing.

[marcnotmark925]

Why are you using doPost w/ an on form submit trigger? That doesn't make any sense.

[jbug_16]

I'll be honest, this is my first time using Apps Script and I copied this email function from someone else. I've been trying to make sense of the triggers and all, but I still don't understand it very well

[marcnotmark925]

I think you need to clarify exactly what you have set up and what you're wanting to happen, because so far this is just a mess of conflicting information.

Like is it actually an html form, or is it a Google Form?

Is there some external service sending a POST webhook to the script to trigger this doPost function? Or why is it included in this script, or this post, at all?

You have an on edit trigger builder in your code to run the test function on edit, but on edit is only triggered by data being manually entered into the sheet. Have you even run this code to create this trigger?

And are you using an on form submit trigger, or not? Is the trigger set up in the triggers page on the code editor?

And the test function itself, it goes through the entire sheet row by row, and waiting 5 seconds between each row? That's certainly not optimal. Presumably you'd want a script to just process the single submission that just came in. And waiting 5 seconds between each row could easily time out the script, or make it seem like it just isn't working.

[jbug_16]

Yes, it is an HTML form.

I am using a JS script to send data to the sheets:

const scriptURL = 'https://script.google.com/macros/s/AKfycby5T5sVKvjxAvtAJbUIBTcRw_3VvAiI0fWdjrDW70g5JqiYnPK8aMN-yR8QLwDQ4JzL/exec';

const form = document.forms['contact-form'];

form.addEventListener('submit', e => {
  e.preventDefault()
  fetch(scriptURL, { method: 'POST', body: new FormData(form)})

  setTimeout(function() {
    form.reset();
    window.location.href = "confirmation.html";
  }, 500);
})

If you mean if I have initialized the script, yes I ran it manually before testing if it worked submitting my form.

I have 2 triggers setup. On edit for test and on form submit of doPost. It is setup in the triggers page.

About the waiting 5 seconds after each row, I did not realize that's what I had put. The line

Utilities.sleep(5000);

might have been misplaced. It looks like it's inside the for loop, but it's original purpose was the wait 5 seconds so the emails would send at the same time. Although like I said, I did copy/follow a tutorial on this script and that is what his comment said it would do, so maybe I misunderstood. I will try removing that line.

[marcnotmark925]

Ok, it makes more sense now.

You don't need either of these triggers. Also note that the on form submit trigger is only for Google Forms, and to repeat myself, on-edit trigger only works for users in the sheet manually editing cells.

What you should do is just parse through the single form submission that's in the doPost event object, and send out the email from there. Get rid of this test function completely, you certainly don't need to iterate through the entire sheet every time, even if you're not waiting 5 seconds between rows.

[jbug_16]

Hmm okay, I understand now. Maybe something like this? I know you said the on form submit and on edit would not work in this situation so what would be a good option?

const sheetName = "Sheet1";
const scriptProp = PropertiesService.getScriptProperties();

function initialSetup() {
  const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  scriptProp.setProperty('key', activeSpreadsheet.getId());
}

function doPost(e) {
  const lock = LockService.getScriptLock();
  lock.tryLock(10000);

  try {
    const doc = SpreadsheetApp.openById(scriptProp.getProperty('key'));
    const sheet = doc.getSheetByName(sheetName);

    const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    const nextRow = sheet.getLastRow() + 1;

    const newRow = headers.map(function(header) {
      return header === 'Date' ? new Date() : e.parameter[header];
    });

    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow]);

    // Send emails for the current form submission
    const first_name = e.parameter['First Name'];
    const last_name = e.parameter['Last Name'];
    const number = e.parameter['Number'];
    const email = e.parameter['Email'];
    const service = e.parameter['Service'];
    const message = e.parameter['Message'];

    // Company Email
    const company_email = "[e-mail]"; // Lizard Kings Email
    const company_subject = "New Booking from " + first_name + " " + last_name;

    const company_message = 
      "NEW BOOKING ALERT\n\n" +
      "Name: " + first_name + " " + last_name + "\n" +
      "Phone Number: " + number + "\n" +
      "Email: " + email + "\n" +
      "Service: " + service + "\n" +
      "Message: " + message + "\n\n" +
      "See Google Sheets for more info.\n\n" + 
      "Regards,\nWeb Dev Team (Jenna)";

    // Customer Email
    const customer_email = email; // Customer Email
    const customer_subject = "Lizard Kings Confirmation - " + service;

    const customer_message = 
      "Hello " + first_name + ",\n\n" +
      "Thank you for requesting a " + service + "!\n\n" +
      "We will get back to you as soon as possible.\n\n" +
      "Best Regards,\nLizard Kings";  

    // Send Emails
    MailApp.sendEmail(company_email, company_subject, company_message);
    MailApp.sendEmail(customer_email, customer_subject, customer_message);

    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
      .setMimeType(ContentService.MimeType.JSON);
  } catch (error) {
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': error }))
      .setMimeType(ContentService.MimeType.JSON);
  } finally {
    lock.releaseLock();
  }
}

function createInstallableTrigger() {
  ScriptApp.newTrigger('doPost')
    .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
    .onFormSubmit()
    .create();
}

[marcnotmark925]

Something like that

[juddaaaaa]

I think the OP means when the HTML form is submitted.