Call report (evergreen)

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 = 'INSERT-SPREADSHEET-ID';
  var SHEET_NAME = 'INSERT-SHEET-NAME';

  // Specify the date range in YYYYMMDD format
  var startDate = '20250316'; // Replace with your start date
  var endDate = '20260316';   // Replace with a future 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);

  // Determine how many rows to clear (excluding header row)
  var lastRow = sheet.getLastRow();
  if (lastRow > 1) {
    sheet.getRange(2, 1, lastRow - 1, 8).clearContent(); // Clears A2:H for existing rows
  }

  // Optional: Clear old header row and write a fresh one (in case it was missing)
  var headers = [
    'Date', 
    'Duration of Phone Call (seconds)', 
    'Caller Country Code', 
    'Area Code', 
    'Campaign', 
    'Status', 
    'Call Source', 
    'Call Type'
  ];
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

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

  // Paste new data starting at row 2, columns A–H
  if (output.length > 0) {
    sheet.getRange(2, 1, output.length, 8).setValues(output);
  }

  Logger.log('Call data from ' + startDate + ' to ' + endDate + ' has been successfully exported without clearing Column I.');
}

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.