My script is timing out. How can I optimize it to run faster

Question

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()
        ]);
      }
    }
  }
}

Answer 1

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.