Get an n-gram analysis of Google Ads ad texts

Ad Text N-Grams

/**
*
* Ad Text N-Gram Mining Tool
*
* Get aggregated metrics for when the same word sequence is used in ads across your account.
* Discover better performing phrases from all your Google Ads.
*
* Based on a script by Daniel Gilbert and BrainLabsDigital.com (https://searchengineland.com/brainlabs-script-find-best-worst-search-queries-using-n-grams-228379)
*
* Adapted by Fred Vallaeys and Optmyzr.com to work with expanded ad text rather than search terms data
*
**/

function main() {
  //////////////////////////////////////////////////////////////////////////////
  // Options
  
  var startDate = "2019-01-01";
  var endDate = "2019-02-10";
  // The start and end date of the date range for your data
  // Format is yyyy-mm-dd
  
  var currencySymbol = "$";
  // The currency symbol used for formatting. For example "£", "$" or "€".
  
  var campaignNameContains = " ";
  // Use this if you only want to look at some campaigns
  // such as campaigns with names containing 'Brand' or 'Shopping'.
  // Leave as "" if not wanted.
  
  var campaignNameDoesNotContain = "";
  // Use this if you want to exclude some campaigns
  // such as campaigns with names containing 'Brand' or 'Shopping'.
  // Leave as "" if not wanted.
  
  var ignorePausedCampaigns = false;
  // Set this to true to only look at currently active campaigns.
  // Set to false to include campaigns that had impressions but are currently paused.
  
  var ignorePausedAdGroups = false;
  // Set this to true to only look at currently active ad groups.
  // Set to false to include ad groups that had impressions but are currently paused.
  
  var includeHeadline = true;
  // Set this to true if you want to do analysis of words in Ad Headlines
  
  var includeDescription = true;
  // Set this to true if you want to do analysis of words in Ad Description Lines
  
  var spreadsheetUrl = "https://docs.google.com/spreadsheets/d/xxx-yyy/edit#gid=0"; // <--- Put your own Google Sheet URL here!!!
  // The URL of the Google Doc the results will be put into.
  
  var minNGramLength = 2;
  var maxNGramLength = 6;
  // The word length of phrases to be checked.
  // For example if minNGramLength is 1 and maxNGramLength is 3, 
  // phrases made of 1, 2 and 3 words will be checked.
  // Change both min and max to 1 to just look at single words.
  
  var clearSpreadsheet = true;
  
  
  //////////////////////////////////////////////////////////////////////////////
  // Thresholds
  
  var queryCountThreshold = 0;
  var impressionThreshold = 10;
  var clickThreshold = 0;
  var costThreshold = 0;
  var conversionThreshold = 0;
  // Words will be ignored if their statistics are lower than any of these thresholds
  
  
  //////////////////////////////////////////////////////////////////////////////
  // Check the spreadsheet has been entered, and that it works
  if (spreadsheetUrl.replace(/[AEIOU]/g,"X") == "https://docs.google.com/YXXR-SPRXXDSHXXT-XRL-HXRX") {
    Logger.log("Problem with the spreadsheet URL: make sure you've replaces the default with a valid spreadsheet URL.");
    return;
  }
  try {
    var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  } catch (e) {
    Logger.log("Problem with the spreadsheet URL: '" + e + "'");
    return;
  }
  
  // Get the IDs of the campaigns to look at
  var dateRange = startDate.replace(/-/g, "") + "," + endDate.replace(/-/g, "");
  var activeCampaignIds = [];
  var whereStatements = "";
  
  if (campaignNameDoesNotContain != "") {
    whereStatements += "AND CampaignName DOES_NOT_CONTAIN_IGNORE_CASE '" + campaignNameDoesNotContain + "' ";
  }
  if (ignorePausedCampaigns) {
    whereStatements += "AND CampaignStatus = ENABLED ";
  } else {
    whereStatements += "AND CampaignStatus IN ['ENABLED','PAUSED'] ";
  }
  
  var campaignReport = AdWordsApp.report(
    "SELECT CampaignName, CampaignId " +
    "FROM   CAMPAIGN_PERFORMANCE_REPORT " +
    "WHERE CampaignName CONTAINS_IGNORE_CASE '" + campaignNameContains + "' " +
    "AND Impressions > 0 " + whereStatements +
    "DURING " + dateRange
  );
  var campaignRows = campaignReport.rows();
  while (campaignRows.hasNext()) {
    var campaignRow = campaignRows.next();
    activeCampaignIds.push(campaignRow["CampaignId"]);
  }//end while
  
  if (activeCampaignIds.length == 0) {
    Logger.log("Could not find any campaigns with impressions and the specified options.");
    return;
  }
  
  var whereAdGroupStatus = "";
  if (ignorePausedAdGroups) {
    var whereAdGroupStatus = "AND AdGroupStatus = ENABLED ";
  } else {
    whereAdGroupStatus += "AND AdGroupStatus IN ['ENABLED','PAUSED'] ";
  }
  
  
  
  //////////////////////////////////////////////////////////////////////////////
  // Define the statistics to download or calculate, and their formatting
  
  var statColumns = ["Clicks", "Impressions", "Cost", "Conversions", "ConversionValue"];
  var calculatedStats = [["CTR","Clicks","Impressions"],
                         ["CPC","Cost","Clicks"],
                         ["Conv. Rate","Conversions","Clicks"],
                         ["Cost / conv.","Cost","Conversions"],
                         ["Conv. value/cost","ConversionValue","Cost"]]
  var currencyFormat = currencySymbol + "#,##0.00";
  var formatting = ["#,##0", "#,##0", "#,##0", currencyFormat, "#,##0", currencyFormat,"0.00%",currencyFormat,"0.00%",currencyFormat,"0.00%"];
  
  var adColumns = ['HeadlinePart1', 'HeadlinePart2', 'ExpandedTextAdHeadlinePart3', 'Description', 'ExpandedTextAdDescription2'];
  
  //////////////////////////////////////////////////////////////////////////////
  // Go through the search query report, remove searches already excluded by negatives
  // record the performance of each word in each remaining query
  
  var query = [
    "SELECT CampaignName, CampaignId, AdGroupId, AdGroupName,",
    adColumns.join(", ") + ', ',
    statColumns.join(", "),
    "FROM AD_PERFORMANCE_REPORT",
    "WHERE CampaignId IN [" + activeCampaignIds.join(",") + "] " + whereAdGroupStatus,
    "DURING " + dateRange
  ].join(' ');
  
  var queryReport = AdWordsApp.report(query, {apiVersion: 'v201809'});
  
  var numberOfWords = {}, totalNGrams = {};
  var campaignNGrams = {};
  var adGroupNGrams = {};
  
  if(includeHeadline) {
    numberOfWords['Headline'] = [];
    totalNGrams['Headline'] = [];
    campaignNGrams['Headline'] = {};
    adGroupNGrams['Headline'] = {};    
  }
  
  if(includeDescription) {
    numberOfWords['Description'] = [];
    totalNGrams['Description'] = [];
    campaignNGrams['Description'] = {};
    adGroupNGrams['Description'] = {};    
  }
  
  for(var type in totalNGrams) {
    for (var n=minNGramLength; n<maxNGramLength+1; n++) {
      totalNGrams[type][n] = {};
    }
  }
  
  var queryRows = queryReport.rows();
  while (queryRows.hasNext()) {
    var queryRow = queryRows.next();
    var headline = [queryRow['HeadlinePart1'], queryRow['HeadlinePart2'], queryRow['ExpandedTextAdHeadlinePart3']].join(' ').replace(/[\.\!\?]/g,'');
    var desc = [queryRow['Description'], queryRow['ExpandedTextAdDescription2']].join(' ').replace(/[\.\!\?]/g,'');
    
    var toCheck = {};
    if(includeHeadline) {
      toCheck['Headline'] = headline;
    }
    
    if(includeDescription) {
      toCheck['Description'] = desc;
    }
    
    for(var type in toCheck) {
      var currentWords = toCheck[type].split(" ");
      
      if (campaignNGrams[type][queryRow["CampaignName"]] == undefined) {
        campaignNGrams[type][queryRow["CampaignName"]] = [];
        adGroupNGrams[type][queryRow["CampaignName"]] = {};
        
        for (var n=minNGramLength; n<maxNGramLength+1; n++) {
          campaignNGrams[type][queryRow["CampaignName"]][n] = {};
        }
      }
      
      if (adGroupNGrams[type][queryRow["CampaignName"]][queryRow["AdGroupName"]] == undefined) {
        adGroupNGrams[type][queryRow["CampaignName"]][queryRow["AdGroupName"]] = [];
        for (var n=minNGramLength; n<maxNGramLength+1; n++) {
          adGroupNGrams[type][queryRow["CampaignName"]][queryRow["AdGroupName"]][n] = {};
        }
      }
      
      var stats = [];
      for (var i=0; i<statColumns.length; i++) {
        stats[i] = parseFloat(queryRow[statColumns[i]].replace(/,/g, ""));
      }
      
      /*var wordLength = currentWords.length;
      if (wordLength > 6) {
        wordLength = "7+";
      }
      if (numberOfWords[type][wordLength] == undefined) {
        numberOfWords[type][wordLength] = [];
      }
      for (var i=0; i<statColumns.length; i++) {
        if (numberOfWords[type][wordLength][statColumns[i]] > 0) {
          numberOfWords[type][wordLength][statColumns[i]] += stats[i];
        } else {
          numberOfWords[type][wordLength][statColumns[i]] = stats[i];
        }
      }*/
      
      // Splits the query into n-grams and records the stats for each
      for (var n=minNGramLength; n<maxNGramLength+1; n++) {
        if (n > currentWords.length) {
          break;
        }
        
        var doneNGrams = [];
        
        for (var w=0; w < currentWords.length - n + 1; w++) {
          var currentNGram = '="' + currentWords.slice(w,w+n).join(" ") + '"';
          
          if (doneNGrams.indexOf(currentNGram) < 0) {
            
            if (campaignNGrams[type][queryRow["CampaignName"]][n][currentNGram] == undefined) {
              campaignNGrams[type][queryRow["CampaignName"]][n][currentNGram] = {};
              campaignNGrams[type][queryRow["CampaignName"]][n][currentNGram]["Query Count"] = 0;
            }
            if (adGroupNGrams[type][queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram] == undefined) {
              adGroupNGrams[type][queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram] = {};
              adGroupNGrams[type][queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram]["Query Count"] = 0;
            }
            if (totalNGrams[type][n][currentNGram] == undefined) {
              totalNGrams[type][n][currentNGram] = {};
              totalNGrams[type][n][currentNGram]["Query Count"] = 0;
            }
            
            campaignNGrams[type][queryRow["CampaignName"]][n][currentNGram]["Query Count"]++;
            adGroupNGrams[type][queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram]["Query Count"]++;
            totalNGrams[type][n][currentNGram]["Query Count"]++;
            
            for (var i=0; i<statColumns.length; i++) {
              if (campaignNGrams[type][queryRow["CampaignName"]][n][currentNGram][statColumns[i]] > 0) {
                campaignNGrams[type][queryRow["CampaignName"]][n][currentNGram][statColumns[i]] += stats[i];
              } else {
                campaignNGrams[type][queryRow["CampaignName"]][n][currentNGram][statColumns[i]] = stats[i];
              }
              
              if (adGroupNGrams[type][queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram][statColumns[i]] > 0) {
                adGroupNGrams[type][queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram][statColumns[i]] += stats[i];
              } else {
                adGroupNGrams[type][queryRow["CampaignName"]][queryRow["AdGroupName"]][n][currentNGram][statColumns[i]] = stats[i];
              }
              
              if (totalNGrams[type][n][currentNGram][statColumns[i]] > 0) {
                totalNGrams[type][n][currentNGram][statColumns[i]] += stats[i];
              } else {
                totalNGrams[type][n][currentNGram][statColumns[i]] = stats[i];
              }
            }
            
            doneNGrams.push(currentNGram);
          }
        }
      }
    }
  }
  
  Logger.log("Finished analysing queries.");
  
  
  
  //////////////////////////////////////////////////////////////////////////////
  // Output the data into the spreadsheet
  
  for(var type in totalNGrams) {
    
    var wordLengthOutput = [];
    var wordLengthFormat = [];
    var outputs = [];
    var formats = [];
    
    for (var n=minNGramLength; n<maxNGramLength+1; n++) {
      outputs[n] = {};
      outputs[n]['campaign'] = [];
      outputs[n]['adgroup'] = [];
      outputs[n]['total'] = [];
      formats[n] = {};
      formats[n]['campaign'] = [];
      formats[n]['adgroup'] = [];
      formats[n]['total'] = [];
    }
    
    // Create headers
    var calcStatNames = [];
    for (var s=0; s<calculatedStats.length; s++) {
      calcStatNames.push(calculatedStats[s][0]);
    }
    var statNames = statColumns.concat(calcStatNames);
    for (var n=minNGramLength; n<maxNGramLength+1; n++) {
      outputs[n]['campaign'].push(["Campaign","Phrase","Word Count"].concat(statNames));
      outputs[n]['adgroup'].push(["Campaign","Ad Group","Phrase","Word Count"].concat(statNames));
      outputs[n]['total'].push(["Phrase","Word Count"].concat(statNames));
    }
    wordLengthOutput.push(["Word count"].concat(statNames));
    
    // Organise the ad group level stats into an array for output
    for (var n=minNGramLength; n<maxNGramLength+1; n++) {
      for (var campaign in adGroupNGrams[type]) {
        for (var adGroup in adGroupNGrams[type][campaign]) {
          for (var nGram in adGroupNGrams[type][campaign][adGroup][n]) {
            
            // skips nGrams under the thresholds
            if (adGroupNGrams[type][campaign][adGroup][n][nGram]["Query Count"] < queryCountThreshold) {continue;}
            if (adGroupNGrams[type][campaign][adGroup][n][nGram]["Impressions"] < impressionThreshold) {continue;}
            if (adGroupNGrams[type][campaign][adGroup][n][nGram]["Clicks"] < clickThreshold) {continue;}
            if (adGroupNGrams[type][campaign][adGroup][n][nGram]["Cost"] < costThreshold) {continue;}
            if (adGroupNGrams[type][campaign][adGroup][n][nGram]["Conversions"] < conversionThreshold) {continue;}
            
            var printline = [campaign, adGroup, nGram, adGroupNGrams[type][campaign][adGroup][n][nGram]["Query Count"]];
            
            for (var s=0; s<statColumns.length; s++) {
              printline.push(adGroupNGrams[type][campaign][adGroup][n][nGram][statColumns[s]]);
            }
            
            for (var s=0; s<calculatedStats.length; s++) {
              var multiplier = calculatedStats[s][1];
              var divisor = calculatedStats[s][2];
              if (adGroupNGrams[type][campaign][adGroup][n][nGram][divisor] > 0) {
                printline.push(adGroupNGrams[type][campaign][adGroup][n][nGram][multiplier] / adGroupNGrams[type][campaign][adGroup][n][nGram][divisor]);
              } else {
                printline.push("-");
              }
            }
            outputs[n]['adgroup'].push(printline);
            formats[n]['adgroup'].push(["0","0","0"].concat(formatting));
          }
        }
      }
    }
    
    // Organise the campaign level stats into an array for output
    for (var n=minNGramLength; n<maxNGramLength+1; n++) {
      for (var campaign in campaignNGrams[type]) {
        for (var nGram in campaignNGrams[type][campaign][n]) {
          
          // skips nGrams under the thresholds
          if (campaignNGrams[type][campaign][n][nGram]["Query Count"] < queryCountThreshold) {continue;}
          if (campaignNGrams[type][campaign][n][nGram]["Impressions"] < impressionThreshold) {continue;}
          if (campaignNGrams[type][campaign][n][nGram]["Clicks"] < clickThreshold) {continue;}
          if (campaignNGrams[type][campaign][n][nGram]["Cost"] < costThreshold) {continue;}
          if (campaignNGrams[type][campaign][n][nGram]["Conversions"] < conversionThreshold) {continue;}
          
          var printline = [campaign, nGram, campaignNGrams[type][campaign][n][nGram]["Query Count"]];
          
          for (var s=0; s<statColumns.length; s++) {
            printline.push(campaignNGrams[type][campaign][n][nGram][statColumns[s]]);
          }
          
          for (var s=0; s<calculatedStats.length; s++) {
            var multiplier = calculatedStats[s][1];
            var divisor = calculatedStats[s][2];
            if (campaignNGrams[type][campaign][n][nGram][divisor] > 0) {
              printline.push(campaignNGrams[type][campaign][n][nGram][multiplier] / campaignNGrams[type][campaign][n][nGram][divisor]);
            } else {
              printline.push("-");
            }
          }
          outputs[n]['campaign'].push(printline);
          formats[n]['campaign'].push(["0","0"].concat(formatting));
        }
      }
    }
    
    // Organise the account level stats into an array for output
    for (var n=minNGramLength; n<maxNGramLength+1; n++) {
      for (var nGram in totalNGrams[type][n]) {
        
        // skips n-grams under the thresholds
        if (totalNGrams[type][n][nGram]["Query Count"] < queryCountThreshold) {continue;}
        if (totalNGrams[type][n][nGram]["Impressions"] < impressionThreshold) {continue;}
        if (totalNGrams[type][n][nGram]["Clicks"] < clickThreshold) {continue;}
        if (totalNGrams[type][n][nGram]["Cost"] < costThreshold) {continue;}
        if (totalNGrams[type][n][nGram]["Conversions"] < conversionThreshold) {continue;}
        
        var printline = [nGram, totalNGrams[type][n][nGram]["Query Count"]];
        
        for (var s=0; s<statColumns.length; s++) {
          printline.push(totalNGrams[type][n][nGram][statColumns[s]]);
        }
        
        for (var s=0; s<calculatedStats.length; s++) {
          var multiplier = calculatedStats[s][1];
          var divisor = calculatedStats[s][2];
          if (totalNGrams[type][n][nGram][divisor] > 0) {
            printline.push(totalNGrams[type][n][nGram][multiplier] / totalNGrams[type][n][nGram][divisor]);
          } else {
            printline.push("-");
          }
        }
        outputs[n]['total'].push(printline);
        formats[n]['total'].push(["0"].concat(formatting));
      }
    }
    
    // Organise the word count analysis into an array for output
    /*for (var i = 1; i<8; i++) {
      if (i < 7) {
        var wordLength = i;
      } else {
        var wordLength = "7+";
      }
      
      var printline = [wordLength];
      
      if (numberOfWords[type][wordLength] == undefined) {
        printline.push(0,0,0,0,0,"-","-","-","-","-");
      } else {
        for (var s=0; s<statColumns.length; s++) {
          printline.push(numberOfWords[type][wordLength][statColumns[s]]);
        }
        
        for (var s=0; s<calculatedStats.length; s++) {
          var multiplier = calculatedStats[s][1];
          var divisor = calculatedStats[s][2];
          if (numberOfWords[type][wordLength][divisor] > 0) {
            printline.push(numberOfWords[type][wordLength][multiplier] / numberOfWords[type][wordLength][divisor]);
          } else {
            printline.push("-");
          }
        }
      }
      
//      Logger.log(printline);
      wordLengthOutput.push(printline);
      wordLengthFormat.push(formatting);
    }*/
    
    var filterText = "";
    if (ignorePausedAdGroups) {
      filterText = "Active ad groups";
    } else {
      filterText = "All ad groups";
    }
    
    if (ignorePausedCampaigns) {
      filterText += " in active campaigns";
    } else {
      filterText += " in all campaigns";
    }
    
    if (campaignNameContains != "") {
      filterText += " containing '" + campaignNameContains + "'";
      if (campaignNameDoesNotContain != "") {
        filterText += " and not containing '" + campaignNameDoesNotContain + "'";
      }
    } else if (campaignNameDoesNotContain != "") {
      filterText += " not containing '" + campaignNameDoesNotContain + "'";
    }
    
    // Find or create the required sheets
    var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
    var campaignNGramName = [];
    var adGroupNGramName = [];
    var totalNGramName = [];
    var campaignNGramSheet = [];
    var adGroupNGramSheet = [];
    var totalNGramSheet = [];
    
    for (var n=minNGramLength; n<maxNGramLength+1; n++) {
      if (n==1) {
        campaignNGramName[n] = "Campaign Word Analysis (" + type + ")" ;
        adGroupNGramName[n] = "Ad Group Word Analysis (" + type + ")" ;
        totalNGramName[n] = "Account Word Analysis (" + type + ")" ;
      } else {
        campaignNGramName[n] = "Campaign " + n + "-Gram Analysis (" + type + ")" ;
        adGroupNGramName[n] = "Ad Group " + n + "-Gram Analysis (" + type + ")" ;
        totalNGramName[n] = "Account " + n + "-Gram Analysis (" + type + ")" ;
      }
      
      campaignNGramSheet[n] = spreadsheet.getSheetByName(campaignNGramName[n]);
      if (campaignNGramSheet[n] == null) {
        campaignNGramSheet[n] = spreadsheet.insertSheet(campaignNGramName[n]);
      }
      
      adGroupNGramSheet[n] = spreadsheet.getSheetByName(adGroupNGramName[n]);
      if (adGroupNGramSheet[n] == null) {
        adGroupNGramSheet[n] = spreadsheet.insertSheet(adGroupNGramName[n]);
      }
      
      totalNGramSheet[n] = spreadsheet.getSheetByName(totalNGramName[n]);
      if (totalNGramSheet[n] == null) {
        totalNGramSheet[n] = spreadsheet.insertSheet(totalNGramName[n]);
      }
    }
    
    /*var wordCountSheet = spreadsheet.getSheetByName("Word Count Analysis (" + type + ")");
    if (wordCountSheet == null) {
      wordCountSheet = spreadsheet.insertSheet("Word Count Analysis (" + type + ")");
    }*/
    
    // Write the output arrays to the spreadsheet
    for (var n=minNGramLength; n<maxNGramLength+1; n++) {
      var nGramName = n + "-Grams";
      if (n == 1) {
        nGramName = "Words";
      }
      
      writeOutput(outputs[n]['campaign'], formats[n]['campaign'], campaignNGramSheet[n], nGramName, "Campaign", filterText, clearSpreadsheet);
      writeOutput(outputs[n]['adgroup'], formats[n]['adgroup'], adGroupNGramSheet[n], nGramName, "Ad Group", filterText, clearSpreadsheet);
      writeOutput(outputs[n]['total'], formats[n]['total'], totalNGramSheet[n], nGramName, "Account", filterText, clearSpreadsheet);
    }
    
    //writeOutput(wordLengthOutput, wordLengthFormat, wordCountSheet, "Word Count", "Account", filterText, clearSpreadsheet);
  }
  
  Logger.log("Finished writing to spreadsheet.");
} // end main function


