如何修复能正常运行的OnChange脚本，然后在修改Google表格后停止工作 [英] How to fix an OnChange script that worked perfectly, then stopped working when the Google Sheet was modified

问题描述

概述:工作簿根据每个记录的类别得分计算每个记录(或行)的总体得分.类别分数是根据对每个记录的数百个数据点执行的计算得出的.我根据在网上找到的一些示例和教程，编译了代码以发送一封名为checkComplete的电子邮件onChange(如下).仅当记录"的状态更改为已完成"时，checkComplete才会发送电子邮件.因为工作簿使用Importrange加载原始数据，所以onChange触发器是必需的，并且onEdit触发器不会发生人工交互.

Overview: the workbook calculates an Overall Score for each Record (or row) based on the Category Scores for each Record. Category Scores are derived from computations performed on hundreds of data points for each Record. I compiled code to send an email onChange called checkComplete (below) based on several examples and tutorials I found online. checkComplete would only send an email if the status of the Record changed to "Complete." An onChange trigger is required since the workbook uses Importrange to load the raw data, and no human interaction takes place for an onEdit trigger.

问题开始于当我注意到进行任何更改时都会发送一封电子邮件，而不仅仅是O列中的状态从空白单元格更改为完成".但是代码运行良好，直到我对Google表格工作簿进行了重大修改，其中包括:

The problem began when I noticed an email would be sent upon any change being made, not just the status change in column O from a blank cell to "Complete". But the code worked well enough until I made significant edits to the Google Sheets workbook that included:

1. 修改计算公式以显示设定的小数位数

1. Modifying computational formulas to display a set number of decimals

例如= if(round('2-Comp'！F14,4)= 0，"，round('2-Comp'！F14,4))

e.g. =if(round('2-Comp'!F14,4)=0,"",round('2-Comp'!F14,4))

移动列

Moving columns around

例如切换N和O列，其中N是由onChange触发器检查的原始列

e.g. switching columns N and O, where N was the original column checked by the onChange trigger

更新源数据，从而更新分数.

Updating source data, which therefore updated scores.

例如记录的数据点从$ 52,420变为$ 54,323

e.g. a Record's data point changed from $52,420 to $54,323

然后，电子邮件轰炸开始了.最终结果是，脚本开始从第一条记录开始为每个记录发送电子邮件，并且将连续生成以下错误消息:

Then the email bombardment began. The net result is that the script began to send an email for every Record starting with the first record, and would generate the following error messages in succession:

服务在一天中被调用太多次:电子邮件.(第47行，文件"checkComplete")

Service invoked too many times for one day: email. (line 47, file "checkComplete")

超出最大执行时间

一天中使用过多计算机时间的服务

Service using too much computer time for one day

此后，我已注释掉MailApp.sendEmail()以停止炸毁我的收件箱，但是脚本继续生成超出了最大执行时间"和服务占用一天的计算机时间过多".我以为该脚本正在解决上述所有更改，但是现在这个问题持续了一个多星期，我决定寻求帮助.

I have since commented out MailApp.sendEmail() to cease blowing up my inbox, but the script continues to generate the "Exceeded maximum execution time" and "Service using too much computer time for one day". My thought was that the script was working off all the changes described above, but now that this problem has persisted for over a week I decided to seek out help.

我认为问题出在数组上，尤其是var oldValues，所以我尝试了:

I thought the issue is with the array, specifically var oldValues, so I tried:

1. 调用flush()函数清除数组，但这并不能解决问题.

1. to call flush() function to clear out the array, which did not solve the issue.

按照此线程清空数组-如何我要在JavaScript中清空数组吗?，但这也不能解决问题.

to empty the array as per this thread - How do I empty an array in JavaScript?, but that did not resolve the issue either.

我现在认为问题可能与记录器有关，但老实说，我很困惑.

I now think the problem may be related to the logger, but to be honest I am stumped.

以下是脚本的全部源代码:

Below is the script's source code in its entirety:

checkComplete.gs

checkComplete.gs

var admin_email='[e-mail]'; //<- list of email recipients goes here

function checkComplete() {
  var sh = SpreadsheetApp.getActiveSheet();
  var values = sh.getRange('O14:O').getValues().join('-');
  if(PropertiesService.getScriptProperties().getKeys().length==0){ // first time you run the script
  PropertiesService.getScriptProperties().setProperty('oldValues', values);
  return;
}
  var oldValues = PropertiesService.getScriptProperties().getProperty('oldValues').split('-');
  var valuesArray = values.split('-');
  while (valuesArray.length>oldValues.length){
    oldValues.push('x'); // if you append some rows since last exec
  }
  Logger.log('oldValues = '+oldValues)
  Logger.log('current values = '+valuesArray)
  for(var n=0;n<valuesArray.length;n++){
    if(oldValues[n] != valuesArray[n]){ // check for any difference
      sendMail(n+1,valuesArray[n]);
    }
  }
   PropertiesService.getScriptProperties().setProperty('oldValues', values);

}
function sendMail(row,val){
  Logger.log('value changed on row '+row+' value = '+val+' ,  mail sent');
  var rank = SpreadsheetApp.getActiveSheet().getRange(row,1).getValue();
// other var that I deleted here to shorten this post
// MailApp.sendEmail(admin_email,'message that calls vars');

}

我希望仅在将O列中的记录状态从空白单元格更改为完成"时收到电子邮件.所有其他数据更新均不应触发任何电子邮件.任何建议/帮助表示赞赏.感谢您抽出宝贵的时间来审查我的问题.

I expect to receive an email only upon the change of a Record's status, indicated in column O, from a blank cell to "Complete". All other data updates should not trigger any email. Any advice/help is appreciated. Thank you for taking the time to review my problem.

推荐答案

更新

在指定O列中的条目将由人类完成之后并会触发 onEdit()触发器，这是一个简单而优雅的方法使用事件对象:

After specifying that the entries in Column O will be made by a human and will fire an onEdit() trigger, here is an easy and elegant solution making use of event objects:

 onEdit(e){
  if( e.range.getSheet().getName()=='Sheet1' &&
    e.range.getColumn()==15&&e.value=="Complete"&&e.oldValue!="Complete"){
    sendMail(e.range.getRow(),e.value);
   }  
}  

您可以将 function checkComplete()及其onChange触发器替换为此 onEdit 函数.

You can replace function checkComplete() and its onChange trigger by this onEdit function.

---

查看您的电子表格后，我发现了以下问题:

---

After reviewing your spreadsheet I noticed following issues:

1. 如我的评论中所述，onEdit将由电子表格中任何工作表的更改触发，但是您希望代码仅针对工作表"Dashboard"中的更改运行.而且，您也不想将 Dashboard 中的O列与其他工作表中的O列进行比较-此处的所有条目都是不同的，并且会触发对 sendMail .您应该实施条件语句，以在继续其余代码之前，在仪表板的最开始处验证您是否在正确的工作表中.

1. As mentioned in my comment, onEdit will be triggered by a change in any of the sheets within the spreadsheet, but you want the code to run only for changes in the sheet "Dashboard". And also, you do not want to compare the column O in Dashboard against the columns O in the other sheets - where all entries will be different and would trigger the call of sendMail. You should implement a condition statement to verify at the very beginning of "Dashboard that you are in the right sheet, before continuing with the rest of the code.

您的函数 sendMail 包含

  var rank = SpreadsheetApp.getActiveSheet().getRange(row,1).getValue();
  var totalranked = SpreadsheetApp.getActiveSheet().getRange(4,1).getValue();
  var address = SpreadsheetApp.getActiveSheet().getRange(row,2).getValue();
  var score = SpreadsheetApp.getActiveSheet().getRange(row,6).getValue();
  var outlook = SpreadsheetApp.getActiveSheet().getRange(row,14).getValue();
  var ildemand = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('1-Dmnd').getRange(row+1,9).getValue();
  var aldemand = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('1-Dmnd').getRange(row+1,14).getValue();
  var mcdemand = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('1-Dmnd').getRange(row+1,19).getValue();
  var increase = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('2-Comp').getRange(row,11).getValue();
  var occupancy = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('2-Comp').getRange(row,13).getValue();
  var medHHI = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('4-Demo').getRange(row+2,17).getValue();
  var medHV = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('4-Demo').getRange(row+2,19).getValue();
  var walkscore = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('3-Muni').getRange(row,19).getValue();
  var dmnd = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('1-Dmnd').getRange(row+1,5).getValue()
  var comp = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('2-Comp').getRange(row,5).getValue();
  var muni = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('3-Muni').getRange(row,5).getValue();
  var grade = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('3-Muni').getRange(row,17).getValue();
  var demo  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('4-Demo').getRange(row+2,5).getValue();

这是对每个 sendMail 调用的18次对 SpreadsheetApp 的调用.如果您将两个不同工作表的列O相互比较(请参阅1.)，则会调用此乘以 sendMail 的时间.请注意，对外部服务的任何请求都非常耗时且效率低下，应避免使用，请参阅

This is 18 calls to SpreadsheetApp for each call of sendMail. And this multiplied by the amount of time sendMail will be called if you compare columns O of two different sheets against each other (see 1.)! Be aware that any requests to external services are very time consuming and inefficient and should be avoided, see Best Practices. You should retrieve all those variables only once, outside of sendMail. And given that after this the code of sendMail will be short, you can implement it directly within your for loop.

1. 如果在工作表的开头而不是末尾添加一行-这将使新值与旧值发生偏移，因此，每一行都会触发 sendMail 的调用.您可以避免使用 indexOf .

1. If a row is appended in the beginning rather than the end of the sheet - this will shift the new values against the old values and thus, each row will trigger the call of sendMail. You can avoid but checking either the new contents are contained within the array with the old contents with indexOf.

从Apps脚本编辑器用户界面转到 Edit->当前项目的触发器，并检查是否意外地将多个触发器附加到文件中，这些触发器同时运行.

Go from the Apps Script editor UI to Edit->Current project's triggers and check if you accidentally attached multiple triggers to the file, which all run simultaneously.

考虑到第1，，2和3点，这是您的代码的改进建议:

Taking in consideration the points 1., 2. and 3., here is an improvement suggestion for your code:

var admin_email=XXXXX; //<- list email addresses here

function checkComplete() {
  var ss=SpreadsheetApp.getActive();
  var sh = ss.getActiveSheet();
  if(sh.getName()=="Dashboard"){
   var values = sh.getRange('O14:O').getValues().join('-');
   if(PropertiesService.getScriptProperties().getKeys().length==0){ // first time you run the script
    PropertiesService.getScriptProperties().setProperty('oldValues', values);
    return;
   }
   var oldValues = PropertiesService.getScriptProperties().getProperty('oldValues').split('-');
   var valuesArray = values.split('-');
   while (valuesArray.length>oldValues.length){
    oldValues.push('x'); // if you append some rows since last exec
   }
   Logger.log('oldValues = '+oldValues)
   Logger.log('current values = '+valuesArray)
   for(var n=0;n<valuesArray.length;n++){
    if(oldValues.indexOf(valuesArray[n])==-1){ // check for any difference
        Logger.log('value changed on row '+n+1+' value = '+valuesArray[n]+' ,  mail sent');
      sendMail(n+1,valuesArray[n]);
    }
   }  
   PropertiesService.getScriptProperties().setProperty('oldValues', values);
  }   
}


function sendMail(row,val){

   Logger.log('value changed on row '+row+' value = '+val+' ,  mail sent');

   var rank = sh.getRange(row,1).getValue();
   var totalranked = sh.getRange(4,1).getValue();
   var address = sh.getRange(row,2).getValue();
   var score = sh.getRange(row,6).getValue();
   var outlook = sh.getRange(row,14).getValue();
   var ildemand = ss.getSheetByName('1-Dmnd').getRange(row+1,9).getValue();
   var aldemand = ss.getSheetByName('1-Dmnd').getRange(row+1,14).getValue();
   var mcdemand = ss.getSheetByName('1-Dmnd').getRange(row+1,19).getValue();
   var increase = ss.getSheetByName('2-Comp').getRange(row,11).getValue();
   var occupancy = ss.getSheetByName('2-Comp').getRange(row,13).getValue();
   var medHHI = ss.getSheetByName('4-Demo').getRange(row+2,17).getValue();
   var medHV = ss.getSheetByName('4-Demo').getRange(row+2,19).getValue();
   var walkscore = ss.getSheetByName('3-Muni').getRange(row,19).getValue();
   var dmnd = ss.getSheetByName('1-Dmnd').getRange(row+1,5).getValue()
   var comp = ss.getSheetByName('2-Comp').getRange(row,5).getValue();
   var muni = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('3-Muni').getRange(row,5).getValue();
   var grade = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('3-Muni').getRange(row,17).getValue();
   var demo  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('4-Demo').getRange(row+2,5).getValue();


      // MailApp.sendEmail(admin_email,'EARLY results for '+address+' are ready!','EARLY says the Site is '+outlook+' and ranks # '+rank+' out of '+totalranked+' with an overall Score of '+score+'. The projected Net Demand is '+dmnd+' with: IL '+ildemand+' units, AL '+aldemand+' units and MC '+mcdemand+' units. The competitive environment is '+comp+' with an occupancy rate of '+occupancy*100+'% for nearby properties, and an expected supply increase of '+increase*100+'%. The location is '+walkscore+', and its public schools have a grade of '+grade+' making it '+muni+'. The demographic trends are '+demo+' with Median HHI of $'+medHHI+' and a median Home Value of $'+medHV+'. For more information, please visit https://docs.google.com/spreadsheets/d/1ydcXQilx6hxhI6HPpWTPT7Bq9a-gXPa7h8UC6KL9W8c/edit?usp=sharing.');
    // add senior growth rate(s) to the Demographic summary sentence - Sept 3 2019
}

这篇关于如何修复能正常运行的OnChange脚本，然后在修改Google表格后停止工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！