Google Ads Script: Account Budget Allocator

function main() {
  listAccountCostsAndSendEmail(); // Lists Account Names, Costs, Projected Monthly Costs, checks against Max Budget, and sends email
}

function listAccountCostsAndSendEmail() {
  var spreadsheetUrl = '[SPREADSHEET URL]'; // Placeholder for the actual URL of the Google Spreadsheet
  var sheet = SpreadsheetApp.openByUrl(spreadsheetUrl).getSheetByName('Table'); // Access the specific sheet by name

  var iterator = AdsManagerApp.accounts().get(); // Get all managed accounts
  var currentDate = new Date();
  var daysInMonth = new Date(currentDate.getFullYear(), currentDate.getMonth() + 1, 0).getDate();
  var dayOfMonth = currentDate.getDate();

  var results = []; // Array to hold all the rows of data to write back to the sheet
  var emailBody = '<h1>Monthly Projected Costs Report</h1>' +
                  '<p>Click <a href="' + spreadsheetUrl + '">here</a> to access the full report in Google Sheets.</p>' +
                  '<table border="1" style="width:100%; border-collapse:collapse;">' +
                  '<tr><th>Account Name</th><th>Total Costs this Month</th><th>Projected Costs (Month)</th>' +
                  '<th>Max Budget</th><th>Exceeding</th><th>% From Max Budget</th></tr>';

  while (iterator.hasNext()) {
    var account = iterator.next();
    AdsManagerApp.select(account);
    var stats = account.getStatsFor("THIS_MONTH");
    var totalSpent = stats.getCost();
    var projectedMonthlyCosts = (totalSpent / dayOfMonth) * daysInMonth;

    var maxBudgetCell = sheet.getRange(results.length + 2, 4);
    var maxBudget = parseFloat(maxBudgetCell.getValue());
    if (isNaN(maxBudget)) {
      maxBudget = 0;
      maxBudgetCell.setValue(0);
    }

    var exceeding = projectedMonthlyCosts > maxBudget ? 'YES' : 'NO';
    var percentFromBudget = ((projectedMonthlyCosts - maxBudget) / maxBudget) * 100;
    var rowColor = exceeding === 'YES' ? ' style="background-color:red;"' : '';

    var dataRow = [
      account.getName(),
      totalSpent.toFixed(2),
      projectedMonthlyCosts.toFixed(2),
      maxBudget.toFixed(2),
      exceeding,
      percentFromBudget.toFixed(2) + '%'
    ];
    results.push(dataRow);

    emailBody += '<tr' + rowColor + '><td>' + account.getName() + '</td><td>$' + totalSpent.toFixed(2) + '</td>' +
                 '<td>$' + projectedMonthlyCosts.toFixed(2) + '</td><td>$' + maxBudget.toFixed(2) + '</td>' +
                 '<td>' + exceeding + '</td><td>' + percentFromBudget.toFixed(2) + '%</td></tr>';
  }
  emailBody += '</table>';

  var range = sheet.getRange(2, 1, results.length, 6);
  range.setValues(results);

  MailApp.sendEmail({
    to: "[EMAIL ADDRESS]",
    subject: "Monthly Projected Costs Report",
    htmlBody: emailBody
  });
}