比较谷歌电子表格中的2列,如果值不匹配,使用谷歌脚本发送邮件 [英] Compare 2 columns in google spreadsheet, and if values do not match send a mail using google script
问题描述
我正在为Google表格中的列创建一个验证模块。
我在c列中有一个原始费率(oRate)列表。我在b栏中有实际的销售价格。每列销售率(sRate)记录在b栏中。脚本应将b列中的销售率与c列中的原始费率列表进行比较,如果任何值不相等,则应在d列中记录'相等'或'不相等',并且我应该收到警报邮件,因为每一个不等于的条目都提及产品的名称与价格不匹配。 (名字是用colA写的)。我写了部分脚本,但它们并不令人满意。
ABCDEF ...
1 name orRate sRate是否相等?
2项目1 10 10等于
3项目2 30 28不等于
。
n
第A部分 - 追踪变更
function trackChange(){
var s = SpreadsheetApp.getActiveSheet();
if(s.getName()==Auth){//检查我们是否在正确的表单上
var orate = s.getActiveCell();
if(orate.getColumn()== 2){//检查列
var srate = r.offset(0,1);
if(srate.getValue()!= orate.getValue())//是否相等?
var auth ='不等于';
var authCell = srate.offset(0,1);
authCell.setValue(auth);
};
};
}
使用时间限制触发器进行设置。然而,它从不编辑表。
B部分 - 发送邮件
function sendMail(){
if(SpreadsheetApp.getActive()。getSheetByName('Auth')。getRange('E2:E676')。getValue()=='not equal')返回;
MailApp.sendEmail(abc@xyz.com,URGENT:AUTHORIZE CHANGE OF sale price,mail body,{
name:abc
});
即使所有单元格都相等,它也会每分钟发送一封邮件。我无法弄清楚如何添加colA产品的名称。
请帮助!
尝试使用以下代码:
函数checkEqual(){
var source = SpreadsheetApp.openById(fileID)
var sheet = source.getSheetByName(Sheet2) ;
var data = sheet.getDataRange()。getValues();
for(var i = 1; i< data.length; i ++){
//第一列对于结果是空的
var first = data [i] [1];
var second = data [i] [2];
Logger.log(first ++ second)
if(first == second){
var cell = sheet.getRange( 1 + 1,4);
cell.setValue(equal)
// emailOwner();
var cell2 = sheet.getRange(i + 1,5);
cell2.setValue(emailed)
}
else {
}
}
函数emailOwner(){
//电子邮件代码
}
这应该有助于您检查列是否相同,如果不相等则会发送电子邮件。
希望这有助于您。
I am creating an authentication module for a rate list in a column on google sheet. I have an original rate (oRate) list in column c. I have the actual selling price in column b. every sale rate (sRate) is recorded in column b. the script should compare sale rate in column b with the original rate list in column c, and if any value is not equal, it should record, 'equal' or 'not equal' in column d, and i should get an alert mail, for every 'not equal' entry mentioning the name of product for with the rates do not match. (names are written in colA). I have written parts of script but they are not satisfactory.
Data Format
A B C D E F ...
1 name oRate sRate is equal?
2 item1 10 10 equal
3 item2 30 28 not equal
.
n
PART A - Track Changes
function trackChange() {
var s = SpreadsheetApp.getActiveSheet();
if( s.getName() == "Auth" ) { //checks that we're on the correct sheet
var orate = s.getActiveCell();
if( orate.getColumn() == 2 ) { //checks the column
var srate = r.offset(0, 1);
if( srate.getValue() != orate.getValue() ) //is it equal?
var auth = 'not equal';
var authCell = srate.offset(0,1);
authCell.setValue(auth);
};
};
}
Set with a time bound trigger. However it never edits sheet.
PART B - Send mail
function sendMail() {
if (SpreadsheetApp.getActive().getSheetByName('Auth').getRange('E2:E676').getValue()== 'not equal') return;
MailApp.sendEmail("abc@xyz.com", "URGENT: AUTHORIZE CHANGE OF sale price", "mail body", {
name: "abc"
});
It sends a mail every every minute even when all cells are equal. And i can not figure out how to add the name of product from colA. Please Help!
Try using this code:
function checkEqual(){
var source = SpreadsheetApp.openById("fileID")
var sheet = source.getSheetByName("Sheet2");
var data = sheet.getDataRange().getValues();
for (var i=1; i<data.length; i++){
//first column will be empty for results
var first = data[i][1];
var second = data[i][2];
Logger.log(first + " " +second)
if(first == second){
var cell = sheet.getRange(i+1,4);
cell.setValue("equal")
//emailOwner();
var cell2 = sheet.getRange(i+1,5);
cell2.setValue("emailed")
}
else{
}
}
}
function emailOwner(){
//email code
}
This should help you check the column if they are equal and email if they are not.
Hope this helps.
这篇关于比较谷歌电子表格中的2列,如果值不匹配,使用谷歌脚本发送邮件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!