Skip to content

Instantly share code, notes, and snippets.

@tomcritchlow
Created February 4, 2022 21:13
Show Gist options
  • Star 4 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save tomcritchlow/cbb06a9298fb6cc0804372552fda1f96 to your computer and use it in GitHub Desktop.
Save tomcritchlow/cbb06a9298fb6cc0804372552fda1f96 to your computer and use it in GitHub Desktop.
Gist code for Electric Tables V0.2.
// 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);
// 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