Below is the script that we use for the daily check of our accounts.
- Create a Google Spreadsheet and give access to your GAds email.
- Copy the below code to the scripts section in Google Ads.
// Replace with your Google Sheet ID
var SPREADSHEET_ID = ‘Your spreadsheet id’;
var TO_EMAIL = ‘youremail@account’;
// Replace with the subject of the email
var EMAIL_SUBJECT = ‘Daily Ads Report’;
function main() {
// Clear the entire sheet content before processing accounts
clearSheetContent();
var accountIterator = MccApp.accounts().get();
while (accountIterator.hasNext()) {
var account = accountIterator.next();
MccApp.select(account);
var statsYesterday = getStatsForDate(getYesterdayDate());
var statsLastWeek = getStatsForDate(getLastWeekDate());
// Update the Google Sheet with the data and alarm status
updateSheet(account.getCustomerId(), account.getName(), statsYesterday, statsLastWeek);
}
// Send email with the spreadsheet as an attachment
sendEmailWithAttachment();
}
function getStatsForDate(date) {
var report = AdsApp.report(
“SELECT Impressions, Cost, Conversions ” +
“FROM ACCOUNT_PERFORMANCE_REPORT ” +
“DURING ” + date + “,” + date
);
var row = report.rows().next();
return {
impressions: parseInt(row.Impressions, 10),
cost: parseFloat(row.Cost),
purchases: parseFloat(row.Conversions)
};
}
function getYesterdayDate() {
var yesterday = new Date();
yesterday.setDate(yesterday.getDate() – 1);
return Utilities.formatDate(yesterday, AdsApp.currentAccount().getTimeZone(), ‘yyyyMMdd’);
}
function getLastWeekDate() {
var lastWeek = new Date();
lastWeek.setDate(lastWeek.getDate() – 7);
return Utilities.formatDate(lastWeek, AdsApp.currentAccount().getTimeZone(), ‘yyyyMMdd’);
}
function clearSheetContent() {
var spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
var sheet = spreadsheet.getActiveSheet();
// Clear the entire sheet content
sheet.clearContents();
}
function updateSheet(accountId, accountName, statsYesterday, statsLastWeek) {
var spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
var sheet = spreadsheet.getActiveSheet();
// Add header row if the sheet is empty
if (sheet.getLastRow() === 0) {
sheet.appendRow([“Timestamp”, “Account ID”, “Account Name”, “Impressions (Yesterday)”, “Cost (Yesterday)”, “Purchases (Yesterday)”, “Impressions (Last Week)”, “Cost (Last Week)”, “Purchases (Last Week)”, “Alarm”]);
}
// Calculate alarm status
var alarmStatus = (statsYesterday.impressions < 0.2 * statsLastWeek.impressions || statsYesterday.cost < 0.2 * statsLastWeek.cost) ? “Alarm” : “”;
// Append data to the sheet
sheet.appendRow([
Utilities.formatDate(new Date(), AdsApp.currentAccount().getTimeZone(), ‘yyyy-MM-dd HH:mm:ss’),
accountId,
accountName,
statsYesterday.impressions,
statsYesterday.cost,
statsYesterday.purchases,
statsLastWeek.impressions,
statsLastWeek.cost,
statsLastWeek.purchases,
alarmStatus
]);
// Apply light grey background to sales columns
var lastRow = sheet.getLastRow();
var salesColumnsRange = sheet.getRange(lastRow, 7, 1, 3); // Columns G to I
salesColumnsRange.setBackground(‘#EFEFEF’);
}
function sendEmailWithAttachment() {
var spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
var sheetName = spreadsheet.getName();
var sheetAsBlob = spreadsheet.getBlob();
var sheetData = sheetAsBlob.getBytes();
var emailBody = ‘Please find the daily ads report attached.’;
MailApp.sendEmail({
to: TO_EMAIL,
subject: EMAIL_SUBJECT,
body: emailBody,
attachments: [{fileName: sheetName + ‘.pdf’, content: sheetData, mimeType: ‘application/pdf’}]
});
}
3. make ti run every day
This will create a daily updated spreadsheet with the below information for all the accounts that you manage.
Timestamp | Account ID | Account Name | Impressions (Yesterday) | Cost (Yesterday) | Purchases (Yesterday) | Impressions (Last Week) | Cost (Last Week) | Purchases (Last Week) | Alarm |
2023-08-14 12:02:19 | XXX-XXX-XXX | XXXXXX | 0 | 0 | 0 | 0 | 0 | 0 |
alarm will be filled when the //calculate alarm status is triggered so you can play with this.
This is a vey usefull script to be able to see if your ads are running and be alarmed
You will recieve the PDF of the spreadsheet also in email so please add your email in the var TO_EMAIL = ‘youremail@account’;