Call report (date range)

umhghmghm,

To set up this script, follow these steps:

1. Open your Google Ads account.

2. Go to the "Tools & Settings" menu and select "Scripts".

3. Click the "Create" or "+" button to create a new script.

4. Copy and paste the following script.

function main() {
  // Replace with your Google Spreadsheet ID and sheet name
  var SPREADSHEET_ID = '15J6wVzRh-IcHIPw5fC2LIrzePVswb8u6inW0qehh4rA';
  var SHEET_NAME = 'Call history';

  // Specify the date range in YYYYMMDD format
  var startDate = '20240101'; // Replace with your start date
  var endDate = '20240814';   // Replace with your end date

  // Define the report query with the specified date range
  var report = AdsApp.report(
    "SELECT " +
      "CallStartTime, " +
      "CallDuration, " +
      "CallerCountryCallingCode, " +
      "CallerNationalDesignatedCode, " +
      "CampaignName, " +
      "CallStatus, " +
      "CallTrackingDisplayLocation, " +
      "CallType " +
    "FROM CALL_METRICS_CALL_DETAILS_REPORT " +
    "WHERE CallStartTime >= '" + startDate + "' AND CallStartTime <= '" + endDate + "'"
  );

  // Get the spreadsheet and the sheet
  var spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
  var sheet = spreadsheet.getSheetByName(SHEET_NAME);

  // Clear the sheet before writing new data
  sheet.clear();

  // Write headers to the first row
  var headers = [
    'Date', 
    'Duration of Phone Call (seconds)', 
    'Caller Country Code', 
    'Area Code', 
    'Campaign', 
    'Status', 
    'Call Source', 
    'Call Type'
  ];
  sheet.appendRow(headers);

  // Write the report rows to the spreadsheet
  var rows = report.rows();
  while (rows.hasNext()) {
    var row = rows.next();
    sheet.appendRow([
      row['CallStartTime'], 
      row['CallDuration'], 
      row['CallerCountryCallingCode'], 
      row['CallerNationalDesignatedCode'], 
      row['CampaignName'], 
      row['CallStatus'], 
      row['CallTrackingDisplayLocation'], 
      row['CallType']
    ]);
  }

  Logger.log('Call data from ' + startDate + ' to ' + endDate + ' has been successfully exported to the spreadsheet.');
}

1. Replace the spreadsheet URL, sheet name, and campaign name.

2. Save the script.

3. Review and authorize the script to access your Google Ads and Google Sheets accounts.

4. Run the script manually for the first time to populate the initial values in the spreadsheet.

5. (Optional) Schedule the script to run on a specific cadence (ie: daily, weekly, monthly, etc.)

Note: Ensure that you have the necessary permissions to access and modify the target Google Spreadsheet.