function writeOutput(outputArray, formatArray, sheet, nGramName, levelName, filterText, clearSpreadsheet) {
  for (var i=0;i<5;i++) {
    try {
      if (clearSpreadsheet) {
        sheet.clear();
      }
      
      if (nGramName == "Word Count") {
        sheet.getRange("R1C1").setValue("Analysis of Search Query Performance by Word Count");
      } else {
        sheet.getRange("R1C1").setValue("Analysis of " + nGramName + " in Search Query Report, By " + levelName);
      }
      
      sheet.getRange("R" + (sheet.getLastRow() + 2) + "C1").setValue(filterText);
      
      var lastRow = sheet.getLastRow();
      
      if (formatArray.length == 0) {
        sheet.getRange("R" + (lastRow + 1) + "C1").setValue("No " + nGramName.toLowerCase() + " found within the thresholds.");
      } else {
        sheet.getRange("R" + (lastRow + 1) + "C1:R" + (lastRow+outputArray.length) + "C" + outputArray[0].length).setValues(outputArray);
        sheet.getRange("R" + (lastRow + 2) + "C1:R" + (lastRow+outputArray.length) + "C" + formatArray[0].length).setNumberFormats(formatArray);
        
        var sortByColumns = [];
        if (outputArray[0][0] == "Campaign" || outputArray[0][0] == "Word count") {
          sortByColumns.push({column: 1, ascending: true});
        }
        if (outputArray[0][1] == "Ad Group") {
          sortByColumns.push({column: 2, ascending: true});
        }
        sortByColumns.push({column: outputArray[0].indexOf("Cost") + 1, ascending: false});
        sortByColumns.push({column: outputArray[0].indexOf("Impressions") + 1, ascending: false});
        sheet.getRange("R" + (lastRow + 2) + "C1:R" + (lastRow+outputArray.length) + "C" + outputArray[0].length).sort(sortByColumns);
      }
      
      break;
      
    } catch (e) {
      if (e == "Exception: This action would increase the number of cells in the worksheet above the limit of 2000000 cells.") {
        Logger.log("Could not output " + levelName + " level " + nGramName.toLowerCase() + ": '" + e + "'");
        try {
          sheet.getRange("R" + (sheet.getLastRow() + 2) + "C1").setValue("Not enough space to write the data - try again in an empty spreadsheet");
        } catch (e2) {
          Logger.log("Error writing 'not enough space' message: " + e2);
        }
        break;
      }
      
      if (i == 4) {
        Logger.log("Could not output " + levelName + " level " + nGramName.toLowerCase() + ": '" + e + "'");
      }
    }
  }
}

@caioricciuti
I added a space and now it works again. Gotta love coding!

@caioricciuti
I added a space and now it works again. Gotta love coding!

I can confirm it works at my end! Thanks for diving in to it!

How we can optimize this script if we want to set it up for a single campaign?

Can I run it from the Spreadsheet itself, by eanbling the service API? Which one? AdSense? Analytics? Thanks for sharing such a great content!

@santosonit Not sure what you mean but since it is an Ad related script I would try enable Ads api's, the script will get all info from your campaigns so Ads api's is the way to go.

You're right. After I made the question, I read/understood that by enabling the API I could potentially use this script (with some modifications) in a script editor bounded to a spreadsheet. Thanks, @caioricciuti

Only me not geting Headlines anymore? Just Descriptions that is being populated with data