值正确且在范围内，但仍会发送电子邮件 [英] Values are correct and within range but still sends an email

问题描述

我在电子表格回复中有一个上限和下限，我在回复的右边输入.极限值都在F2和G2上.F2为下限，G2为上限.该代码使用onFormSubmit函数，并且具有 MailApp.sendEmail 函数，该函数将在有人输入超出限制的数字时发送电子邮件.

I have a Upper Limit and Lower Limit on my spreadsheet responses that I input towards the right of my responses. The limits are both on F2 and G2. F2 is Lower Limit and G2 is Upper Limit. The code is using the function onFormSubmit and it has an MailApp.sendEmail function that will send an email in case someone enters a figure that is out of the limit.

我不明白，提交表单时读取的值实际上在范围内，但仍会触发电子邮件.

What I do not understand that, the value that I read when the form is submitted is actually within the range but it still triggers an email.

我已经在全新的表格和电子表格上专门尝试了此代码，但没有任何问题.

I have tried this code specifically on a brand new form and spreadsheet and it did not have any problem.

function onFormSubmit(e){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Responses 1");

  var speedUL = sheet.getRange("K2").getValue(); //250V
  var speedLL = sheet.getRange("L2").getValue(); //207V

  //var speedUL= parseInt(activess.getRange("K2").getValue()); //250V
  //var speedLL = parseInt(activess.getRange("L2").getValue()); //207V

  var speed1 = parseInt(e.values[2]);
  if((speed1 >= speedUL) || (speed1 <= speedLL)){

    console.log('SpeedUL: '+speedUL);
    console.log('SpeedLL: '+speedLL);
    console.log('Speed1: '+speed1);

  var template1 = HtmlService.createTemplateFromFile("speed1");
  template1.speed1 = speed1;

  MailApp.sendEmail("[e-mail] ",
                    "Out of Range Notification Speed of VSD",
                    "",
                    {htmlBody : template1.evaluate().getContent()});

  }else
  { 
    return;
  }
}

这是我要尝试的测试脚本.有了这段代码，我得到了

This is the test script that I am trying to do. With this code, I'm getting this

错误2019年8月26日，下午5:24:29TypeError:无法调用null的方法"getRange".在onFormSubmit(Code:7)

第7行指的是 var speedUL = sheet.getRange("K2").getValue();

推荐答案

可能的原因:

• getRange()是在电子表格类( ss )而不是工作表类( sheet )上调用的.这通常意味着获取第一张纸的G2和F2值，而不是指定的纸.
• e.values 是字符串数组.将数字与字符串进行比较时，会进行隐式转换.这可能不是造成问题的原因，但仍需要解决.

Probable Cause:

• getRange() is called upon spreadsheet class (ss) and not on the sheet class (sheet). This usually means value of G2 and F2 of the first sheet is taken and not the specied sheet.
• e.values is array of strings. Implicit conversion takes place when numbers are compared with strings. This is probably not the cause of your issue, but still needs to be addressed.

• 在电子表格上调用 getRange()时，在工作表上调用 getRange()或指定工作表名称.

• Call getRange() on the sheet or specify the sheet name, when calling getRange() on spreadsheet.

使用 e.value 到数字的显式类型转换.

Use explicit type conversion of e.value to number.

var temperature = Number(e.values[1]);
var temperatureUCL = ss.getRange("H3 Reponses!G2").getValue();
//or
var temperatureUCL = sheet.getRange("G2").getValue();

参考文献:

• Spreadsheet#getRange
• Sheet#getRange

这篇关于值正确且在范围内，但仍会发送电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！