Sklik skript pro kontrolu chybových stránek

/** ID of spreadsheet to use. Fill-in either ID or URL. */
var SPREADSHEET_ID = '';
/** URL of spreadsheet to use. Fill-in either ID or URL. */
var SPREADSHEET_URL = '';
/** Fetch only ads in active campaigns? Set to false if you have a large campaigns with almost everything active. */
var ACTIVE_ONLY = true;
/** Number of URL's to check at once. */
var URL_CHECK_BATCH_SIZE = 100;


/**
 * Script entry-point.
 */
function main() {
  var spreadsheet;
  if (SPREADSHEET_ID) {
    spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
  }
  else {
    spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  }
  var sklik = SklikAPI.Connect();
  sklik.loginFromProperties();
  var locale = Session.getActiveUserLocale();
  spreadsheet.setSpreadsheetLocale(locale);
  var accountToCheckSheet = lynt_create_or_get_sheet(spreadsheet, "Účty ke kontrole");
  var accounts = getAccountsToCheck(accountToCheckSheet);
  if (accounts.length > 0) {
    var results = {};
    for (var i = 0; i < accounts.length; i++) {
      var result = checkUrlsForAccount(sklik, accounts[i], spreadsheet);
      results[accounts[i].username] = result;
      Logger.log(result);
    }
    var historySheet = lynt_create_or_get_sheet(spreadsheet, "Přehled");
    spreadsheet.setActiveSheet(historySheet);
    spreadsheet.moveActiveSheet(1);
    writeHistory(historySheet, results);
  }
  else {
    var clientInfo = sklik.Client.get();
    var allAccountList = [["ID", "E-mail"]];
    for (var i = 0; i < clientInfo.foreignAccounts.length; i++) {
      allAccountList.push([
                clientInfo.foreignAccounts[i].userId,
                clientInfo.foreignAccounts[i].username
            ]);
    }
    accountToCheckSheet.clear();
    var range = accountToCheckSheet.getRange(1, 1, allAccountList.length, allAccountList[0].length);
    range.setValues(allAccountList);
    range.offset(0, 0, 1, 2).setFontWeight("bold");
    Logger.log("Na list Účty ke kontrole byl zapsán seznam všech účtů. Odstraňte ty, které si nepřejete kontrolovat, a spustťe skript znovu.");
  }
  sklik.Client.logout();
  lynt_remove_empty_sheets(spreadsheet);
}


/**
 * Gets the today date.
 * @returns {string}
 */
function getToday() {
  var date = new Date();
  date.toDateString();
  return date.toLocaleDateString();
}


/**
 * Gets the list of accounts specified for checking.
 */
function getAccountsToCheck(sheet) {
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
  if (lastRow > 1 && lastCol >= 2) {
    var accounts = [];
    var range = sheet.getRange(1, 1, lastRow, lastCol);
    var data = range.getValues();
    for (var row = 1; row < data.length; row++) {
      accounts.push({
        userId: data[row][0],
        username: data[row][1]
      });
    }
    return accounts;
  }
  else {
    return [];
  }
}


/**
 * Checks URL addresses for all adverts in the given account and creates a new report on separate sheet.
 */
