Google Apps Script function executes when I run it manually, but fails when run as trigger, citing error code INTERNAL [closed]

4

Closed. This question needs to be more focused. It is not currently accepting answers.

---

Want to improve this question? Update the question so it focuses on one problem only by editing this post.

Closed 22 days ago.

Improve this question

I have a Google Forms linked to a Google Sheets. Within the Google Sheets, I have an Google Apps Script function that separates the input from Google Forms and stores each item row by row. I have a VLookup setup to pull information from another spreadsheet, based on the parsing from the Google Apps Script. Once the information is pulled, I then email the information to the email provided through Google Forms. I have a trigger setup to automatically run the function when the form is submitted. The picture below is the form results in the sheet.

Form submission information

The trigger works when I select some options on Google Forms. Once I select more options, around 20+, it returns the following error code:

We're sorry, the JavaScript engine reported an unexpected error. Error code INTERNAL.

When I run the function manually though, it executes perfectly. I'm not quite sure what the issue is. The only other question with this issue that I've found is Web App call fails with "... Error code INTERNAL" with Custom API, but I'm already using openByID. The e in the function onFormSubmit below is my attempt at using the form object, but I haven't gotten that to work. I don't believe this causes the problem though.

//Object to store city & state in one object
function city (city, state) {
  this.city = city;
  this.state = state;
}

function onFormSubmit (e) {
  // Set active sheet and store form information in variables
  //This function runs based off a formSubmit trigger
  var sheet = SpreadsheetApp.openById('sampleID'); 
  SpreadsheetApp.setActiveSheet(sheet.getSheets()[0]);
  var lastRow = String(sheet.getLastRow());
  var email = String(sheet.getRange('D' +lastRow).getValue());
  var name = String(sheet.getRange('B' + lastRow).getValue());
  var company = String(sheet.getRange('C' + lastRow).getValue());
  var state = String(sheet.getRange('E' + lastRow).getValue()).split(",");
  var list_cities = [];

  //Separates City, State into object with City & State properties
  n = 0
  for (x of String(sheet.getRange('F' + lastRow).getValue()).split(",")) {
    if (n == 0) {
      var city_name = x.trim();
      n = 1;
    }
    else {
      n = 0;
      var temp = new city(city_name, x.trim());
      list_cities.push(temp);
    }
  }

  //Moves to next sheet
  SpreadsheetApp.setActiveSheet(sheet.getSheets()[1]);

  //Clear Form tab
  lastRow = String(SpreadsheetApp.getActive().getLastRow());
  SpreadsheetApp.getActiveSheet().getRangeList(['A2:B2', 'A3:F' + lastRow]).clear();
  
  // Initializes first row
  var row = 2;
 
  //Fills in State column
  for (x of state) {
    SpreadsheetApp.getActiveSheet().getRange(row, 1).setValue(x.trim());
    row = row + 1;
  }
  
  //Fills in City column
  for (x of list_cities) {
    SpreadsheetApp.getActiveSheet().getRange(row,1).setValue(x.state);
    SpreadsheetApp.getActiveSheet().getRange(row, 2).setValue(x.city);
    row = row + 1
  }

  //List of cell column headers
  var letters = ["C", "D", "E", "F"];
  
  //Copies down formula for each column
  for (x of letters) {
    var sourceRange = SpreadsheetApp.getActiveSheet().getRange(x + String(2));
    var destination = SpreadsheetApp.getActiveSheet().getRange(x + String(2) + ":" + x + String(row-1));
    sourceRange.autoFill(destination, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  }

  SpreadsheetApp.flush();

  //Extracts only completed sheet
  var url = "https://docs.google.com/";
  var params = {
    method      : "get",
    headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
    muteHttpExceptions: true
  };
  var blob = UrlFetchApp.fetch(url, params).getBlob();
  blob.setName(SpreadsheetApp.getActive().getName() + ".xlsx");

  //Set email information and send email
  var message = {
    to: email,
    name: 'Name',
    subject: company + " Subject",
    body: "Body text",
    attachments: blob
  }
  MailApp.sendEmail(message)
}

javascript google-apps-script google-sheets google-forms

Share

Improve this question

edited Apr 8 at 0:15

googlinghowtogoogle

asked Apr 7 at 23:33

googlinghowtogooglegooglinghowtogoogle

4144 bronze badges

4

• Please add a minimal reproducible example – Rubén Apr 7 at 23:48
• I've added my code as well as an example form submission. – googlinghowtogoogle Apr 8 at 0:12
• I've been experiencing the exact same issue. As of about 12 hours ago, my programmatic triggers, which all worked nominally the day before have been frequently failing to even execute, – The Swift Coder Apr 8 at 2:07
• I have a routine triggers and it failed from today morning. The same function works good when we ran manually. – Nirmal Apr 8 at 8:08

Add a comment  |