Created
February 4, 2022 21:13
-
-
Save tomcritchlow/cbb06a9298fb6cc0804372552fda1f96 to your computer and use it in GitHub Desktop.
Gist code for Electric Tables V0.2.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Convert this JS code into a bookmarklet using a tool like: http://js.do/blog/bookmarklets/ | |
// Be sure to add your own macro_url and spreadsheet_url | |
var macro_url = "THIS IS YOUR MACRO URL"; | |
var spreadsheet_id = "THIS IS YOUR SPREADSHEET URL"; | |
var iframe = document.createElement("iframe"); | |
iframe.setAttribute("name","dummyframe"); | |
iframe.setAttribute("id","dummyframe"); | |
iframe.setAttribute("style","display:none;"); | |
var closebutton = document.createElement("button"); | |
closebutton.setAttribute("id","closebutton"); | |
closebutton.setAttribute( | |
"style", | |
"all:revert;z-index:90000;position:fixed;top:5px;right:40px;margin:10px;padding:5px;border:1px solid white;border-radius:5px;color:white;background-color:#35092C" | |
); | |
closebutton.innerHTML = "close"; | |
closebutton.setAttribute("href", "#"); | |
closebutton.onclick = function () { | |
document.getElementById("electrictables").remove(); | |
document.getElementById("closebutton").remove(); | |
}; | |
var mydiv = document.createElement("div"); | |
mydiv.setAttribute("id","electrictables"); | |
mydiv.setAttribute("style", "font-size:14px;background-color:white;z-index:90000;position:fixed;top:50px;right:40px;border:1px solid rgba(0,0,0,0.2);border-radius:10px;width:400px;height:300px; box-shadow: 2px 2px 8px 0 rgb(0 0 0 / 20%);"); | |
var myform = `<div style="border-radius:10px 10px 0px 0px;width:100%;height:50px;color:white;background-color:#35092C"><span style="font-family:sans serif;font-weight:bold;font-size:18px;line-height:50px">⚡Electric Tables</span></div> | |
<form style="padding:20px" onsubmit="document.getElementById('submitbutton').value = 'submitted!'" target="dummyframe" method="post" action="${macro_url}"> | |
<input type="text" id="table" name="table" size="40" placeholder="table"><br><br> | |
<input type="text" id="url" name="url" size="40" value="${window.location.href}"><br><br> | |
<input type="text" id="notes" name="notes" size="40"placeholder="notes"><br><br> | |
<input type="hidden" id="spreadsheet" name="spreadsheet" value="${spreadsheet_id}"> | |
<br><br> | |
<input type="submit" value="Submit" id="submitbutton"> | |
</form>`; | |
mydiv.innerHTML = myform; | |
document.body.appendChild(iframe); | |
document.body.appendChild(closebutton); | |
document.body.appendChild(mydiv); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 1) Host this as a script at script.google.com | |
// 2) Publish to the web as a macro - you'll get a URL endpoint for your function | |
// 3) The bookmarklet just sends a POST request to that URL containing: | |
// ---- URL | |
// ---- table (optional) | |
// ---- notes (optional) | |
// ---- spreadsheet (this is the spreadsheet key ID that tells the script which spreadsheet to save data to) | |
// 4) Be sure to update the hardcoded spreadsheet URLs... | |
// | |
function doPost(e){ | |
var bookmarkletdata = e.parameter; | |
scrapeURL(bookmarkletdata["url"],bookmarkletdata["table"],bookmarkletdata["spreadsheet"],bookmarkletdata["notes"]); | |
return ContentService.createTextOutput(e.parameter["url"]); // Please add this. | |
} | |
function scrapeURL(url, table, spreadsheet, notes){ | |
var ss = SpreadsheetApp.openByUrl( "https://docs.google.com/spreadsheet/ccc?key="+spreadsheet); | |
var contents = UrlFetchApp.fetch(url).getContentText(); | |
const $ = Cheerio.load(contents); | |
var extracted_data = {}; | |
extracted_data["Timestamp"] = new Date(); | |
extracted_data["url"] = url; | |
try{ | |
extracted_data["title"] = $("*[property='og:title']").attr("content"); | |
}catch(e){ | |
extracted_data["title"] = $('title').text(); | |
} | |
var configsheet = ss.getSheetByName("config"); | |
if(configsheet){ | |
var configdata = configsheet.getDataRange().getValues(); | |
var config_rules = []; | |
for(var i = 1; i < configdata.length; i++){ | |
var obj = {}; | |
obj["config_domain"] = configdata[i][0]; | |
obj["config_title"] = configdata[i][1]; | |
obj["config_rule"] = configdata[i][2]; | |
config_rules.push(obj); | |
} | |
console.log(config_rules); | |
for(i in config_rules){ | |
if(url.includes(config_rules[i]["config_domain"])){ | |
console.log("trying: "+config_rules[i]["config_title"]); | |
extracted_data[config_rules[i]["config_title"]] = eval(config_rules[i]["config_rule"]); | |
} | |
} | |
} | |
if(table){ | |
var sheet = ss.getSheetByName(table); | |
if (!sheet) { | |
newsheet = ss.insertSheet(); | |
newsheet.setName(table); | |
sheet = newsheet; | |
var newheader = Object.keys(extracted_data); | |
sheet.appendRow(newheader); | |
} | |
}else{ | |
var sheet = ss.getSheets()[0]; | |
} | |
if(notes){ | |
extracted_data["notes"] = notes; | |
} | |
var rowNum = 1; | |
var headers = sheet.getRange(rowNum +":"+ rowNum).getValues() | |
headers = headers[0]; | |
headers = headers.filter(n => n); | |
var nextRow = sheet.getLastRow(); | |
var lastcol = sheet.getLastColumn(); | |
var cell = sheet.getRange('a1'); | |
var col = 0; | |
for(var i in Object.keys(extracted_data)){ | |
if(headers.includes(Object.keys(extracted_data)[i])){ | |
}else{ | |
headers.push(Object.keys(extracted_data)[i]); | |
cell.offset(0, lastcol).setValue(Object.keys(extracted_data)[i]); | |
} | |
} | |
for (i in headers){ | |
if (headers[i] == "Timestamp"){ | |
val = new Date(); | |
} | |
else { | |
val = extracted_data[headers[i]]; | |
} | |
cell.offset(nextRow, col).setValue(val); | |
col++; | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment