debugging 错误消息引用了Google Apps脚本代码中不存在的行和列

qcbq4gxm  于 2022-11-14  发布在  Go
关注(0)|答案(2)|浏览(144)

我在Google Apps脚本中运行某些代码时收到以下错误消息。我不理解行/列引用,代码:46:18。它似乎指向列太少的行或行太少的进程。我认为我没有正确解释引用。

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

我所有代码的第46行是这样的,当然没有18列(它关闭了一个不引用格式的函数):

}

错误消息processInbox所引用的进程只有39行。
通过在菜单中选择“运行脚本”来调用脚本,在相关的Google Sheet中选择“CiviSchedule”来触发doTasks函数。此菜单和触发器在onOpen函数中创建。
我是如何误解错误消息的?(完整代码如下)
[错误截图] x1c 0d1x
[第40-46行的屏幕截图]

参考代码:

//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}'); 
  }
  }
}
s4n0splo

s4n0splo1#

在处理收件箱(处理收件箱代码:46:18)
语法为
位于${函数}(${文件}:${行}:${列})
这表明导致错误的代码在其他地方。
在文件中
处理收件箱代码
函数内
处理收件箱
在第一行
46
和列
18
您可能在另一个名为processInbox Code的文件中有一个相同的函数名processInbox。在该文件中,在4618列,您将看到您的错误。

s71maibg

s71maibg2#

我想,你的问题可能在于这一行:
var data = sheet.getRange(3, 1, sheet.getLastRow(), COL.total).getValues();
应该是这样的:
var data = sheet.getRange(3, 1, sheet.getLastRow() - 2, COL.total).getValues();

相关问题