Google Apps script stops randomly or refuses to launch

Question

I have developed a Google Apps script in a Google Drive spreadsheet that processes e-mails with a certain label (download notifications) and adds these to the spreadsheet. I'm running this through the script editor of the spreadsheet.

My initial solution was quite inefficient - the whole analysis was repeated each time on all e-mails, which caused the runtime to increase for each day. A few days ago I got an error message "runtime exceeded", which is not strange.

My problem is that when trying to update the script to be more efficient, I get some weird problems. The script either stops randomly after processing a few e-mails, or simply refuses to start. Especially the script debugger, it begins to load but never starts.

I have tried several times over the last few days, and even created a new spreadsheet with the same code (in the same account), but still having these problems.

Sometimes when the script manages to run for a while, I have noted that the script output log does not match recent changes in the logging. I have of course saved the script before running it. It feels like there is some lock preventing my script from running/updating/refreshing?

Is there anyone here that recognize these problems?

The code is attached below. The two relevant entry points are:

processInbox: Updates the spreadsheet based on new (starred) e-mails with a specific label. The label and star is set by an e-mail filter on reception. The star (indicating "new") is removed from each message after processing.

resetAllMsgs: Clears the spreadsheet and stars all relevant messages, causing processInbox to process all messages received with the relevant label.

function resetAllMsgs() {
  Logger.log("Starting ResetAll");

  var d = SpreadsheetApp.getActive();

  var dl_sheet = d.getSheetByName("Download List");
  var dlperday_sheet = d.getSheetByName("DownloadsPerDay");

  dl_sheet.clear();
  dlperday_sheet.clear();

  Logger.log("Clearing spreadsheet");

  dl_sheet.appendRow(["", "", "Downloads", ""]);
  dl_sheet.appendRow(["", "", "Downloaders", ""]);

  dl_sheet.appendRow(["Last Download Date", "First Download Date", "Email" , "Product", "Download Count"]);

  dlperday_sheet.appendRow(["Date", "Download Count"]);

  var label = GmailApp.getUserLabelByName("Download Notification");

  // get all threads of the label
  var threads = label.getThreads();

  for (var i = 0; i < threads.length; i++) {
    // get all messages in a given thread
    var messages = threads[i].getMessages();

    Logger.log("Starring thread " + i);

    for (var j = 0; j < messages.length; j++) {

      Logger.log("   Starring message " + j);

      // Only starred messages are processed by processInbox
      messages[j].star();

      Utilities.sleep(100);

    }
  }
};

function processInbox() {

  var d = SpreadsheetApp.getActive();

  var dl_sheet = d.getSheetByName("Download List");
  var dlperday_sheet = d.getSheetByName("DownloadsPerDay");

  // If empty spreadsheet, reset the status of all relevant e-mails and add the spreadsheet headers
  if (dl_sheet.getLastRow() <= 1) {
    resetAll();
  }

  var label = GmailApp.getUserLabelByName("Download Notification");
  var k = 0;

  // get all threads of the label
  var threads = label.getThreads();

  for (var i = 0; i < threads.length; i++) {

    if (threads[i].hasStarredMessages()) {

      // get all messages in a given thread
      var messages = threads[i].getMessages();

      // iterate over each message
      for (var j = 0; j < messages.length; j++) {

        // Unread messages are not previously processed...
        if (messages[j].isStarred()) {

          var msg = messages[j].getBody();

          msg = msg.replace(/\r?\n/g, "");

          var email = getDownloader(msg);

          if (email == "") {
            Logger.log("Found no downloader info: " + messages[j].getSubject() + " " + messages[j].getDate());
          }

          var date = formatDate(getDownloadDate(msg));

          // Check if a new date
          var dateCell = find(date, dlperday_sheet.getDataRange(), 0);

          if (dateCell == null) {
            // If new date, append row in "downloads per day"
            dlperday_sheet.appendRow([date, 1]);
            dlperday_sheet.getRange(2, 1, dl_sheet.getLastRow()-1, 2).sort([1]);
          }
          else
          {
            // If existing date, update row in "downloads per day"
            var dlcount = dlperday_sheet.getRange(dateCell.getRow(), dateCell.getColumn()+1).getValue();
          }

          var productname = getProductName(msg);

          // Check if e-mail (user) already exists in the downloads list
          var matchingCell = find(email, dl_sheet.getDataRange(), 0);

          if ( matchingCell != null ) { 
            // If user e-mail exists, update this row

            var lastDownloadDate = dl_sheet.getRange(matchingCell.getRow(), matchingCell.getColumn()-1).getValue();
            var lastDownloadCount = dl_sheet.getRange(matchingCell.getRow(), matchingCell.getColumn()+2).getValue();

            if (lastDownloadDate != date) {                
              dl_sheet.getRange(matchingCell.getRow(), matchingCell.getColumn()-1).setValue(date);                
            }

            dl_sheet.getRange(matchingCell.getRow(), matchingCell.getColumn()+2).setValue(lastDownloadCount+1);          

          }
          else // If new user e-mail, add new download row
          {
            dl_sheet.appendRow([date, date, email, productname, 1]);
            dl_sheet.getRange(2, 4).setValue(dl_sheet.getRange(2, 4).getValue() + 1);  
            dl_sheet.getRange(4, 1, dl_sheet.getLastRow()-3, 5).sort([1]);
          }

          // Mark message as processed, to avoid processing it on the next run
          messages[j].unstar();

        }
      }
    }
  }     
};

