Skript pro sledování nových a odstraněných produktů v eshopu

/* ====== SETTINGS ======= */
var SpreadSheetUrl = "";

var DefaultFeedUrl = '';
var DefaultFeedType = 'merchant'; //zbozi|heureka|merchant
var DefaultNotifyEmail = '';

var SettingListName = "Nastaveni";
var ActiveListName = "Aktivni";
var DeletedListName = "Smazane";
/* ====== SETTINGS ======= */




/* ====== VARIABLES ======= */
var now = new Date();
var SettingList;
var ActiveList;
var DeletedList;
var FeedUrl;
var FeedType;
var NotifyEmail;
/* ====== VARIABLES ======= */


function lynt_arrayFlatten(arr, index) {
  var newArr = new Array();
  for (var i = 0; i < arr.length; i++) {
    newArr[i] = arr[i][index];
  }
  return newArr;
}

function lynt_parseXmlZbozi(feed) {

  var feedData = {
    urls: new Array(),
    names: new Array(),
    prices: new Array(),
    count: 0
  };

  var xml = UrlFetchApp.fetch(feed).getContentText();
  var document = XmlService.parse(xml);
  var root = document.getRootElement();
  var ns = root.getNamespace();
  var entries = document.getRootElement().getChildren();

  var i;
  for (i = 0; i < entries.length; i++) {
    feedData.urls[i] = entries[i].getChildText('URL', ns);


    if (entries[i].getChildText('PRICE_VAT', ns)) {
      feedData.prices[i] = entries[i].getChildText('PRICE_VAT', ns);
    } else {
      feedData.prices[i] = 0;
    }

    if (entries[i].getChildText('PRODUCTNAME', ns)) {
      feedData.names[i] = entries[i].getChildText('PRODUCTNAME', ns);
    } else if (entries[i].getChildText('PRODUCT', ns)) {
      feedData.names[i] = entries[i].getChildText('PRODUCT', ns);
    } else {
      feedData.names[i] = 'UNKNOWN';
    }
  }

  feedData.count = i + 1;

  return feedData;
}

function lynt_parseXmlHeureka(feed) {

  var feedData = {
    urls: new Array(),
    names: new Array(),
    prices: new Array(),
    count: 0
  };

  var xml = UrlFetchApp.fetch(feed).getContentText();
  var document = XmlService.parse(xml);
  var root = document.getRootElement();
  var entries = document.getRootElement().getChildren();

  var i;
  for (i = 0; i < entries.length; i++) {
    feedData.urls[i] = entries[i].getChildText('URL');

    if (entries[i].getChildText('PRICE_VAT', ns)) {
      feedData.prices[i] = entries[i].getChildText('PRICE_VAT', ns);
    } else {
      feedData.prices[i] = 0;
    }


    feedData.names[i] = entries[i].getChildText('PRODUCTNAME');
  }

  feedData.count = i + 1;

  return feedData;
}


function lynt_parseXmlMerchant(feed) {

  var feedData = {
    urls: new Array(),
    names: new Array(),
    prices: new Array(),
    category: new Array(),
    count: 0
  };

  var xml = UrlFetchApp.fetch(feed).getContentText();
  var document = XmlService.parse(xml);
  var root = document.getRootElement();
  var ns = root.getNamespace('g');
  var entries = document.getRootElement().getChild('channel').getChildren('item');

  var i;

  for (i = 0; i < entries.length; i++) {
    feedData.urls[i] = entries[i].getChildText('link');
    feedData.names[i] = entries[i].getChildText('title');
    if (entries[i].getChildText('price', ns)) {
      var price = entries[i].getChildText('price', ns).replace(' Kč', '').replace(/\s/g, '');
      price = price * 1;
      feedData.prices[i] = price;
    } else {
      feedData.prices[i] = 0;
    }

  }

  for (i = 0; i < entries.length; i++) {
    feedData.urls[i] = entries[i].getChildText('link');
    feedData.names[i] = entries[i].getChildText('title');
    if (entries[i].getChildText('custom_label_0', ns)) {
      feedData.category[i] = entries[i].getChildText('custom_label_0', ns);
    } else {
      feedData.category[i] = '';
    }
  }
  feedData.count = i + 1;
  return feedData;
}

