Background :
Recently there have been community posts about emails not being sent due to rate limit quota being reached error.
The AppSheet help article mentions the following about email quota rate limits. The automation monitor and audit logs are not properly catching this error.
Clarification on help article. request to @lizlynch
Certain points are not clear as to if an email ID , abc@defg.com receives 5 emails in an hour, if it will be considered as 1 recipient or 5 and what exact time is considered for sampling - clock hours or elapsed one hour whenever the rate quota limit exceeds. Will request @lizlynch to mention it in the help document.
Tip Overview:
Summary
One can count the number of email recipients in a certain time period using Google Apps Script.
It is very important to note that this is an indirect method and relies on another component ( GAS).
Also it does not track each and every bot running incident but counts emails recipients based on emails received in a certain time period ( GAS is set for hourly trigger.).
Also since AppSheet server’s hourly timestamp and GAS sampling timestamp instances may differ from each other, this GAS method is not a 100 % reliable alternative but can give a reasonably good idea of email volume every hour.
Logic behind the tip:
Summary
The GAS tracks emails received in a specific email ID ( in this case, app creator’s email ID) based on the email subject line having certain text ( “Order Confirmation for” in this test app and GAS , please replace suitably) and the email sender being “noreply@appsheet.com”.
The app creator will need to mention one email ID in the AppSheet’s email automation in “To” or “CC” or “BCC” settings. The GAS will track emails sent to this ID every hour.
The test app and GAS makes use of my ID ( app creator’s ID) in the “To” settings.
The main assumption in this tip is if app creator’s ID has received the email sent by the AppSheet automation bot, then all other Email IDs mentioned in the automation bot also have received the email. This idea behind this assumption has been discussed in the community before. I am just quantifying it using GAS to know more quantifiable crosschecking.
The GAS Code:
The GAS Code
The GAS for this task is given below
function countNewEmailsAndLog() {
const SHEET_ID = "Please mention your Google sheet file ID";
const SHEET_NAME = "Email_Tracker";
// const TARGET_CELL = "C2";
const SENDER = "noreply@appsheet.com"; // A) Sender email
const SUBJECT_KEYWORD = "Order Confirmation for"; // B) Subject contains this text
const scriptProperties = PropertiesService.getScriptProperties();
// Retrieve previous run timestamp
let lastRunTimestamp = Number(scriptProperties.getProperty("LAST_RUN_TIMESTAMP")) || 0;
// Retrieve last processed email timestamp
let lastEmailTimestamp = Number(scriptProperties.getProperty("LAST_EMAIL_TIMESTAMP")) || 0;
const currentRunTimestamp = Date.now();
// Gmail search query
const query = `from:${SENDER} subject:${SUBJECT_KEYWORD} newer_than:1d`;
const threads = GmailApp.search(query);
let firstEmailTime = null;
let lastEmailTime = null;
let totalRecipientCount = 0;
let latestProcessedEmailTime = lastEmailTimestamp;
threads.forEach(thread => {
const messages = thread.getMessages();
messages.forEach(msg => {
const msgTime = msg.getDate().getTime();
if (msgTime > lastEmailTimestamp) {
// Count recipients in "To"
const toRecipients = msg.getTo();
const recipientList = toRecipients
? toRecipients.split(",").map(r => r.trim()).filter(r => r.length > 0)
: [];
totalRecipientCount += recipientList.length;
// Track first and last email timestamps
if (!firstEmailTime || msgTime < firstEmailTime) {
firstEmailTime = msgTime;
}
if (!lastEmailTime || msgTime > lastEmailTime) {
lastEmailTime = msgTime;
}
// Update latest processed email timestamp
if (msgTime > latestProcessedEmailTime) {
latestProcessedEmailTime = msgTime;
}
}
});
});
// Prepare row data
const uniqueKey = Math.floor(Math.random() * 1e12); // 12-digit random key
const sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName(SHEET_NAME);
const row = [
uniqueKey, // A: Unique record key
firstEmailTime ? new Date(firstEmailTime) : "", // B: First email timestamp
lastEmailTime ? new Date(lastEmailTime) : "", // C: Last email timestamp
lastRunTimestamp ? new Date(lastRunTimestamp) : "", // D: Previous script run
new Date(currentRunTimestamp), // E: Current script run
totalRecipientCount // F: Count for this run
];
// Append row
sheet.appendRow(row);
// Save timestamps for next run
scriptProperties.setProperty("LAST_RUN_TIMESTAMP", currentRunTimestamp);
scriptProperties.setProperty("LAST_EMAIL_TIMESTAMP", latestProcessedEmailTime);
}
Further details:
Summary
The above GAS script queries a specific GMAIL ID ( It can be app creator’s ID") and counts the number of recipients in an hour in each email sent in that time period , where the email sender is “noreply@appsheet.com” and the email subject contains “Order Confirmation for”
The count is then stored in a specific cell of a Google sheet along with other important data points such as
A) first and last email’s timestamp to ensure that the emails have not overlapped between previous GAS run and current Gas run,
B) The last GAS run timestamp and the current GAS run time stamp to know that it 60 minutes ( 1 hour). Note that GAS running can be officially displaced by +/- 15 minutes per Google specs. But practically in may test, they always run accurately at one hour duration.
C) the count of recipients in one hour.
The screenshot of the backend Google sheet is as follows
The table view on the app for email tracking looks like below
The first two records are test records when GAs was manually run so they are not spaced at 60 minutes interval.
Acknowledgements:
Acknowledgements
The GAS code is entirely suggested by the AI option in Google search and mainly Microsoft Copilot. The code was near perfect and I did not have to do any changes except entering sheet ID , sheet name etc. I of course tested it using a test app before posting the tip.
Disclaimer
Summary
I believe a similar tip has not been published before. The community repository has become very large to search for overlap 100 % accurately and at times the ideas may overlap and posted before. I apologize, if similar tip has been posted before.
I have just tested it based on recent posts and not deployed in a production app. So there could be a few improvement points or maybe I missed some other easier implementation.