My script is timing out. How can I optimize it to run faster
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.
Modified script: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" }); Note: In this answer, it supposes that you have already had the correct value of [account.getName(),adGroup.getCampaign().getName(),adGroup.getName()] in your script. Please be careful about this.