CODEWITHSUNDEEP
google-apps-script
jad
jad

Reputation: 582

SpreadsheetApp getRange fails when ran via a trigger

I have a function in my script which trims data in a spreadsheet column. This works perfectly fine when invoked manually, but always fails when ran via a timed-trigger. It seems to me like a bug, but I thought I should ask here first as there could be something I'm missing. Here's my code.

function trimColumnValues(columnIndex) {
  try {
    columnIndex = columnIndex || 2;
    var config = PropertiesService.getScriptProperties()
                          .getProperties();
    var firstDataRow = parseInt(config.FIRST_DATA_ROW);
    var sheet = SpreadsheetApp.openById('valid_spreadsheet_id')
                              .getSheetByName(config.MAIN_SHEET_NAME);
    var lastRow = sheet.getLastRow();
    var noColumns = lastRow - firstDataRow;
    var range = sheet.getRange(firstDataRow + 1, columnIndex, noColumns);
    var data = range.getValues();
    for (var i in data) {
      data[i][0] = data[i][0].toString().replace(/^\s+|\s+$/g,"");
    }
    range.setValues(data);
  } catch(e) {
    MailApp.sendEmail("[email protected]", "Error in script", 
      "Error: " + e.message + ". Line: " + e.lineNumber);
  }
}

The error I receive in my email is:

Error: Cannot convert [object Object] to (class).. Line: 28

Line 28: var range = sheet.getRange(firstDataRow + 1, columnIndex, noColumns);

Upvotes: 0

Views: 110

Answers (1)

jad
jad

Reputation: 582

I have added a Logger.log() line after each variable and had the log emailed with error. It turns out columnIndex was type: object, value: [object Object]. I changed the code to this:

function trimSKUs(index) {//columnIndex
  try {
    var columnIndex = (typeof index !== "number") ? 2 : index;

This works fine, manually and via timed trigger.

EDIT: Out of curiosity, I tested the old line with log call for JSON string value of the object and had this value:

{"year":2015,"month":9,"day-of-month":24,"day-of-week":4,"week-of-year":39,"hour":19,"minute":43,"second":13,"timezone":"UTC","authMode":{},"triggerUid":1787123430}

Apparently, trigger object is passed as argument to time-triggered functions.

Upvotes: 1

Related Questions