Google Ads Scripts - ads manager get data and update campaigns

get_all_accounts.gs

/* ------------------- ADD GOOGLE SHEET URL HERE -------------------------------- */
var GoogleSheetURL = "SHEET_URL_HERE";
/* ------------------------------------------------------------------------------ */
var googleSheet, timeStamp = Date.now();


function main() {
  googleSheet = connectToGoogleSheet(GoogleSheetURL);
  createAccountsList();
}

function createAccountsList() {
  var accountIterator = AdsManagerApp.accounts().get();
  var sheetData = [[
      "Account ID",
      "Account Name",
      "Enabled Campaigns"
    ]];
  
  while (accountIterator.hasNext()) {
    var account = accountIterator.next();
    AdsManagerApp.select(account);
    var campaigns = AdsApp.campaigns()
      .withCondition("Status = ENABLED")
      .get();
    
    // Set Data Rows
    sheetData.push([
      account.getCustomerId(),
      account.getName(),
      campaigns.totalNumEntities()
    ]);
  }
  
  writeDataToSheet(sheetData, "All Accounts");
};

function connectToGoogleSheet(urlString) {
  try {
    var sheet = SpreadsheetApp.openByUrl(urlString);
    Logger.log("Google Sheets Success: %s",sheet.getName());
    return sheet;
  } catch (error) {
    Logger.log("Google Sheets Fail: %s", error); 
  }
}

function getSheet(sheet, tabName) {
  return sheet.getSheetByName(tabName) || sheet.insertSheet().setName(tabName); 
}

function writeDataToSheet(data, tabName) {
  var maxCellCount = 50000;
  
  if (data.length > 1) {
    if (data.length > maxCellCount) {
      var chunks = Math.ceil(data.length / maxCellCount);
      for (var x = 0;chunks > x; x++) {
        var newSheet = googleSheet.insertSheet(tabName + " pg" + (x + 1) + " " + timeStamp);
        var start = x * maxCellCount;
        var end = (x + 1) * maxCellCount;
        var page = data.slice(start, end);
        newSheet.getRange(1,1,page.length,page[0].length).setValues(page);
        Logger.log("%s %s added to sheet.", page.length, tabName);
      }
    } else {
      var newSheet = googleSheet.insertSheet(tabName + " " + timeStamp);
      newSheet.getRange(1,1,data.length,data[0].length).setValues(data);
      Logger.log("%s %s added to sheet.", data.length, tabName);
    }
  } else {
    Logger.log("No active %s - nothing to do here.", tabName); 
  }
}

get_all_active_campaign_adgroup_ad.gs

/* ------------------- ADD GOOGLE SHEET URL HERE -------------------------------- */
var GoogleSheetURL = "https://docs.google.com/spreadsheets/d/1VwuyKevbDIUCXnu3NnWc8X_MdUgKNaZ3UGSbw2AKPSg/edit#gid=0";
/* ------------------------------------------------------------------------------ */
var actionListValues, googleSheet, timeStamp = Date.now();

function main() {
  googleSheet = connectToGoogleSheet(GoogleSheetURL);
  actionListValues = getActionListValues();
  createAdsList();
  createCampaignsList();
  createAdGroupList();
}

function createAdGroupList(sheet) {
  var accountIterator = AdsManagerApp.accounts().withIds(actionListValues).get();
  var sheetData = [[
    //"Account ID",
    "Campaign ID",
    "Ad Group ID",
    //"Ad Group Name",
    "Custom Params",
    "Final URL Suffix",
    "Tracking Template",
  ]];
  
  while (accountIterator.hasNext()) {
    var account = accountIterator.next();
    AdsManagerApp.select(account);
    
    var adGroups = AdsApp.adGroups()
      .withCondition("Status = ENABLED")
      .withCondition("CampaignStatus = ENABLED")
      .get();
    
    Logger.log("AdGroups Count: %s, account: %s",adGroups.totalNumEntities(),account.getCustomerId());
    
    while (adGroups.hasNext()) {
      var adG = adGroups.next();
      sheetData.push([
        //account.getCustomerId(),
        adG.getCampaign().getId(),
        adG.getId(),
        //adG.getName(),
        adG.urls().getCustomParameters(),
        adG.urls().getFinalUrlSuffix(),
        adG.urls().getTrackingTemplate()
      ]);
    }
  }
  
  writeDataToSheet(sheetData, "AdGroups");
};

function createAdsList(sheet) {
  var accountIterator = AdsManagerApp.accounts().withIds(actionListValues).get();
  var sheetData = [[
    //"Account ID",
    //"Campaign ID",
    "Ad Group ID",
    "Ad ID",
    "Custom Params",
    "Tracking Template",
    "Final URL",
    "Mobile Final URL",
    "Final URL Suffix",
    //"Type",
    //"Display URL",
    //"Headline"
  ]];
  
  while (accountIterator.hasNext()) {
    var account = accountIterator.next();
    AdsManagerApp.select(account);
    
    var ads = AdsApp.ads()
      .withCondition("Status = ENABLED")
      .withCondition("CampaignStatus = ENABLED")
      .withCondition("AdGroupStatus = ENABLED")
      //.withCondition("CreativeFinalUrls CONTAINS 'tracking.'")
      .get();
    
    Logger.log("Ads Count: %s, account: %s",ads.totalNumEntities(),account.getCustomerId());
    
    while (ads.hasNext()) {
      var ad = ads.next();
      sheetData.push([
        //account.getCustomerId(),
        //ad.getCampaign().getId(),
        ad.getAdGroup().getId(),
        ad.getId(),
        ad.urls().getCustomParameters(),
        ad.urls().getTrackingTemplate(),
        ad.urls().getFinalUrl(),
        ad.urls().getMobileFinalUrl(),
        ad.urls().getFinalUrlSuffix(),
        //ad.getType(),
        //ad.getDisplayUrl(),
        //ad.getHeadline()
      ]);
    }
  }
  
  writeDataToSheet(sheetData, "Ads");
};

function createCampaignsList(sheet) {
  var accountIterator = AdsManagerApp.accounts().withIds(actionListValues).get();
  var sheetData = [[
    "Account ID",
    "Campaign ID",
    //"Campaign Name",
    "Custom Params",
    "Final URL Suffix",
    "Tracking Template",
  ]];
  
  while (accountIterator.hasNext()) {
    var account = accountIterator.next();
    AdsManagerApp.select(account);
    
    var campaigns = AdsApp.campaigns()
      .withCondition("Status = ENABLED")
      .get();
    
    Logger.log("Campaigns Count: %s, account: %s",campaigns.totalNumEntities(),account.getCustomerId());
    
    while (campaigns.hasNext()) {
      var cmp = campaigns.next();
      sheetData.push([
        account.getCustomerId(),
        cmp.getId(),
        //cmp.getName(),
        cmp.urls().getCustomParameters(),
        cmp.urls().getFinalUrlSuffix(),
        cmp.urls().getTrackingTemplate(),
      ]);
    }
  }
  
  writeDataToSheet(sheetData, "Campaigns");
};

function connectToGoogleSheet(urlString) {
  try {
    var sheet = SpreadsheetApp.openByUrl(urlString);
    Logger.log("Google Sheets Success: %s",sheet.getName());
    return sheet;
  } catch (error) {
    Logger.log("Google Sheets Fail: %s", error); 
  }
}

function getSheet(sheet, tabName) {
  return sheet.getSheetByName(tabName) || sheet.insertSheet().setName(tabName); 
}

function getActionListValues() {
  var sheet = getSheet(googleSheet,"Action List");
  var values = sheet.getRange(1,1,sheet.getLastRow()).getValues() || [];
  return [].concat.apply([], values);
}

function writeDataToSheet(data, tabName) {
  var maxCellCount = 50000;
  
  if (data.length > 1) {
    if (data.length > maxCellCount) {
      var chunks = Math.ceil(data.length / maxCellCount);
      for (var x = 0;chunks > x; x++) {
        var newSheet = googleSheet.insertSheet(tabName + " pg" + (x + 1) + " " + timeStamp);
        var start = x * maxCellCount;
        var end = (x + 1) * maxCellCount;
        var page = data.slice(start, end);
        newSheet.getRange(1,1,page.length,page[0].length).setValues(page);
        Logger.log("%s %s added to sheet.", page.length, tabName);
      }
    } else {
      var newSheet = googleSheet.insertSheet(tabName + " " + timeStamp);
      newSheet.getRange(1,1,data.length,data[0].length).setValues(data);
      Logger.log("%s %s added to sheet.", data.length, tabName);
    }
  } else {
    Logger.log("No active %s in accounts - nothing to do here.", tabName); 
  }
}

