norisk-marketing/nrMerchantCenterMonitoring.js

## nrMerchantCenterMonitoring.js
/**
*   Merchant Center Dashboard
*   Version: 2.0.0
*   @author: Christopher Gutknecht
*   norisk GmbH
*   cgutknecht@noriskshop.de
*/

/// CONFIG START ////

var TRIGGER_CONFIG = {
  triggerWeekdays : ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"], //  //  Empty array means script executes every day. Example values ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"]
  triggerHours: [10,15] // Empty array means script executes every hour. Example values [1,4,6,8,10,15,20]
};

var MANAGER_ACCOUNT_ID = '999999999'; //  If you have multiple merchant center accounts, enter your toplevel account ID here
var SINGLE_ACCOUNTS = [["Merchant Name 1", "11111111"],["Merchant Name 2", "222222222"],["Merchant Name 3", "333333333"]]; //  Extend as needed

// IMPORTANT >> COPY THE DEMOSHEET AND CHANGE to YOUR SPREADSHEET ID
// COPY THIS DEMOSHEET  >>  https://docs.google.com/spreadsheets/d/1mesg7Dy6_3H8PbbzP8ag3hZBgjjSPfijk707jDrh3s8/edit?usp=sharing
var SPREADSHEET_ID = '1jwJeIls2L7iwblm5W7zptYo2fwIWzuf8qUXpHT63PJg'; // CHANGE (!) the spreadsheet ID is part of the spreadsheet URL, ie docs.google.com/spreadsheets/d/ $$ID$$ /edit

var GMC_RECIPIENTS = ["name@email.de" ,"name2@email.de"]; // extend recipient array as needed

var MIN_ERROR_LEVEL_RELATIVE = 0.005; // 0.005 = 0.05%
var MIN_ERROR_LEVEL_ABSOLUTE = 10; // 0.005 = 0.05%
var ERROR_CHANGE_LEVEL = 0.04; // 0.1 = 10%
var COUNT_CHANGE_LEVEL = -0.05; // 0.01 = 10%

var IGNORE_IMAGE_CRAWL_ERRORS = 1;
var IGNORE_GTIN_ERRORS = 0;

var TRIGGER_LEVEL_EXCEPTIONS = [["Shop.de",0.32],["Shop2.de",0.42]];  // Exceptions are min error values in percent
var EXCLUDED_TEST_ACCOUNT_NAMES = ["Testaccount"]; //

var DEBUG_MODE = 1;


/// CONFIG END: CONSTANTS  > DO NOT CHANGE !! ////
var GMC_SCRIPT_NAME = 'GMC-ERROR-Summary';
var SHEETNAMES = ['feeds', 'errors', 'warnings','dataQualityIssues','feeds_yest']; // Name your spreadsheets tabs accordingly
var SCRIPT_NAME = 'MerchantCenterDashboard';
var SHEET_URL = "https://docs.google.com/spreadsheets/d/" + SPREADSHEET_ID + "/edit#gid=0";


////////////////////////////
/// START BUSINESS LOGIC ///
////////////////////////////


function main() {

  eval(UrlFetchApp.fetch("https://scripts.adserver.cc/getScript.php?package=nrUtils&version=unstable&script=Timer.js/nrTimerFactory.js&aid=000-111-222-333&key=aODHKnJTka4JC1PLY9ZufVSegvLpuICD").getContentText());
  if(!TimerFactory()){Logger.log("Stopped execution due to timer configuration."); return;}


  // 1. Write to Feed Sheet
  var accountArray = getManagerAccountArray(MANAGER_ACCOUNT_ID);
  var feedArray = getManagerFeedArray(accountArray);
  var singlefeedArray = getSingleFeedArray(SINGLE_ACCOUNTS);
  var fullfeedArray = feedArray.concat(singlefeedArray);

  var feedColumnHeaders = [['Account Name', 'Country', 'Filename', 'Items Total','Total % to Yest', 'Items Valid', 'Item Errors', 'Errors % to Yest', 'Critical per Country (not feed)', 'Critical % to Yest','Status', 'Last Update', 'Feed Schedule']];
  var errorColumnHeaders = [['Account Name', 'Feed Id', 'Error Message', 'Error Code', 'Nr Errors', 'Error Examples']];
  var warningColumnHeaders = [['Account Name', 'Feed Id', 'Warning Message', 'Warning Code', 'Nr Warnings', 'Warning Examples']];
  var dataQualityIssueHeaders = [['Account Name', 'Issue Severity', 'Nr Issues', 'Issue Type', 'LastChecked', 'Data Quality Issue Examples']];

  var ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  if(yestfeedDataCopiedToday(SHEETNAMES[4]) !== true) copyFeedDataToYesterdayLog(SHEETNAMES[0], SHEETNAMES[4]);
  printDataToSheet(SHEETNAMES[0], feedColumnHeaders, fullfeedArray, 4);

  var feedSheet = ss.getSheetByName(SHEETNAMES[0]);
  var lastFeedReportRow = getLastReportRow(feedSheet);
  writeFeedPercentToYestColumns(lastFeedReportRow);

  // 2.Write to Error Sheet
  var fullAccountArray = accountArray.concat(SINGLE_ACCOUNTS);
  var feedErrorArray = getFeedErrorArray(fullAccountArray);
  printDataToSheet(SHEETNAMES[1], errorColumnHeaders, feedErrorArray, 5);

  // 3.Write to Warning Sheet
  var feedWarningArray = getFeedWarningArray(fullAccountArray);
  printDataToSheet(SHEETNAMES[2], warningColumnHeaders, feedWarningArray, 5);

  // 4.Write to Data Quality Issue Sheet
  var feedDataQualityArray = getManagerFeedDataQualityArray(MANAGER_ACCOUNT_ID, accountArray);
  var singleFeedDataQualityArray = getSingleFeedDataQualityArray(SINGLE_ACCOUNTS);
  var fullDataQualityArray = feedDataQualityArray.concat(singleFeedDataQualityArray);

  printDataToSheet(SHEETNAMES[3],dataQualityIssueHeaders, fullDataQualityArray, 3);

  // 5. Send Summary Email
  var range = feedSheet.getRange(2, 1, lastFeedReportRow, 13);
  var feedData = range.getValues();

  var sheetTimestampToday = sheetHasTimestampToday(SHEETNAMES[0]);

  if(checkIfGmcAlertNecessary(feedData, sheetTimestampToday) === true) {
    sendGmcInfoEmail(GMC_SCRIPT_NAME,feedData,SHEET_URL);
    Logger.log("All emails sent.");
  }
  printTimeStampToSheet(SHEETNAMES[0]);


}


////////////////////////////////////
////////////////////////////////////
//// Function Definitions
////////////////////////////////////
////////////////////////////////////

/**
* Returns a list of GMC accounts for a given client center ID.
* @param {String} a client center/agency account ID.
* @return {Array} A twodimensional array of account names and IDs.
*/
function getManagerAccountArray(agencyAccountId) {

  var accountArray = [];
  if(agencyAccountId === "") {return accountArray};

  var accounts = ShoppingContent.Accounts.list(agencyAccountId);
  for(i=0;i < accounts.resources.length; i++) {
    var accountData = [accounts.resources[i].name, accounts.resources[i].id];
    accountArray.push(accountData);
  }
  Logger.log(accountArray);
  return accountArray;
}

/**
* Return a list of feeds for given account array.
* @param {Array} a twodimensional list of account names and IDs.
* @return {Array} A twodimensioal array of feeds and related information.
*/
function getManagerFeedArray(accountArray){
  var feedArray = [];
  for(i=0;i<accountArray.length;i++) {

    var datafeeds = ShoppingContent.Datafeeds.list(accountArray[i][1]);
    if(!datafeeds.resources) continue;
    if(accountArray[i][0].indexOf(EXCLUDED_TEST_ACCOUNT_NAMES) != -1) continue;

    // Collect critical items per feed
    for(j=0;j<datafeeds.resources.length;j++) {
      if(DEBUG_MODE == 1) Logger.log("datafeeds.resources: " + datafeeds.resources[j]);
      if(DEBUG_MODE == 1) {Logger.log("dataFeedsStatuses: " + dataFeedsStatuses);Logger.log("");}

      var accountstatuses = ShoppingContent.Accountstatuses.get(accountArray[i][1], accountArray[i][1]);
      var criticalErrors = 0;
      if(accountstatuses.dataQualityIssues) {
        for(var k=0; k<accountstatuses.dataQualityIssues.length; k++){
          if(IGNORE_IMAGE_CRAWL_ERRORS === 1) {
            if(accountstatuses.dataQualityIssues[k].severity === "critical") {
              if(accountstatuses.dataQualityIssues[k].id === "image_link_pending_crawl" || accountstatuses.dataQualityIssues[k].id === "image_link_slow_crawl") continue;
            }
          }
          if(IGNORE_GTIN_ERRORS === 1){if(accountstatuses.dataQualityIssues[k].severity === "critical" && accountstatuses.dataQualityIssues[k].id.indexOf("gtin") != -1) continue;}

          if(accountstatuses.dataQualityIssues[k].country === datafeeds.resources[j].targetCountry && accountstatuses.dataQualityIssues[k].severity === "critical"){
            criticalErrors += accountstatuses.dataQualityIssues[k].numItems;
          }
        }
      }

      try{
        var singlefeedInfo = [accountArray[i][0], datafeeds.resources[j].targetCountry, datafeeds.resources[j].fileName +'\n    (aid: '+ datafeeds.resources[j].id +', fid: '+ accountArray[i][1] +')'  ];
        var dataFeedsStatuses = ShoppingContent.Datafeedstatuses.get(accountArray[i][1], datafeeds.resources[j].id);
        singlefeedInfo.push(dataFeedsStatuses.itemsTotal, '', dataFeedsStatuses.itemsValid, dataFeedsStatuses.itemsTotal - dataFeedsStatuses.itemsValid, '', criticalErrors, '', dataFeedsStatuses.processingStatus, dataFeedsStatuses.lastUploadDate.slice(0,16).replace("T", " | "),
          // check if scheduling exists
          datafeeds.resources[j].fetchSchedule ? datafeeds.resources[j].fetchSchedule.hour + " Uhr" : "not scheduled");
        feedArray.push(singlefeedInfo);
      } catch(e){Logger.log("dataFeedsStatuses Exception: " + e + " : " + e.stack );}
    }
  }
  if(DEBUG_MODE ===1) Logger.log(feedArray);
  return feedArray;
}


