AdWords Script to create, update and apply sitelinks and callout extensions based on definitions and variables in a Google Sheet.

Dynamic Ad Extensions.js

/**
*
* Dynamic Ad Extensions
*
* Script to dynamically add and/or update sitelinks and callouts and apply them to
* all campaigns or ad groups, based on definitions and variables in a Google Sheet.
*
* Version: 1.0
* Google AdWords Script maintained on brainlabsdigital.com
*
**/


//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//Options

var spreadsheetUrl = "https://docs.google.com/YOUR-SPREADSHEET-URL-HERE";
// The URL of the Google Sheet with the extension definitions
// Should be a copy of https://docs.google.com/spreadsheets/d/1ROGMwhpIaZXuIsThZ6yaVKkO0Txa5aaYgNG0oM5qYgk/edit#gid=0

var emailRecipients = [];
// If set, these addresses will be emailed if there are errors when the script runs.
// Enter like ["[email protected]"] or ["[email protected]","[email protected]","[email protected]"]
// Leave as [] to skip.


//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//

function main() {
  
  // This array is used to store any errors to be emailed
  var problems = {};
  problems.general = [];
  problems.sitelink = [];
  problems.callout = [];
  
  // Check the spreadsheet URL works
  try {
    var spreadsheet = checkSpreadsheet(spreadsheetUrl, "the spreadsheet");
  } catch (e) {
    problems.general.push(e);
    var subject = "Dynamic Ad Extensions - Could Not Open Spreadsheet";
    notify(problems, emailRecipients, subject);
    throw(e);
  }
  
  // Process sitelinks
  try {
    var charLimits = {"hl":25, "dl1":35, "dl2":35, "url":2048};
    var slSheet = spreadsheet.getSheetByName("Sitelinks");
    generateSitelinks(slSheet, charLimits, problems.sitelink);
    refreshSitelinks(slSheet, charLimits, problems.sitelink);
    applyExtensions(slSheet, "sitelink", problems.sitelink);
  } catch (e) {
    Logger.log(e);
    problems.general.push(e);
  }
  
  // Process callouts
  try {
    charLimits = {"callout":25}
    var coSheet = spreadsheet.getSheetByName("Callouts");
    generateCallouts(coSheet, charLimits, problems.callout);
    refreshCallouts(coSheet, charLimits, problems.callout);
    applyExtensions(coSheet, "callout", problems.callout);
  } catch (e) {
    Logger.log(e);
    problems.general.push(e);
  }
  
  // Send error emails
  var subject = "Dynamic Ad Extensions - problems encountered";
  notify(problems, emailRecipients, subject);
  
  Logger.log("Finished.");
}

/**
* Checks the spreadsheet URL has been entered, and that it works
*
* @param String  spreadsheetUrl       URL of a Google Sheet
* @param String spreadsheetName  Name of the sheet, for use in error messages
*
* @return void
*/
function checkSpreadsheet(spreadsheetUrl, spreadsheetName) {
  if (spreadsheetUrl.replace(/[AEIOU]/g,"X") == "https://docs.google.com/YXXR-SPRXXDSHXXT-XRL-HXRX") {
    throw("Problem with " + spreadsheetName + " URL: make sure you've replaced the default with a valid spreadsheet URL.");
  }
  try {
    var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
    return spreadsheet;
  } catch (e) {
    throw("Problem with " + spreadsheetName + " URL: '" + e + "'");
  }
}

