Generate an AdWords bulksheet with suggestions for expanded text ads based on your landing page meta data

Expanded Text Ad Suggestor.js

// Generate Expanded Text Ads From Landing Page Meta Data - AdWords Script
//
// Copyright 2016 - Optmyzr Inc - All Rights Reserved
// For more AdWords Scripts and PPC Management Tools and Reports, visit
//
//     https://www.optmyzr.com/
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//     http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

// EDIT THE FOLLOWING SECTION //
// --------------------------- //

DEBUG = 0; // set to 1 to see more data in the logs
H1TEXT = "h1"; // set to either "h1" or "H1" depending on what capitalization your site uses
MAX_TOTAL_HEADLINE_LENGTH = 60; // if you want the length of headline1 + headline2 to be shorter than the 60 characters Google allows, set that number here, e.g. 33
GOOGLE_ACCOUNTS = "[email protected], [email protected]"; // the Google accounts that should be allowed to open the spreadsheet this script creates
CAMPAIGN_NAME_INCLUDES = ""; // if your account is very large and the script times out, use this to limit which campaigns the script evaluates, e.g. "brand" will match any campaigns that contain the text "brand" or "Brand"
USING_WRAPPED_URLS = 0; // set to 1 if you use a redirect where the final URL is inside another URL, e.g. http://redirect.com/?dest=http://www.finalsite.com
STRIP_QUERY_STRINGS = 0; // set to 1 if the script should remove all URL parameters before crawling your landing pages
// END OF SETTINGS