get_all_enabled_campaigns.gs

/* ------------------- ADD GOOGLE SHEET URL HERE -------------------------------- */
var GoogleSheetURL = "https://docs.google.com/spreadsheets/d/1OvOaBhLyw7uENXFc0o_z823_ly1wQ2CPcT8IDNgt1HI/edit#gid=0";
/* ------------------------------------------------------------------------------ */
var actionListValues, googleSheet, timeStamp = Date.now();

function main() {
  googleSheet = connectToGoogleSheet(GoogleSheetURL);
  //actionListValues = getActionListValues(googleSheet);
  createCampaignsList();
}

function createCampaignsList() {
  var accountIterator = AdsManagerApp.accounts()
    //.withIds(actionListValues)
    .get();
  
  var sheetData = [[
    "Account Name",
    "Account ID",
    "Campaign ID",
    "Campaign Name",
  ]];
  
  while (accountIterator.hasNext()) {
    var account = accountIterator.next();
    AdsManagerApp.select(account);
    
    var campaigns = AdsApp.campaigns()
      .withCondition("Status = ENABLED")
      .get();
    
    Logger.log("Campaigns Count: %s, account: %s",campaigns.totalNumEntities(),account.getCustomerId());
    
    while (campaigns.hasNext()) {
      var cmp = campaigns.next();
      sheetData.push([
        account.getName(),
        account.getCustomerId(),
        cmp.getId(),
        cmp.getName(),
      ]);
    }
  }
  
  writeDataToSheet(sheetData, "All Campaigns");
};

function connectToGoogleSheet(urlString) {
  try {
    var sheet = SpreadsheetApp.openByUrl(urlString);
    Logger.log("Google Sheets Success: %s",sheet.getName());
    return sheet;
  } catch (error) {
    Logger.log("Google Sheets Fail: %s", error); 
  }
}

function getSheet(sheet, tabName) {
  return sheet.getSheetByName(tabName) || sheet.insertSheet().setName(tabName); 
}

function getActionListValues() {
  var sheet = getSheet(googleSheet,"Action List");
  var values = sheet.getRange(1,1,sheet.getLastRow()).getValues() || [];
  return [].concat.apply([], values);
}

function writeDataToSheet(data, tabName) {
  var maxCellCount = 50000;
  
  if (data.length > 1) {
    if (data.length > maxCellCount) {
      var chunks = Math.ceil(data.length / maxCellCount);
      for (var x = 0;chunks > x; x++) {
        var newSheet = googleSheet.insertSheet(tabName + " pg" + (x + 1) + " " + timeStamp);
        var start = x * maxCellCount;
        var end = (x + 1) * maxCellCount;
        var page = data.slice(start, end);
        newSheet.getRange(1,1,page.length,page[0].length).setValues(page);
        Logger.log("%s %s added to sheet.", page.length, tabName);
      }
    } else {
      var newSheet = googleSheet.insertSheet(tabName + " " + timeStamp);
      newSheet.getRange(1,1,data.length,data[0].length).setValues(data);
      Logger.log("%s %s added to sheet.", data.length, tabName);
    }
  } else {
    Logger.log("No active %s in accounts - nothing to do here.", tabName); 
  }
}

update_camp_custom_params.gs

/* ------------------- ADD GOOGLE SHEET URL HERE -------------------------------- */
var GoogleSheetURL = "https://docs.google.com/spreadsheets/d/1OvOaBhLyw7uENXFc0o_z823_ly1wQ2CPcT8IDNgt1HI/edit#gid=0";
/* ------------------------------------------------------------------------------
/  Sheet must have a tab called "Action List" to function
/  Starting with cell A1 - list all the account IDs down column A with campiagn IDs in column B
/  each row should have an account ID and campaign ID
/  ------------------------------------------------------------------------------ */ 