/**
* Searches sheet for any Sitelink entries without an ID and creates them
* Inputs their IDs into Column A for future reference
*
* @param Sheet  sheet       Sheet object containing Sitelink details
* @param Object charLimits  Character limits of each entity
*
* @return void
*/
function generateSitelinks(sheet, charLimits, problems)
{
  var lastRow = sheet.getLastRow();
  var headers = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues()[0];
  var idIndex = headers.indexOf("ID")+1;
  var ids = sheet.getRange(2,idIndex,lastRow-1, 1).getValues();
  
  // Array of row numbers missing ids
  var missingIds = [];
  for(var i = 0; i < ids.length; i++) if(ids[i][0] == "") missingIds.push(i+2);
  
  Logger.log("Creating " + missingIds.length + " new sitelinks");
  
  for(var i = 0; i < missingIds.length; i++)
  {
    var rowNum = missingIds[i];    
    var details = getSitelinkDetails(sheet, rowNum, headers, charLimits);  
    
    // Check char limits
    var legit = true;
    var entities = ["hl","dl1", "dl2", "url"];
    for(var e in entities) if(details[entities[e]].length > charLimits[entities[e]]) legit = false;
    
    if(legit)
    {
      var builder = AdWordsApp.extensions().newSitelinkBuilder();
      var sl = builder
      .withLinkText(details.hl)
      .withDescription1(details.dl1)
      .withDescription2(details.dl2)
      .withFinalUrl(details.url)
      .withMobilePreferred(details.mobPref);
      
      sl = sl.build().getResult();
      
	  // If the script is being previewed, the sitelink won't be made,
	  // so we don't write the ID into the sheet
      if(AdWordsApp.getExecutionInfo().isPreview() || sl == null) ;
      else sheet.getRange(rowNum, 1).setValue(sl.getId());
      
    }
    else 
    {
      Logger.log("Sitelink text too long in row "+rowNum+" - extension not created.");
      Logger.log(details);
      problems.push("Sitelink text too long in row "+rowNum+" - extension not created.");
    }
  }
}

/**
* Searches sheet for any Callout entries without an ID and creates them
* Inputs their IDs into Column A for future reference
*
* @param Sheet  sheet       Sheet object containing Callout details
* @param Object charLimits  Character limits of each entity
*
* @return void
*/
function generateCallouts(sheet, charLimits, problems)
{
  var lastRow = sheet.getLastRow();
  var headers = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues()[0];
  var idIndex = headers.indexOf("ID")+1;
  var ids = sheet.getRange(2,idIndex,lastRow-1, 1).getValues();
  
  // Array of row numbers missing ids
  var missingIds = [];
  for(var i = 0; i < ids.length; i++) if(ids[i][0] == "") missingIds.push(i+2);
  
  Logger.log("Creating " + missingIds.length + " new callouts");
  
  for(var i = 0; i < missingIds.length; i++)
  {
    var rowNum = missingIds[i];    
    var details = getCalloutDetails(sheet, rowNum, headers, charLimits);  
    
    // Check char limits
    var legit = details.callout.length <= charLimits.callout;
    
    if(legit)
    {
      var builder = AdWordsApp.extensions().newCalloutBuilder();
      var co = builder
      .withText(details.callout)
      .withMobilePreferred(details.mobPref);
      
      var callout = co.build().getResult();
      
	  // If the script is being previewed, the callout won't be made,
	  // so we don't write the ID into the sheet
      if(AdWordsApp.getExecutionInfo().isPreview() || callout == null) ;
      else sheet.getRange(rowNum, 1).setValue(callout.getId());
    }
    else 
    {
      Logger.log("Callout text too long in row "+rowNum+" - extension not created.");
      problems.push("Callout text too long in row "+rowNum+" - extension not created.");
    }
  }
}