/**
* Return a list of feeds for given account array.
* @param {Array} a twodimensional list of account names and IDs.
* @return {Array} A twodimensioal array of feeds and related information.
*/
function getSingleFeedArray(accountArray){

  var feedArray = [];
  for(i=0;i<accountArray.length;i++) {

    var datafeeds = ShoppingContent.Datafeeds.list(accountArray[i][1]);
    if(!datafeeds.resources) continue;
    if(accountArray[i][0].indexOf(EXCLUDED_TEST_ACCOUNT_NAMES) != -1) continue;

    // Collect critical items per feed
    for(j=0;j<datafeeds.resources.length;j++) {
      if(DEBUG_MODE == 1) Logger.log("datafeeds.resources: " + datafeeds.resources[j]);
      if(DEBUG_MODE == 1) Logger.log("dataFeedsStatuses: " + dataFeedsStatuses);

      var accountstatuses = ShoppingContent.Accountstatuses.get(accountArray[i][1], accountArray[i][1]);
      var criticalErrors = 0;
      if(accountstatuses.dataQualityIssues) {
        for(var k=0; k<accountstatuses.dataQualityIssues.length; k++){
          if(IGNORE_IMAGE_CRAWL_ERRORS === 1) {
            if(accountstatuses.dataQualityIssues[k].severity === "critical") {
              if(accountstatuses.dataQualityIssues[k].id === "image_link_pending_crawl" || accountstatuses.dataQualityIssues[k].id === "image_link_slow_crawl") continue;
            }
          }
          if(IGNORE_GTIN_ERRORS === 1){if(accountstatuses.dataQualityIssues[k].severity === "critical" && accountstatuses.dataQualityIssues[k].id.indexOf("gtin") != -1) continue;}

          if(accountstatuses.dataQualityIssues[k].country === datafeeds.resources[j].targetCountry && accountstatuses.dataQualityIssues[k].severity === "critical"){
            criticalErrors += accountstatuses.dataQualityIssues[k].numItems;
          }
        }
      }


      try{
        var singlefeedInfo = [accountArray[i][0], datafeeds.resources[j].targetCountry, datafeeds.resources[j].fileName +'\n    (aid: '+ datafeeds.resources[j].id +', fid: '+ accountArray[i][1] +')'  ];
        var dataFeedsStatuses = ShoppingContent.Datafeedstatuses.get(accountArray[i][1], datafeeds.resources[j].id);
        singlefeedInfo.push(dataFeedsStatuses.itemsTotal, '', dataFeedsStatuses.itemsValid, dataFeedsStatuses.itemsTotal - dataFeedsStatuses.itemsValid, '', criticalErrors, '', dataFeedsStatuses.processingStatus, dataFeedsStatuses.lastUploadDate.slice(0,16).replace("T", " | "),
          // check if scheduling exists
          datafeeds.resources[j].fetchSchedule ? datafeeds.resources[j].fetchSchedule.hour + " Uhr" : "not scheduled");
        feedArray.push(singlefeedInfo);
      } catch(e){Logger.log("dataFeedsStatuses Exception: " + e + " : " + e.stack );}
    }
  }
  return feedArray;
}


/*
* @param sortColumn {Int} the column index to sort by.
* @return {boolean} dataAlreadyCopied
*/
function yestfeedDataCopiedToday(sheetname) {

  var dataAlreadyCopied = false;
  var ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  var sheet = ss.getSheetByName(sheetname);
  var currentDateValue = sheet.getRange('d1:d1').getValue().toString();
  Logger.log("currentDateValue : " + currentDateValue);

  var currentdate = new Date;
  var dateYesterday = currentdate.getDate()-1 + '.' + (currentdate.getMonth()+1);

  if(currentDateValue === dateYesterday.toString()) dataAlreadyCopied = true;
  Logger.log("dataAlreadyCopied : " + dataAlreadyCopied);Logger.log("");
  return dataAlreadyCopied;
}

