0

My sheet runs some calculations on a chart of numbers which updates automatically from a time-driven script. Within that script I put at its very end a function which compares two of the resulting values corresponding to some price ratios, and according to which value is bigger it checks whether a certain cell contains the phrase "PRICES UP" or "PRICES DOWN". If the phrase contained corresponds to the opposite ratio, the function should send me an email notifying me of the prices crossing up or down and then change the contained phrase to the appropriate one.

var currentStatus = calcSheet.getRange("H18").getValue();
var currentGreen = calcSheet.getRange("H3").getValue() * (-1);
var currentRed = calcSheet.getRange("H5").getValue();
if(currentGreen > currentRed){
  if(currentStatus == "PRICES UP"){
    MailApp.sendEmail("myemail", "Prices Crossed Down", "");
    calcSheet.getRange("H18").setValue("PRICES DOWN");
  }
  else{
    return;
  }
}
else if(currentRed > currentGreen){
  if(currentStatus == "PRICES DOWN"){
    MailApp.sendEmail("myemail", "Prices Crossed Up", "")
    calcSheet.getRange("H18").setValue("PRICES UP");
  }
  else{
    return;
  }
}

But the issue is that whenever the ifs return true and it tries to send an email, it results in the error "Exception: You do not have permission to call MailApp.sendEmail. Required permissions: https://www.googleapis.com/auth/script.send_mail". I don't know if triggered scripts should even encounter such errors, but I tried adding the "script.send_email" permission to the appsscript.json file as suggested elsewhere and that didn't solve it.

Any idea what is wrong and how to fix it?

0

OK, I found the issue so I'll answer my question. It wasn't with the code itself and it's not even about adding authorization lines to the appsscript.json file — I think writing these actually limited the types of authorization Google asked me to approve when creating a new trigger even though there were others required within the spreadsheet.

So the issue was that Google asks you to approve the required authorizations, as automatically deduced from the code written in the scripts, only when a new trigger is created or an existing one is edited. Creating a new trigger after updating the script opened a window asking to allow the macros to send emails under my name. Now it works fine and I didn't actually need to replace the original trigger (I preferred to keep that one as its timing is exactly at round and half hours), as changes to authorizations seem to apply to existing triggers as well.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.