Formatting the result and making it check if it goes up or down 15%

Question

I'm pretty new at coding so I apologize if this is something simple that I'm overlooking or missing.

function emailTest() {  
var sheet=SpreadsheetApp.getActiveSheet();
var currentROI=sheet.getRange("D2").getValue();
var previousROI=sheet.getRange("D3").getValue();

if((currentROI/previousROI)-1<=.10) {
 pctChange=currentROI/previousROI-1;
 MailApp.sendEmail({
 to:"test@test.com",
 subject:"Test email for auto-trigger",
 htmlBody:"sampleROI increased by " + pctChange + "from " + previousROI +" to " + currentROI +"."
 });
}

}

My code is above. Essentially what I want to do is find out if the cell value drops goes under 10%, then send an email to a given account.

I eventually want this to do "if the current cell goes up or down by 15% from the previous day, then send an email to x people".

It looks like the code is saving just fine but once I try to add a time trigger, it gives me the "a server error occurred" error message which, after extensive reading, seems like its a code issue.

Edit:

It's actually running now (forgot to add permissions) but if I could get some help with formatting the result and making it check if it goes up or down 15% that would be super helpful.

score 1 · Accepted Answer · answered May 31 '18 at 04:38

1

This code should work (you had a bunch of syntax problems in your code)

function emailTest() {  
  var sheet = SpreadsheetApp.getActiveSheet();
  var currentROI = sheet.getRange("D2").getValue();
  var previousROI = sheet.getRange("D3").getValue();

  if (((currentROI/previousROI)-1 >= .15) || ((currentROI/previousROI)-1 <= -.15)) {
    pctChange = currentROI/previousROI-1;
    sheet.getRange("D4").setValue([pctChange])
    var to = "test@test.com"
    var subject = "Test email for auto-trigger"
    var body = "sampleROI increased by " + pctChange + "from " + previousROI +" to " + currentROI +"."
    GmailApp.createDraft(to, subject, body);
    //After debugging change ^^ to GmailApp.sendEmail(to, subject, body);
  }
}

I always start by creating Drafts and when I am 100% sure my code works, I switch to GmailApp.sendEmail. If you want to use HTML, ask for specifics; it's easy to incorporate simple HTML.

answered May 31 '18 at 04:38

Steve Gon

347
6
18

Hey Steve, what was wrong syntax wise with the code I wrote? The main difference I see is that you made the draft and fed in the email info into variables. – Falcor May 31 '18 at 16:03
The email I received after I ran the code above is the following "sampleROI increased by -0.46270171315651865 from 0.2207439196159684 to 0.11860532984077499." I want it to be formatted as follows "sampleROI increased(or decreased) by -46.27% from 22.07% to 11.86%." – Falcor May 31 '18 at 16:08
See here https://stackoverflow.com/questions/149055/how-can-i-format-numbers-as-dollars-currency-string-in-javascript for formatting. – Steve Gon May 31 '18 at 21:37
Quick question Steve, is there a way to specify which spreadsheet to pull from? For example, I have multiple spreadsheets in a given workbook and the code above is sending an email from the incorrect spreadsheet. – Falcor Jun 01 '18 at 23:30
Hey Falcor. Yes. Try this: myTab = sheet.getSheetByName("Sheet1"); – Steve Gon Jun 03 '18 at 04:42
FYI, Google Docs -> Spreadsheet (in Excel "workbook) -> Sheet (in excel tab) -> row, column, cell – Steve Gon Jun 03 '18 at 04:44

Formatting the result and making it check if it goes up or down 15%

1 Answers1