javascriptgoogle-apps-scriptgoogle-sheetsadsenseadsense-api

Import AdSense in Google Sheets

I'm using the generateReport function from Google's AdSense Service to import AdSense data into Google Spreadsheets. How do I edit the code to import data only from a specific domain (AdSense ads are shown on various domains) and to append this data to an existing tab inside a Google Spreadsheet instead of creating a new Google Spreadsheet each time?

Here is the full code:

/**
 * Generates a spreadsheet report for a specific ad client in an account.
 * @param {string} accountName The resource name of the account.
 * @param {string} adClientReportingDimensionId The reporting dimension ID
 *     of the ad client.
 */
function generateReport(accountName, adClientReportingDimensionId) {
  // Prepare report.
  const today = new Date();
  const oneWeekAgo = new Date(today.getTime() - 7 * 24 * 60 * 60 * 1000);

  const report = AdSense.Accounts.Reports.generate(accountName, {
    // Specify the desired ad client using a filter.
    filters: ['AD_CLIENT_ID==' + escapeFilterParameter(adClientReportingDimensionId)],
    metrics: ['PAGE_VIEWS', 'AD_REQUESTS', 'AD_REQUESTS_COVERAGE', 'CLICKS',
      'AD_REQUESTS_CTR', 'COST_PER_CLICK', 'AD_REQUESTS_RPM',
      'ESTIMATED_EARNINGS'],
    dimensions: ['DATE'],
    ...dateToJson('startDate', oneWeekAgo),
    ...dateToJson('endDate', today),
    // Sort by ascending date.
    orderBy: ['+DATE']
  });

  if (!report.rows) {
    console.log('No rows returned.');
    return;
  }
  const spreadsheet = SpreadsheetApp.create('AdSense Report');
  const sheet = spreadsheet.getActiveSheet();

  // Append the headers.
  sheet.appendRow(report.headers.map((header) => header.name));

  // Append the results.
  sheet.getRange(2, 1, report.rows.length, report.headers.length)
      .setValues(report.rows.map((row) => row.cells.map((cell) => cell.value)));

  console.log('Report spreadsheet created: %s',
      spreadsheet.getUrl());
}

/**
 * Escape special characters for a parameter being used in a filter.
 * @param {string} parameter The parameter to be escaped.
 * @return {string} The escaped parameter.
 */
function escapeFilterParameter(parameter) {
  return parameter.replace('\\', '\\\\').replace(',', '\\,');
}

/**
 * Returns the JSON representation of a Date object (as a google.type.Date).
 *
 * @param {string} paramName the name of the date parameter
 * @param {Date} value the date
 * @return {object} formatted date
 */
function dateToJson(paramName, value) {
  return {
    [paramName + '.year']: value.getFullYear(),
    [paramName + '.month']: value.getMonth() + 1,
    [paramName + '.day']: value.getDate()
  };
}

Solution

  • To avoid creating a new sheet every time data is imported I ended up doing this. Instead of this:

    const spreadsheet = SpreadsheetApp.create('AdSense Report');
  const sheet = spreadsheet.getActiveSheet();

// Append the results.
sheet.getRange(2, 1, report.rows.length, report.headers.length)
  .setValues(report.rows.map((row) => row.cells.map((cell) => cell.value)));

    I used this:

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("AdSense");

var last_row = sheet.getRange("A1:A").getValues();
var last_row = last_row.filter(String).length + 1;

// Append the results.
sheet.getRange(last_row, 1, report.rows.length, report.headers.length)
  .setValues(report.rows.map((row) => row.cells.map((cell) => cell.value)));