Google Ads Script to Get Search Terms to a Spreadsheet

Google Ads Script to Get Search Terms to a Spreadsheet Given a Date Range

I was writing this Google Ads script to extract Search Terms from my campaigns for a client. And I happen stumble upon a comment on the Google Ads Strategy Facebook group asking for a similar script, so I decided to polish it a little bit and post this script on my website and link it from the facebook group.

You can use this script to extract search terms from your account, so you can add new keywords into your Google Ads account or add negative keywords into the account, whatever the purpose this should help speed up your search terms analysis. You can schedule this script to run weekly so you can do your search terms analysis weekly or however often you’d like.

The script requires a few configuration so you can get the most out of it.

First, copy and paste the script below to your Google Ads account.

Second, copy the spreadsheet template and enter the url into the script by replacing the spreadsheetUrl variable(line 4) in the script.

Copy this Spreadsheet Template

Third, create a label and attach it to all the campaigns you want to extract your search terms from and enter the label into the spreadsheet by replacing the YourLabelHere.

Fourth, pick the date range you want the script to use to extract from your Google Ads account.

Fifth, optional, you can schedule this script to run weekly or monthly.

Enjoy! Please let me know what you think! My email is in the spreadsheet.

function main() {
  //mcc();

  var spreadsheetUrl = 'https://docs.google.com/spreadsheets/d/1x5ADciCWOBgBlkyrdPhgB1EEECjTdIIpTRXS9WAlI3I/edit?usp=sharing';

  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  var sheet = spreadsheet.getSheetByName("Config");
  var range = sheet.getDataRange();
  var data = range.getValues();

  var dateRange = 'Today';
  var extractLabel = 'ValidLabel';
  for(i=0;i<data.length;i++){
    if(data[i][0] == 'EXIT'){
      break;
    }else if(data[i][0] == 'DateRange'){
      dateRange = data[i][1];
    }else if(data[i][0] == 'CampaignLabel'){
      extractLabel = data[i][1];
    }
  }

  var campaignIds = [];

  var campaignSelector = AdsApp
  .campaigns()
  .withCondition("LabelNames CONTAINS_ANY ['" + extractLabel + "']");

  var campaignIterator = campaignSelector.get();
  while (campaignIterator.hasNext()) {
    var campaign = campaignIterator.next();
    campaignIds.push(campaign.getId());
  }  

  var awql = "SELECT CampaignName, AdGroupName, Query, Ctr, Cost, Impressions, Conversions " +
      "FROM   SEARCH_QUERY_PERFORMANCE_REPORT " +
      "WHERE  Impressions < 10 " +
      " AND CampaignId in [" + campaignIds.join(',') + "] " +
      "DURING " + dateRange;
  Logger.log(awql);
  var report = AdsApp.report(awql);

  report.exportToSheet(spreadsheet.insertSheet());
  Logger.log("Report available at " + spreadsheet.getUrl());
}