function setup() {
  //set up sheets and global variables
  var reportSpreadsheet = SpreadsheetApp.openByUrl(SpreadSheetUrl);
  SettingList = reportSpreadsheet.getSheetByName(SettingListName);
  if (SettingList == null) {
    SettingList = reportSpreadsheet.insertSheet(SettingListName, 0);
    SettingList.setColumnWidth(2, 150);
    SettingList.setColumnWidth(3, 400);
    var range = SettingList.getRange(2, 2, 4, 2).setBorder(true, true, true, true, true, true);
    range.setValues([
      ['URL feedu', DefaultFeedUrl],
      ['Typ feedu', DefaultFeedType],
      ['Notifikaèní e-mail', DefaultNotifyEmail],
      ['Poslední zpracování', '']
    ]);
  }
  FeedUrl = SettingList.getRange(2, 3).getValue();
  FeedType = SettingList.getRange(3, 3).getValue();
  NotifyEmail = SettingList.getRange(4, 3).getValue();
  ActiveList = reportSpreadsheet.getSheetByName(ActiveListName);
  if (ActiveList == null) {
    ActiveList = reportSpreadsheet.insertSheet(ActiveListName, 1);
    ActiveList.setColumnWidth(1, 400);
    ActiveList.setColumnWidth(2, 300);
    ActiveList.setColumnWidth(3, 150);
    if (FeedType == 'merchant') {
      ActiveList.getRange(1, 1, 1, 5).setValues([
        ['URL produktu', 'Název produktu', 'Cena', 'Kategorie', 'Datum přidání']
      ]);
    } else {
      ActiveList.getRange(1, 1, 1, 4).setValues([
        ['URL produktu', 'Název produktu', 'Cena', 'Datum přidání']
      ]);
    }
  }
  DeletedList = reportSpreadsheet.getSheetByName(DeletedListName);
  if (DeletedList == null) {
    DeletedList = reportSpreadsheet.insertSheet(DeletedListName, 2);
    DeletedList.setColumnWidth(1, 400);
    DeletedList.setColumnWidth(2, 300);
    DeletedList.setColumnWidth(3, 150);
    DeletedList.setColumnWidth(4, 150);
    DeletedList.getRange(1, 1, 1, 4).setValues([
      ['URL produktu', 'Název produktu', 'Datum přidání', 'Datum odebrání']
    ]);
  }
  //Deleting 'list 1' sheet
  var list1_sheet = reportSpreadsheet.getSheetByName("List 1");
  if (list1_sheet != null) {
    reportSpreadsheet.deleteSheet(list1_sheet);
  }
  return reportSpreadsheet;
}

function main() {
  var feedData;
  var reportSpreadsheet = setup();
  switch (FeedType) {
    case 'zbozi':
      feedData = lynt_parseXmlZbozi(FeedUrl);
      break;
    case 'heureka':
      feedData = lynt_parseXmlHeureka(FeedUrl);
      break;
    case 'merchant':
      feedData = lynt_parseXmlMerchant(FeedUrl);
      break;
  }
  //Logger.log("XML feed parsed");
  if (feedData) { //get number of rows
    var numRows = reportSpreadsheet.getSheetByName(ActiveListName).getLastRow();
    if (numRows > 1) {
      //get products URLs
      aktivni = lynt_arrayFlatten(reportSpreadsheet.getSheetByName(ActiveListName).getRange(2, 1, numRows, 1).getValues(), 0);

      //delete rows with past produducts
      for (var i = aktivni.length; i > 1; i--) {
        if (aktivni[i - 2] && feedData.urls.indexOf(aktivni[i - 2]) === -1) {
          var row = reportSpreadsheet.getSheetByName(ActiveListName).getRange(i, 1, 1, 3).getValues();
          row[0][3] = now;
          reportSpreadsheet.getSheetByName(DeletedListName).appendRow(row[0]);
          reportSpreadsheet.getSheetByName(ActiveListName).deleteRow(i);
        }
      }
    }
    //Logger.log("outdated products removed");
    //add rows with new products
    var toAdd = new Array();
    var numLines = 0;
    var mailMessage = "";
    for (var i = 0; i < feedData.urls.length; i++) {
      if (feedData.urls[i] && (numRows < 2 || aktivni.indexOf(feedData.urls[i]) === -1)) {

        if (FeedType == 'merchant') {
          toAdd.push([feedData.urls[i], feedData.names[i], feedData.prices[i], feedData.category[i], now]);
          mailMessage += feedData.names[i] + " (" + feedData.urls[i] + ")\n";
          numLines++;
        } else {
          toAdd.push([feedData.urls[i], feedData.names[i], feedData.prices[i], now]);
          numLines++;
          mailMessage += feedData.names[i] + " (" + feedData.urls[i] + ")\n";
        }
      }
    }
    if (numLines) {
      mailMessage = "V XML feedu " + FeedUrl + " se objevily nové záznamy (" + numLines + "): \n\n" + mailMessage;
      numRows = reportSpreadsheet.getSheetByName(ActiveListName).getLastRow();
      //Logger.log(mailMessage);
      var matches = FeedUrl.match(/^https?\:\/\/([^\/?#]+)(?:[\/?#]|$)/i);
      var domain = matches && matches[1];
      MailApp.sendEmail(NotifyEmail, 'Změna v XML feedu ' + domain, mailMessage);
      reportSpreadsheet.getSheetByName(ActiveListName).getRange(numRows + 1, 1, numLines, 5).setValues(toAdd);
      //Logger.log("new products added");
    }

    SettingList.getRange(5, 3).setValue(now);
  }
}