Skip to content

Instantly share code, notes, and snippets.

@dsottimano
Last active January 5, 2024 20:01
Show Gist options
  • Save dsottimano/e3e6294f80cb1cbb526d1defed322850 to your computer and use it in GitHub Desktop.
Save dsottimano/e3e6294f80cb1cbb526d1defed322850 to your computer and use it in GitHub Desktop.
Tech seo boost 2019 - Dave Sottimano - Apps script demo
//serpApiKey from serpapi.com
var GLOBAL_VARIABLES = {
serpApiKey : "add your api key"
}
/*
/***************************
/BEGIN PRESENTATION SCRIPTS
@DSOTTIMANO ON TWITTER
LICENSE: DO WHATEVER YOU WANT ;)
****************************
*/
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Menu')
.addItem('Check if Indexed', 'showDialog')
.addToUi();
}
function showDialog() {
var html = HtmlService.createHtmlOutputFromFile('index').setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
SpreadsheetApp.getUi().showModalDialog(html, "Google Indexed Checker Demo")
}
function checkIndexed(urls) {
try {
var urls = urls.split('\n').slice(0,10);
SpreadsheetApp.getActiveSpreadsheet().toast('Check started', "Demo running",3);
var resultArray = [["Original URL","Indexed ?"]];
for (var i in urls) {
var search = GOOGLE_SEARCH(urls[i]);
var indexed = search.includes(urls[i]) ? true : false;
resultArray.push([urls[i],indexed])
}
var newSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet("Index report " + Date.now())
return newSheet.getRange(1, 1, resultArray.length, resultArray[0].length).setValues(resultArray)
} catch(e) {
SpreadsheetApp.getUi().alert(e)
}
}
// 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. "protocol","root","subdomain","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 {
if (part == "root") {
var newPart = "root";
part = "host";
}
if (part == "subdomain") part = "host"
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(newPart == "root") {
uri = uri[part].split(".")
if (uri.length >= 4) return uri.slice(1, 4).join(".");
if (uri.length == 3) return uri.slice(1, 3).join(".");
if (uri.length == 2) return uri.slice(0, 2).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
}
};
/**
* 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
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 {
if (textArray.map) textArray = textArray.flat(Infinity).join(' ').toString()
removeStopWords ? text = REMOVE_STOPWORDS(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
/**
* Removes English stopwords
* @param {"this is a sentence"} str REQUIRED The words you want to remove stopwords from
* @return Returns combination of protocols + www subdomains
* @customfunction
*/
function REMOVE_STOPWORDS(str) {
if (str.map) {
return str.map(function(s) {return REMOVE_STOPWORDS(s) })
} else {
var stopwords = ['i','me','are','who','what','my','myself','we','our','ours','ourselves','you','your','yours','yourself','yourselves','he','him','his','himself','she','her','hers','herself','is','is ','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 res = []
words = str.split(' ')
for(i=0;i<words.length;i++) {
if(!stopwords.includes(words[i]) && 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_COLUMN(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(",");
}
var collectData = function(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 WAYBACK_SAVE () {
var urlArray = [
'https://davidsottimano.com',
'https://davidsottimano.com/things-easier-google-docs-versus-excel/',
'https://opensourceseo.org/'
];
var result;
try {
urlArray.forEach(function(url) {
Utilities.sleep(800);
result = UrlFetchApp.fetch("https://web.archive.org/save/" + url);
});
var emailAddress = null
if(emailAddress) MailApp.sendEmail(emailAddress, "Wayback Machine Archiver Ran", "For the following URLs \n " + urlArray.map(function(u) { return u + "\n"}));
} catch(e) {
Logger.log(e)
}
}
/**
* Returns capture URLs from the Wayback Machine archive
* @param {"https://msn.com"} url REQUIRED The exact URL you want capture dates from
* @param {"20180101"} dateFrom REQUIRED YYYYMMDD format - The starting date for captures
* @param {"20190101"} dateTo REQUIRED YYYYMMDD format - The end date for captures
* @param {true} includeDate OPTIONAL true if you want dates returned also. Default is false
* @return Returns combination of protocols + www subdomains
* @customfunction
*/
function WAYBACK_GET (url, dateFrom, dateTo, includeDate) {
try {
var result = UrlFetchApp.fetch("http://web.archive.org/cdx/search/cdx?url="+url+"&matchType=exact&limit=10&output=json&from="+dateFrom+"&to="+dateTo+"&filter=statuscode:200&limit=-2");
var parsedResult = JSON.parse(result.getContentText());
var resultArray = [];
for (var i = 1; i < parsedResult.length; i++) {
var line = parsedResult[i][1];
if (includeDate) {
resultArray.push(line.substring(0,8), "https://web.archive.org/web/"+line+"/"+url);
} else {
resultArray.push("https://web.archive.org/web/"+line+"/"+url);
}
}
if(resultArray.length == 0) return "No results";
return [resultArray]
} catch(e) {
return e
}
}
/*********************************
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)
function COMBINE_STRINGS(string1,string2){
 return string1 + string2 
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment