SMXL MILAN 2019 PRESENTATION CODE BUNDLE

gistfile1.txt

//this is where you put in your api keys!
//serpApiKey from serpapi.com
//proxyCrawlToken & proxyCrawlJavascriptToken from proxycrawl.com

var GLOBAL_VARIABLES = {
  
  serpApiKey : "",
  proxyCrawlToken: "",
  proxyCrawlJavascriptToken: ""
  
}

/*
/***************************
/BEGIN PRESENTATION SCRIPTS
@DSOTTIMANO ON TWITTER

LICENSE: DO WHATEVER YOU WANT ;)
****************************
*/


function CONCAT_STRING(string1,string2) {
  return string1 + string2 
}


//functions to parse URLs adapted for easy usage in apps script
//past the code below in the tools > script editor of any google sheet 
//call the PARSE_URI() formula directly from a cell


// PARSE_URI 1.2.2
// (c) Steven Levithan <stevenlevithan.com>
// MIT License
//adapted for apps script by @dsottimano

/**
* Returns URL parts 
* @param {"https://developer.mozilla.org/en-US/docs/Web/"} url the url you want to parse
* @param {"host"} part the url part you want to return. "source","protocol","authority","userInfo","user","password","host","port","relative","path","directory","file","query","anchor" are acceptable values
* @customfunction
*/
function PARSE_URI(url,part) {  
  try {
    if(url.map) {
      return url.map(function(u) {return PARSE_URI(u,part)})
      
    } else {
      PARSE_URI.options = {
        strictMode: false,
        key: ["source","protocol","authority","userInfo","user","password","host","port","relative","path","directory","file","query","anchor"],
        q:   {
          name:   "queryKey",
          parser: /(?:^|&)([^&=]*)=?([^&]*)/g
        },
        parser: {
          strict: /^(?:([^:\/?#]+):)?(?:\/\/((?:(([^:@]*)(?::([^:@]*))?)?@)?([^:\/?#]*)(?::(\d*))?))?((((?:[^?#\/]*\/)*)([^?#]*))(?:\?([^#]*))?(?:#(.*))?)/,
          loose:  /^(?:(?![^:@]+:[^:@\/]*@)([^:\/?#.]+):)?(?:\/\/)?((?:(([^:@]*)(?::([^:@]*))?)?@)?([^:\/?#]*)(?::(\d*))?)(((\/(?:[^?#](?![^?#\/]*\.[^?#\/.]+(?:[?#]|$)))*\/?)?([^?#\/]*))(?:\?([^#]*))?(?:#(.*))?)/
        }
      };
      
      if(!IS_VALID_URL(url)) return "Please enter a valid URL";
      var	o   = PARSE_URI.options,
          m   = o.parser[o.strictMode ? "strict" : "loose"].exec(url),
          uri = {},
          i   = 14;
      
      while (i--) uri[o.key[i]] = m[i] || "";
      
      uri[o.q.name] = {};
      uri[o.key[12]].replace(o.q.parser, function ($0, $1, $2) {
        if ($1) uri[o.q.name][$1] = $2;
      });
      if (!uri[part]) return ("No part defined")
      
      if(part === "host") {
      uri = uri[part].split(".")
      if (uri.length > 2) return uri[uri.length-2]  + "." + uri[uri.length-1]
      return uri.join('.')
      }
     
      if(part) return uri[part]
      return uri
        }
  }catch(e) {
    return e
  }
};

//isValidURl credit: https://stackoverflow.com/a/49849482/2121455

function IS_VALID_URL(str) {
  if (typeof str != 'string' || !str) throw "Please enter a valid string"
  try { 
    if (str.map) {
      return str.map(function(s) {return IS_VALID_UR(s)});
    } else {
      var res = str.match(/(http(s)?:\/\/.)?(www\.)?[-a-zA-Z0-9@:%._\+~#=]{2,256}\.[a-z]{2,6}\b([-a-zA-Z0-9@:%_\+.~#?&//=]*)/g);
      if (res) return true
      return false
    }
  } catch(e) {
    return e
  }
};



function PROXYCRAWL_CRAWL(crawlerType,url,crawlerName, country,device,userAgent,waitTime) {
  //if (!crawlerName) return "You must provide the crawler name";
  
  var requestUrl =  "https://api.proxycrawl.com/?token="+ GLOBAL_VARIABLES.proxyCrawlJavascriptToken  +"&url="+encodeURIComponent(url)+ "&country=" + country + "&page_wait=" + waitTime
  Logger.log(requestUrl) 
  return
  var chrome = 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36'
  var googlebot = 'Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)'
  
  
  //  ou must append &callback=true
  //You must append &crawler=YourCrawlerName
  
  var url = "https://api.proxycrawl.com/?token=WLGBVGrIexSB1ET98YjY8Q&url=https://davidsottimano.com/"
  var res = UrlFetchApp.fetch(url).getContentText()
  return res[0]
  
}


/**
* Returns Google organic links from serpapi.com data
*
* @param {"cars"} query REQUIRED The search term you want to query for
* @param {"en"} language OPTIONAL The language you want to search in. Default is "en" for English
* @param {"us"} country OPTIONAL The country you want to search in. Default is "us" for United States
* @param {10} num OPTIONAL The number of results you want to return. Acceptable values are 10, 20, 50, 100. Default is 10
* @param {"desktop"} device OPTIONAL The device you want results for. Acceptable values are "mobile", "tablet" or "desktop" Default is desktop
* @return number
* @customfunction
*/

function GOOGLE_SEARCH(query,language,country,num,device) {
  if(Array.isArray(query) || query == "") return "Please enter a single query string"
  
  
  try {
    
    var location = (location || "585069adee19ad271e9b7e61")
    var language = language || "en"
    var  country = country || "us"
    var device = (device || "desktop")
    var num = num || 10
    var res
    var url
    var resultArray = []
    
    
    url = "https://serpapi.com/search?q="+query+"&hl="+language+"&gl="+country+"&device="+device+"&api_key="+GLOBAL_VARIABLES.serpApiKey+"&num="+num 
    request = UrlFetchApp.fetch(url, {muteHttpExceptions:true}).getContentText()
    
    res = JSON.parse(request)
    
    if(res.hasOwnProperty("error")) throw res.error
    
    if (!res.hasOwnProperty["organic_results"]) return "No results found"
    
    res["organic_results"].forEach(function(item) {
      resultArray.push(item.link)
      
    })
    if (resultArray.length > 0) return resultArray
    return "No results found";
  }
  catch(e) {
    return e 
  }
}


/**
* Returns Google featured snippet data from serpapi.com
*
* @param {"cars"} query REQUIRED The search term you want to query for
* @param {"en"} language OPTIONAL The language you want to search in. Default is "en" for English
* @param {"us"} country OPTIONAL The country you want to search in. Default is "us" for United States
* @param {10} num OPTIONAL The number of results you want to return. Acceptable values are 10, 20, 50, 100. Default is 10
* @param {"desktop"} device OPTIONAL The device you want results for. Acceptable values are "mobile", "tablet" or "desktop" Default is desktop
* @return number
* @customfunction
*/

function GOOGLE_FEATURED_SNIPPET(query,language,country,num,device) {
  if(Array.isArray(query) || query == "") return "Please enter a single query string"
  
  
  try {
    
    var location = (location || "585069adee19ad271e9b7e61")
    var language = language || "en"
    var  country = country || "us"
    var device = (device || "desktop")
    var num = num || 10
    var res
    var url
    var resultArray = []
    
    
    url = "https://serpapi.com/search?q="+query+"&hl="+language+"&gl="+country+"&device="+device+"&api_key="+GLOBAL_VARIABLES.serpApiKey+"&num="+num 
    request = UrlFetchApp.fetch(url, {muteHttpExceptions:true}).getContentText()
    
    res = JSON.parse(request)
    
    if(res.hasOwnProperty("error")) throw res.error
    
    if (!res.hasOwnProperty("answer_box")) {
      return "No featured snippet found for this query"
    } else {
      resultArray.push(["Title:", res['answer_box'].title])
      resultArray.push(["Link:", res['answer_box'].link])
      resultArray.push(["Snippet:", res['answer_box'].snippet])
      
    }
    
    return resultArray
    
  }
  catch(e) {
    return e 
  }
}



//Script adapted below for use in apps scripts for sheets by @dsottimano David Sottimano October 2019

/*@author Rob W, created on 16-17 September 2011, on request for Stackoverflow (http://stackoverflow.com/q/7085454/938089)
* Modified on 17 juli 2012, fixed IE bug by replacing [,] with [null]
* This script will calculate words. For the simplicity and efficiency,
* there's only one loop through a block of text.
* A 100% accuracy requires much more computing power, which is usually unnecessary
**/


/**
* Returns a table of ngrams and their importance
*
* @param {"cars are the best"} textArray REQUIRED The corpus you want statistics from
* @param {"3"} numberOccurances OPTIONAL Show results with at least X occurrences. Default is 2
* @param {"4"} numberOfWords OPTIONAL Show statistics for one to X words. Default is 5
* @param {"false"} removeStopWords OPTIONAL true or false. False by default
* @customfunction
*/

function KEYWORD_FREQUENCY_TABLE(textArray,numberOccurances,numberOfWords,removeStopWords) {
  var text = ''
  try {
    //var textArray = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test').getRange(1, 1, 30).getValues()
    if (textArray.map) textArray = textArray.flat(Infinity).join(' ').toString()
    removeStopWords ? text = REMOVE_STOPWORDS(textArray) : text = textArray
    text = textArray
    var atLeast = numberOccurances || 2; // Show results with at least .. occurrences
    var numWords = numberOfWords || 5; // Show statistics for one to .. words
    var ignoreCase = true; // Case-sensitivity
    var REallowedChars = /[^a-zA-Z'\-]+/g;
    // RE pattern to select valid characters. Invalid characters are replaced with a whitespace
    
    var i, j, k, textlen, len, s;
    // Prepare key hash
    var keys = [null]; //"keys[0] = null", a word boundary with length zero is empty
    var results = [];
    numWords++; //for human logic, we start counting at 1 instead of 0
    for (i = 1; i <= numWords; i++) {
      keys.push({});
    }
    
    // Remove all irrelevant characters
    text = text.replace(REallowedChars, " ").replace(/^\s+/, "").replace(/\s+$/, "");
    Logger.log(text)
    // Create a hash
    if (ignoreCase) text = text.toLowerCase();
    text = text.split(/\s+/);
    
    for (i = 0, textlen = text.length; i < textlen; i++) {
      s = text[i];
      keys[1][s] = (keys[1][s] || 0) + 1;
      for (j = 2; j <= numWords; j++) {
        if (i + j <= textlen) {
          s += " " + text[i + j - 1];
          keys[j][s] = (keys[j][s] || 0) + 1;
        } else break;
      }
    }
    
    // Prepares results for advanced analysis
    for (var k = 1; k <= numWords; k++) {
      results[k] = [];
      var key = keys[k];
      for (var i in key) {
        if (key[i] >= atLeast) results[k].push({
          "word": i,
          "count": key[i]
        });
      }
    }
    
    // Result parsing
    var outputHTML = []; 
    
    var f_sortAscending = function (x, y) {
      return y.count - x.count;
    };
    for (k = 1; k < numWords; k++) {
      results[k].sort(f_sortAscending); //sorts results
      
      // Customize your output. For example:
      var words = results[k];
      if (words.length) {
        if (k>1) outputHTML.push([,,,])
        outputHTML.push([k + ' word' + (k == 1 ? "" : "s"),"Count","% Importance / Density"]);
        outputHTML.push([,,,])
      }
      for (i = 0, len = words.length; i < len; i++) {
        outputHTML.push([words[i].word, words[i].count ,((words[i].count / text.length) * 100).toFixed(2)]);
        
      }
    }
    
    if (outputHTML.length < 1) return "Sorry, not enough data"
    return outputHTML
    
    
  } catch(e) {
    Logger.log(e)
    return e
  }
  
  
}

//https://stackoverflow.com/a/57153507/2121455

function REMOVE_STOPWORDS(str) {
  var stopwords = ['i','me','my','myself','we','our','ours','ourselves','you','your','yours','yourself','yourselves','he','him','his','himself','she','her','hers','herself','it','its','itself','they','them','their','theirs','themselves','what','which','who','whom','this','that','these','those','am','is','are','was','were','be','been','being','have','has','had','having','do','does','did','doing','a','an','the','and','but','if','or','because','as','until','while','of','at','by','for','with','about','against','between','into','through','during','before','after','above','below','to','from','up','down','in','out','on','off','over','under','again','further','then','once','here','there','when','where','why','how','all','any','both','each','few','more','most','other','some','such','no','nor','not','only','own','same','so','than','too','very','s','t','can','will','just','don','should','now']
  var str = str.toString()
  res = []
  words = str.split(' ')
  for(i=0;i<words.length;i++) {
    if(!stopwords.includes(words[i])) {
      res.push(words[i])
    }
  }
  return(res.join(" "))
}


/**
* Substitute multiple words or characters at once
* @param {"@,hello,test"} params REQUIRED The words you want to remove separated by commas ","
* @param {"a1"} text REQUIRED The string you want to subsitute from
* @param {"-"} replacement OPTIONAL The replacement string. Default is blank space
* @return Returns combination of protocols + www subdomains
* @customfunction
*/
function SUBSTITUTE_ALL(params, text,replacement) {
    
  try {
    if (text.map) {
      return text.map(function(t) { return SUBSTITUTE_ALL(params, t,replacement)});
    } else {
      var replacement = replacement || ' ';
      var newText = '';
      var nparams = params.split(",");
     
      nparams.forEach(function(character) {
        newText = text.replace(new RegExp(escapeRegExp(character),"g"),replacement);
        text = newText;
      })
      
      newText = text.replace(/\s{2,}/g, ' ').trim();      
      return newText;
    }
  } catch(e) {
    return e;
  }
}

var escapeRegExp= function(str) {
  return str.replace(/([.*+?^=!:${}()|\[\]\/\\])/g, "\\$1");
}


//the following three array comparsion formulas are originall from https://stackoverflow.com/questions/1187518/how-to-get-the-difference-between-two-arrays-in-javascript/33034768#33034768
//the functions have been adapted for use in Google apps script (sheets) @dsottimano



/**
* Returns common values between 2 arrays
* @param {"a1:a5"} array1 REQUIRED The first array
* @param {"b1:b5"} array2 REQUIRED The second array
* @return array
* @customfunction
*/
function INTERSECTION(array1,array2) {
  try {
    if (!array1.map || !array2.map) return "Please ensure that both parameters are ranges of cells"; 
    var array1 = array1.flat(Infinity).filter(Boolean);
    var array2 = array2.flat(Infinity).filter(Boolean);
    var intersect = array1.filter(function(x) {return array2.includes(x)})
    
    if (!intersect || intersect == "") return "No matches found";
    return intersect;
  }catch(e) {
    return e
  }
}

/**
* Returns unique values from the first array between 2 arrays
* @param {"a1:a5"} array1 REQUIRED The first array
* @param {"b1:b5"} array2 REQUIRED The second array
* @return array
* @customfunction
*/
function DIFFERENCE(array1,array2) {
  try {
    if (!array1.map || !array2.map) return "Please ensure that both parameters are ranges of cells"; 
    var array1 = array1.flat(Infinity).filter(Boolean);
    var array2 = array2.flat(Infinity).filter(Boolean);
    var difference = array1.filter(function(x) {return !array2.includes(x)})
    
    if (!difference || difference == "") return "No matches found";
    return difference;
  }catch(e) {
    return e
  }
}


/**
* Returns unique values from two arrays
* @param {"a1:a5"} array1 REQUIRED The first array
* @param {"b1:b5"} array2 REQUIRED The second array
* @return array
* @customfunction
*/
function DIFFERENCE_COMBINED(array1,array2) {
  try {
    if (!array1.map || !array2.map) return "Please ensure that both parameters are ranges of cells"; 
    var array1 = array1.flat(Infinity).filter(Boolean);
    var array2 = array2.flat(Infinity).filter(Boolean);
    var difference = array1.filter(function(x) {return !array2.includes(x)}).concat(array2.filter(function(x){ return !array1.includes(x)}));
    if (!difference || difference == "") return "No matches found";
    return difference;
  }catch(e) {
    return e
  }
}



/**
* Combines an array into a column
* @param {"a1:a5"} array REQUIRED The first array
* @return Combines selected array into one column
* @customfunction
*/

function COMBINE_TO_ROWS(array) {
  try {
  if(!array || !array.map) return "Please enter a valid range of cells as a parameter";  
  return array.flat(Infinity).filter(Boolean);
  } catch(e) {
  return e;
  }
}




/**
* Returns Google autosuggest results, 1 level deep
* @param {"cars"} keyword REQUIRED The keyword seed
* @param {"en"} lang OPTIONAL The language parameter, same as Google search &hl= parameter value. Default is "en"
* @param {"us"} country  OPTIONAL The country parameter, same as Google search &gl= parameter value. Default is "us"
* @customfunction
*/
function GOOGLE_SUGGEST(keyword,lang,country) {
  var lang = lang || "en"
  var country = country || "us"
  var totalResult = []
  var data = collectData(keyword, lang,country);
  Logger.log(data)
  
  if (data.length < 2) return "No results";
  
  totalResult.push(data)
  data.forEach(function(kw) {
    totalResult.push(collectData(kw, lang,country)) 
  })
  
  return totalResult.filter(Boolean).join().split(",");
}


function collectData (keyword,lang,country) {
  Utilities.sleep(500)
  var result = UrlFetchApp.fetch("http://clients1.google.com/complete/search?hl="+lang+"&gl="+country+"&output=toolbar&q="+encodeURIComponent(keyword));
  var document = XmlService.parse(result);
  var root = document.getRootElement();
  var keywordHolder = [];
  var a = root.getChildren();
  
  for (var i = 0 ; i < a.length; i ++ ) {
    keywordHolder.push(a[i].getChild("suggestion").getAttribute("data").getValue());
  }
  
  return keywordHolder;
  
}


function go () {
var r = UrlFetchApp.fetch("https://web.archive.org/save/https://davidsottimano.com/things-easier-google-docs-versus-excel/").getContentText();
Logger.log(r)
}


/*********************************
Polyfills
**********************************
*/

// Production steps of ECMA-262, Edition 5, 15.4.4.21
// Reference: http://es5.github.io/#x15.4.4.21
// https://tc39.github.io/ecma262/#sec-array.prototype.reduce
if (!Array.prototype.reduce) {
  Object.defineProperty(Array.prototype, 'reduce', {
    value: function(callback /*, initialValue*/) {
      if (this === null) {
        throw new TypeError( 'Array.prototype.reduce ' + 
          'called on null or undefined' );
      }
      if (typeof callback !== 'function') {
        throw new TypeError( callback +
          ' is not a function');
      }

      // 1. Let O be ? ToObject(this value).
      var o = Object(this);

      // 2. Let len be ? ToLength(? Get(O, "length")).
      var len = o.length >>> 0; 

      // Steps 3, 4, 5, 6, 7      
      var k = 0; 
      var value;

      if (arguments.length >= 2) {
        value = arguments[1];
      } else {
        while (k < len && !(k in o)) {
          k++; 
        }

        // 3. If len is 0 and initialValue is not present,
        //    throw a TypeError exception.
        if (k >= len) {
          throw new TypeError( 'Reduce of empty array ' +
            'with no initial value' );
        }
        value = o[k++];
      }

      // 8. Repeat, while k < len
      while (k < len) {
        // a. Let Pk be ! ToString(k).
        // b. Let kPresent be ? HasProperty(O, Pk).
        // c. If kPresent is true, then
        //    i.  Let kValue be ? Get(O, Pk).
        //    ii. Let accumulator be ? Call(
        //          callbackfn, undefined,
        //          « accumulator, kValue, k, O »).
        if (k in o) {
          value = callback(value, o[k], k, o);
        }

        // d. Increase k by 1.      
        k++;
      }

      // 9. Return accumulator.
      return value;
    }
  });
}

//array flat and flatmap

Array.prototype.flat || Object.defineProperty(Array.prototype, "flat", {
    configurable: !0,
    value: function r() {
        var t = isNaN(arguments[0]) ? 1 : Number(arguments[0]);
        return t ? Array.prototype.reduce.call(this, function (a, e) {
            return Array.isArray(e) ? a.push.apply(a, r.call(e, t - 1)) : a.push(e), a
        }, []) : Array.prototype.slice.call(this)
    },
    writable: !0
}), Array.prototype.flatMap || Object.defineProperty(Array.prototype, "flatMap", {
    configurable: !0,
    value: function (r) {
        return Array.prototype.map.apply(this, arguments).flat()
    },
    writable: !0
})



//array includes



if (!Array.prototype.includes) {
  Object.defineProperty(Array.prototype, 'includes', {
    value: function (searchElement, fromIndex) {

      // 1. Let O be ? ToObject(this value).
      if (this == null) {
        throw new TypeError('"this" is null or not defined');
      }

      var o = Object(this);

      // 2. Let len be ? ToLength(? Get(O, "length")).
      var len = o.length >>> 0;

      // 3. If len is 0, return false.
      if (len === 0) {
        return false;
      }

      // 4. Let n be ? ToInteger(fromIndex).
      //    (If fromIndex is undefined, this step produces the value 0.)
      var n = fromIndex | 0;

      // 5. If n ≥ 0, then
      //  a. Let k be n.
      // 6. Else n < 0,
      //  a. Let k be len + n.
      //  b. If k < 0, let k be 0.
      var k = Math.max(n >= 0 ? n : len - Math.abs(n), 0);

      function sameValueZero(x, y) {
        return x === y || (typeof x === 'number' && typeof y === 'number' && isNaN(x) && isNaN(y));
      }

      // 7. Repeat, while k < len
      while (k < len) {
        // a. Let elementK be the result of ? Get(O, ! ToString(k)).
        // b. If SameValueZero(searchElement, elementK) is true, return true.
        // c. Increase k by 1.
        if (sameValueZero(o[k], searchElement)) {
          return true;
        }
        k++;
      }

      // 8. Return false
      return false;
    }
  });
}

//string includes


if (!String.prototype.includes) {
  String.prototype.includes = function(search, start) {
    'use strict';

    if (search instanceof RegExp) {
      throw TypeError('first argument must not be a RegExp');
    } 
    if (start === undefined) { start = 0; }
    return this.indexOf(search, start) !== -1;
  };
}


function includes (arr,val,label) {
  
  
}


//array indexof

if (!Array.prototype.indexOf)
  Array.prototype.indexOf = (function(Object, max, min) {
    "use strict"
    return function indexOf(member, fromIndex) {
      if (this === null || this === undefined)
        throw TypeError("Array.prototype.indexOf called on null or undefined")

      var that = Object(this), Len = that.length >>> 0, i = min(fromIndex | 0, Len)
      if (i < 0) i = max(0, Len + i)
      else if (i >= Len) return -1

      if (member === void 0) {        // undefined
        for (; i !== Len; ++i) if (that[i] === void 0 && i in that) return i
      } else if (member !== member) { // NaN
        return -1 // Since NaN !== NaN, it will never be found. Fast-path it.
      } else                          // all else
        for (; i !== Len; ++i) if (that[i] === member) return i 

      return -1 // if the value was not found, then return -1
    }
  })(Object, Math.max, Math.min)