function checkUrlsForAccount(sklik, accountInfo, spreadsheet) {
  if (accountInfo) {
    sklik.setUserId(accountInfo.userId);
  }
  var ads = [];
  if (ACTIVE_ONLY) {
    ads = [];
    var campaignIds = [];
    var campaigns = sklik.Campaigns.list({}, ['name', 'deleted', 'dayBudget', 'exhaustedDayBudget', 'adSelection', 'createDate', 'totalBudget', 'exhaustedTotalBudget', 'exhaustedTotalClicks', 'premise ', 'targetDevices']).campaigns;
    for (var i = 0; i < campaigns.length; i++) {
      if (campaigns[i].status == 'active') {
        campaignIds.push(campaigns[i].id);
      }
    }
    var groupIds = [];
    var batchSize = 100;
    for (var offset = 0; offset < campaignIds.length; offset += batchSize) {
      var groups = sklik.Groups.list({
        campaignIds: campaignIds.slice(offset, offset + batchSize)
      }, ['deleted', 'campaign', 'cpc', 'cpcContext', 'cpt', 'createDate', 'maxUserDailyImpression']).groups;
      for (var i = 0; i < groups.length; i++) {
        if (groups[i].status == 'active') {
          groupIds.push(groups[i].id);
        }
      }
    }
    for (var offset = 0; offset < groupIds.length; offset += batchSize) {
      var adsBatch = sklik.Ads.list({
        groupIds: groupIds.slice(offset, offset + batchSize)
      }, {
        showId: true,
        showCreative1: true,
        showCreative2: false,
        showCreative3: false,
        showClickthruUrl: true,
        showClickthruText: false,
        showStatus: true,
        showStatusId: true,
        showSensitivity: false,
        showSensitivityId: false,
        showDeleted: false,
        showDeletedDate: false,
        showCreateDate: false,
        showPremiseMode: false,
        showPremiseId: false,
        showGroupId: true,
        showGroupName: true,
        showCampaignId: true,
        showCampaignName: true
      }).ads;
      ads = ads.concat(adsBatch);
    }
  }
  else {
    // Get all ads.
    ads = sklik.Ads.list({}, {
      showId: true,
      showCreative1: true,
      showCreative2: false,
      showCreative3: false,
      showClickthruUrl: true,
      showClickthruText: false,
      showStatus: true,
      showStatusId: true,
      showSensitivity: false,
      showSensitivityId: false,
      showDeleted: false,
      showDeletedDate: false,
      showCreateDate: false,
      showPremiseMode: false,
      showPremiseId: false,
      showGroupId: true,
      showGroupName: true,
      showCampaignId: true,
      showCampaignName: true
    }).ads;
  }
  // Iterate all ads and extract all unique URLs.
  var urls = {};
  for (var i = 0; i < ads.length; i++) {
    var ad = ads[i];
    if (ad.status == 'active') {
      var url = stripUtmFromUrl(ad.clickthruUrl);
      if (urls[url] != undefined) {
        urls[url].ads.push(ad);
      }
      else {
        urls[url] = {
          url: url,
          responseCode: null,
          ads: [ad]
        };
      }
    }
  }
  // Sends the extracted URLs to the specified gateway.
  // Prepare the list as a simple array.
  var urlsToCheck = [];
  for (var url in urls) {
    if (!urls.hasOwnProperty(url)) {
      continue;
    }
    urlsToCheck.push(url);
  }
  var bulkSize = URL_CHECK_BATCH_SIZE;
  for (var offset = 0; offset < urlsToCheck.length; offset += bulkSize) {
    var responseList = sklik.connector.urlCheck(urlsToCheck.slice(offset, offset + bulkSize));
    // Process the response and set the HTTP codes for each URL.
    for (var url in responseList) {
      if (!responseList.hasOwnProperty(url)) {
        continue;
      }
      urls[url].responseCode = responseList[url];
    }
  }
  // Convert the URL object (used for deduplication) to array.
  // Additionally, count the invalid URLs.
  var urlArray = [];
  var invalidUrlCount = 0;
  var redirectCount = 0;
  for (var url in urls) {
    if (urls.hasOwnProperty(url)) {
      urlArray.push(urls[url]);
      if (!isNumeric(urls[url].responseCode) || urls[url].responseCode >= 400) {
        invalidUrlCount++;
      }
      else if (urls[url].responseCode >= 300) {
        redirectCount++;
      }
    }
  }
  // Sort the URLs by response code.
  urlArray.sort(function(lhs, rhs) {
    var lnum = isNumeric(lhs.responseCode);
    var rnum = isNumeric(rhs.responseCode);
    if (lnum && rnum) {
      return rhs.responseCode - lhs.responseCode;
    }
    else if (lnum) {
      return -1;
    }
    else if (rnum) {
      return 1;
    }
    else {
      return 0;
    }
  });
  // Write the report.
  var sheet = lynt_create_or_get_sheet(spreadsheet, accountInfo.username);
  writeUrlReport(sheet, urlArray);
  if (accountInfo) {
    sklik.setUserId(null);
  }
  return {
    errors: invalidUrlCount,
    redirects: redirectCount
  };
}


/**
 * Writes the URL checking history.
 */
