GCLID Report

Overview

Quick report for exporting a breakdown of GCLIDs to a Google Sheet.

function main() {

  var SPREADSHEET_URL = "MY_GOOGLE_SHEET_URL";

  var sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL).getActiveSheet();

  // ---------- date range: last 7 days ----------

  var now = new Date();

  var startDate = new Date(now.getFullYear(), now.getMonth(), now.getDate() - 7);

  var yesterday = new Date(now.getFullYear(), now.getMonth(), now.getDate() - 1);

  // Helper: format date as YYYYMMDD for AWQL

  function formatAWQLDate(d) {

    return Utilities.formatDate(d, AdsApp.currentAccount().getTimeZone(), 'yyyyMMdd');

  }

  // Prepare array to collect new rows

  var rowsOut = [];

  // Loop day by day

  for (var d = new Date(startDate.getTime()); d <= yesterday; d.setDate(d.getDate() + 1)) {

    var dayStr = formatAWQLDate(d);

    var query = ''

      + 'SELECT Date, GclId, CampaignId, CampaignName, AdGroupId, AdGroupName, ClickType '

      + 'FROM CLICK_PERFORMANCE_REPORT '

      + 'WHERE GclId != "" '

      + 'DURING ' + dayStr + ',' + dayStr;

    try {

      var report = AdsApp.report(query);

      var rows = report.rows();

      while (rows.hasNext()) {

        var r = rows.next();

        rowsOut.push([

          r['Date'],

          r['GclId'],

          r['CampaignId'],

          r['CampaignName'],

          r['AdGroupId'],

          r['AdGroupName'],

          r['ClickType']

        ]);

      }

    } catch (e) {

      Logger.log('Error pulling report for ' + dayStr + ': ' + e);

    }

  }

  if (rowsOut.length > 0) {

    // ---------- Insert new rows after header ----------

    sheet.insertRowsAfter(1, rowsOut.length);

    sheet.getRange(2, 1, rowsOut.length, rowsOut[0].length).setValues(rowsOut);

    // ---------- Deduplicate all columns using removeDuplicates ----------

    var lastRow = sheet.getLastRow();

    if (lastRow > 1) {

      sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn())

        .removeDuplicates([1,2,3,4,5,6,7]); // deduplicate using all 7 columns

    }

    // ---------- Sort by Date ascending (first column) ----------

    if (sheet.getLastRow() > 1) {

      sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn())

        .sort([{column: 1, ascending: true}]);

    }

    // ---------- Delete extra empty rows ----------

    var maxRows = sheet.getMaxRows();

    lastRow = sheet.getLastRow();

    if (maxRows > lastRow) {

      sheet.deleteRows(lastRow + 1, maxRows - lastRow);

    }

    Logger.log('Added ' + rowsOut.length + ' new rows, deduplicated, sorted, and cleaned empty rows.');

  } else {

    Logger.log('No new rows found for the last 7 days.');

  }

}