Google Sheet Script not fully executing

2
votes

I'm using a Google Form to trigger this script.
When I run the script with the Play button it works perfect.
When I let the onsubmit trigger run it, the check box populates fine but the setValue date does not.
I've also tried using setFormula but I get the same result.

function AddCheckBox_toSchoolLunchForm(F) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1")
  var criteria = SpreadsheetApp.DataValidationCriteria.CHECKBOX;
  var rule = SpreadsheetApp.newDataValidation().requireCheckbox().build();
  var range = sheet.getRange(sheet.getLastRow(), 8);
  range.clearContent();
  range.setDataValidation(rule);
  var FormulaCell = sheet.getRange(sheet.getLastRow(), 10);
  FormulaCell.setFormula("=DATEVALUE(A" + (sheet.getLastRow()) + ")");
}

Example of result from a Manual Code run

Example of result from a Triggered Code Run

The end goal is to have column J populate with the shot formatted date from column A each time the form is submitted.
I need this format to run a countIfs on another sheet.

Another option would be to somehow embed a format tag into this CountIfs command so that they match.

=COUNTIFS('Form Responses 1'!J:J,A2,'Form Responses 1'!D:D,B2)
1
google-apps-scriptgoogle-sheetstriggersgoogle-forms

1 Answers

0
votes

UPDATED ANSWER

If you have problems running a function on trigger while it works as intended without a trigger, check the following:

  • For heavy files / form data they can be a delay in populating the sheet with new form data.

    • To avoid conflicts, give the spreadsheet some time to populate the new row before retrieving sheet.getLastRow() or accessing data in the sheet.

    • You can dot is easily e.g. with Utilities.sleep().

  • Check that your trigger has been installed correctly. For this check that

    • the type of trigger is correct
    • the trigger is bound to the correct function
    • avoid conflicts by creating a new trigger when renaming the funciton.

  • Check that the problem is not due to trigger restrictions

  • This is a common error source when using simple triggers.

  • Check that account under whose authorization the trigger is running has permissions to edit the sheet /range. This is important to check when the trigger owner is a diferent person that the person who runs the script manualy.

  • Note that for setFormula() it is not necessary to incorporate the = into the formula

  • Note that DATEVALUE() will only return the expected result when the cell is formatted correctly as a date