var googleSheet, actionListValues, timeStamp = Date.now();

function main() {
  googleSheet = connectToGoogleSheet(GoogleSheetURL);
  actionListValues = getActionListValues();
  updateCampaigns();
}

function updateCampaigns() {
  var accountIterator = AdsManagerApp.accounts()
    .withIds(actionListValues.accounts)
    .get();
  var sheetData = [[
    "Campaign ID",
    "Campaign Name",
    "Old Custom Parameters",
    "New Custom Parameters"
  ]];
  
  while (accountIterator.hasNext()) {
    var account = accountIterator.next();
    AdsManagerApp.select(account);
    
    var campaigns = AdsApp.campaigns()
      .withIds(actionListValues.campaigns)
      .get();
    
    Logger.log("Campaigns Count: %s, account: %s",campaigns.totalNumEntities(),account.getCustomerId());
    
    while (campaigns.hasNext()) {
      var cmp = campaigns.next();      
      try {
        var customParams = cmp.urls().getCustomParameters() || {};
        cmp.urls().setCustomParameters(actionListValues.customParams[cmp.getId()]);
        sheetData.push([
          cmp.getId(),
          cmp.getName(),
          customParams,
          cmp.urls().getCustomParameters()
        ])
      } catch (error) {
        Logger.log("%s error getting parameters: %s", name, error);
      }
    }
  }
  
  writeDataToSheet(sheetData, "Updated Campaigns Custom Param");
}

function connectToGoogleSheet(urlString) {
  try {
    var sheet = SpreadsheetApp.openByUrl(urlString);
    Logger.log("Google Sheets Success: %s",sheet.getName());
    return sheet;
  } catch (error) {
    Logger.log("Google Sheets Fail: %s", error); 
  }
}

function getSheet(sheet, tabName) {
  return sheet.getSheetByName(tabName) || sheet.insertSheet().setName(tabName); 
}

function getActionListValues() {
  var campaigns = [];
  var accounts = [];
  var customParams = {};
  var sheet = getSheet(googleSheet,"Action List");
  var values = sheet.getRange(1,1,sheet.getLastRow(),3).getValues() || [[]];
  for (var x = 0; values.length > x; x++) {
    campaigns.push(values[x][1] + "");
    accounts.push(values[x][0] + "");
    customParams[values[x][1] + ""] = JSON.parse(values[x][2]);
  }
  return {
    accounts: removeDuplicates(accounts),
    campaigns: campaigns,
    customParams: customParams
  }
}

function writeDataToSheet(data, tabName) {
  var maxCellCount = 50000;
  
  if (data.length > 1) {
    if (data.length > maxCellCount) {
      var chunks = Math.ceil(data.length / maxCellCount);
      for (var x = 0;chunks > x; x++) {
        var newSheet = googleSheet.insertSheet(tabName + " pg" + (x + 1) + " " + timeStamp);
        var start = x * maxCellCount;
        var end = (x + 1) * maxCellCount;
        var page = data.slice(start, end);
        newSheet.getRange(1,1,page.length,page[0].length).setValues(page);
        Logger.log("%s %s added to sheet.", page.length, tabName);
      }
    } else {
      var newSheet = googleSheet.insertSheet(tabName + " " + timeStamp);
      newSheet.getRange(1,1,data.length,data[0].length).setValues(data);
      Logger.log("%s %s added to sheet.", data.length - 1, tabName);
    }
  } else {
    Logger.log("No active %s in accounts - nothing to do here.", tabName); 
  }
}

function removeDuplicates(data) {
  var unique = [];
  for (var x = 0; data.length > x; x++) {
    if (!~unique.indexOf(data[x])) unique.push(data[x]);
  }
  return unique;
}

update_camp_final_url_suffix.gs

/* ------------------- ADD GOOGLE SHEET URL HERE -------------------------------- */
var GoogleSheetURL = "https://docs.google.com/spreadsheets/d/1OvOaBhLyw7uENXFc0o_z823_ly1wQ2CPcT8IDNgt1HI/edit#gid=0";
/* ------------------------------------------------------------------------------
/  Sheet must have a tab called "Action List" to function
/  Starting with cell A1 - list all the account IDs down column A with campiagn IDs in column B
/  each row should have an account ID and campaign ID
/  ------------------------------------------------------------------------------ */ 

