Google Ads Script for searchterms data to sheet

951 views
Skip to first unread message

Renke Pieters

unread,
May 10, 2023, 5:56:29 AM5/10/23
to Google Ads Scripts Forum
Hi, 

I want to add a google ads script to my account that send the searchterms per campaign with impressions, clicks, costs and conversions to a google sheet. Only search term with costs. This is the Google Ads Script I have: 

function main() {
  var spreadsheetUrl = 'https://docs.google.com/spreadsheets/d/16ns2DohbtyHpvDsUWAeUwfyhE7A-fL8eWkmdGaV4wb8/edit#gid=0';
  var sheetName = 'Blad1';
  var sheet = SpreadsheetApp.openByUrl(spreadsheetUrl).getSheetByName(sheetName);
 
  var campaigns = AdsApp.campaigns().get();
  while (campaigns.hasNext()) {
    var campaign = campaigns.next();
    var campaignName = campaign.getName();
    var keywords = campaign.keywords()
      .withCondition("Cost > 0")
      .forDateRange("LAST_MONTH")
      .report(
        "SELECT Query, Impressions, Clicks, Conversions, Cost " +
        "FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
        "WHERE CampaignName = '" + campaignName + "' " +
        "DURING LAST_MONTH");
    var rows = keywords.rows();
    while (rows.hasNext()) {
      var row = rows.next();
      var searchTerm = row['Query'];
      var impressions = row['Impressions'];
      var clicks = row['Clicks'];
      var conversions = row['Conversions'];
      var cost = row['Cost'];
      sheet.appendRow([campaignName, searchTerm, impressions, clicks, conversions, cost]);
    }
  }
}

Could you please help me resolve this?

Google Ads Scripts Forum

unread,
May 10, 2023, 8:33:32 AM5/10/23
to Google Ads Scripts Forum
Reposting the last inquiry (https://groups.google.com/g/adwords-scripts/c/6QkFAUJjmPw) from the forum as it wasn't routed to our support queue.

Regards,
Google Ads Scripts Team

Google Ads Scripts Forum

unread,
May 10, 2023, 8:40:36 AM5/10/23
to Google Ads Scripts Forum

Hello,


Thank you for reaching out to the Google Ads Scripts Team Forum Channel.


Would you be able to clarify if you had encountered an error with your script? Upon checking, I've observed that the query the script is using still conforms to the AdWords Query Language which the legacy version makes use of. Please do note that the legacy version is deprecated, and scripts still conforming to the said version will not perform as expected. Kindly note that, currently, Google Ads Scripts' reporting infrastructure is backed by the Google Ads API and uses the Google Ads Query Language. With this, we would recommend you migrate the script to the new script experience version to ensure continued functionality.


You may do so by following our migration guide. I've also included a link below for a report example for you to refer to in order to properly implement a script which uses a report. I believe the resource mapping documentation and our query migration tool are helpful when migrating queries, as it lists which Google Ads API field each resource field maps to. Since you're using the SEARCH_QUERY_PERFORMANCE_REPORT, this maps to the search_term_view report. After migrating your query, it results to the one below:


"SELECT search_term_view.search_term, metrics.impressions, metrics.clicks, metrics.conversions, metrics.cost_micros FROM search_term_view WHERE campaign.name = '" + campaignName + "' " AND segments.date DURING LAST_MONTH"


Kindly make use of the query above and let us know if an issue would persist along with your Google Ads account ID and the name of the script in the account so we're able to further investigate.

 

Reference links:

 

Best regards, Google Ads Scripts Team

Reply all
Reply to author
Forward
0 new messages