An “Orders” sheet grabs unique data from the App: Sales sheet
I want to generate an email to the restaurant that a new order has been placed (new row from a Unique formula in the “Orders” sheet)
This script below works with an onChange trigger (I’ve tested it manually by making various edits in the sheet itself). It checks to make sure that the OrderID (column A) and Sent (column M) is not blank. Then sends an email. Then marks Column M as “sent”:
function sendEmail() {
//setup function
var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
if (ActiveSheet.getName() == 'Orders') {
var StartRow = 2;
var RowRange = ActiveSheet.getLastRow() - StartRow + 1;
var WholeRange = ActiveSheet.getRange(StartRow,1,RowRange,14);
var AllValues = WholeRange.getValues();
//iterate loop
for (i in AllValues) {
//set current row
var CurrentRow = AllValues[i];
var SendTo = CurrentRow[10];
var subject = 'New Order';
var message = CurrentRow[11];
if (CurrentRow[0] !== "" && CurrentRow[12] == "") {
MailApp.sendEmail(SendTo, subject, message, {name:'Potluck', noReply: true});
var setRow = parseInt(i) + StartRow;
ActiveSheet.getRange(setRow, 13).setValue("sent");
}//if review ready
}//For loop close
}//if sheetName Review
}//End Func
However, when a new row comes into the App: Sales sheet (and thus populates a new row in the Orders sheet), the script doesn’t run.
As you have found out this event does not fire via the adding of rows via the API.
What you can do is create a function that loops through the sheet looking for new rows, that would then mark them in some way as not being new, then it would call this function. This looping function would be triggered by a Google Script timed trigger.
If you have not found the solution I will try to reproduce it in my test app tomorrow.
I have not tried something like you have done here, which is grabbing unique values from a sheet that is filled by API.
The difference between what I normally do and this is every time you have a new order in the App: Sales sheet, the unique formula runs and overwrite all rows in the sheet instead of a “new row” action.
What I would try first in this case is to change the ActiveSheet.getName to App: Sales then in the actions below grab the Orders sheet and start doing everything as you wish, as in telling that when ‘App: Sales’ is populated with a new row then go find the last row without “sent” label in the ‘Orders’ sheet.
What’s funny is that the onChange trigger works in the same sheet when I’m checking if a particular column value has been changed. Perhaps I replicate the same type of formula checking for my “sent” column.
You might also want to add a buffer time using sleep function to account for the time for the UNIQUE function to update your Orders sheet as well, if you want to try my method above.
Yes it would. I took a quick look before and just saw you were sending an email not realizing you had a loop already. [UPDATE] @ThinhDinh is correct however, you can’t check for active sheet as with the timed trigger you can’t count on that sheet being the active one. Just hard code the sheet to loop through.
onChange sees “App: Sales” as the active sheet because it’s the one that is actually being updated by the API, and not the UNIQUE function Bob uses in the “Orders” sheet despite it actually runs as well and add new rows.
The workaround I proposed would be: set the active sheet to check as “App: Sales”, whenever there’s a change in “App: Sales”, wait 2 or 3 seconds and then grabs the “Orders” sheet, check for any rows not labeled “sent” and put it into the email.
Hopefully it helps Bob and tell me if it does, would be a good note for me if it ever happens in the future. Thanks for the insights from George as well.
I don’t see the need or reason to check if “App: Sales” is the active sheet or not. If you run the script based on a timed trigger as far as Google is concerned there is no active sheet. The onChange event has a parameter that contains information about the event. https://developers.google.com/apps-script/guides/triggers/events. Timed Triggers do not have this event information as they are not fired as a result of an event.
So you would just have this line to open the sheet and eliminate the if statement “(ActiveSheet.getName() == ‘Orders’)”:
var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Orders');
The var name is irrelevant but technically it is not the “ActiveSheet.”
Ok…so if I’m understanding you correctly, replace the if statement in my app with the declaration that the Active Sheet is the Orders sheet and then run a timed trigger?