I have a Google Form that records an expense into the expenses
sheet in a Google Sheet. Following this guide, I created this script that is supposed to send me an email with a value from another sheet in the Google Sheet every time the expenses
sheet is changed. I tested the email part of it and it works. The email sends, formatting looks good, etc.
function onChange(e) {
var sheetName = "expenses"
var sheet = e.source.getSheetByName(sheetName)
if (!sheet) {
Logger.log("Sheet not found: " + sheetName)
return
}
var currentRowCount = sheet.getLastRow()
var properties = PropertiesService.getScriptProperties()
var lastRowCount = properties.getProperty("lastRowCount")
if (!lastRowCount) {
properties.setProperty("lastRowCount", currentRowCount)
return
}
lastRowCount = parseInt(lastRowCount)
if (currentRowCount > lastRowCount) {
sendSpendingSummary()
Logger.log("Sent spending summary by email")
}
properties.setProperty("lastRowCount", currentRowCount)
}
function sendSpendingSummary() {
var sheet = SpreadsheetApp.getActive().getSheetByName('summary')
var range = sheet.getRange("A1")
var spending = range.getValue().toLocaleString("en-US", { style: "currency", currency: "USD"})
var recipient = "michael@example.com"
var subject = "Month-to-Date Spending Summary"
var body = "Month-to-Date Spending as of " + Utilities.formatDate(new Date(), "EST", "MMM d, yyyy") + ": " + spending
MailApp.sendEmail(recipient, subject, body)
}
However, the trigger itself doesn't actually run. It doesn't fail. It simply doesn't run, as shown in the Triggers page:
I'm sure there are other things about this script that could be improved. For now, though, I'm just trying to figure out why it doesn't run at all, even though submitting the Google Form does successfully add a row to the expenses
sheet.
There is a specific event for the form submissions. You can use it instead of the on-change event.
Only Google engineers can answer why the on-change event is not activated when a new form submission "changes" a spreadsheet; however, I think that a fair guess is that one of the safeguards they have set is preventing this from working.