Script for sending Campaign Data to email

[Natanael Santos]

Hey, hello!

I'm trying to create a script using ChatGPT to send in my email a report of Google Ads campaigns. The script runs without error, but the email is not being sent.

Where is the error? I used the right email in line 2.

function main() {
  const EMAIL_RECIPIENT = 'em...@email.com'; // Substitua pelo seu e-mail
  const yesterday = getFormattedDate(new Date(new Date().getTime() - 1 * 24 * 60 * 60 * 1000)); // D-1

  const metrics = [
    { name: 'CampaignName', label: 'Campanha', format: value => value },
    { name: 'Cost', label: 'Custo', format: value => (value / 1000000).toFixed(2) }, // Converte de micros para moeda padrão
    { name: 'Impressions', label: 'Impressões', format: value => value },
    { name: 'Clicks', label: 'Cliques', format: value => value },
    { name: 'Conversions', label: 'Conversões', format: value => value },
    { name: 'AverageCpc', label: 'CPC', format: value => (value / 1000000).toFixed(2) }, // Converte para moeda padrão
    { name: 'Ctr', label: 'CTR', format: value => `${(value * 100).toFixed(2)}%` }, // Converte de proporção para porcentagem
    { name: 'ConversionRate', label: 'Taxa de Conversão', format: value => `${(value * 100).toFixed(2)}%` }
  ];

  const reportData = getReportData(`${yesterday},${yesterday}`, metrics);

  if (reportData.length === 0) {
    Logger.log('Nenhum dado encontrado para o dia anterior.');
    return; // Evita enviar e-mail vazio
  }

  const htmlTable = generateHtmlTable(reportData, metrics);

  const subject = 'Relatório Diário de Campanhas Google Ads (D-1)';
  const body = `
    <p>Segue o relatório com os resultados do dia anterior:</p>
    ${htmlTable}
  `;

  try {
    MailApp.sendEmail({
      to: EMAIL_RECIPIENT,
      subject: subject,
      htmlBody: body
    });
    Logger.log('E-mail enviado com sucesso para: ' + EMAIL_RECIPIENT);
  } catch (e) {
    Logger.log('Erro ao enviar e-mail: ' + e.message);
  }
}

function getReportData(dateRange, metrics) {
  const data = [];

  const report = AdsApp.report(`
    SELECT
      ${metrics.map(metric => metric.name).join(',')}
    FROM
      CAMPAIGN_PERFORMANCE_REPORT
    WHERE
      Impressions > 0
    DURING
      ${dateRange}
  `);

  const rows = report.rows();
  while (rows.hasNext()) {
    const row = rows.next();
    const campaignData = {};

    metrics.forEach(metric => {
      campaignData[metric.name] = parseFloat(row[metric.name]) || row[metric.name];
    });

    // Calcula o CPM manualmente
    const cost = parseFloat(row['Cost']) || 0;
    const impressions = parseFloat(row['Impressions']) || 0;
    campaignData['CPM'] = impressions > 0 ? (cost / impressions) * 1000 : 0;

    data.push(campaignData);
  }

  return data;
}

function generateHtmlTable(data, metrics) {
  let table = '<table border="1" style="border-collapse: collapse; width: 100%;">';
  table += `
    <thead>
      <tr>
        ${metrics.map(metric => `<th>${metric.label}</th>`).join('')}
        <th>CPM</th>
      </tr>
    </thead>
  `;

  table += '<tbody>';
  data.forEach(row => {
    table += '<tr>';
    metrics.forEach(metric => {
      const value = row[metric.name];
      table += `<td>${metric.format(value)}</td>`;
    });
    table += `<td>${row['CPM'].toFixed(2)}</td>`;
    table += '</tr>';
  });
  table += '</tbody></table>';

  return table;
}

function getFormattedDate(date) {
  const year = date.getFullYear();
  const month = (`0${date.getMonth() + 1}`).slice(-2);
  const day = (`0${date.getDate()}`).slice(-2);
  return `${year}${month}${day}`; // Formato aceito pelo Google Ads Scripts
}

Thank you for the help!

Natanael

[Google Ads Scripts Forum Advisor]

Hi Natanael,

Thank you for reaching out to the Google Ads Scripts support team.

Regarding your concern, I'm afraid that the creation of a script through ChatGPT is currently unreliable. I would highly suggest going through our developer documentation for the available methods and features that Google Ads Scripts offers. As per checking, it appears that you are using the CAMPAIGN_PERFORMANCE_REPORT. That said, I would recommend migrating your query to avoid issues on your end in the future. You need to use this Query Migration tool to translate AWQL queries into GAQL.

In order to analyze the issue further, please share the below details :

• Google Ads account ID / CID
• Script name

You can send the details via Reply privately to the author option, or direct private reply to this email.
 

This message is in relation to case "ref:!00D1U01174p.!5004Q02vGxgw:ref" (ADR-00285300)

Thanks,
 

Google Ads Scripts Team

Feedback
How was our support today?