CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
vacamaromba
vacamaromba

Reputation: 3

Google Apps Script: Compare two columns and do something if condition match in a any row of the range

I'm completely new is this thing of scripting/programming, so I'm sorry if I've made something really stupid below

The scenario:

In column "D" I pull stock prices from Google with =GOOGLEFINANCE(B2;"PRICE") In column "K" I setup a value that I'm waiting the paper to reach

Real sheet image

I've wrote (or at least I thing I did) a script (pasted below) that sends me an e-mail when "D" < "K". Actually it's working, but only for the first row. The trigger is correctly configured and working, since when in the first row the condition above is matched, I receive an e-mail.

What I need:

A script that reads the column "D" and compare it with the column "K", row by row. If "D" < "K" send me an e-mail for each occurrence. Preferably with the paper name in the subject, which is stored in column "B" Something like: "Alert! Paper TAEE3 reched the value"

function PaperValue() {
  var valorAlertaRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Situação").getRange("K2:K16"); 
  var valorAlerta = valorAlertaRange.getValue();
  var valorAtualRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Situação").getRange("D2:D16"); 
  var valorAtual = valorAtualRange.getValue();
  if (valorAtual <= valorAlerta){
    var subject = 'VALOR DO ALERTA ATINGIDO!';
    var message = 'O valor da ação atingiu o preço definido no alerta;  
    MailApp.sendEmail("[email protected]", subject, message);
  }
}

Thank you for your time!

Upvotes: 0

Views: 1595

Answers (1)

Cooper
Cooper

Reputation: 64042

Try this:

function PaperValue() {
  var krg = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Situação").getRange("K2:K16");
  var kvs = krg.getValues();
  var drg = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Situação").getRange("D2:D16");
  var dvs = drg.getValues();
  var subject = 'VALOR DO ALERTA ATINGIDO!';
  var message = 'O valor da ação atingiu o preço definido no alerta';
  kvs.forEach((k, i) => {
    if (dvs[i][0] <= k[0] && MailApp.getRemainingDailyQuota() > 0) {
      MailApp.sendEmail("[email protected]", subject, message);
    }
  });
}

Explanation:

getValue() returns a single (top-left) cell in the range. Because of this, it is only working for the first row. Use getValues() and iterate instead.

Upvotes: 2

Related Questions