Impressions heatmap (last 30 days)

This script fetches impression data from a specific campaign, then writes this data into a spreadsheet. It structures the data into a heatmap showing impressions per hour for each day for the last 30 days

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() {
  var SPREADSHEET_URL = 'INSERT-SPREADSHEET-URL'; // Replace with your Spreadsheet's URL
  var SHEET_NAME = 'INSERT_SHEET-NAME'; // Replace with your sheet name
  var CAMPAIGN_NAME = 'INSERT-CAMPAIGN-NAME'; // Replace with your campaign name

  var today = new Date();
  var endDate = Utilities.formatDate(today, 'GMT', 'yyyyMMdd');
  var startDate = new Date(today.getTime() - 30 * 24 * 60 * 60 * 1000);
  var formattedStartDate = Utilities.formatDate(startDate, 'GMT', 'yyyyMMdd');

  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = spreadsheet.getSheetByName(SHEET_NAME);

  // Optional: Clear the contents of the sheet before running the script
  sheet.clearContents();

  // Define the header row
  sheet.appendRow(['HourOfDay', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']);

  // Build and run the report
  var report = AdsApp.report(
    'SELECT DayOfWeek, HourOfDay, Impressions ' +
    'FROM CAMPAIGN_PERFORMANCE_REPORT ' +
    'WHERE CampaignName = "' + CAMPAIGN_NAME + '" ' +
    'DURING ' + formattedStartDate + ',' + endDate
  );

  var rows = report.rows();
  var data = {};

  // Convert 24-hour time format to 12-hour format
  function convertTo12Hour(time) {
    return time == 0 ? '12:00 AM' : (time < 12 ? time + ':00 AM' : (time == 12 ? '12:00 PM' : time - 12 + ':00 PM'));
  }

  // Initialize data
  for (var i = 0; i < 24; i++) {
    var time = convertTo12Hour(i);
    data[time] = [time, '', '', '', '', '', '', ''];
  }

  while (rows.hasNext()) {
    var row = rows.next();
    var dayOfWeek = row['DayOfWeek'];
    var hourOfDay = convertTo12Hour(parseInt(row['HourOfDay']));
    var impressions = row['Impressions'];

    // Update data
    var columnIndex = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'].indexOf(dayOfWeek) + 1;
    data[hourOfDay][columnIndex] = impressions;
  }

  // Append the data to the sheet
  for (var hourOfDay in data) {
    sheet.appendRow(data[hourOfDay]);
  }

  // Set color scales (pseudo-heatmap) for Impressions
  var maxImpressions = Math.max.apply(Math, Object.values(data).map(function (row) { return Math.max.apply(Math, row.slice(1)); }));
  var range = sheet.getRange(2, 2, 24, 7); // Adjust the range if needed

  // Clear any previous conditional formatting rules
  range.clearFormat();

  var colorScale = SpreadsheetApp.newConditionalFormatRule()
    .setGradientMinpoint('#FFFFFF') // White for min value
    .setGradientMidpointWithValue('#B0A8FE', SpreadsheetApp.InterpolationType.PERCENTILE, 50) // Yellow for median
    .setGradientMaxpointWithValue('#6052fb', SpreadsheetApp.InterpolationType.NUMBER, maxImpressions) // Red for max value
    .setRanges([range])
    .build();

  var rules = sheet.getConditionalFormatRules();
  rules.push(colorScale);
  sheet.setConditionalFormatRules(rules);
}

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.