var googleSheet, actionListValues, timeStamp = Date.now();

function main() {
  googleSheet = connectToGoogleSheet(GoogleSheetURL);
  actionListValues = getActionListValues();
  updateCampaigns();
}

function updateCampaigns() {
  var accountIterator = AdsManagerApp.accounts()
    .withIds(actionListValues.accounts)
    .get();
  var sheetData = [[
    "Campaign ID",
    "Campaign Name",
    "Old Final URL Suffix",
    "New Final URL Suffix"
  ]];
  
  while (accountIterator.hasNext()) {
    var account = accountIterator.next();
    AdsManagerApp.select(account);
    
    var campaigns = AdsApp.campaigns()
      .withIds(actionListValues.campaigns)
      .get();
    
    Logger.log("Campaigns Count: %s, account: %s",campaigns.totalNumEntities(),account.getCustomerId());
    
    while (campaigns.hasNext()) {
      var cmp = campaigns.next();      
      try {
        var customParams = cmp.urls().getFinalUrlSuffix() || "";
        cmp.urls().setFinalUrlSuffix(actionListValues.finalUrlSuffix[cmp.getId()]);
        sheetData.push([
          cmp.getId(),
          cmp.getName(),
          customParams,
          cmp.urls().getFinalUrlSuffix()
        ])
      } catch (error) {
        Logger.log("%s error getting parameters: %s", name, error);
      }
    }
  }
  
  writeDataToSheet(sheetData, "Updated Campaigns Final URL Suffix");
}

function connectToGoogleSheet(urlString) {
  try {
    var sheet = SpreadsheetApp.openByUrl(urlString);
    Logger.log("Google Sheets Success: %s",sheet.getName());
    return sheet;
  } catch (error) {
    Logger.log("Google Sheets Fail: %s", error); 
  }
}

function getSheet(sheet, tabName) {
  return sheet.getSheetByName(tabName) || sheet.insertSheet().setName(tabName); 
}

function getActionListValues() {
  var campaigns = [];
  var accounts = [];
  var finalUrlSuffix = {};
  var sheet = getSheet(googleSheet,"Action List");
  var values = sheet.getRange(1,1,sheet.getLastRow(),3).getValues() || [[]];
  for (var x = 0; values.length > x; x++) {
    campaigns.push(values[x][1] + "");
    accounts.push(values[x][0] + "");
    finalUrlSuffix[values[x][1] + ""] = values[x][2];
  }
  return {
    accounts: removeDuplicates(accounts),
    campaigns: campaigns,
    finalUrlSuffix: finalUrlSuffix
  }
}

function writeDataToSheet(data, tabName) {
  var maxCellCount = 50000;
  
  if (data.length > 1) {
    if (data.length > maxCellCount) {
      var chunks = Math.ceil(data.length / maxCellCount);
      for (var x = 0;chunks > x; x++) {
        var newSheet = googleSheet.insertSheet(tabName + " pg" + (x + 1) + " " + timeStamp);
        var start = x * maxCellCount;
        var end = (x + 1) * maxCellCount;
        var page = data.slice(start, end);
        newSheet.getRange(1,1,page.length,page[0].length).setValues(page);
        Logger.log("%s %s added to sheet.", page.length, tabName);
      }
    } else {
      var newSheet = googleSheet.insertSheet(tabName + " " + timeStamp);
      newSheet.getRange(1,1,data.length,data[0].length).setValues(data);
      Logger.log("%s %s added to sheet.", data.length - 1, tabName);
    }
  } else {
    Logger.log("No active %s in accounts - nothing to do here.", tabName); 
  }
}

function removeDuplicates(data) {
  var unique = [];
  for (var x = 0; data.length > x; x++) {
    if (!~unique.indexOf(data[x])) unique.push(data[x]);
  }
  return unique;
}

update_camp_tracking_template.gs