function writeHistory(historySheet, results) {
  var lastRow = historySheet.getLastRow();
  var lastCol = historySheet.getLastColumn();
  var data;
  if (lastRow > 0 && lastCol > 0) {
    var historyRange = historySheet.getRange(1, 1, lastRow, lastCol);
    data = historyRange.getValues();
  }
  else {
    data = [];
  }
  var today = getToday();
  if (data.length > 0) {
    data[0].splice(1, 0, today);
  }
  else {
    data.push(["Účet [chyby/přesměrování]", today]);
  }
  for (var row = 1; row < data.length; row++) {
    var account = data[row][0];
    if (results[account]) {
      data[row].splice(1, 0, results[account].errors + "/" + results[account].redirects);
      delete results[account];
    }
    else {
      data[row].splice(1, 0, "");
    }
  }
  for (var account in results) {
    if (results.hasOwnProperty(account)) {
      var newRow = new Array(data[0].length);
      newRow[0] = account;
      newRow[1] = results[account].errors + "/" + results[account].redirects;
      for (var i = 2; i < newRow.length; i++) {
        newRow[i] = "";
      }
      data.push(newRow);
    }
  }
  historySheet.clear();
  var outRange = historySheet.getRange(1, 1, data.length, data[0].length);
  outRange.setValues(data);
  outRange.setBorder(true, true, true, true, false, false);
  if (data.length > 1) {
    var colors = new Array(data.length - 1);
    for (var row = 1; row < data.length; row++) {
      colors[row - 1] = new Array(data[0].length - 1);
      for (var col = 1; col < data[0].length; col++) {
        var color = null;
        if (typeof(data[row][col]) == "string") {
          var value = data[row][col].split('/');
          if (value.length == 2) {
            if (parseInt(value[0], 10) > 0) {
              color = '#FF0000';
            }
            else if (parseInt(value[1], 10) > 0) {
              color = '#FFCC00';
            }
            else {
              color = '#80FF80';
            }
          }
        }
        else {
          color = '#FFFFFF';
        }
        colors[row - 1][col - 1] = color;
      }
    }
    outRange.offset(1, 1, data.length - 1, data[0].length - 1).setBackgrounds(colors);
  }
  outRange.offset(0, 0, 1, data[0].length).setFontWeight('bold').setBorder(true, true, true, true, null, null);
  outRange.offset(0, 0, data.length, 1).setFontWeight('bold').setBorder(true, true, true, true, null, null);
  for (var i = 1; i <= data[0].length; i++) {
    historySheet.autoResizeColumn(i);
  }
}


/**
 * Strips UTM parameters from the given URL. This function also removes the fragment part of the URL.
 */
function stripUtmFromUrl(url) {
  var match, search = /([^&=]+)=?([^&]*)/g,
    query;
  var hashPos = url.indexOf('#');
  if (hashPos === -1) {
    hashPos = url.length;
  }
  var queryPos = url.indexOf('?');
  if (queryPos === -1) {
    return url.substr(0, hashPos);
  }
  query = url.substring(queryPos + 1, hashPos);
  var newQueryParts = [];
  while (match = search.exec(query)) {
    if (match[1].substr(0, 4) != 'utm_') {
      newQueryParts.push(match[0]);
    }
  }
  if (newQueryParts.length > 0) {
    var newQuery = newQueryParts.join('&');
    return url.substr(0, queryPos + 1) + newQuery;
  }
  else {
    return url.substr(0, queryPos);
  }
}


/**
 * Checks if the given string is a number.
 * @param n String possibly containing a number.
 * @returns `true` if the given string contains a number (and nothing else than number).
 */
function isNumeric(n) {
  return !isNaN(parseFloat(n)) && isFinite(n);
}


/**
 * Writes URL report.
 */
function writeUrlReport(sheet, urls) {
  sheet.clear();
  var lastEmptyRow = sheet.getMaxRows();
  if (lastEmptyRow > 2) {
    sheet.deleteRows(3, lastEmptyRow - 2);
  }
  var overviewLen = createUrlOverview(sheet, 1, urls);
  var offset = overviewLen + 2;
  for (var i = 0; i < urls.length; i++) {
    if (urls[i].responseCode == 200) {
      continue;
    }
    var detailLen = createUrlAdsDetail(sheet, offset, urls[i]);
    offset += detailLen + 2;
  }
  for (var i = 1; i <= 4; i++) {
    sheet.autoResizeColumn(i);
  }
}


/**
 * Creates URL overview table.
 */
