CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Apoorv Jain
Apoorv Jain

Reputation: 3

Compare 2 columns in google spreadsheet, and if values do not match send a mail using google script

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("[email protected]", "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!

Upvotes: 0

Views: 1739

Answers (1)

Mr.Rebot
Mr.Rebot

Reputation: 6791

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.

Upvotes: 1

Related Questions