/* ------------------- ADD GOOGLE SHEET URL HERE -------------------------------- */
var GoogleSheetURL = "https://docs.google.com/spreadsheets/d/1OvOaBhLyw7uENXFc0o_z823_ly1wQ2CPcT8IDNgt1HI/edit#gid=0";
/* ------------------------------------------------------------------------------
/  Sheet must have a tab called "Action List" to function
/  Starting with cell A1 - list all the account IDs down column A with campiagn IDs in column B
/  and finally your tracking template string in column C.
/  ------------------------------------------------------------------------------ */ 

var googleSheet, actionListValues, timeStamp = Date.now();

function main() {
  googleSheet = connectToGoogleSheet(GoogleSheetURL);
  actionListValues = getActionListValues();
  updateCampaigns();
}

function updateCampaigns() {
  var accountIterator = AdsManagerApp.accounts()
    .withIds(actionListValues.accounts)
    .get();
  var sheetData = [[
    "Campaign ID",
    "Campaign Name",
    "Old Tracking Template",
    "New Tracking Template"
  ]];
  
  while (accountIterator.hasNext()) {
    var account = accountIterator.next();
    AdsManagerApp.select(account);
    
    var campaigns = AdsApp.campaigns()
      .withIds(actionListValues.campaigns)
      .get();
    
    Logger.log("Campaigns Count: %s, account: %s",campaigns.totalNumEntities(),account.getCustomerId());
    
    while (campaigns.hasNext()) {
      var cmp = campaigns.next();      
      try {
        // -------------- SET PARAMETERS ---------------
        var customParams = cmp.urls().getTrackingTemplate() || "";
        cmp.urls().setTrackingTemplate(actionListValues.trackingTemplate[cmp.getId()]);
        sheetData.push([
          cmp.getId(),
          cmp.getName(),
          customParams,
          cmp.urls().getTrackingTemplate()
        ])
      } catch (error) {
        // if a custom parameter is defined without a value it breaks the getCustomParameters method
        Logger.log("%s error getting parameters: %s", name, error);
      }
    }
  }
  
  writeDataToSheet(sheetData, "Updated Campaign Tracking Template");
}

function connectToGoogleSheet(urlString) {
  try {
    var sheet = SpreadsheetApp.openByUrl(urlString);
    Logger.log("Google Sheets Success: %s",sheet.getName());
    return sheet;
  } catch (error) {
    Logger.log("Google Sheets Fail: %s", error); 
  }
}

function getSheet(sheet, tabName) {
  return sheet.getSheetByName(tabName) || sheet.insertSheet().setName(tabName); 
}

function getActionListValues() {
  var campaigns = [];
  var accounts = [];
  var trackingTemplate = {};
  var sheet = getSheet(googleSheet,"Action List");
  var values = sheet.getRange(1,1,sheet.getLastRow(),3).getValues() || [[]];
  for (var x = 0; values.length > x; x++) {
    campaigns.push(values[x][1] + "");
    accounts.push(values[x][0] + "");
    trackingTemplate[values[x][1] + ""] = values[x][2];
  }
  return {
    accounts: removeDuplicates(accounts),
    campaigns: campaigns,
    trackingTemplate: trackingTemplate
  }
}

function writeDataToSheet(data, tabName) {
  var maxCellCount = 50000;
  
  if (data.length > 1) {
    if (data.length > maxCellCount) {
      var chunks = Math.ceil(data.length / maxCellCount);
      for (var x = 0;chunks > x; x++) {
        var newSheet = googleSheet.insertSheet(tabName + " pg" + (x + 1) + " " + timeStamp);
        var start = x * maxCellCount;
        var end = (x + 1) * maxCellCount;
        var page = data.slice(start, end);
        newSheet.getRange(1,1,page.length,page[0].length).setValues(page);
        Logger.log("%s %s added to sheet.", page.length, tabName);
      }
    } else {
      var newSheet = googleSheet.insertSheet(tabName + " " + timeStamp);
      newSheet.getRange(1,1,data.length,data[0].length).setValues(data);
      Logger.log("%s %s added to sheet.", data.length - 1, tabName);
    }
  } else {
    Logger.log("No active %s in accounts - nothing to do here.", tabName); 
  }
}

function removeDuplicates(data) {
  var unique = [];
  for (var x = 0; data.length > x; x++) {
    if (!~unique.indexOf(data[x])) unique.push(data[x]);
  }
  return unique;
}