Export All Locations and Bid Modifiers to a Google Sheet

[Feras Khalbuss]

Hi,

How would I create a script that exports locations from a campaign as well as the existing bid modifiers to a Google sheet? I'm new to scripting. Here is where I am so far (using script examples from AdWords scripts documentation). I suspect there is some code missing to input the URL and then loop through each location and export it to the spreadsheet.

function getTargetedLocations() {

  var campaignIterator = AdWordsApp.campaigns()

      .withCondition('Name = "INSERT_CAMPAIGN_NAME_HERE"')

      .get();

  if (campaignIterator.hasNext()) {

    var campaign = campaignIterator.next();

    var locationIterator = campaign.targeting().targetedLocations().get();

    while (locationIterator.hasNext()) {

      var targetedLocation = locationIterator.next();

      Logger.log('Location name: ' +

          targetedLocation.getName() + ', country code: ' +

          targetedLocation.getCountryCode() + ', bid modifier: ' +

          targetedLocation.getBidModifier());

    }

  }

}

 var spreadsheet = SpreadsheetApp.create("Report output");

   targetedLocation.exportToSheet(spreadsheet.getActiveSheet());

Thanks in advance for your help,

Feras

[Thea Vega (AdWords Scripts Team)]

Hi Feras,

Based on my understanding, your requirement is to populate a spreadsheet with the list of campaign locations and bid modifiers. If so, please see below sample code to help you get started based on your requirement.

function main(){
  var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';
  var SHEET_NAME = 'INSERT_SHEET_NAME_HERE';
  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getSheetByName(SHEET_NAME);
  
  sheet.appendRow(['Campaign Name','Location','Bid Modifier']); //headers for the spreadsheet
  
  var targetedLocationSelector = AdWordsApp.targeting()
  .targetedLocations();
  
  var targetedLocationIterator = targetedLocationSelector.get();
  while (targetedLocationIterator.hasNext()) {
    var targetedLocation = targetedLocationIterator.next();

    //you may add more rows depending on your requirement. 
    var campaignName = targetedLocation.getCampaign().getName();
    var locationName = targetedLocation.getName();
    var bid = targetedLocation.getBidModifier();
    sheet.appendRow([campaignName, locationName, bid]); 
  }
}

Also, you may see here for sample scripts about the SpreadsheetApp service.

Let me know if this is what you're looking for.

Thanks,

Thea

AdWords Scripts Team

[Feras Khalbuss]

Hi Thea,

This is very helpful thank you for your help. One last question I had is how would I modify this script to pull data for only one campaign that is hardcoded in a Google sheet? 

Thanks,

Feras

[Joyce Lava (AdWords Scripts Team)]

Hi Feras,

I am Thea's teammate and I'm here to provide support in the meantime. Could you please confirm if the spreadsheet where the campaignName will be hard-coded and the spreadsheet where the location and bidModifier will be logged is the same? If yes, please refer to the updated script below to get you started. You may need to create your spreadsheet saved in your Drive with the sample information available for the script to work properly.

Sample spreadsheet:

Campaign Name	Location	Bid Modifier
YOUR_CAMPAIGN_NAME_HERE

function main() {
  var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';
  var SHEET_NAME = 'INSERT_SHEET_NAME_HERE';
   
  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getSheetByName(SHEET_NAME);

  var campaignValue = sheet.getRange("A:A").getValues(); //assuming column A contains the campaign name
 
  var campaignNames = [];
 
  for (var i = 1; i < sheet.getLastRow(); i++) {
    campaignNames = campaignValue[i].join(',');
    Logger.log(campaignNames);
 
    //get campaigns
    var campaignIterator = AdWordsApp.campaigns()
      .withCondition("Name = '"+campaignNames+"'")

      .get();
 
    if (campaignIterator.hasNext()) {
       var campaign = campaignIterator.next();
       var locationIterator = campaign.targeting().targetedLocations().get();

   
       Logger.log(locationIterator.totalNumEntities())
   
       var ctr = locationIterator.totalNumEntities() + 1; //used to log all locations targeted by the campaign

       while (locationIterator.hasNext()) {
              var targetedLocation = locationIterator.next();

              Logger.log(targetedLocation.getName());
              sheet.getRange(ctr, 2).setValue(targetedLocation.getName()); //saves location name in column B
              sheet.getRange(ctr, 3).setValue(targetedLocation.getBidModifier()) //saves bidModifier in column C
              ctr --;
       }
     }
  }
}

Regards,

Joyce Lava

AdWords Scripts Team