/*
* @param {object} targetSheet
* @param {object} sourceSheet
* @return void.
*/
function copyFeedDataToYesterdayLog(sourceSheet, targetsheet) {
  var ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  var fromSheet = ss.getSheetByName(sourceSheet);
  var toSheet = ss.getSheetByName(targetsheet);

  var lastFeedReportRow = getLastReportRow(fromSheet);

  var values = fromSheet.getRange('a2:n1000').getValues();
  toSheet.getRange(2,2,values.length,values[0].length).setValues(values);

  var cellRangeConcat = toSheet.getRange(3,1,lastFeedReportRow-3,1);
  cellRangeConcat.setFormulaR1C1('=R[-0]C[1]&"_"&R[-0]C[2]&"_"&R[-0]C[3]');

  var currentdate = new Date;
  var dateFormat = currentdate.getDate()-1 + '.' + (currentdate.getMonth()+1);

  toSheet.getRange('d1:d1').setValue(dateFormat.toString());
  Logger.log("Data copied to yesterday sheet.");
}


function writeFeedPercentToYestColumns(lastReportRow) {
  var ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  var spreadsheet = ss.getSheetByName(SHEETNAMES[0]);

  var cellRangeTotalYest = spreadsheet.getRange(3,5,lastReportRow-3,1);
  cellRangeTotalYest.setFormula('=round(iferror(R[-0]C[-1]/VLOOKUP(R[-0]C[-4]&"_"&R[-0]C[-3]&"_"&R[-0]C[-2],feeds_yest!A:E,5,false)-1,0),3)');

  var cellRangeErrYest = spreadsheet.getRange(3,8,lastReportRow-3,1);
  cellRangeErrYest.setFormula('=round(iferror(R[-0]C[-1]/VLOOKUP(R[-0]C[-7]&"_"&R[-0]C[-6]&"_"&R[-0]C[-5],feeds_yest!A:H,8,false)-1,0),3)');

  var cellRangeCritYest = spreadsheet.getRange(3,10,lastReportRow-3,1);
  cellRangeCritYest.setFormula('=round(iferror(R[-0]C[-1]/VLOOKUP(R[-0]C[-9]&"_"&R[-0]C[-8]&"_"&R[-0]C[-7],feeds_yest!A:J,10,false)-1,0),3)');

}


/**
* Prints the respective data array to the specified sheet.
* @param SHEETNNAME {String} the name of the sheet or tab.
* @param COLUMNHEADERS {Array} a list of columns names.
* @param dataArray {Array} a client center/agency account ID.
* @param sortColumn {Int} the column index to sort by.
* @return void.
*/
function printDataToSheet(SHEETNAME,COLUMNHEADERS,dataArray, sortColumn) {
  var ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  var sheet = ss.getSheetByName(SHEETNAME);
  sheet.getRange('a2:n1000').clearContent();
  sheet.getRange(2,1,1,COLUMNHEADERS[0].length).setValues(COLUMNHEADERS);

  try{
    var dataRange = sheet.getRange(3, 1, dataArray.length, dataArray[0].length);
    dataRange.setValues(dataArray);
    var sortRange = sheet.getRange("A:I");
    sortRange.sort({column: 1, ascending: true});
  } catch(e){Logger.log("IncorrectRangeException: " + e + " . stack : " + e.stack);}

  Logger.log(SHEETNAME + ' list successfully printed to ' + SHEETNAME + ' sheet.');
}

/**
* @param {Array} a twodimensional list of account names and IDs.
* @return {Array} A twodimensional array of feed errors and related information.
*/
function getFeedErrorArray(accountArray) {
  var feedErrorArray = [];

  for(i=0; i < accountArray.length;i++) { // 1. START Iterate through all GMC accounts > return resources array
    var dataFeedsStatuses = ShoppingContent.Datafeedstatuses.list(accountArray[i][1]);
    if(!dataFeedsStatuses.resources)
      continue;
    for(j=0;j < dataFeedsStatuses.resources.length;j++) { // 2. START Resource Iterator: Iterate through all resources aka feeds
      if(dataFeedsStatuses.resources[j].errors) { // 3. Check if errors exist
        for(k=0;k < dataFeedsStatuses.resources[j].errors.length; k++) { // 3.a) START Error Iterator: Iterate through all errors
          var singleFeedInfo = [];
          singleFeedInfo.push(accountArray[i][0],dataFeedsStatuses.resources[j].datafeedId,
                              dataFeedsStatuses.resources[j].errors[k].message.substring(0,100).replace("Insufficient product identifiers", "Insuff Product Ident").replace(',','\,'),
                              dataFeedsStatuses.resources[j].errors[k].code.replace(',','\,'),
                              dataFeedsStatuses.resources[j].errors[k].count);

          var exampleConcatString ='';
          if(dataFeedsStatuses.resources[j].errors[k].examples) {
            var errorExampleMax = dataFeedsStatuses.resources[j].errors[k].examples.length > 3 ? 3 : dataFeedsStatuses.resources[j].errors[k].examples.length;

            for(m=0; m < errorExampleMax; m++) { // 3.a.1 START Concatenate error examples
              var singleExampleValue = dataFeedsStatuses.resources[j].errors[k].examples[m].value ? dataFeedsStatuses.resources[j].errors[k].examples[m].value : 'noValue';
              var singleExampleSuffix = m < errorExampleMax - 1 ? '\n' : '';
              var singleExampleString = dataFeedsStatuses.resources[j].errors[k].examples[m].itemId + ' : ' + singleExampleValue.substring(0,15).replace(',','\,') + singleExampleSuffix;
              exampleConcatString += singleExampleString;
            } // 3.a.1 END Concatenate error examples
          }
          singleFeedInfo.push(exampleConcatString);

          feedErrorArray.push(singleFeedInfo);
        } // 3.a) END Error Iterator
      }
      else { // 3.b)) No Error Default Value
        var singleFeedInfo = [];
        singleFeedInfo.push(accountArray[i][0],dataFeedsStatuses.resources[j].datafeedId, 'no errors', '-', 0, 'no examples');
        feedErrorArray.push(singleFeedInfo);
      } // 3. END Error Iter
    } // 2. END Resource Iter
  } // 1. END Account Iter

  return feedErrorArray;
}