/**
* Pulls the existing sitelinks, substitutes vars
* If it can update existing sitelinks it does
*
* @param Sheet  sheet       Sheet object containing Sitelink details
* @param Object charLimits  Character limits of each entity
*
* @return void
*/
function refreshSitelinks(sheet, charLimits, problems)
{
  var lr = sheet.getLastRow()
  var idsArray = sheet.getRange(2,1,lr-1).getValues();
  
  // Flatten ids
  var ids = [];
  for(var i = 0; i < idsArray.length; i++)
  {
    if(idsArray[i][0] !== undefined) ids.push(idsArray[i][0]);
    else ids.push("");
  }
  
  var headers = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues()[0];
  var startIndex = headers.indexOf("Headline")+1;
  
  Logger.log("Updating " + ids.length + " sitelinks");
  
  for(var i = 0; i< ids.length; i++)
  {
    var idString = ids[i].toString();
    var idArray = idString.split("^");
    for(var j = 0; j<idArray.length; j++)
    {
      var id = idArray[j];
      if(id == "") continue;
      
      var rowNum = i+2;
      var details = getSitelinkDetails(sheet, rowNum, headers, charLimits);
      
      var sl = AdWordsApp.extensions().sitelinks().withIds([id]).get();
      
      // Check char limits
      var legit = true;
      var entities = ["hl","dl1", "dl2", "url"];
      for(var e in entities) if(details[entities[e]].length > charLimits[entities[e]]) legit = false;
      
      if (!legit)
      {
		// Can't update the sitelink as the new text is invalid
        Logger.log("Sitelink text too long in row " + rowNum + " - extension not updated.");
        problems.push("Sitelink text too long in row " + rowNum + " - extension not updated.");
      }
      else if(sl.hasNext())
      {
        sl = sl.next();
        if(sl.getLinkText() !== details.hl) sl.setLinkText(details.hl);
        if(details.dl1 == "") sl.clearDescription1();
        else if(sl.getDescription1() !== details.dl1) sl.setDescription1(details.dl1);
        if(details.dl2 == "") sl.clearDescription2();
        else if(sl.getDescription2() !== details.dl2) sl.setDescription2(details.dl2);
        if(sl.urls().getFinalUrl() !== details.url) sl.urls().setFinalUrl(details.url);
        sl.setMobilePreferred(details.mobPref);
        
        var vals = [sl.getLinkText(), sl.getDescription1(), sl.getDescription2(), sl.urls().getFinalUrl()];
        sheet.getRange(rowNum, startIndex, 1, 4).setValues([vals]);
      }
      else
      {
        // Can't find sitelink, so clear the ID and preview from the sheet
        sheet.getRange(rowNum, 1).clear();
        sheet.getRange(rowNum, startIndex, 1, 4).clear()
      }
    }
  }
}

/**
* Pulls the existing callouts, substitutes vars
* If it can update existing callout it does
*
* @param Sheet  sheet       Sheet object containing callout details
* @param Object charLimits  Character limits of each entity
*
* @return void
*/
function refreshCallouts(sheet, charLimits, problems)
{
  var lr = sheet.getLastRow()
  var idsArray = sheet.getRange(2,1,lr-1).getValues();
  
  // Flatten ids
  var ids = [];
  for(var i = 0; i < idsArray.length; i++)
  {
    if(idsArray[i][0] !== undefined) ids.push(idsArray[i][0]);
    else ids.push("");
  }
  
  var headers = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues()[0];
  var startIndex = headers.indexOf("Callout")+1;
  
  Logger.log("Updating " + ids.length + " callouts");
  
  for(var i = 0; i< ids.length; i++)
  {
    var idString = ids[i].toString();
    var idArray = idString.split("^");
    for(var j = 0; j<idArray.length; j++)
    {
      var id = idArray[j];
      if(id == "") continue;
      
      var rowNum = i+2;
      var details = getCalloutDetails(sheet, rowNum, headers, charLimits);
      var callout = AdWordsApp.extensions().callouts().withIds([id]).get();
      
      // Check char limits
      var legit = details.callout.length <= charLimits.callout;
      
      if(!legit)
      {
		// Can't update the callout as the new text is invalid
        Logger.log("Callout text too long in row " + rowNum + " - extension not updated.");
        problems.push("Callout text too long in row " + rowNum + " - extension not updated.");
      }
      else if(callout.hasNext())
      {
        callout = callout.next();
        if(callout.getText() !== details.callout) callout.setText(details.callout);
        callout.setMobilePreferred(details.mobPref);
        
        var vals = [callout.getText()];
        sheet.getRange(rowNum, startIndex).setValues([vals]);
      }
      else
      {
        // Can't find the callout so clear ID and preview from the sheet
        sheet.getRange(rowNum, 1).clear();
        sheet.getRange(rowNum, startIndex).clear()
      }
    }
  }
}

