Use variables in the .withCondition method

[Jake Baker]

So I'm writing a script that pulls details from a google spreadsheet and uses them to get stats from all our accounts. However, this part of the code is not working:

  

    var campaignIterator = AdWordsApp.campaigns()

      .withCondition(condition1)

      .withCondition(condition2)

      .get();

 

The "i" variable below refers to a for loop iterating through rows in the spreadsheet for all our accounts, and condition1 and condition2 are defined as such:

  var condition1Cell = sheet.getRange("f" + i); 

  var condition1 = condition1Cell.getValue();

  var condition2Cell = sheet.getRange("g" + i); 

  var condition2 = condition2Cell.getValue();

Finally, the content of the two cells are the following strings:

1st cell: 'Name CONTAINS "Brand"'

2nd cell: 'Name CONTAINS "US-"'

I'd expect the conditions to pull this content from the spreadsheet (i.e. .withCondition('Name CONTAINS "Brand"' and .withCondition('Name CONTAINS "US-"')), but instead my script throws an error on the line for "var campaignIterator = AdWordsApp.campaigns()": One of the conditions in the query is invalid. Do AdWords scripts forbid the use variables in the .withCondition method?

[AussieWeb Conversion]

Hi Jake

Variables are allowed in the .withCondition method. It may be just the way it's being built.
eg I use the following and it works perfectly.

    .withCondition("LabelNames DOES_NOT_CONTAIN '"+FINISHED_LABEL_NAME+"'")

Try

    var campaignIterator = AdWordsApp.campaigns()
      .withCondition(""+condition1)
      .withCondition(""+condition2)
      .get();

It may just force/coerce it to recognise the condition as a string due to the concatenation.

Also, double check that your condition string is being read from the spreadsheet correctly - Logger.log(condition1)
I recall Excel uses the single quote to designate a string entry for when the cell contains a number or something like that. 
Perhaps that initial single quote is being removed when the cell value is being read?
If that's the case, try switching your usage of single and double quotes for the conditions.
ie

1st cell: "Name CONTAINS 'Brand'"

Regards
Nigel

[Tyler Sidell (AdWords Scripts Team)]

Thank you Nigel for providing that solution.

Please let me know if you are still having issues with the script.

Thanks,
Tyler Sidell
AdWords Scripts Team

[Jake Baker]

Thanks for your help, much appreciated. I implemented your suggestions but still no luck. Below is the code including the spreadsheet from where I'm trying to grab data. Could you take a quick look? The script throws an error at the following line: var campaignIterator = AdWordsApp.campaigns(). The error is: One of the conditions in the query is invalid.

function main() {


  var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/1Fbxo9IWEHrnrjunEYMk07H_Tbdp6wwUKKngKfvX8Ddw/edit#gid=1796401850';
  var SHEET_NAME = 'Settings';
  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getSheetByName(SHEET_NAME);  
 
  // Get current date and subtract 1
  var today = new Date();
  var yesterday = new Date(today);
  yesterday.setDate(today.getDate() - 1);
  yesterday.setHours(0,0,0,0);
   
  var yesterdayFormatted = Utilities.formatDate(yesterday, "CST", "yyyyMMdd");
 
  for (i = 4; i < 18; i++) {  
   
  // Get account ID
  var idCell = sheet.getRange("b" + i);
  var id = idCell.getValue();  

  // Get start & end dates  
  var startDateCell = sheet.getRange("c" + i);
  var startDate = startDateCell.getValue();
  var startDateFormatted = Utilities.formatDate(startDate, "CST", "yyyyMMdd");
 
  var endDateCell = sheet.getRange("d" + i);
  var endDate = endDateCell.getValue();
  var endDateFormatted = Utilities.formatDate(endDate, "CST", "yyyyMMdd");

  // Get conditions (filters) for .withCondition() method  

  var condition1Cell = sheet.getRange("f" + i);
  var condition1 = condition1Cell.getValue();
  var condition2Cell = sheet.getRange("g" + i);
  var condition2 = condition2Cell.getValue();

 
  // Get account spend between start date of campaign(s) and yesterday. Start by selecting the account(s).
  var accountSelector = MccApp
  .accounts()
  .withIds([id]);
 
  // Create variables to hold the sum of each account's cost  
  var toDateSum = 0;
  var yesterdaySum = 0;
  var lastSevenDaysSum = 0;
 
  // Iterate through each account and update toDateSum, yesterdaySum and lastSevenDaysSum
  var accountIterator = accountSelector.get();  
  while (accountIterator.hasNext()) {
    var account = accountIterator.next();

  // Select the client account.
  MccApp.select(account);

  // Use conditions from spreadsheet to create campaignIterator (NOTE: THIS IS WHERE THE CODE THROWS AN ERROR)

    var campaignIterator = AdWordsApp.campaigns()
      .withCondition(condition1)
      .withCondition(condition2)
      .get();

    while (campaignIterator.hasNext()) {
      var campaign = campaignIterator.next();
      var toDateStats = campaign.getStatsFor(startDateFormatted, yesterdayFormatted);
      var yesterdayStats = campaign.getStatsFor('YESTERDAY');  
      var lastSevenDaysStats = campaign.getStatsFor('LAST_7_DAYS');  
      toDateSum += toDateStats.getCost();
      yesterdaySum += yesterdayStats.getCost();
      lastSevenDaysSum += lastSevenDaysStats.getCost();
    }  
  }  
 }
}

One more question about this. As you can see in the spreadsheet, not every account will have a .withCondition, but I'm using a for loop to iterate through them. Is it possible to add "if" logic to the .withCondition method? Something like this:

    var campaignIterator = AdWordsApp.campaigns()

      if (CELL IS NOT EMPTY) {

.withCondition(condition1)

      .withCondition(condition2)

      .get();

[Jake Baker]

Woops, hit the wrong button and posted too soon. 

As you can see in the spreadsheet, not every account will have a .withCondition, but I'm using a for loop to iterate through them. Is it possible to add "if" logic to the .withCondition method? Something like this:

    var campaignIterator = AdWordsApp.campaigns()

      if (CELL IS NOT EMPTY) {

         .withCondition(condition1)  

         .withCondition(condition2)

      }

      .get();

Some accounts have one campaign condition, others have two, others have none. Any kind of "if" logic to make the for loop work would be great. 

Thanks!

Jake

[Tyler Sidell (AdWords Scripts Team)]

Hi Jake,

Would you be able to supply me with access to your CID (reply privately to the author)?  I'm also going to request access to your spreadsheet to test the script.  I have a feeling that it may be due to quotation marks not added to the condition1 variable.  Try Logger.log(condition1) before your iterator to see what gets returned.

Thanks,

Tyler Sidell

AdWords Scripts Team

[Jake Baker]

Solution: The contents of the spreadsheet cells that get stored in variables condition1 and condition2 should not have encompassing quotes. For example, the cell whose contents are stored in variable condition1 should be Name DOES_NOT_CONTAIN 'Brand' rather than "Name DOES_NOT_CONTAIN 'Brand'".

[AussieWeb Conversion]

Hi Jake

Thanks for posting the solution.  I don't think I'd have guessed that.

Regards
Nigel