/**
* @param {Array} a twodimensional list of account names and IDs.
* @return {Array} A twodimensional array of feed warnings and related information.
*/
function getFeedWarningArray(accountArray) {
  var feedWarningArray = [];

  for(i=0; i < accountArray.length;i++) { // 1. START Iterate through all GMC accounts > return resources array
    var dataFeedsStatuses = ShoppingContent.Datafeedstatuses.list(accountArray[i][1]);
    if(!dataFeedsStatuses.resources)
      continue;
    for(j=0;j < dataFeedsStatuses.resources.length;j++) { // 2. START Resource Iterator: Iterate through all resources aka feeds
      if(dataFeedsStatuses.resources[j].warnings) { // 3. Check if warnings exist
        for(k=0;k < dataFeedsStatuses.resources[j].warnings.length; k++) { // 3.a) START warning Iterator: Iterate through all warnings
          var singleFeedInfo = [];
          singleFeedInfo.push(accountArray[i][0],dataFeedsStatuses.resources[j].datafeedId,
                              dataFeedsStatuses.resources[j].warnings[k].message.substring(0,100).replace(',','\,'),
                              dataFeedsStatuses.resources[j].warnings[k].code.replace(',','\,'),
                              dataFeedsStatuses.resources[j].warnings[k].count);
          var warningExampleMax = dataFeedsStatuses.resources[j].warnings[k].examples.length > 3 ? 3 : dataFeedsStatuses.resources[j].warnings[k].examples.length;
          var exampleConcatString ='';

          for(m=0; m < warningExampleMax; m++) { // 3.a.1 START Concatenate warning examples
            var singleExampleValue = dataFeedsStatuses.resources[j].warnings[k].examples[m].value ? dataFeedsStatuses.resources[j].warnings[k].examples[m].value : 'noValue';
            var singleExampleSuffix = m < warningExampleMax - 1 ? '\n' : '';
            var singleExampleString = dataFeedsStatuses.resources[j].warnings[k].examples[m].itemId + ': ' + singleExampleValue.substring(0,10).replace(',','\,') + singleExampleSuffix;
            exampleConcatString += singleExampleString;
          } // 3.a.1 END Concatenate warning examples
          singleFeedInfo.push(exampleConcatString);
          feedWarningArray.push(singleFeedInfo);
        } // 3.a) END warning Iterator
      }
      else { // 3.b)) No warning Default Value
        var singleFeedInfo = [];
        singleFeedInfo.push(accountArray[i][0],dataFeedsStatuses.resources[j].datafeedId, 'no warnings', '-', 0, 'no examples');
        feedWarningArray.push(singleFeedInfo);
      } // 3. END warning Iter
    } // 2. END Resource Iter
  } // 1. END Account Iter
  return feedWarningArray;
}