/**
* Reads the sitelink info on a row and returns it as an object
*
* @param Sheet sheet     Sheet object containing Sitelink details
* @param Int   rowNum    The row number to fetch
* @param Array headers   The header row loaded as array, used to get correct columns
* @param Object charLimits  Character limits of each entity
*
* @return Object          Object keyed by sitelink properties
*/
function getSitelinkDetails(sheet, rowNum, headers, charLimits)
{
  var startIndex = headers.indexOf("Dynamic Headline");
  var row = sheet.getRange(rowNum,1,1,sheet.getLastColumn()).getValues()[0];
  var hl = row[startIndex].toString(),
      dl1 = row[++startIndex].toString(),
      dl2 = row[++startIndex].toString(),
      url = row[++startIndex].toString(),
      mobPref = row[++startIndex];
  
  hl = subParamValues(hl, row, headers, charLimits.hl);
  dl1 = subParamValues(dl1, row, headers, charLimits.dl1);
  dl2 = subParamValues(dl2, row, headers, charLimits.dl2);
  url = subParamValues(url, row, headers, charLimits.url);
  mobPref = !(mobPref == "No");
  
  return {
    "hl":hl,
    "dl1":dl1,
    "dl2":dl2,
    "url":url,
    "mobPref":mobPref
  };
}

/**
* Reads the callout info on a row and returns it as an object
*
* @param Sheet sheet        Sheet object containing callout details
* @param Int   rowNum       The row number to fetch
* @param Array headers      The header row loaded as array, used to get correct columns
* @param Object charLimits  Character limits of each entity
*
* @return Object          Object keyed by callout properties
*/
function getCalloutDetails(sheet, rowNum, headers, charLimits)
{
  var startIndex = headers.indexOf("Dynamic Callout");
  var row = sheet.getRange(rowNum,1,1,sheet.getLastColumn()).getValues()[0];
  var callout = row[startIndex].toString(),
      mobPref = row[++startIndex]
  
  callout = subParamValues(callout, row, headers, charLimits.callout);
  mobPref = !(mobPref == "No");
  
  return {"callout":callout, "mobPref":mobPref};
}

/**
* Searches the string for what vars to lookup
* Puts in var value if exists, default value otherwise
* If subbed value is too long, reverts to default
*
* @param String search   String to search
* @param Int   row       The row the extension is on
* @param Array headers   The header row loaded as array, used to get correct columns
* @param Int   limit     The character limit for the field
*
* @return String         The final string value
*/
function subParamValues(search, row, headers, limit)
{
  var regex = /{var[1-9]+:[^}]*}/g
  var matches = search.match(regex);
  
  var ans = search;
  for(var i = 0; matches !== null && i<matches.length; i++)
  {
    var match = matches[i].split("{")[1].split("}")[0].split(":");
    var varx = match[0];
    var def = match[1];
    var val = row[headers.indexOf(varx)];
    
    var ans = search.replace(matches[i], def);
    if(val != "")
    {
      var ans = search.replace(matches[i], val);
      if(ans.length <= limit) search = ans;
      else 
      {
        ans = search.replace(matches[i], def);
        search = ans.trim();
      }
    }
  }
  return ans;  
}