function createUrlOverview(sheet, offset, urls) {
  var rows = new Array(urls.length + 2);
  rows[0] = ["Přehled odkazovaných URL z inzerátů", ""];
  rows[1] = ["URL adresa", "Stavový kód"];
  var colors = [];
  var rowN = 2;
  var code;
  for (var i = 0; i < urls.length; i++) {
    code = urls[i].responseCode;
    rows[rowN] = [urls[i].url, formatResponseCode(code)];
    colors.push([getResponseColor(code)]);
    rowN++;
  }
  sheet.insertRows(offset, rows.length + 2);
  var range = sheet.getRange(offset, 1, rows.length, rows[0].length);
  range.setValues(rows);
  range.setBorder(true, true, true, true, false, false);
  if (urls.length > 0) {
    range.offset(2, 1, urls.length, 1).setBackgrounds(colors);
  }
  range.offset(0, 0, 2, 2).setFontWeight('bold').setBorder(true, true, true, true, false, true);
  return rows.length;
}
var httpStatusCodes = {
  "100": "100 Continue",
  "101": "101 Switching Protocols",
  "200": "200 OK",
  "201": "201 Created",
  "202": "202 Accepted",
  "203": "203 Non-Authoritative Information",
  "204": "204 No Content",
  "205": "205 Reset Content",
  "206": "206 Partial Content",
  "300": "300 Multiple Choices",
  "301": "301 Moved Permanently",
  "302": "302 Found",
  "303": "303 See Other",
  "304": "304 Not Modified",
  "305": "305 Use Proxy",
  "307": "307 Temporary Redirect",
  "400": "400 Bad Request",
  "401": "401 Unauthorized",
  "402": "402 Payment Required",
  "403": "403 Forbidden",
  "404": "404 Not Found",
  "405": "405 Method Not Allowed",
  "406": "406 Not Acceptable",
  "407": "407 Proxy Authentication Required",
  "408": "408 Request Timeout",
  "409": "409 Conflict",
  "410": "410 Gone",
  "411": "411 Length Required",
  "412": "412 Precondition Failed",
  "413": "413 Request Entity Too Large",
  "414": "414 Request-URI Too Long",
  "415": "415 Unsupported Media Type",
  "416": "416 Requested Range Not Satisfiable",
  "417": "417 Expectation Failed",
  "500": "500 Internal Server Error",
  "501": "501 Not Implemented",
  "502": "502 Bad Gateway",
  "503": "503 Service Unavailable",
  "504": "504 Gateway Timeout",
  "505": "505 HTTP Version Not Supported"
};


/**
 * Gets the response code with human-friendly description (if available).
 */
function formatResponseCode(code) {
  if (httpStatusCodes[code]) {
    return httpStatusCodes[code];
  }
  else {
    return code;
  }
}


/**
 * Gets the color for coloring the response code.
 */
function getResponseColor(code) {
  var numeric = isNumeric(code);
  var codeNum = parseInt(code, 10);
  if (numeric && codeNum < 300) {
    return '#80FF80';
  }
  else if (numeric && codeNum >= 300 && codeNum < 400) {
    return '#FFCC00';
  }
  else {
    return '#FF0000';
  }
}


/**
 * Creates ad details for the given URL.
 */
function createUrlAdsDetail(sheet, offset, url) {
  var rows = new Array(url.ads.length + 2);
  rows[0] = [url.url, formatResponseCode(url.responseCode), "", ""];
  rows[1] = ["Kampaň", "Sestava", "Inzerát", "URL"];
  var rowN = 2;
  for (var i = 0; i < url.ads.length; i++) {
    var ad = url.ads[i];
    rows[rowN] = [
            ad.campaign.name,
            ad.group.name,
            ad.creative1,
            formatAdvertUrl(ad)
        ];
    rowN++;
  }
  sheet.insertRows(offset, rows.length + 2);
  var range = sheet.getRange(offset, 1, rows.length, rows[0].length);
  range.setBorder(true, true, true, true, false, false);
  range.setValues(rows);
  range.offset(0, 0, 1, 2).setFontSize(14);
  range.offset(0, 1, 1, 1).setBackground(getResponseColor(url.responseCode));
  range.offset(0, 0, 2, 4).setFontWeight('bold').setBorder(true, true, true, true, false, true);
  return rows.length;
}


/**
 * Gets URL for editing the given advert.
 */
function formatAdvertUrl(advert) {
  return 'https://www.sklik.cz/detail-inzeratu?id=' + advert.id + '&referrerPage=group&returnUrl=%2Fcampaigns%2F' + advert.campaign.id + '%2Fgroups%2F' + advert.group.id + '%2Fadverts';
}


/**
 * Gets the day specified number of days before today.
 *
 * This function correctly transforms between normal time and DST.
 */
function lynt_get_date(daysBack) {
  var past = new Date();
  past.setHours(0);
  past.setMinutes(0);
  past.setSeconds(0);
  past.setMilliseconds(0);
  var originalOffset = past.getTimezoneOffset();
  var pastTime = past.getTime() - daysBack * 86400 * 1000;
  past.setTime(pastTime);
  var newOffset = past.getTimezoneOffset();
  var offsetDiff = newOffset - originalOffset;
  if (offsetDiff != 0) {
    pastTime += offsetDiff * 60 * 1000;
    past.setTime(pastTime);
  }
  return past;
}


/**
 * Gets the last sunday.
 */
function lynt_get_suday() {
  var dayOffset = new Date().getDay();
  return lynt_get_date(dayOffset);
}