/**
 * Finds a value within a given range. 
 * @param value The value to find.
 * @param range The range to search in.
 * @return A range pointing to the first cell containing the value, 
 *     or null if not found.
 */
function find(value, range, log) {
  var data = range.getValues();
  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[i].length; j++) {
      if (log == 1)
      {
        Logger.log("Comparing " + data[i][j] + " and " + value);
      }
      if (data[i][j] == value) {
        return range.getCell(i + 1, j + 1);
      }
    }
  }
  return null;
};

function getDownloader(bodystring) {
  var marker = "Buyer Info";
  var marker_begin_index = bodystring.indexOf(marker, 1000);
  var email_begin_index = bodystring.indexOf("mailto:", marker_begin_index) + 7;

  var email_end_index = bodystring.indexOf("\"", email_begin_index);

  if (email_end_index < 1000)
  {
    return "";
  }

  var email = bodystring.substring(email_begin_index, email_end_index);

  if (log == 1)
  {
    Logger.log("Found e-mail address: " + email + "");
    Logger.log("   marker_begin_index: " + marker_begin_index);
    Logger.log("   email_begin_index: " + email_begin_index);
    Logger.log("   email_end_index: " + email_end_index);
  }

  latestIndex = email_end_index;

  return email;

};


function formatDate(mydate)
{  
  var str = "" + mydate;

  var dateParts = str.split("/");

  var day = dateParts[1];
  if (day.length == 1)
    day = "0" + day;

  var month = dateParts[0];
  if (month.length == 1)
    month = "0" + month;

  return dateParts[2] + "-" + month + "-" + day; 
};

function getDownloadDate(bodystring) {
  var marker = "Download Date:</strong>";
  var marker_begin_index = bodystring.indexOf(marker, latestIndex);
  var date_begin_index = marker_begin_index + marker.length;

  var date_end_index = bodystring.indexOf("<br>", date_begin_index);

  latestIndex = date_end_index;

  return bodystring.substring(date_begin_index, date_end_index).trim();  
};


function getProductName(bodystring) {
  var marker = "Item:</strong>";
  var marker_begin_index = bodystring.indexOf(marker, latestIndex);
  var pname_begin_index = marker_begin_index + marker.length;
  var pname_end_index = bodystring.indexOf("</td>", pname_begin_index);

  latestIndex = pname_end_index;

  return bodystring.substring(pname_begin_index, pname_end_index).trim();

};

Answer 1

Update: Any script I run stops after about 5 seconds, even if it does not call any services. I tried the following code:

function test() {
  Logger.log("Test begins");
  Utilities.sleep(5000);
  Logger.log("Test ends");
} 

The script terminates after about 5 sec, but the last line is not printed. If decreasing the delay to 3 seconds it behaves as expected.

Moreover, to make the script update properly after modifying it, I need to save it, start it, click cancel, and then start it again, otherwise it the log output seems to come from the old version. (I'm running this through the script editor.)

Also, the debugger refuses to start, even for the small script above. Seems to be some problem with my account ([email protected]). Are there any google employee out there that can check this?