How can I optimize the following script? It is timing out due to probably multiple loops. The loops are in order:
1-Loop through all Google Ads accounts.
2-Loop through all Ad Groups.
3-Within each Ad Group, check if any ads have a "DISAPPROVED" status. If yes, append the data to Google Sheets.
function main() {
var sheetId = 'XXX';
var sheetName = 'XXX';
var spreadsheet = SpreadsheetApp.openById(sheetId);
var sheet = spreadsheet.getSheetByName(sheetName);
if (!sheet) {
sheet = spreadsheet.insertSheet(sheetName);
} else {
// Clear the existing contents
sheet.clearContents();
}
// Adding headers (column names)
var headers = ['Account Name', 'Campaign', 'Ad Group'];
sheet.appendRow(headers);
// Iterating through each client account
var accountIterator = AdsManagerApp.accounts().get();
while (accountIterator.hasNext()) {
var account = accountIterator.next();
AdsManagerApp.select(account);
var adGroupIterator = AdsApp.adGroups()
.withCondition('CampaignStatus = ENABLED')
.withCondition('AdGroupStatus = ENABLED')
.get();
while (adGroupIterator.hasNext()) {
var adGroup = adGroupIterator.next();
// Check if the ad group has any disapproved ads
var disapprovedAdFound = false;
var adIterator = adGroup.ads().get();
while (adIterator.hasNext()) {
var ad = adIterator.next();
if (ad.getPolicyApprovalStatus() === 'DISAPPROVED') {
disapprovedAdFound = true;
break;
}
}
if (disapprovedAdFound) { // Disapproved ads found
// Record the details of the ad group with disapproved ads
sheet.appendRow([
account.getName(),
adGroup.getCampaign().getName(),
adGroup.getName()
]);
}
}
}
}
When I saw your script, in order to put values, appendRow
is used in a loop. In this case, the process cost becomes high. Ref (Author: me) In this answer, I modified appendRow
to setValues
.
function main() {
var sheetId = 'XXX';
var sheetName = 'XXX';
var spreadsheet = SpreadsheetApp.openById(sheetId);
var sheet = spreadsheet.getSheetByName(sheetName);
if (!sheet) {
sheet = spreadsheet.insertSheet(sheetName);
} else {
// Clear the existing contents
sheet.clearContents();
}
// Adding headers (column names)
var headers = ['Account Name', 'Campaign', 'Ad Group'];
sheet.appendRow(headers);
var values = []; // Added
// Iterating through each client account
var accountIterator = AdsManagerApp.accounts().get();
while (accountIterator.hasNext()) {
var account = accountIterator.next();
AdsManagerApp.select(account);
var adGroupIterator = AdsApp.adGroups()
.withCondition('CampaignStatus = ENABLED')
.withCondition('AdGroupStatus = ENABLED')
.get();
while (adGroupIterator.hasNext()) {
var adGroup = adGroupIterator.next();
// Check if the ad group has any disapproved ads
var disapprovedAdFound = false;
var adIterator = adGroup.ads().get();
while (adIterator.hasNext()) {
var ad = adIterator.next();
if (ad.getPolicyApprovalStatus() === 'DISAPPROVED') {
disapprovedAdFound = true;
break;
}
}
if (disapprovedAdFound) { // Disapproved ads found
// Record the details of the ad group with disapproved ads
// Modified
values.push([
account.getName(),
adGroup.getCampaign().getName(),
adGroup.getName()
]);
}
}
}
// Added
if (values.length == 0) return;
sheet.getRange(sheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
}
When this script is run, the retrieved values are put with setValues
.
Although I'm not sure about your actual situation, if the length of values
is large, please test using Sheets API. At that time, please modify the above script as follows. When you use Sheets API, please enable Sheets API at Advanced Google services.
From
sheet.getRange(sheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
To
Sheets.Spreadsheets.Values.update({ values }, sheetId, `'${sheetName}'!A${sheet.getLastRow() + 1}`, { valueInputOption: "USER_ENTERED" });
[account.getName(),adGroup.getCampaign().getName(),adGroup.getName()]
in your script. Please be careful about this.