How To Get Data from Google Ads to Google Sheets for Free (Hourly)

Mitch Cartwright

23 Feb 2025

•

3 min read

There are a lot of paid third party tools that will give you this feature (with a price tag). I'm going to show you how you can get data from Google Ads to Google Sheets every hour for free.

‍

First, you are going need the script below. What this does is take in a Google Sheet, pull the ID from it, export the account number, account name, date, cost and conversions.

‍

Then we will preview the script to test that it's working. Once data is pulling in correctly then we will set the frequency of the script to run every hour.

‍

How to Export Google Ads Data to Google Sheets

Go to your scripts section in your ad account and add a new script.

Copy and paste the script below.

function main() {
  var today = new Date();
  var firstDayOfMonth = new Date(today.getFullYear(), today.getMonth(), 1);
  
  // Open the specific spreadsheet
  var spreadsheetId = "YOUR_SPREADSHEET_HERE";
  var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  var sheet = spreadsheet.getSheetByName("RAW_DATA_EXPORT");
  
  // Create the sheet if it doesn't exist
  if (!sheet) {
    sheet = spreadsheet.insertSheet("RAW_DATA_EXPORT");
    sheet.appendRow(["Account Number", "Account Name", "Date", "Cost", "Conversions"]);
  } else {
    // Clear existing content if the sheet already exists
    sheet.clear();
    sheet.appendRow(["Account Number", "Account Name", "Date", "Cost", "Conversions"]);
  }
  
  // Check if this is an MCC account
  if (typeof AdsManagerApp !== 'undefined') {
    // MCC account
    var accounts = AdsManagerApp.accounts().get();
    while (accounts.hasNext()) {
      var account = accounts.next();
      AdsManagerApp.select(account);
      runReportForAccount(sheet, firstDayOfMonth, today);
    }
  } else {
    // Individual account
    runReportForAccount(sheet, firstDayOfMonth, today);
  }
  
  Logger.log("Report has been updated in the spreadsheet: " + spreadsheet.getUrl());
}

function runReportForAccount(sheet, firstDayOfMonth, today) {
  var report = AdsApp.report(
    "SELECT ExternalCustomerId, AccountDescriptiveName, Date, Cost, Conversions " +
    "FROM ACCOUNT_PERFORMANCE_REPORT " +
    "WHERE Date >= '" + formatDate(firstDayOfMonth) + "' AND Date <= '" + formatDate(today) + "' " +
    "ORDER BY Date ASC");
  
  var rows = report.rows();
  while (rows.hasNext()) {
    var row = rows.next();
    sheet.appendRow([
      row['ExternalCustomerId'],
      row['AccountDescriptiveName'],
      row['Date'],
      row['Cost'],
      row['Conversions']
    ]);
  }
}

function formatDate(date) {
  var year = date.getFullYear();
  var month = padZero(date.getMonth() + 1);
  var day = padZero(date.getDate());
  return year + "-" + month + "-" + day;
}

function padZero(number) {
  return (number < 10 ? '0' : '') + number;
}

Create a new Google Sheet (life hack: you can type sheets.new into your address bar and if you're logged into your Google Account it will spin up a blank sheet instantly).
Copy your spreadsheet ID and replace the variable in the script above on line 6 where it says var spreadsheetId = "YOUR_SPREADSHEET_HERE";
Hit preview and the script will run

You should then see data fill the sheet of the Google Sheet ID you added.

When you know it's working, you can then head back to the scripts tabs and set the script to run hourly.

‍

Now that you have your data pulling into a Google Sheet and updating hourly, you can use this data to build your own reports that will allow you to have an almost real time overview of all of your ad accounts.

If you're having any issues getting this set up feel free to email me mitch@adsthatconvert.co.

Mitch Cartwright