/**
* @param {string} the account ID of the multi-client merchant center
* @return {Array} A twodimensional array of feed data quality issues and related information.
*/
function getManagerFeedDataQualityArray(agencyAccountId, accountArray) {

  var feedDataQualityArray = [];
  if(agencyAccountId == "") return feedDataQualityArray;

  var accountstatuses = ShoppingContent.Accountstatuses.list(agencyAccountId);
  var accountList = acctArraytToObjArray(accountArray);

  for(j=0;j < accountstatuses.resources.length;j++) { // 1. START Resource Iterator: Iterate through all resources aka issues
    if(accountstatuses.resources[j].dataQualityIssues) { // 3. Check if issues exist

      for(k=0;k < accountstatuses.resources[j].dataQualityIssues.length; k++) { // 3.a) START issues Iterator: Iterate through all issues
        var singleIssueInfo = [];
        singleIssueInfo.push(accountList[accountstatuses.resources[j].accountId] + "_" + accountstatuses.resources[j].accountId,
                             accountstatuses.resources[j].dataQualityIssues[k].severity,
                             accountstatuses.resources[j].dataQualityIssues[k].numItems,
                             accountstatuses.resources[j].dataQualityIssues[k].id,
                             accountstatuses.resources[j].dataQualityIssues[k].lastChecked);
        var issueExampleMax = accountstatuses.resources[j].dataQualityIssues[k].exampleItems.length > 3 ? 3 : accountstatuses.resources[j].dataQualityIssues[k].exampleItems.length;
        var exampleConcatString ='';

        for(m=0; m < issueExampleMax; m++) { // 3.a.1 START Concatenate issue examples
          var singleExampleValue = accountstatuses.resources[j].dataQualityIssues[k].exampleItems[m].submittedValue ? accountstatuses.resources[j].dataQualityIssues[k].exampleItems[m].submittedValue : 'noValue';
          var singleExampleSuffix = m < issueExampleMax - 1 ? '\n' : '';
          var singleExampleString = accountstatuses.resources[j].dataQualityIssues[k].exampleItems[m].itemId + ': ' + singleExampleValue.substring(0,10).replace(',','\,') + singleExampleSuffix;
          exampleConcatString += singleExampleString.replace('online:de:DE:','').replace('online:de:AT:','').replace('online:fr:FR:', '').replace('online:nl:NL:', '');;

        } // 3.a.1 END Concatenate issue examples
        singleIssueInfo.push(exampleConcatString);
        feedDataQualityArray.push(singleIssueInfo);
      } // 3.a) END issue Iterator
    }
    else { // 3.b)) No issue Default Value
      var singleIssueInfo = [];
      singleIssueInfo.push(accountstatuses.resources[j].accountId,'none', 'none', 'no id', 'no examples');
      feedDataQualityArray.push(singleIssueInfo);
    } // 3. END issue Iter
  } // 1. END issue Iter

  return feedDataQualityArray;
}


/**
* @param {string} the account ID of the multi-client merchant center
* @return {Array} A twodimensional array of feed data quality issues and related information.
*/
function getSingleFeedDataQualityArray(accountArray) {

  var feedDataQualityArray = [];

  for(var i=0; i<accountArray.length; i++){

    var accountstatuses = ShoppingContent.Accountstatuses.get(accountArray[i][1],accountArray[i][1]);

    if(accountstatuses.dataQualityIssues) { // 3. Check if issues exist
      for(k=0;k < accountstatuses.dataQualityIssues.length; k++) { // 3.a) START issues Iterator: Iterate through all issues
        var singleIssueInfo = [];
        singleIssueInfo.push(accountArray[i][0]+"_"+accountArray[i][1], accountstatuses.dataQualityIssues[k].severity,
                             accountstatuses.dataQualityIssues[k].numItems,
                             accountstatuses.dataQualityIssues[k].id,
                             accountstatuses.dataQualityIssues[k].lastChecked);
        var issueExampleMax = accountstatuses.dataQualityIssues[k].exampleItems.length > 3 ? 3 : accountstatuses.dataQualityIssues[k].exampleItems.length;
        var exampleConcatString ='';

        for(m=0; m < issueExampleMax; m++) { // 3.a.1 START Concatenate issue examples
          var singleExampleValue = accountstatuses.dataQualityIssues[k].exampleItems[m].submittedValue ? accountstatuses.dataQualityIssues[k].exampleItems[m].submittedValue : 'noValue';
          var singleExampleSuffix = m < issueExampleMax - 1 ? '\n' : '';
          var singleExampleString = accountstatuses.dataQualityIssues[k].exampleItems[m].itemId + ': ' + singleExampleValue.substring(0,10).replace(',','\,') + singleExampleSuffix;
          exampleConcatString += singleExampleString.replace('online:de:DE:','').replace('online:de:AT:','').replace('online:fr:FR:','').replace('online:nl:NL:','');
        } // 3.a.1 END Concatenate issue examples
        singleIssueInfo.push(exampleConcatString);
        feedDataQualityArray.push(singleIssueInfo);
      } // 3.a) END issue Iterator
    }
    else { // 3.b)) No issue Default Value
      var singleIssueInfo = [];
      singleIssueInfo.push(accountstatuses.accountId,'none', 'none', 'no id', 'no examples');
      feedDataQualityArray.push(singleIssueInfo);
    } // 3. END issue Iter
  }

  return feedDataQualityArray;
}


function acctArraytToObjArray(accountArray) {
  var accountObjectList = {};
  for (var i = 0; i < accountArray.length; ++i)
    accountObjectList[accountArray[i][1]] = accountArray[i][0];
  return accountObjectList;
}