/**
* Creates a selector, reads sheet to apply appropriate conditions
* Applies extension to appropriate entities
* Does row by row, so new selector for each extension
*
* @param Sheet  sheet       Sheet object containing extension details
* @param Strin  type        Type of extension to add
*
* @return void
*/
function applyExtensions(sheet, type, problems)
{
  var lr = sheet.getLastRow();
  var lc = sheet.getLastColumn();
  
  var headers = sheet.getRange(1,1,1,lc).getValues()[0];
  var levelIndex = headers.indexOf("Entity level");
  var includesIndex = headers.indexOf("Entity name contains");
  var excludesIndex = headers.indexOf("Entity name excludes");
  var parentIncludesIndex = headers.indexOf("Parent contains");
  var parentExcludesIndex = headers.indexOf("Parent excludes");
  var parentIsIndex = headers.indexOf("Parent is");
  var isIndex = headers.indexOf("Entity name is");
  var labelIndex = headers.indexOf("Entity has label");
  
  var vals = sheet.getRange(1,1,lr, lc).getValues();
  
  Logger.log("Applying "+type+"s to campaigns / ad groups");
  
  rows:
  for(var i = 1; i < vals.length; i++)
  {
    var row = vals[i];
    if(row[0] === "" && !AdWordsApp.getExecutionInfo().isPreview())
    {
      Logger.log("Failed to generate "+type+" from row "+i);
      problems.push("Failed to generate "+type+" from row "+i);
      continue;
    }
    
	// Make the campaign/ad group selector
    var selector;
    var level = row[levelIndex];
    switch(level) {
      case "Campaign" : 
        selector = AdWordsApp.campaigns();
        break;
      case "Ad Group" :
        selector = AdWordsApp.adGroups() 
        break;
      default : 
        Logger.log("Invalid level for "+type+" on row "+(i+1));
        problems.push("Invalid level for "+type+" on row "+(i+1));
        continue rows;
    }
    
    var is = row[isIndex].toString().split("'").join("\'");
    if(is !== "")
    {
      selector.withCondition("Name = '"+is+"'");
    }
    else
    {
      var includes = row[includesIndex].toString().split("'").join("\'");
      var excludes = row[excludesIndex].toString().split("'").join("\'");
      var label = row[labelIndex].toString().split("'").join("\'");
      
      if(includes !== "") selector.withCondition("Name CONTAINS '"+includes+"'");
      if(excludes !== "") selector.withCondition("Name DOES_NOT_CONTAIN '"+excludes+"'");
      if(label !== "") selector.withCondition("LabelNames CONTAINS_ANY ['"+label+"']");
    }
    
    if(level == "Ad Group")
    {
      var parentIs = row[parentIsIndex].toString().split("'").join("\'");
      if(parentIs !== "")
      {     
        selector.withCondition("CampaignName = '"+parentIs+"'");
      }
      else
      {
        var parentInclude = row[parentIncludesIndex].toString().split("'").join("\'");
        var parentExclude = row[parentExcludesIndex].toString().split("'").join("\'");
        
        if(parentInclude !== "") selector.withCondition("CampaignName CONTAINS '"+parentInclude+"'");
        if(parentExclude !== "") selector.withCondition("CampaignName DOES_NOT_CONTAIN '"+parentInclude+"'");
      }
    }
    
	// Get the extension to apply
    var ids = row[0].toString().split("^");
    for(var j = 0; j<ids.length; j++)
    {
      var id = ids[j];
      if(id == "") continue;
      
      switch(type) {
        case "sitelink":
          var extension = AdWordsApp.extensions().sitelinks().withIds([id]).get().next();
          break;
        case "callout":
          var extension = AdWordsApp.extensions().callouts().withIds([id]).get().next();
          break;
        default:
          Logger.log("Invalid type "+type+" inputed in applyExtensions");
          return;
      }
      
	  // Apply the extension to the campaigns/ad groups in the selector
      var entities = selector.get();
      while(entities.hasNext())
      {
        var entity = entities.next();
        if(type == "sitelink") entity.addSitelink(extension);
        else entity.addCallout(extension);
      }
    }
  }
}

/**
* Emails the problems that occurred during the script to the relevent recipients
* 
* @param Object problems       {type : [problems]}  
* @param Array email           Array of email addresses
* @param String subject        Email subject line
*
* @return void
*/
function notify(problems, recipients, subject)
{
  if (recipients.length == 0) {
    // No one wants an email
    return;
  }
  
  var message = "";
  
  for(var type in problems)
  {
    var list = problems[type];
    for(var i=0; i<list.length;i++)
    {
      message += type + ":\t"+list[i]+"\n";
    }
  }
  
  if (message == "") {
    Logger.log("No problems to report.");
    return;
  }
  
  message = "The following problems were encountered during the script:\n" + message;
  
  MailApp.sendEmail(recipients.join(","), subject, message);
  Logger.log("Error email sent to " + recipients.join(", "));
}

@jamesk the mistake is in the spreadsheet. You need to move the mobile preferred column after the Dynamic URL column and it should be fine, because the script is looking at that column. If you then use No it should be targeting all devices. Otherwise only mobile

Hey, do you have a new version for the new "upgraded sitelinks" in Google Ads