Automate Google Ads Reports to Google Sheets for a Custom Dashboard

By Jake Livingood – SEO Specialist & Web Designer

Managing multiple Google Ads reports manually can be time-consuming and inefficient. With automation, you can have your reports automatically update into a pre-determined Google Sheet, allowing for real-time insights and a customized dashboard. In this step-by-step guide, I’ll walk you through how to set up this automation using a simple Google Ads script.

🔹 Why Automate Google Ads Reports to Google Sheets?

✅ Save Time – No more manual exports and imports.
✅ Live Data Updates – View real-time performance metrics.
✅ Custom Dashboard Integration – Combine multiple data sources using Google Sheets extensions.
✅ Shareability – Easily share the dashboard with team members or clients.

📌 Step 1: Set Up Your Google Sheet

1️⃣ Open Google Sheets and create a new sheet for your Google Ads reports.
2️⃣ Copy the URL of your Google Sheet (you’ll need this in the next step).
3️⃣ Make sure the sheet is shared so that the Google Ads script can write data to it.

📌 Step 2: Add the Google Ads Script

1️⃣ Log into Google Ads and go to Tools & Settings > Scripts.
2️⃣ Click the + button to create a new script.
3️⃣ Copy and paste the Google Ads script below into the editor:

(Jake will provide the script here)

4️⃣ Replace YOUR_GOOGLE_SHEET_URL in the script with your copied Google Sheet URL.
5️⃣ Click Authorize to grant permissions.
6️⃣ Click Save and Run the script.

📌 Step 3: Automate the Report Updates

1️⃣ In the Google Ads Scripts dashboard, click on the script you just created.
2️⃣ Click on Schedules and set the frequency (e.g., daily, weekly).
3️⃣ Save the settings, and your reports will now update automatically! 🚀

📌 Step 4: Build Your Custom Dashboard

Now that your Google Ads data flows into Google Sheets, you can:

✅ Use Google Data Studio (Looker Studio) to create visual reports.
✅ Add other data sources like Facebook Ads, Google Analytics, and more using extensions.
✅ Apply filters, pivot tables, and charts for better insights.

🎯 Final Thoughts

This automation simplifies tracking Google Ads performance while allowing you to build a custom dashboard with all your important marketing data. With just a few minutes of setup, you’ll have a fully automated reporting system that saves time and improves decision-making.

💡 Need help setting this up? Drop a comment below! 👇

#GoogleAds #Automation #GoogleSheets #MarketingTips #PPCReports #DataStudio #AdReporting #SEO #JakeLivingood

⬇Post this in Google Ads Script⬇

				
					function main() {
  try {
    var spreadsheetUrl = 'Add Google Sheet URL Here';
    var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
    
    // Calculate date ranges
    var today = new Date();
    var last30DaysStart = getFormattedDate(addDays(today, -30));  // 30 days ago
    var last30DaysEnd = getFormattedDate(today);                 // Today

    var prev30DaysStart = getFormattedDate(addDays(today, -60)); // 60 days ago
    var prev30DaysEnd = getFormattedDate(addDays(today, -30));   // 30 days ago

    Logger.log("Fetching data from " + last30DaysStart + " to " + last30DaysEnd);
    Logger.log("Fetching previous period from " + prev30DaysStart + " to " + prev30DaysEnd);

    var queryTemplate = "SELECT CampaignName, Impressions, Clicks, Cost, Conversions, ConversionRate, CostPerConversion, " +
                        "AverageCpc, Ctr, SearchImpressionShare, SearchRankLostImpressionShare " +
                        "FROM CAMPAIGN_PERFORMANCE_REPORT " +
                        "WHERE Impressions > 0 " + // Filter to include only campaigns with impressions
                        "DURING {START_DATE},{END_DATE}";

    // Pull reports
    updateFullReport(queryTemplate.replace("{START_DATE}", last30DaysStart).replace("{END_DATE}", last30DaysEnd), 
                     spreadsheet, "Last 30 Days Report", last30DaysEnd);

    updateFullReport(queryTemplate.replace("{START_DATE}", prev30DaysStart).replace("{END_DATE}", prev30DaysEnd), 
                     spreadsheet, "Previous 30 Days Report", prev30DaysEnd);
    
  } catch (e) {
    Logger.log("Error in main function: " + e.toString());
  }
}

function updateFullReport(query, spreadsheet, sheetName, dateValue) {
  try {
    var sheet = spreadsheet.getSheetByName(sheetName);
    if (!sheet) {
      sheet = spreadsheet.insertSheet(sheetName);
    } else {
      sheet.clear(); // Clear existing data if sheet exists
    }

    var report = AdsApp.report(query);
    var headers = ['Date', 'Campaign', 'Impressions', 'Clicks', 'Cost', 'Conversions', 'Conv. Rate', 
                   'Cost/Conv', 'Avg. CPC', 'CTR', 'Search Impr. Share', 'Search Lost IS (rank)'];
    sheet.appendRow(headers);

    var rows = report.rows();
    var data = [];
    
    while (rows.hasNext()) {
      var row = rows.next();
      data.push([
        dateValue,
        row['CampaignName'],
        row['Impressions'],
        row['Clicks'],
        row['Cost'],
        row['Conversions'],
        row['ConversionRate'],
        row['CostPerConversion'],
        row['AverageCpc'],
        row['Ctr'],
        row['SearchImpressionShare'],
        row['SearchRankLostImpressionShare']
      ]);
    }

    if (data.length > 0) {
      sheet.getRange(2, 1, data.length, data[0].length).setValues(data);

      // Formatting (Only apply if there are rows)
      var lastRow = sheet.getLastRow();
      if (lastRow > 1) {
        sheet.getRange(2, 3, lastRow - 1, 2).setNumberFormat('#,##0');
        sheet.getRange(2, 5, lastRow - 1, 1).setNumberFormat('$#,##0.00');
        sheet.getRange(2, 6, lastRow - 1, 1).setNumberFormat('#,##0');
        sheet.getRange(2, 7, lastRow - 1, 5).setNumberFormat('0.00%');
        sheet.getRange(2, 8, lastRow - 1, 1).setNumberFormat('$#,##0.00');
        sheet.getRange(2, 9, lastRow - 1, 1).setNumberFormat('$#,##0.00');
      }

      sheet.autoResizeColumns(1, sheet.getLastColumn());
      Logger.log("Report successfully updated for " + sheetName + " with " + data.length + " rows.");
    } else {
      Logger.log("No data found for " + sheetName);
    }
  } catch (e) {
    Logger.log("Error in updateFullReport: " + e.toString());
  }
}

// Utility function to format date as yyyyMMdd
function getFormattedDate(date) {
  return Utilities.formatDate(date, "GMT", "yyyyMMdd");
}

// Utility function to add days to a date
function addDays(date, days) {
  var newDate = new Date(date);
  newDate.setDate(newDate.getDate() + days);
  return newDate;
}

Team

See Full Bio