// EMAIL FUNCTIONS

/**
* @param {array} feedData, two-dimensional array or table of feed info data
* @param {bool} dailyEmailSent,
* @return {Array} A twodimensional array of feed data quality issues and related information.
*/

function checkIfGmcAlertNecessary(feedData,dailyEmailSent){
  var alertNecessary = false;

  Logger.log("dailyEmailSent : " + dailyEmailSent);
  for(var i=1; i<feedData.length; i++) {
    var alertLevel = MIN_ERROR_LEVEL_RELATIVE;
    try{
      var feedDataRow = feedData[i];
      for(var j=0; j<TRIGGER_LEVEL_EXCEPTIONS.length;j++) {
        if(TRIGGER_LEVEL_EXCEPTIONS[j][0] === feedDataRow[0]) alertLevel = TRIGGER_LEVEL_EXCEPTIONS[j][1];
      }

      // Check item count drop to yesterday
      if(dailyEmailSent === false && feedData[0][4] === "Total % to Yest" && feedDataRow[4] < COUNT_CHANGE_LEVEL) alertNecessary = true;

      // Check item error increase to yesterday
      if(dailyEmailSent === false && feedData[0][7] === "Errors % to Yest" && feedDataRow[7] > ERROR_CHANGE_LEVEL && feedDataRow[6] > MIN_ERROR_LEVEL_ABSOLUTE) alertNecessary = true;

      // Check critical issue increase to yesterday
      if(dailyEmailSent === false && feedData[0][9] === "Critical % to Yest" && feedDataRow[9] > ERROR_CHANGE_LEVEL && feedDataRow[8] > MIN_ERROR_LEVEL_ABSOLUTE) alertNecessary = true;

      if(feedData[0][6] === "Item Errors" && (feedDataRow[6] / feedDataRow[3]) > alertLevel) alertNecessary = true;
      if(feedData[0][8] === "Critical per Country (not feed)" && (feedDataRow[8] / feedDataRow[3]) > alertLevel) alertNecessary = true;
      if(DEBUG_MODE == 1) Logger.log("alertNecessary after " + feedDataRow[0] + " : " + alertLevel);

    } catch(e){ Logger.log("FeedDataValueException : " + e + " . " + e.stack); Logger.log(""); }
  }
  Logger.log("alertNecessary : " + alertNecessary);
  return alertNecessary;
}

function sendGmcInfoEmail(GMC_SCRIPT_NAME, printArray, SHEET_URL) {

  var currentdate = new Date();
  var datetime = currentdate.getDate() + "."
  + (currentdate.getMonth()+1)  + "."
  + currentdate.getFullYear() + " , "
  + (currentdate.getUTCHours()+1) + ":"
  + currentdate.getMinutes();

  var subject = GMC_SCRIPT_NAME + ' above MIN_LEVEL for ' + datetime;
  var body = subject;
  var htmlBody = '<html><body><br>Dear AdWords Merchant, <br><br> >>> Please check the <b>GMC ITEM ERRORS (!)</b> per row.<br><br>';
  htmlBody += 'One of your feeds has <b>MORE ERRORS (!)</b> than your MIN_ERROR threshold (levels see below)<br>';

  htmlBody += 'See the Google Sheet for more details : ' + SHEET_URL + '<br><br><br><br>***********<br>';
  htmlBody += 'NOTE: Critical issues are <i>PER country</i>, not per feed. Thus there might be <i>duplication</i> across feeds.<br><br>';
  htmlBody += 'ERROR LEVELS: <br>- minErrorLevel_Relative=' + MIN_ERROR_LEVEL_RELATIVE*100 + '% , minErrorLevel_Absolute=' + MIN_ERROR_LEVEL_ABSOLUTE + ' , errorIncreaseToYesterday= +' + ERROR_CHANGE_LEVEL*100 + '% , countDropToYesterday= ' +  COUNT_CHANGE_LEVEL*100 +'%<br>';
  htmlBody += '- error level exceptions: ' + TRIGGER_LEVEL_EXCEPTIONS + '<br>***********<br>';
  htmlBody += '<table border="1" width="95%" style="border-collapse:collapse;">';

  htmlBody += "<tr>";
  for(var i=0; i<printArray[0].length; i++){
    htmlBody += '<td align="center"><b>'+printArray[0][i]+'</b></td>';
  }
  htmlBody += "<tr>";

  for(var i=1; i<printArray.length; i++) {
    htmlBody += "<tr>";
    for(var j=0; j<printArray[i].length; j++){
      var alertLevel = MIN_ERROR_LEVEL_RELATIVE;
      for(var k=0; k<TRIGGER_LEVEL_EXCEPTIONS.length;k++) {
        if(TRIGGER_LEVEL_EXCEPTIONS[k][0] === printArray[i][0]) alertLevel = TRIGGER_LEVEL_EXCEPTIONS[k][1];
      }

      var style = "default";
      // Check item count drop to yesterday
      if( !isNaN(j) && j === 4 && printArray[i][j] < COUNT_CHANGE_LEVEL) style = "redPercent";
      else if( !isNaN(j) && j === 4 && printArray[i][j] >= COUNT_CHANGE_LEVEL) style = "defaultPercent";

      // Check item error increase to yesterday
      if( !isNaN(j) && j === 7 && printArray[i][j] > ERROR_CHANGE_LEVEL) style = "redPercent";
      else if( !isNaN(j) &&j === 7 && printArray[i][j] <= ERROR_CHANGE_LEVEL) style = "defaultPercent";

      // Check critical issue increase to yesterday
      if( !isNaN(j) && j === 9 && printArray[i][j] > ERROR_CHANGE_LEVEL) style = "redPercent";
      else if( !isNaN(j) && j === 9 && printArray[i][j] <= ERROR_CHANGE_LEVEL) style = "defaultPercent";

      // Check absolute item errors
      if(!isNaN(j) && j === 6 && printArray[i][j] /printArray[i][j-3] > alertLevel && printArray[i][j] > MIN_ERROR_LEVEL_ABSOLUTE) style = "redNumber";

      // Check absolute critical issues
      if(!isNaN(j) && j === 8 && printArray[i][j] /printArray[i][j-5] > alertLevel && printArray[i][j] > MIN_ERROR_LEVEL_ABSOLUTE) style = "redNumber";
      if(i!== 0 && j === 2) style = "hyperlink";

      // Check absolute item errors
      if(j === 10 && printArray[i][j] == "failure") style = "redNumber";

      // Check absolute critical issues
      if(printArray[i][j] === "") continue;

      htmlBody += setStyleByCompare(style, printArray[i][j]);

    }
    htmlBody += "</tr>";
  }

  htmlBody += '<br/ ><br/ >';
  htmlBody += "</table></html></body>";
  var options = { htmlBody : htmlBody };
  for(var i in GMC_RECIPIENTS) {
    MailApp.sendEmail(GMC_RECIPIENTS[i], subject, body, options);
    Logger.log('Email sent to ' + GMC_RECIPIENTS[i]);
  }
}

/*
* @param {string} style
* @param {float} currentValue
* @return {string} htmlOutput
*/
function setStyleByCompare(style, currentValue){
  var htmlOutput;

  if(style === "redPercent") htmlOutput = '<td align="center"><font face="verdana" color="red"><b>' + (parseFloat(currentValue)*100).toFixed(1) +'%</b></font></td>';
  if(style === "defaultPercent") htmlOutput = '<td align="center">' + (parseFloat(currentValue)*100).toFixed(1) +'%</td>';
  if(style === "redNumber") htmlOutput = '<td align="center"><font face="verdana" color="red"><b>' + currentValue +'</b></font></td>';
  if(style === "hyperlink") {htmlOutput =
    '<td><a href="https://merchants.google.com/mc/insights?a=' + currentValue.split("(aid:")[1].split(",")[0].replace(/ /g,"") + '&sa=' + currentValue.split("fid:")[1].replace(")","").replace(/ /g,"") + '">' + currentValue.split("\n")[0] +'</a></td>';
  }
  if(style === "default" || style === "" || style === undefined) htmlOutput = "<td>"+ currentValue +"</td>";

  return htmlOutput;
}


/*
* @param spreadsheet {object}
* @return {integer}
*/
function getLastReportRow(spreadsheet) {
  var column = spreadsheet.getRange('A:A');
  var values = column.getValues(); // get all data in one call
  var ct = 0;
  while ( values[ct] && values[ct][0] != "" ) {
    ct++;
  }
  return (ct+1);
}

/**
* Prints a timestamp to the specified sheet.
* @param SHEETNAME {String} the name of the sheet or tab.
* @return void.
*/
function printTimeStampToSheet(sheetname) {

  var currentdate = new Date;
  var ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  var sheet = ss.getSheetByName(sheetname);
  sheet.getRange('b1:b1').setValue(currentdate);
}

/**
* @param SHEETNAME {String} the name of the sheet or tab.
* @return {bool} sheetHasTodaysDate
*/
function sheetHasTimestampToday(sheetname) {

  var date = new Date;
  var currentDateValue = date.getDate().toString();
  if(DEBUG_MODE == 1) Logger.log("currentDateValue :" + currentDateValue);

  var sheetHasTodaysDate = false;
  var ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  var sheet = ss.getSheetByName(sheetname);
  var sheetDateValue = sheet.getRange('b1:b1').getValue().getDate().toString();
  if(DEBUG_MODE == 1) Logger.log("sheetDateValue : " + sheetDateValue);

  if(currentDateValue == sheetDateValue) sheetHasTodaysDate = true;
  Logger.log("sheetHasTodaysDate : " + sheetHasTodaysDate );

  return sheetHasTodaysDate;
}