Script in Google Ads account to export data to a Google Sheets

google-ads-2-google-sheets.js

function main() {
  var spreadsheetUrl = 'https://docs.google.com/spreadsheets/d/id/edit?usp=sharing';
  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  
  var pivotedSheet = getOrCreateSheet(spreadsheet, 'PivotedConversionActions');  // New pivoted data sheet
  
  var firstDate = '2022-01-01';
  var startDate = getDateNDaysAgo(200);
  var endDate = getDateNDaysAgo(1);
  
  // Step 1: Fetch conversion action names
  var conversionActionNames = getConversionActionNames(firstDate, endDate);

  // Step 2: Fetch conversion actions data
  var conversionQuery = `
    SELECT 
      segments.date, 
      segments.week, 
      segments.month,       
      campaign.status,
      campaign.name, 
      customer.currency_code, 
      campaign.advertising_channel_type,
      segments.conversion_action_name,
      metrics.conversions
    FROM campaign
    WHERE segments.date BETWEEN '${startDate}' AND '${endDate}' AND
    customer.currency_code = "EUR"
  `;
  var conversionData = fetchData(conversionQuery);

  // Step 3: Fetch campaign data
  var campaignQuery = `
    SELECT 
      segments.date, 
      segments.week, 
      segments.month,       
      campaign.status,
      campaign.name, 
      customer.currency_code,    
      campaign.advertising_channel_type,
      metrics.clicks, 
      metrics.impressions, 
      metrics.cost_micros, 
      metrics.conversions,
      metrics.conversions_value
    FROM campaign
    WHERE segments.date BETWEEN '${startDate}' AND '${endDate}' AND
    customer.currency_code = "EUR"
  `;
  var campaignData = fetchData(campaignQuery);

  // Step 4: Pivot conversion actions and store the data in a new sheet
  var pivotedData = pivotConversionActions(campaignData, conversionData, conversionActionNames);
  
  // Step 5: Store the pivoted data in the PivotedConversionActions sheet
  storePivotedData(pivotedData, pivotedSheet);
}

// Function to fetch conversion action names
function getConversionActionNames(startDate, endDate) {
  var conversionActionQuery = `
    SELECT 
      segments.date, 
      segments.conversion_action_name
    FROM campaign
    WHERE segments.date BETWEEN '${startDate}' AND '${endDate}'
    ORDER BY segments.date ASC
  `;
  
  var conversionActionData = fetchData(conversionActionQuery);
  var conversionActionOrder = {}; 
  var orderedConversionActionNames = [];

  conversionActionData.forEach(function(row) {
    var actionName = String(row[1]).trim(); // Ensure clean names
    
    if (!(actionName in conversionActionOrder)) {
      conversionActionOrder[actionName] = orderedConversionActionNames.length;
      orderedConversionActionNames.push(actionName);
    }
  });

  Logger.log("Ordered Conversion Action Names: " + orderedConversionActionNames);
  return orderedConversionActionNames;
}

// Function to fetch data
function fetchData(query) {
  var report = AdsApp.report(query);
  var rows = report.rows();
  var data = [];
  while (rows.hasNext()) {
    var row = rows.next();
    var rowData = Object.values(row);
    rowData = rowData.filter(value => typeof value !== 'function');  
    if (rowData.length > 0) {
      data.push(rowData);
    }
  }
  return data;
}

// Pivot conversion actions and merge with campaign data
function pivotConversionActions(campaignData, conversionData, conversionActionNames) {
  var pivotedData = [];

  // Define headers
  var headers = [
    'Day', 'Week', 'Month', 'Status', 'Campaign', 'Currency', 'Campaign type',
    'SUM of Clicks', 'SUM of Impr.', 'SUM of Cost', 'SUM of Conversions', 'SUM of Conv. value'
  ].concat(conversionActionNames); 

  pivotedData.push(headers); 

  // Create lookup table for campaign data
  var campaignMap = {};

  campaignData.forEach(function(row) {
    var key = row.slice(0, 7).map(val => String(val).trim()).join('|'); // 0-7: row[0]...row[6]
    var baseRow = [
      row[0], // Date
      row[1], // Week
      row[2], // Month
      row[3], // Status
      row[4], // Campaign Name
      row[5], // Currency
      row[6], // Advertising Channel Type
      row[7], // Clicks
      row[8], // Impressions
      row[9] / 1000000,  // Cost
      row[10], // Conversions
      row[11], // Conversion Value
    ];

    var conversionCounts = Array(conversionActionNames.length).fill(0); 
    campaignMap[key] = baseRow.concat(conversionCounts);
  });

  Logger.log("✅ Campaign Keys Count: " + Object.keys(campaignMap).length);

  // Populate pivoted conversion values
  var missingKeys = 0;

  conversionData.forEach(function(row) {
    var key = row.slice(0, 7).map(val => String(val).trim()).join('|'); // 0-7: row[0]...row[6]
    var conversionAction = String(row[7]).trim();
    var conversionIndex = conversionActionNames.indexOf(conversionAction);

    if (campaignMap[key] && conversionIndex !== -1) {
      var columnIndex = 12 + conversionIndex; 
      campaignMap[key][columnIndex] += parseFloat(row[8]) || 0; 
    } else {
      Logger.log(`⚠️ Missing Key: ${key} | Conversion Action: ${conversionAction}`);
      missingKeys++;
    }
  });

  Logger.log(`⚠️ Total Missing Keys: ${missingKeys}`);

  // Store pivoted rows
  Object.values(campaignMap).forEach(function(rowData) {
    while (rowData.length < headers.length) {
        rowData.push(0);
    }
    while (rowData.length > headers.length) {
        rowData.pop();
    }
    pivotedData.push(rowData);
  });

  Logger.log("✅ Final Pivoted Data Length: " + pivotedData.length);
  return pivotedData;
}

// Store data in Google Sheets
function storePivotedData(data, sheet) {
  if (data.length > 0) {
    sheet.clear();
    
    var numColumns = data[0].length;
    Logger.log("📊 Expected Columns (Headers): " + numColumns);
    
    // Check if all data rows have the correct number of columns
    data.forEach((row, index) => {
      if (row.length !== numColumns) {
        Logger.log(`⚠️ Row ${index} has ${row.length} columns (Expected: ${numColumns})`);
        Logger.log("➡️ Row Data: " + row);
      }
    });

    try {
      sheet.getRange(1, 1, data.length, numColumns).setValues(data);
    } catch (e) {  
      Logger.log("🚨 Error when setting values: " + e.message);
      Logger.log("⚠️ ERROR: Column count mismatch!");
      throw e;
    }
  }
}

// Get or create a Google Sheet
function getOrCreateSheet(spreadsheet, sheetName) {
  var sheet = spreadsheet.getSheetByName(sheetName);
  if (!sheet) {
    sheet = spreadsheet.insertSheet(sheetName);
  }
  return sheet;
}

// Get date N days ago
function getDateNDaysAgo(n) {
  var date = new Date();
  date.setDate(date.getDate() - n);
  var timeZone = AdsApp.currentAccount().getTimeZone();
  return Utilities.formatDate(date, timeZone, 'yyyy-MM-dd');
}