/**
 * Gets the date specified number of days before the last sunday.
 */
function lynt_get_xdays_back_from_sunday(pocet_dni) {
  var dayOffset = new Date().getDay();
  return lynt_get_date(dayOffset + pocet_dni);
}


/**
 * Adds specified number of milliseconds to the specified date.
 *
 * This function correctly transforms between normal time and DST.
 */
function lynt_date_add(date, miliseconds) {
  var newTime = date.getTime() + miliseconds;
  var newDate = new Date(newTime);
  var offsetDiff = newDate.getTimezoneOffset() - date.getTimezoneOffset();
  if (offsetDiff != 0) {
    newTime += offsetDiff * 60 * 1000;
    newDate.setTime(newTime);
  }
  return newDate;
}


var SortOrder;
(function(SortOrder) {
  SortOrder[SortOrder["ASC"] = 0] = "ASC";
  SortOrder[SortOrder["DESC"] = 1] = "DESC";
})(SortOrder || (SortOrder = {}));


/**
 * Creates or opens specified sheet in spreadsheet.
 */
function lynt_create_or_get_sheet(spreadsheet, name, position) {
  var sheet = spreadsheet.getSheetByName(name);
  if (sheet == null) {
    sheet = spreadsheet.insertSheet(name, position);
  }
  return sheet;
}

function lynt_remove_empty_sheets(spreadsheet) {
  var sheets = spreadsheet.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].getLastRow() == 0) {
      spreadsheet.deleteSheet(sheets[i]);
    }
  }
}

function lynt_highlight_range(range, higlightingRules) {
  var rows = range.getNumRows();
  var cols = range.getNumColumns();
  var values = range.getValues();
  var backgrounds = new Array(rows);
  var value, color;
  for (var r = 0; r < rows; r++) {
    backgrounds[r] = new Array(cols);
    for (var c = 0; c < cols; c++) {
      color = '#FFFFFF';
      value = values[r][c];
      if (typeof(value) == 'number') {
        for (var ri = 1; ri < higlightingRules.length; ri++) {
          if (value <= higlightingRules[ri].value) {
            color = lynt_color_to_hex(lynt_get_color_from_range(value, higlightingRules[ri - 1], higlightingRules[ri]));
            break;
          }
        }
      }
      backgrounds[r][c] = color;
    }
  }
  range.setBackgrounds(backgrounds);
}


/**
 * Computes color from the given range.
 */
function lynt_get_color_from_range(value, upper, lower) {
  var ratio = (value - lower.value) / (upper.value - lower.value);
  return {
    r: lower.color.r + ratio * (upper.color.r - lower.color.r),
    g: lower.color.g + ratio * (upper.color.g - lower.color.g),
    b: lower.color.b + ratio * (upper.color.b - lower.color.b),
  };
}

function lynt_color_to_hex(color) {
  var rHex = Math.round(color.r).toString(16);
  var gHex = Math.round(color.g).toString(16);
  var bHex = Math.round(color.b).toString(16);
  if (rHex.length < 2) {
    rHex = '0' + rHex;
  }
  if (gHex.length < 2) {
    gHex = '0' + gHex;
  }
  if (bHex.length < 2) {
    bHex = '0' + bHex;
  }
  return '#' + rHex + gHex + bHex;
}


function lynt_store_save(dataFolderName, fileName, object) {
  var objectJson = JSON.stringify(object);
  var dataFolder = lynt_get_data_folder(dataFolderName);
  var files = dataFolder.getFilesByName(fileName);
  var dataFile;
  if (files.hasNext()) {
    dataFile = files.next();
    dataFile.setContent(objectJson);
  }
  else {
    dataFolder.createFile(fileName, objectJson, "application/json");
  }
  return true;
}

function lynt_store_load(dataFolderName, fileName) {
  var dataFolder = lynt_get_data_folder(dataFolderName);
  var files = dataFolder.getFilesByName(fileName);
  var dataFile;
  if (files.hasNext()) {
    dataFile = files.next();
  }
  if (dataFile) {
    var objectJson = dataFile.getBlob().getDataAsString("UTF-8");
    return JSON.parse(objectJson);
  }
  else {
    return null;
  }
}

function lynt_get_data_folder(dataFolderName) {
  var root = DriveApp.getRootFolder();
  var folders = root.getFoldersByName(dataFolderName);
  var dataFolder;
  if (folders.hasNext()) {
    dataFolder = folders.next();
  }
  else {
    dataFolder = root.createFolder(dataFolderName);
  }
  return dataFolder;
}