Error message cites non-existent line and column in code for Google Apps Script-CodePudding

I get the following error message when running some code in Google Apps Script. I don't understand the Line/Column reference, Code:46:18. It appears to point to either a line with too few columns or a process with too few lines. I assume I am not interpreting the reference correctly.

TypeError: Cannot set property 'format' of undefined
at processInbox(processInbox Code:46:18)

Line 46 of all my code is this and certainly doesn't have 18 columns (and it closes a function that doesn't refer to format):

The process referred to by the error message, processInbox, is only 39 lines long.

The script is called by selecting "Run Script" in the menu, "CiviSchedule" in the related Google Sheet, which triggers the doTasks function. This menu and and trigger are created in the onOpen function.

How am I misinterpreting the error message? (Full code follows)

[screenshot of error]

[screenshot of lines 40-46]

The code for reference:

//General Info
//
// As detailed in Managing Scheduled Jobs URL method http://wiki.civicrm.org/confluence/display/CRMDOC/Managing Scheduled Jobs#ManagingScheduledJobs-URLmethod :
//
//  a valid Username and Password (for a Drupal, Joomla or WordPress user who has adequate permissions 
//  for the job or jobs being run. The minimal permissions for that user are: “view all contacts”, “access 
//  CiviCRM”, “access CiviMail”). It also requires you to pass in the CIVICRM_SITE_KEY which is configured 
//  by editing your copy of civicrm.settings.php
//
// I’d recommend setting up a dedicated account for scheduling reports with only minimal permissions. 
// Once you have a username/password setup open File > Project Properties and open the Script Properties 
// tab. Click ‘Add row’ link and add your setup account name (username), pass (password), key (site key).
// Save the Script Properties and then in the script editor window enter the BASE_URL below of your Civi 
// installation (in Drupal this looks like http://[SITEROOT]/sites/all/modules/civicrm/bin/cron.php?. 

// File > Save your script

var BASE_URL = "https://www.fubar.org/sites/all/modules/civicrm/bin/cron.php?";

// To get this script to run automatically open Resources > Current project triggers
// and slect doTasks to run as a day timer (we set reports to run between 7-8am)
// If you want to run earlier or later also adjust the RERUN_HOUR below which sets the next run time

var RERUN_HOUR = 1;

var PS = PropertiesService.getScriptProperties();
var param = PS.getProperties();
param.job = "mail_report";

// helper so we know which value is in which column
var COL = {report_id: 0,
           type: 1,
           last_run: 2,
           next_run: 3,
           format: 4,
           ss_id: 5,
           ss_sht: 6,
           total: 7};
           
function onOpen(){
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('CiviSchedule')
      .addItem('Run Script', 'doTasks')
      .addToUi();
}

function doTasks() {
  var doc = SpreadsheetApp.getActiveSpreadsheet(); // get spreadsheet
  var sheet = doc.getSheetByName("Tasks"); // get sheet
  var data = sheet.getRange(3, 1, sheet.getLastRow(), COL.total).getValues(); // get values
  var now = new Date(); // time now
  // for each row of the sheet interate accross
  for (var i = 0; i < data.length; i  ){
    if (data[i][COL.report_id] != ""){ // if there is instance id do something
      // collect row values
      var report_id = data[i][COL.report_id]
      var type = data[i][COL.type];
      var next_run = data[i][COL.next_run] || 0; 
      // check if it's time to run the report again
      if (next_run < now && type != "never"){
        // if it is ping the report trigger
        var new_next_run = callUrl(report_id, type, {format: data[i][COL.format], ss_id: data[i][COL.ss_id], ss_sht: data[i][COL.ss_sht]} );
        // ..and record when to run again
        sheet.getRange(parseInt(i) 3, 3, 1, 2).setValues([[now, new_next_run]]);
      }
    }
  }
}

function callUrl(report_id, type, optParam){
  // build the url to trigger the report
  param.format = optParam.format || "print";
  if (optParam.ss_id && optParam.ss_sht){
    // if we have a sheet name and id force csv
    param.format = 'csv';
    // make a search string to find our report
    optParam.search_str = 'report/instance/' report_id '?reset=1 has:attachment is:unread';
    // store our search for later
    PS.setProperty('search_str_' report_id, JSON.stringify(optParam));
    // set the script to read the email run 15min later
    ScriptApp.newTrigger("processInbox")
    .timeBased()
    .after(1 * 60 * 1000)
    .create();
  }
  // make url
  var qs = BASE_URL
  for(var key in param) {
    if (key.substring(0, 10) != "search_str"){
      var value = param[key];
      qs  = key   "="   value   "&";
    }
  }
  qs  = "instanceId=" report_id;
  try {
    //gg var resp = UrlFetchApp.fetch(qs); // hit the url
    // now calculate when to run again
    var d = new Date();
    d.setHours(RERUN_HOUR);
    d.setMinutes(0);
    switch (type){
      case "daily":
        d.setDate(d.getDate()   1);
        break;
      case "weekly":
        d.setDate(d.getDate()   7);
        break;
      case "monthly":
        // Get the first Monday in the month
        d.setDate(1);
        d.setMonth(d.getMonth()   1);
        while (d.getDay() !== 1) {
          d.setDate(d.getDate()   1);
        }
        break;
    }
    return d;
  } catch(e) {
    return e.message; 
  }
}

function processInbox(){
  var PS = PropertiesService.getScriptProperties();
  var data = PS.getProperties();
  for (var key in data) {
    try { if (key.substring(0, 10) == "search_str"){
      var param_raw = data[key];
      var param = JSON.parse(param_raw);
      // get last 20 message threads using serach term
      var threads = GmailApp.search(param.search_str, 0, 20); 
      // assume last thread has our latest data
      var last_thread = threads.length-1;
      if (last_thread > -1){
        // get message in the last thread        
        var msg =  threads[last_thread].getMessages()[0];
        // get the attachments
        var attachments = msg.getAttachments();
        for (var k = 0; k < attachments.length; k  ) {
          // get the attachment as a string
          var csv_str = attachments[k].getDataAsString();
          // parse string as csv
          var csv = Utilities.parseCsv(csv_str);
          // create destination object
          var doc = SpreadsheetApp.openById(param.ss_id);
          var sheet = doc.getSheetByName(param.ss_sht);
          // clear any old data
          sheet.clear();
          // write new data
          sheet.getRange(1, 1,  csv.length, csv[0].length).setValues(csv);
          // mark message are read and archive (you could also label or delete)
          threads[last_thread].moveToArchive().markRead();
          PS.deleteProperty(key);
        }
      }
    }
    } catch(e) {
    SpreadsheetApp.getUi().alert('problem: ${e}'); 
  }
  }
}

CodePudding user response：

at processInbox(processInbox Code:46:18)

The syntax is

at ${FUNCTION}(${FILE}:${LINE}:${COLUMN})

This would suggest that the code causing the error is elsewhere.

At file

processInbox Code

within function

processInbox

And at line

46

and at column

18

You probably have a same function name processInbox at a different file named processInbox Code. In that file, at line 46, col 18, you'll have your error.

CodePudding user response：

I think that you problem may be that this line:

var data = sheet.getRange(3, 1, sheet.getLastRow(), COL.total).getValues();

should be like this:

var data = sheet.getRange(3, 1, sheet.getLastRow() - 2, COL.total).getValues();