function main(){
    
  var currentSetting = new Object();
  currentSetting.accountManagers = GOOGLE_ACCOUNTS;
  var spreadsheetUrl = "NEW";
  currentSetting.stripQueryStrings = 0;
  currentSetting.includeZeroImpressions = 1;
  currentSetting.wrappedUrls = 0;
  currentSetting["Campaign_Name_Includes"] = CAMPAIGN_NAME_INCLUDES;
  currentSetting.wrappedUrls = USING_WRAPPED_URLS;
  currentSetting.stripQueryStrings = STRIP_QUERY_STRINGS;
 
  if(spreadsheetUrl.toLowerCase().indexOf("new") != -1)
  {
    var spreadsheet = SpreadsheetApp.create("Expanded Text Ads From Meta Data (" + AdWordsApp.currentAccount().getName() + ")");
    var spreadsheetUrl = spreadsheet.getUrl();
  } 
  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  if(currentSetting.accountManagers && currentSetting.accountManagers!=""){
    currentSetting.accountManagersArray = currentSetting.accountManagers.replace(/\s/g, "").split(",");
    spreadsheet.addEditors(currentSetting.accountManagersArray);
  }
  
  //get all sheets except first and delete them and insert new sheets every time to avoid name error
  var allSheets = spreadsheet.getSheets();
  
  for(var i=1,len=allSheets.length;i<len;i++){
    spreadsheet.deleteSheet(allSheets[i]);
  }
  
  allSheets[0].setName("Meta Data");
  var metaSheet = spreadsheet.getSheetByName("Meta Data");
  
  metaSheet.appendRow(["URL", "Meta Title", "Meta Description", "First H1", "Headline 1", "Len", "Headline 2", "Len", "Description", "Len", "Path 1", "Len", "Path 2", "Len", "Final URL", "Final mobile URL", "Campaign", "AdGroup"]);
  metaSheet.setFrozenRows(1);
  
  
  if(currentSetting.includeZeroImpressions) {
    var withImpressionsQueryString = "WHERE Impressions >= 0";
  } else {
    var withImpressionsQueryString = "WHERE Impressions > 0";
  }
  if(DEBUG == 1) Logger.log("withImpressionsQueryString: " + withImpressionsQueryString);
  
  
  var urlMap = new Object();
  urlMap.campaigns = new Array();
  urlMap.urls = new Array();
  
  
  var urlArray = new Array();
  var query ='SELECT CreativeDestinationUrl, CreativeFinalUrls, CreativeFinalMobileUrls, AdGroupName, CampaignName, AdGroupId, Status' + " " +
    'FROM AD_PERFORMANCE_REPORT ' +
      withImpressionsQueryString + ' ' +
        'AND AdType IN ["TEXT_AD"] ' +
          'AND AdGroupStatus=ENABLED AND Status=ENABLED AND CampaignStatus=ENABLED AND CampaignName CONTAINS_IGNORE_CASE "' + currentSetting["Campaign_Name_Includes"].replace(/[\"']/g, "") + '" ' +
            'DURING LAST_30_DAYS';
  if(DEBUG == 1) Logger.log("query: " + query);
  var adReport = AdWordsApp.report(query);
  
  
  var adRows = adReport.rows();
  var adCounter = 0;
  
  
  while(adRows.hasNext()) {
    adCounter++;
    var row = adRows.next();
    var destUrl = row["CreativeDestinationUrl"];
    var finalUrls = row["CreativeFinalUrls"];
    var creativeFinalMobileUrls = row['CreativeFinalMobileUrls'];
    var status = row["Status"];
    var adGroupName = row["AdGroupName"];
    var campaignName = row["CampaignName"];
    
    if(!urlMap.campaigns[campaignName]) {
      urlMap.campaigns[campaignName] = new Object();
      urlMap.campaigns[campaignName].adGroups = new Array();
    }
    if(!urlMap.campaigns[campaignName].adGroups[adGroupName]) {
      urlMap.campaigns[campaignName].adGroups[adGroupName] = new Object();
    }
    if(finalUrls && !urlMap.campaigns[campaignName].adGroups[adGroupName].Url) {
      var url = finalUrls.split(";")[0];
      urlMap.campaigns[campaignName].adGroups[adGroupName].Url = url;
      if(!urlMap.urls[url]) {
        if(DEBUG) Logger.log("new url found: " + url);
        urlMap.urls[url] = new Object();
        var results = checkUrl(url);
        urlMap.urls[url].title = results.title;
        urlMap.urls[url].metaDescription = results.metaDescription;
        urlMap.urls[url].firstH1 = results.firstH1;
      }
    }
      
    if(creativeFinalMobileUrls && !urlMap.campaigns[campaignName].adGroups[adGroupName].mobileUrl) {
      var url = creativeFinalMobileUrls.split(";")[0];
      urlMap.campaigns[campaignName].adGroups[adGroupName].mobileUrl = url;
      if(!urlMap.urls[url]) {
        if(DEBUG) Logger.log("new mobile url found: " + url);
        urlMap.urls[url] = new Object();
        var response = checkUrl(url);
        urlMap.urls[url].title = response.title;
        urlMap.urls[url].metaDescription = response.metaDescription;
        urlMap.urls[url].firstH1 = response.firstH1;
      }
    }
    
  }
  
  
  for(var campaignName in urlMap.campaigns) {
    for(var adGroupName in urlMap.campaigns[campaignName].adGroups) {
      var finalUrl = urlMap.campaigns[campaignName].adGroups[adGroupName].Url;
      var finalUrlDetails = urlMap.urls[finalUrl];
      var finalMobileUrl = urlMap.campaigns[campaignName].adGroups[adGroupName].mobileUrl;
      if(typeof finalMobileUrl === "undefined") var finalMobileUrl = "";
      var title = finalUrlDetails.title;
      var metaDescription = finalUrlDetails.metaDescription;
      var firstH1 = finalUrlDetails.firstH1;
      
      if(title) {
        var headline1 = getFullWords(title, 30) ;
        var headline1Length = headline1.length;
        var startPos = headline1.length + 1;
        if(startPos >= 30) startPos = 30;
        var headline2MaxCharacters = MAX_TOTAL_HEADLINE_LENGTH - headline1Length;
        if(headline2MaxCharacters > 30) headline2MaxCharacters = 30;
        var headline2 = getFullWords(title.substr(startPos, title.length), headline2MaxCharacters);
      } else {
        var headline1 = "";
        var headline2 = "";
      }
      if(metaDescription) {
        var description = getFullWords(metaDescription, 80) ;
      } else {
        var description = "";
      }
      metaSheet.appendRow([url, title, metaDescription, firstH1, headline1, "", headline2, "", description, "", "", "", "", "", finalUrl, finalMobileUrl, campaignName, adGroupName]);
    }
  }
  
  var numRows = metaSheet.getDataRange().getNumRows() - 1;
  metaSheet.getRange(2, 6, numRows, 1).setFormulaR1C1("=LEN(R[0]C[-1])");
  metaSheet.getRange(2, 8, numRows, 1).setFormulaR1C1("=LEN(R[0]C[-1])");
  metaSheet.getRange(2, 10, numRows, 1).setFormulaR1C1("=LEN(R[0]C[-1])");    
  metaSheet.getRange(2, 12, numRows, 1).setFormulaR1C1("=LEN(R[0]C[-1])");  
  metaSheet.getRange(2, 14, numRows, 1).setFormulaR1C1("=LEN(R[0]C[-1])");  
  
  Logger.log("Finished... Results: " + spreadsheetUrl); 
  
  function checkUrl(url) {
    var options =
        {
          "muteHttpExceptions" : true
        };
    
    var results = new Object();

    if(DEBUG == 1) Logger.log("checking " + url); 
    try
    {
      var valParameterUrls = removeValueParameters(url);
      for(var urlIndex=0;urlIndex<valParameterUrls.length;urlIndex++){
        var cleanedUrl = cleanUrl(valParameterUrls[urlIndex]);
        cleanedUrl = getProperFormattedUrl(cleanedUrl);
        var response = UrlFetchApp.fetch(cleanedUrl, options);
      }
    }
    catch(err)
    {
      if(DEBUG == 1) Logger.log(err);
    }
    
    if(typeof(response) != 'undefined') {
      var responseCode = response.getResponseCode();
      //if(DEBUG == 1) Logger.log(responseCode);
      if(responseCode == 200){
        var responseText = response.getContentText();

        if(responseText != "") {

          var title = getMetaTitle(responseText);
          results.title = title;
          //Logger.log("Title: " + title);
          
          var metaDescription = getMetaDescription(responseText);
          results.metaDescription = metaDescription;
          //Logger.log("Description: " + metaDescription);

          var firstH1 = getFirstH1(responseText, H1TEXT);
          results.firstH1 = firstH1;
          //Logger.log("First H1: " + firstH1);
          
        } else {
          
        }
      } 
    }
    return results;
  }
  
  
  function getMetaTitle(responseText) {
    if(responseText.indexOf("<title>") != -1) {
      var scrap = responseText.match( /<title>(.*?)<\/title>/ ); 
    } else if(responseText.indexOf("<Title>") != -1) {
      var scrap = responseText.match( /<Title>(.*?)<\/Title>/ ); 
    } else if(responseText.indexOf("<TITLE>") != -1) {
      var scrap = responseText.match( /<TITLE>(.*?)<\/TITLE>/ ); 
    }
    var title = scrap[1];
    
    //Logger.log("Title: " + title);
    return (title);
  }
  
  function getMetaDescription(responseText) {
    var meta = responseText.split("<meta");
    for(var i = 0; i < meta.length; i++) {
      var tag = meta[i];
      //Logger.log("tag: " + tag);
      
      if(tag.indexOf("name=") != -1) {
        var parts = tag.split("name=");
      } else if(tag.indexOf("Name=") != -1) {
        var parts = tag.split("Name=");
      } else if(tag.indexOf("NAME=") != -1) {
        var parts = tag.split("NAME=");
      } 
      
      if(parts) {
        var text = parts[1];
        //Logger.log("text: " + text);
        //Logger.log("");
        if(text.toLowerCase().indexOf("'description'") != -1 || text.toLowerCase().indexOf('"description"') != -1) {
          //Logger.log("This is the decription tag");
          if(tag.toLowerCase().indexOf('content="') != -1) {
            //Logger.log("  --> uses double quotes");
            var description = tag.split('content="')[1].split('"')[0];
          } else if(tag.toLowerCase().indexOf("content='") != -1) {
            //Logger.log("  --> uses single quotes");
            var description = tag.split("content='")[1].split("'")[0];
          } else if(tag.toLowerCase().indexOf('Content="') != -1) {
            var description = tag.split('Content="')[1].split('"')[0];
          } else if(tag.toLowerCase().indexOf("Content='") != -1) {
            var description = tag.split("Content='")[1].split("'")[0];
          } else if(tag.toLowerCase().indexOf('CONTENT="') != -1) {
            var description = tag.split('CONTENT="')[1].split('"')[0];
          } else if(tag.toLowerCase().indexOf("CONTENT='") != -1) {
            var description = tag.split("CONTENT='")[1].split("'")[0];
          }
        }
      }
    }
    //Logger.log("Description: " + description);
    return description;
  }
  
  
  function getFirstH1(responseText, h1Text) {
    var splitText = "<" + h1Text;
     if(responseText.indexOf(splitText) != -1) {
       var h1Parts = responseText.split(splitText);
       if(h1Parts[1][0] == ">") {
         var firstH1Raw = h1Parts[1];
       } else {
         var firstH1Raw = h1Parts[1].substr(1);
       }
       var firstH1Content = firstH1Raw.split(">")[1].split("<")[0];
       var firstH1Content = firstH1Raw.split("</")[0].split(">")[1];
    } else {
      var firstH1Content = "";
    }
    return firstH1Content;
  }
  
  
  function getFullWords(text, cutOff) {
      if(text.length <= cutOff) {
        return text;
      } else {
        var cutOffMinusOne = cutOff - 1;
        var lastCharacter = text[cutOffMinusOne];
        var nextCharacter = text[cutOff];
        if(DEBUG) Logger.log("lastCharacter: " + lastCharacter);
        if(DEBUG) Logger.log("nextCharacter: " + nextCharacter);
        var lastCharacterCleaned = lastCharacter.replace(/[.,\/#!$%\^&\*;:{}=\-_`~()\s]/g,"");
        var nextCharacterCleaned = nextCharacter.replace(/[.,\/#!$%\^&\*;:{}=\-_`~()\s]/g,"");
        if(DEBUG) Logger.log("lastCharacterCleaned: " + lastCharacterCleaned);
        if(DEBUG) Logger.log("nextCharacterCleaned: " + nextCharacterCleaned);
        if(lastCharacterCleaned.length && nextCharacterCleaned.length) {
          if(DEBUG) Logger.log("we need to split before character " + cutOff);
          var textParts = text.substr(0,cutOff).split(" ");
          textParts.pop();
          var outputText = textParts.join(" ");
          if(DEBUG) Logger.log("text: " + outputText);
          //var startPos = headline1.length + 1;
          //var headline2 = title.substr(startPos,cutOff);
          //Logger.log("headline2: " + headline2);
        } else {
          if(DEBUG) Logger.log("we can split at character " + cutOff);
          var outputText = text.substr(0,cutOff);
          if(DEBUG) Logger.log("text: " + outputText);
          //var headline2 = title.substr(30,30);
          //Logger.log("headline2: " + headline2);
        }
        return outputText;
      } 
  }
  
  

  function getProperFormattedUrl(url){
    var firstArr = url.split("?");
    var formattedUrl = "";
    for(var i=0,len=firstArr.length;i<len;i++){
      var tempVal = firstArr[i];
      var tempArr = tempVal.split("&");
      
      for(var j=0;j<tempArr.length;j++){
        var qStr = tempArr[j];
        var qArr = qStr.split("=");
        if(qArr.length>1){
          qArr[0]= encodeURIComponent(decodeURIComponent(qArr[0]));
          qArr[1] = encodeURIComponent(decodeURIComponent(qArr[1]));
          
        }
        qStr = qArr.join("=");
        tempArr[j]=qStr;  
      }
      tempVal = tempArr.join("&");
      firstArr[i] = tempVal;
    }
    
    formattedUrl = firstArr.join("?");
    return formattedUrl;
  }
  
  
  
  function removeValueParameters(url){
    var urls = [];
    if(url==""){
      return urls;   
    }
    if(url.indexOf("{")!=-1){   
      url = url.replace(/{.*?}/g,"");
      if(url!=""){
        urls.push(url);
      }   
    }
    else{
      urls.push(url);   
    }
    
    return urls;
  }
  
  function cleanUrl(url) {
    if(currentSetting.wrappedUrls) {
      url = url.substr(url.lastIndexOf('http'));
      if(decodeURIComponent(url) !== url) {
        url = decodeURIComponent(url);
      }
    }
    if(currentSetting.stripQueryStrings) {
      if(url.indexOf('?')>=0) {
        url = url.split('?')[0];
      }
    }
    if(url.indexOf('{') >= 0) {
      //Let's remove the value track parameters
      url = url.replace(/\{[0-9a-zA-Z:]+\}/g,'');
    }
    url = url.replace(/\|/g,"%7C");
    url = url.replace(/[\[\]\"\']/g,"");
    return url;
  }
}

Hi Fedrick,

I am trying to run this great script, but getting the following error. Can you please help?

The number of rows in the range must be at least 1. (file Code.gs, line 175)