MCC script: hourly script checking if your accounts are alive

function main() {

var mailto = "YOUR_EMAIL";  // add you email to get notifications

var my_sheet = "https://docs.google.com/spreadsheets/blablabla";  // add here url of the spreadsheet copy you made

var label_for_accounts = "YOUR_LABEL" // add here the label you've added to accounts that you want to monitor. If you want to use script on all account find the delete ".withCondition('LabelNames CONTAINS ' + label_for_accounts)"

Logger.log("script is starting");

var sheet_input = SpreadsheetApp.openByUrl(my_sheet).getSheetByName('input');
    sheet_input.clearContents();

var columns = [
 'Account Name',
 'Impressions',
 'ConversionValue',
 'HourOfDay'
 ];


sheet_input.appendRow(columns);


Logger.log("----------------------");
Logger.log("getting hourly data for accounts");
Logger.log("go get some coffee meanwhile...");

var accounts = AdsManagerApp.accounts().withCondition('LabelNames CONTAINS ' + label_for_accounts).get();


while (accounts.hasNext()) {
var account = accounts.next();
AdsManagerApp.select(account)
var account_name = account.getName();


var data = [];
var columnsStr = columns.join(',') + " ";


var report = AdsApp.report("SELECT Impressions, ConversionValue, HourOfDay " +
                               "FROM ACCOUNT_PERFORMANCE_REPORT " +
                               "DURING TODAY");

 var rows = report.rows();
 while (rows.hasNext()) {
 var row = rows.next();

 data.push(row[account_name]);




 sheet_input.appendRow([account_name, row['Impressions'], row['ConversionValue'], row['HourOfDay']]);


}
}

Logger.log("----------------------");
Logger.log("data reporting is finished");
Logger.log("----------------------");
Logger.log("verifying email sending");

var sheet_with_data = SpreadsheetApp.openByUrl(my_sheet).getSheetByName('dashboard');



/*************** EMAIL IMPRESSIONS ****************************/


 var ImpressionsEmaildata = sheet_with_data.getRange("G10:G100").getValues();

Logger.log("----------------------");
Logger.log("CHECKING IMPRESSIONS");

var  ImpressionsEmailrow = 0;
  var ImpressionsEmailcol = 0;
      if (ImpressionsEmaildata[ImpressionsEmailrow][ImpressionsEmailcol] === "" || 0) {
        Logger.log("Nothing to send, all good");
      }
  else
       sendEmailImpressions();


/*************** EMAIL CONVERSIONS ****************************/

var sheet_with_data = SpreadsheetApp.openByUrl(my_sheet).getSheetByName('dashboard');

Logger.log("----------------------");
Logger.log("CHECKING REVENUE");

 var ConversionsEmaildata = sheet_with_data.getRange("I10:I100").getValues();

var  ConversionsEmailrow = 0;
  var ConversionsEmailcol = 0;
      if (ConversionsEmaildata[ConversionsEmailrow][ConversionsEmailcol] === "" || 0) {
        Logger.log("Nothing to send, all good");
      }
  else
       sendEmailConversions();






function sendEmailImpressions(){
 var ImpressionsSpeadseet = SpreadsheetApp.openByUrl(my_sheet).getSheetByName('dashboard');
 var impressions_data_input = ImpressionsSpeadseet.getRange("G10:G100").getValues();
 var impressions_data = [i for each (i in impressions_data_input)if (isNaN(i))];

var TABLEFORMAT = 'cellspacing="10" cellpadding="0" dir="ltr" border="1" style="width:60%;table-layout:fixed;font-size:10pt;font-family:verdana,sans,sans-serif;border-collapse:collapse;border:0.5px solid #fff;font-weight:normal;color:black;background-color:white;text-align:center;text-decoration:none;font-style:normal;'
var htmltable = '<table ' + TABLEFORMAT +' ">';

for (row = 0; row<impressions_data.length; row++){
htmltable += '<tr>';
for (col = 0 ;col<impressions_data[row].length; col++){
if (impressions_data[row][col] === "") {
htmltable += '</table>';
  }
else
if (row === 0)  {
htmltable += '<th>' + impressions_data[row][col] + '</th>';
  }
else {
htmltable += '<td>' + impressions_data[row][col] + '</td>';
             }
}
htmltable += '</tr>';
}
 htmltable += '</table>';

Logger.log(impressions_data);

MailApp.sendEmail(mailto, '💀 Critical alert - Some of your accounts stopped getting impressions','' ,{htmlBody: htmltable})
}




function sendEmailConversions(){
 var ConversionsSpeadseet = SpreadsheetApp.openByUrl(my_sheet).getSheetByName('dashboard');
 var conversions_data_input = ConversionsSpeadseet.getRange("I10:I100").getValues();
 var conversions_data = [i for each (i in conversions_data_input)if (isNaN(i))];


var TABLEFORMAT = 'cellspacing="10" cellpadding="0" dir="ltr" border="1" style="width:60%;table-layout:fixed;font-size:10pt;font-family:verdana,sans,sans-serif;border-collapse:collapse;border:0.5px solid #fff;font-weight:normal;color:black;background-color:white;text-align:center;text-decoration:none;font-style:normal;'
var htmltable = '<table ' + TABLEFORMAT +' ">';
for (row = 0; row<conversions_data.length; row++){
     htmltable += '<tr>';
for (col = 0 ;col<conversions_data[row].length; col++){
 if (conversions_data[row][col] === "") {
    htmltable += '</table>';
  }

  else
    if (row === 0)  {
htmltable += '<th>' + conversions_data[row][col] + '</th>';
    }

  else {
htmltable += '<td>' + conversions_data[row][col] + '</td>';
             }
}
htmltable += '</tr>';

}

   htmltable += '</table>';

Logger.log(conversions_data);

  MailApp.sendEmail(mailto, '💀 Critical alert - Some of your accounts get no reported conversion value','' ,{htmlBody: htmltable})
}

Logger.log("----------------------");
Logger.log("THE END");
}