Automate Weekly Reports Using Google Ads Scripts

Searching for a tool or a service that can easily enable you to report and create dashboards of your Google Adwords campaigns. No Need, we have written a Google Ads script that will save you an hour every week. Either you are on the agency side or the client side, creating Google Ads weekly reports isn’t that much fun. You would rather spend that time (to collect and visualize the data) optimizing your campaigns.

With our script, you can automate your weekly report and use great-looking graphs and data that are presented in a professional look and feel. Through the interactive dashboard, your clients will have access to the KPIs that matter, segmented by network and device type as seen below.

The script fetches statistics in the past five weeks (Monday to Sunday). If you would like to change the number of weeks or even the week type (e.g. Sunday to Saturday), please email us and will modify and send the script source code to you.

How to you use the script?

1- Open the template spreadsheet to make a copy of it.
# In the spreadsheet, menu, select “File -> Make a copy”.
# Enter the new spreadsheet name, click “OK”.

2- In AdWords, navigate to Scripts.

# Create a new AdWords script with the source code below.
# Don’t forget to update INSERT_SPREADSHEET_URL_HERE in your code.
# Schedule the script Weekly (Monday).

Source Code
/***************************************************
* AdWords Weekly Report Script
* Version 1.0
* Created By: Ahmed Ali
*https://optimizationup.com/
****************************************************/

var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';
var SHEET_NAME = 'Source'; 
var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
var sheet = spreadsheet.getSheetByName(SHEET_NAME);


function main() { 
 
 
 sheet.getRange("A:H").clearContent(); 

var last5WeeksMonday = new Date();
var lastweekSunday = new Date();

// set to Monday of this week

last5WeeksMonday.setDate(last5WeeksMonday.getDate() - (last5WeeksMonday.getDay() + 6) % 7);
lastweekSunday.setDate(lastweekSunday.getDate() - (lastweekSunday.getDay() + 6) % 7);



// set to previous Monday

last5WeeksMonday.setDate(last5WeeksMonday.getDate() - 35);

lastweekSunday.setDate(lastweekSunday.getDate() - 1);

var Last5WeeksMonday = Utilities.formatDate(last5WeeksMonday, 'PST', 'yyyyMMdd');
var LastweekSunday = Utilities.formatDate(lastweekSunday, 'PST', 'yyyyMMdd');

var d = new Date(); d.getDate(); var today = Utilities.formatDate(d, 'PST', 'yyyyMMdd');

sheet.getRange("A1").setValue("Start Date");
sheet.getRange("A2").setValue("End Date");
sheet.getRange("B1").setValue(Last5WeeksMonday);
sheet.getRange("B2").setValue(LastweekSunday);
sheet.getRange("A3").setValue("Frequency");
sheet.getRange("B3").setValue("Last Five Weeks");
sheet.getRange("A4").setValue("Last Execution");
sheet.getRange("A5").setValue("Copyright");
sheet.getRange("B5").setValue("https://optimizationup.com/");
sheet.getRange("B4").setValue(today);
sheet.getRange("A8").setValue("Week");
sheet.getRange("B8").setValue("Impressions");
sheet.getRange("C8").setValue("Clicks");
sheet.getRange("D8").setValue("Cost");
sheet.getRange("E8").setValue("Conversions");
sheet.getRange("F8").setValue("Revenue");
sheet.getRange("G8").setValue("Network");
sheet.getRange("H8").setValue("Device");
sheet.getRange("A8").setValue("Week");
 

var report = AdWordsApp.report("SELECT Week, Clicks, Impressions, Ctr, AverageCpc, Cost, Conversions, AdNetworkType1, ConversionValue " +
 "FROM ACCOUNT_PERFORMANCE_REPORT " +
 "WHERE Impressions > 0 " +
 "DURING " + Last5WeeksMonday + ", " + LastweekSunday);

 
 var rows = report.rows();
 while (rows.hasNext()) {
 var row = rows.next();
 
 
sheet.appendRow([ row['Week'], row['Impressions'], row['Clicks'] , row['Cost'] , row['Conversions'],row['ConversionValue'], row['AdNetworkType1'], "All" ]);
 
 }

 var report = AdWordsApp.report("SELECT Week, Clicks, Impressions, Ctr, AverageCpc, Cost, Conversions, ConversionValue " +
 "FROM ACCOUNT_PERFORMANCE_REPORT " +
 "WHERE Impressions > 0 " +
 "DURING " + Last5WeeksMonday + ", " + LastweekSunday);

 var rows = report.rows();
 while (rows.hasNext()) {
 var row = rows.next();
 
 sheet.appendRow([ row['Week'], row['Impressions'], row['Clicks'] , row['Cost'] , row['Conversions'],row['ConversionValue'], "All" , "All" ]);
 
}

 var report = AdWordsApp.report("SELECT Week, Clicks, Impressions, Ctr, AverageCpc, Cost, Conversions, AdNetworkType1, Device, ConversionValue " +
 "FROM ACCOUNT_PERFORMANCE_REPORT " +
 "WHERE Impressions > 0 " +
 "DURING " + Last5WeeksMonday + ", " + LastweekSunday);

 var rows = report.rows();
 while (rows.hasNext()) {
 var row = rows.next();
 
 sheet.appendRow([ row['Week'], row['Impressions'], row['Clicks'] , row['Cost'] , row['Conversions'],row['ConversionValue'], row['AdNetworkType1'] , row['Device'] ]);
 
}
var report = AdWordsApp.report("SELECT Week, Clicks, Impressions, Ctr, AverageCpc, Cost, Conversions, Device, ConversionValue " +
 "FROM ACCOUNT_PERFORMANCE_REPORT " +
 "WHERE Impressions > 0 " +
 "DURING " + Last5WeeksMonday + ", " + LastweekSunday);

 var rows = report.rows();
 while (rows.hasNext()) {
 var row = rows.next();
 
 sheet.appendRow([ row['Week'], row['Impressions'], row['Clicks'] , row['Cost'] , row['Conversions'],row['ConversionValue'], "All" , row['Device'] ]);
 
}

var report = AdWordsApp.report("SELECT Week, Clicks " +
 "FROM ACCOUNT_PERFORMANCE_REPORT " +
 "WHERE Impressions > 0 " +
 "DURING " + Last5WeeksMonday + ", " + LastweekSunday);

var rows = report.rows()
for (var row = 1; rows.hasNext(); row ++) {
 var rowx = rows.next();
 
 sheet.getRange("D1").setValue("The 5 Weeks Sorted ASC"); 
 sheet.getRange("E" + row).setValue(rowx['Week']);
 
 
}


var range = sheet.getRange("E1:F6");
range.sort(5);
}

5 Comments

  1. Hello, Thank you a lot for your work.

    When I’m selecting Search Network in your example of Google Spreadsheet for that script I can’t find any clicks but when I’m using AdWords that data is appearing.

    Could you tell me why I’m seeing this – http://recordit.co/syI08WT0WN ?

    1. Also, What about a parameter in description “Clicks” – http://recordit.co/9ib7U8xzhd ? Why Am I seeing in description “Clicks” but the formula counting impressions? So, I’m seeing an amount of impressions instead Clicks.
      Thank you.

  2. Hi Ahemed!

    Very useful post.

    What if want the report to be running from Sunday to Saturday? What change should I make on the script?

    Thanks in advance

  3. Hi Ahmed,
    Nice Script! Just wondering if it would be possible to segment by campaign?