google scripts for outputting campaign performance for ad account

[Mary Cheung]

Hi team,

Do you have any sample google scripts that can output campaign performance for ad account by campaigns (only eligible campaigns) to google sheet on a daily basis? thanks

Mary

[Google Ads Scripts Forum Advisor]

Hello Mary,

Please see this 'Create a spreadsheet report' sample on our code snippets page. You may also want to email the report, in which case you could add the following line (in bold):

function exportReportToSpreadsheet() {
  var spreadsheet = SpreadsheetApp.create('INSERT_REPORT_NAME_HERE');
  var report = AdsApp.report(
    'SELECT CampaignName, Clicks, Impressions, Cost ' +
    'FROM   CAMPAIGN_PERFORMANCE_REPORT ' +
    'WHERE  Impressions < 10 ' +
    'DURING LAST_30_DAYS');
  report.exportToSheet(spreadsheet.getActiveSheet());
  MailApp.sendEmail("YOUR_EMAIL", "YOUR_SUBJECT", spreadsheet.getUrl();)
}

Regards,
Matt
Google Ads Scripts Team

ref:_00D1U1174p._5001UUzw4I:ref

[Christian Lewandowsky]

I got this Error Guys :( .. any Help? Thanks alot!

[Google Ads Scripts Forum Advisor]

Hi Christian,

Please move the semicolon on line 10 outside of the end parenthesis to clear the syntax error.

[Christian Lewandowsky]

Thx a lot, but it is still not working :(.

I just want a campaignreport in a spreadsheet updating itself via script :(.

U know what i mean? I have a Spreadsheet with campaign-data on accountlevel. The Script is runs every week and is updating the data in the same spreadsheet.

Anyone can help with this script please? Thank u very much!

[Google Ads Scripts Forum Advisor]

Hi Christian,

Please provide us with more details on the errors/issues you are seeing (e.g. screenshots, error messages). This script below will work, if you fill in your email address and subject line on the last line of code, where indicated:

function main() {
  exportReportToSpreadsheet();

}

function exportReportToSpreadsheet() {
  var spreadsheet = SpreadsheetApp.create('INSERT_REPORT_NAME_HERE');
  var report = AdsApp.report(
    'SELECT CampaignName, Clicks, Impressions, Cost ' +
    'FROM   CAMPAIGN_PERFORMANCE_REPORT ' +
    'WHERE  Impressions < 10 ' +
    'DURING LAST_30_DAYS');
  report.exportToSheet(spreadsheet.getActiveSheet());

  MailApp.sendEmail("YOUR_EMAIL", "YOUR_SUBJECT", spreadsheet.getUrl());

[Christian Lewandowsky]

Hey Guys,

i solved the issue.

I have a Script now, filling a Spreadsheet with Data of a period.

Here is the Script:

function main() {

    var spreadsheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1fEau6y5zgCqd9mf1I5r_e9WioBR40Qea9e2LhS-ysxM/edit#gid=0");

    var sheet = spreadsheet.getSheetByName("Sheet1");

    var report = AdWordsApp.report(

        'SELECT Impressions, Clicks, Ctr, AverageCpc, Cost, Conversions, CostPerConversion, ConversionRate, ConversionValue ' +

        'FROM ACCOUNT_PERFORMANCE_REPORT ' +

        'DURING LAST_30_DAYS');

    var date = getDate();

    var rows = report.rows();

    var i = sheet.getLastRow() + 1;

while (rows.hasNext()) {

    var row = rows.next();

    var dataColumn = [];

    dataColumn.push(date);

    dataColumn.push(row['Impressions']);

    dataColumn.push(row['Clicks']);

    dataColumn.push(row['Ctr']);

    dataColumn.push(row['AverageCpc']);

    dataColumn.push(row['Cost']);

    dataColumn.push(row['Conversions']);

    dataColumn.push(row['CostPerConversion']);

    dataColumn.push(row['ConversionRate']);

    dataColumn.push(row['ConversionValue']);

    sheet.appendRow(dataColumn);

    sheet.getRange(i, 6).setNumberFormat("€0.00");

    i++;

}

}

function getDate() {

    var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;

    var now = new Date();

    var startDate = new Date(now.getTime() - 30 * MILLIS_PER_DAY);

    var endDate = new Date(now.getTime() - 1 * MILLIS_PER_DAY);

    var timeZone = AdWordsApp.currentAccount().getTimeZone();

    startDate = Utilities.formatDate(startDate, timeZone, 'yyyy/MM/dd');

    endDate = Utilities.formatDate(endDate, timeZone, 'yyyy/MM/dd');

    return startDate + ' - ' + endDate;

}

Now i would like to spread the Data over all Campaigns and Filter for Display/Youtube/Search.

So in the End i would like to have a Spreadsheet for Each Campaigntype containing Impressions, Clicks, CTR, Conversions and Conversionrate.

I also want to schedule the script so that is continuously filling the spreadsheet.

Can u help me growing my Script to do that?

Thx a lot and Greetings from rainy Hamburg

[Google Ads Scripts Forum Advisor]

Hi Christian,

If you want to report on the campaign level, please use the campaign performance report instead of the account performance report.

You can filter campaigns using the AdNetwork1 (and AdNetwork2) segments from the campaign performance report.

[Christian Lewandowsky]

Hi Matt,

okay cool. Where in my Script do i have to put this segment?

Regards,

Chris

[Google Ads Scripts Forum Advisor]

Hi Chris,

You can refer on the sample below on how to build your query for the Campaign Performance Report and use the AdNetworkType1 and AdNetworkType2 fields as filters :

var query = 'SELECT CampaignName, Clicks, Impressions, Cost, Ctr, AverageCpc, Conversions, CostPerConversion, ConversionRate, ConversionValue '  +
     'FROM CAMPAIGN_PERFORMANCE_REPORT where AdNetworkType1 = <predicate value> AND AdNetworkType2 = <predicate value> ' +
     'DURING LAST_30_DAYS';
 var report = AdsApp.report(query);

Thanks,
Peter