Skip to content

Instantly share code, notes, and snippets.

@oliv-j

oliv-j/TouchPad.php

Last active February 23, 2023 15:37
Show Gist options
  • Save oliv-j/2087f0d68d9ad6e25ec54478bdc789c4 to your computer and use it in GitHub Desktop.
Save oliv-j/2087f0d68d9ad6e25ec54478bdc789c4 to your computer and use it in GitHub Desktop.
Download ZIP
A web service to run in combination with Better Touch Tool (BTT) to create an Elgato Streamdeck like experience with any Touch Screen device (like an old iPad!)
Raw
touchpad.csv
This is a CSV file of the settings I use in Google Sheets and then export to json to store on the server:
ref type badge label img trigger color bgcolor behaviour
r1 menu image PowerPoint powerpoint.svg powerpoint 0
r2 menu image Google Meet google_meet.svg google_meet 0
r3 menu fa Settings sound.svg sound ffffff 0
r4 menu image Gmail gmail.svg gmail 0
r5 menu badge Apps apps.svg apps 0
r6 card-powerpoint fa Format object format.svg ppFormat1 ffffff 151515 0
r7 card-powerpoint fa Group group.svg ppGroup ffffff FEC000 0
r8 card-powerpoint fa Ungroup ungroup.svg ppUngroup ffffff FEC000 0
r9 card-powerpoint fa Duplicate duplicate.svg ppduplicate ffffff 9CE25A 0
r10 card-powerpoint fa Distribute_h distribute_h.png ppDistributte_h ffffff 64B2E9 0
r11 card-powerpoint fa Distribute_v distribute_v.png ppDistributte_v ffffff 64B2E9 0
r12 card-powerpoint fa Align_h align_h.svg ppAlignCentre ffffff 57879A 0
r13 card-powerpoint fa Align_v align_v.svg ppAlignMiddle ffffff 57879A 0
r14 card-powerpoint fa Align_l align_l.svg ppAlignLeft ffffff 57879A 0
r15 card-powerpoint fa Align_r align_r.svg ppAlignRight ffffff 57879A 0
r16 card-powerpoint fa Align_t align_t.svg ppAlignTop ffffff 57879A 0
r17 card-powerpoint fa Align_b align_b.svg ppAlignBottom ffffff 57879A 0
r18 card-powerpoint fa Insert picture insert_picture.svg ppInsertImage ffffff F0C633 0
r19 card-powerpoint fa Spell check spell_check.svg ppSpell ffffff CE71A6 0
r20 card-powerpoint fa Toggle guides toggle_guides.svg ppGuides ffffff 2CB0F0 0
r21 card-powerpoint fa Copy Format format_painter.svg ppCopyStyle ffffff CF1920 1
r22 card-powerpoint fa Paste Format format_painter.svg ppPasteStyle ffffff 9CE25A 1
r23 card-settings fa Eject All Disks eject-solid.svg ejectAllDisks ffffff 0
r24 card-settings fa New empty text file file-solid.svg newTxtFile ffffff 0
r25 card-settings fa Toggle Mic On or Off microphone-solid.svg toggleMic ffffff 0
r26 card-settings fa Do Not Disturb 1hr ban-solid.svg DND1hr ffffff 0
r27 card-settings fa Focus - Work business-time-solid.svg FocusWork5pm ffffff 0
r28 card-apps fa Monday - UL IT monday-icon.svg goULITMonday ffffff 0
r29 card-apps fa Monday - UL C monday-icon.svg goULCMonday ffffff 0
r30 card-apps fa Calendar google_calendar_icon.svg goCalendar ffffff 0
r31 card-apps fa Email - Work gmail.svg goGmailWork ffffff 0
r32 card-apps fa Email - Personal gmail.svg goGmail ffffff 0
r33 card-apps fa Google Meet google_meet_icon.svg goMeet ffffff 0
r34 card-apps fa Google Chat google_chat_icon.svg goChat ffffff 0
r35 card-apps fa Powerpoint powerpoint.svg goPPT ffffff 0
Raw
touchpad.json
[
{
"ref": "r1",
"type": "menu",
"badge": "image",
"label": "PowerPoint",
"img": "powerpoint.svg",
"trigger": "powerpoint",
"behaviour": 0
},
{
"ref": "r2",
"type": "menu",
"badge": "image",
"label": "Google Meet",
"img": "google_meet.svg",
"trigger": "google_meet",
"behaviour": 0
},
{
"ref": "r3",
"type": "menu",
"badge": "fa",
"label": "Settings",
"img": "sound.svg",
"trigger": "sound",
"color": "ffffff",
"behaviour": 0
},
{
"ref": "r4",
"type": "menu",
"badge": "image",
"label": "Gmail",
"img": "gmail.svg",
"trigger": "gmail",
"behaviour": 0
},
{
"ref": "r5",
"type": "menu",
"badge": "badge",
"label": "Apps",
"img": "apps.svg",
"trigger": "apps",
"behaviour": 0
},
{
"ref": "r6",
"type": "card-powerpoint",
"badge": "fa",
"label": "Format object",
"img": "format.svg",
"trigger": "ppFormat1",
"color": "ffffff",
"bgcolor": 151515,
"behaviour": 0
},
{
"ref": "r7",
"type": "card-powerpoint",
"badge": "fa",
"label": "Group",
"img": "group.svg",
"trigger": "ppGroup",
"color": "ffffff",
"bgcolor": "FEC000",
"behaviour": 0
},
{
"ref": "r8",
"type": "card-powerpoint",
"badge": "fa",
"label": "Ungroup",
"img": "ungroup.svg",
"trigger": "ppUngroup",
"color": "ffffff",
"bgcolor": "FEC000",
"behaviour": 0
},
{
"ref": "r9",
"type": "card-powerpoint",
"badge": "fa",
"label": "Duplicate",
"img": "duplicate.svg",
"trigger": "ppduplicate",
"color": "ffffff",
"bgcolor": "9CE25A",
"behaviour": 0
},
{
"ref": "r10",
"type": "card-powerpoint",
"badge": "fa",
"label": "Distribute_h",
"img": "distribute_h.png",
"trigger": "ppDistributte_h",
"color": "ffffff",
"bgcolor": "64B2E9",
"behaviour": 0
},
{
"ref": "r11",
"type": "card-powerpoint",
"badge": "fa",
"label": "Distribute_v",
"img": "distribute_v.png",
"trigger": "ppDistributte_v",
"color": "ffffff",
"bgcolor": "64B2E9",
"behaviour": 0
},
{
"ref": "r12",
"type": "card-powerpoint",
"badge": "fa",
"label": "Align_h",
"img": "align_h.svg",
"trigger": "ppAlignCentre",
"color": "ffffff",
"bgcolor": "57879A",
"behaviour": 0
},
{
"ref": "r13",
"type": "card-powerpoint",
"badge": "fa",
"label": "Align_v",
"img": "align_v.svg",
"trigger": "ppAlignMiddle",
"color": "ffffff",
"bgcolor": "57879A",
"behaviour": 0
},
{
"ref": "r14",
"type": "card-powerpoint",
"badge": "fa",
"label": "Align_l",
"img": "align_l.svg",
"trigger": "ppAlignLeft",
"color": "ffffff",
"bgcolor": "57879A",
"behaviour": 0
},
{
"ref": "r15",
"type": "card-powerpoint",
"badge": "fa",
"label": "Align_r",
"img": "align_r.svg",
"trigger": "ppAlignRight",
"color": "ffffff",
"bgcolor": "57879A",
"behaviour": 0
},
{
"ref": "r16",
"type": "card-powerpoint",
"badge": "fa",
"label": "Align_t",
"img": "align_t.svg",
"trigger": "ppAlignTop",
"color": "ffffff",
"bgcolor": "57879A",
"behaviour": 0
},
{
"ref": "r17",
"type": "card-powerpoint",
"badge": "fa",
"label": "Align_b",
"img": "align_b.svg",
"trigger": "ppAlignBottom",
"color": "ffffff",
"bgcolor": "57879A",
"behaviour": 0
},
{
"ref": "r18",
"type": "card-powerpoint",
"badge": "fa",
"label": "Insert picture",
"img": "insert_picture.svg",
"trigger": "ppInsertImage",
"color": "ffffff",
"bgcolor": "F0C633",
"behaviour": 0
},
{
"ref": "r19",
"type": "card-powerpoint",
"badge": "fa",
"label": "Spell check",
"img": "spell_check.svg",
"trigger": "ppSpell",
"color": "ffffff",
"bgcolor": "CE71A6",
"behaviour": 0
},
{
"ref": "r20",
"type": "card-powerpoint",
"badge": "fa",
"label": "Toggle guides",
"img": "toggle_guides.svg",
"trigger": "ppGuides",
"color": "ffffff",
"bgcolor": "2CB0F0",
"behaviour": 0
},
{
"ref": "r21",
"type": "card-powerpoint",
"badge": "fa",
"label": "Copy Format",
"img": "format_painter.svg",
"trigger": "ppCopyStyle",
"color": "ffffff",
"bgcolor": "CF1920",
"behaviour": 1
},
{
"ref": "r22",
"type": "card-powerpoint",
"badge": "fa",
"label": "Paste Format",
"img": "format_painter.svg",
"trigger": "ppPasteStyle",
"color": "ffffff",
"bgcolor": "9CE25A",
"behaviour": 1
},
{
"ref": "r23",
"type": "card-settings",
"badge": "fa",
"label": "Eject All Disks",
"img": "eject-solid.svg",
"trigger": "ejectAllDisks",
"color": "ffffff",
"behaviour": 0
},
{
"ref": "r24",
"type": "card-settings",
"badge": "fa",
"label": "New empty text file",
"img": "file-solid.svg",
"trigger": "newTxtFile",
"color": "ffffff",
"behaviour": 0
},
{
"ref": "r25",
"type": "card-settings",
"badge": "fa",
"label": "Toggle Mic On or Off",
"img": "microphone-solid.svg",
"trigger": "toggleMic",
"color": "ffffff",
"behaviour": 0
},
{
"ref": "r26",
"type": "card-settings",
"badge": "fa",
"label": "Do Not Disturb 1hr",
"img": "ban-solid.svg",
"trigger": "DND1hr",
"color": "ffffff",
"behaviour": 0
},
{
"ref": "r27",
"type": "card-settings",
"badge": "fa",
"label": "Focus - Work",
"img": "business-time-solid.svg",
"trigger": "FocusWork5pm",
"color": "ffffff",
"behaviour": 0
},
{
"ref": "r28",
"type": "card-apps",
"badge": "fa",
"label": "Monday - UL IT",
"img": "monday-icon.svg",
"trigger": "goULITMonday",
"color": "ffffff",
"behaviour": 0
},
{
"ref": "r29",
"type": "card-apps",
"badge": "fa",
"label": "Monday - UL C",
"img": "monday-icon.svg",
"trigger": "goULCMonday",
"color": "ffffff",
"behaviour": 0
},
{
"ref": "r30",
"type": "card-apps",
"badge": "fa",
"label": "Calendar",
"img": "google_calendar_icon.svg",
"trigger": "goCalendar",
"color": "ffffff",
"behaviour": 0
},
{
"ref": "r31",
"type": "card-apps",
"badge": "fa",
"label": "Email - Work",
"img": "gmail.svg",
"trigger": "goGmailWork",
"color": "ffffff",
"behaviour": 0
},
{
"ref": "r32",
"type": "card-apps",
"badge": "fa",
"label": "Email - Personal",
"img": "gmail.svg",
"trigger": "goGmail",
"color": "ffffff",
"behaviour": 0
},
{
"ref": "r33",
"type": "card-apps",
"badge": "fa",
"label": "Google Meet",
"img": "google_meet_icon.svg",
"trigger": "goMeet",
"color": "ffffff",
"behaviour": 0
},
{
"ref": "r34",
"type": "card-apps",
"badge": "fa",
"label": "Google Chat",
"img": "google_chat_icon.svg",
"trigger": "goChat",
"color": "ffffff",
"behaviour": 0
},
{
"ref": "r35",
"type": "card-apps",
"badge": "fa",
"label": "Powerpoint",
"img": "powerpoint.svg",
"trigger": "goPPT",
"color": "ffffff",
"behaviour": 0
}
]
Raw
TouchPad.php
//run this on a server to output the UI for a client
//references config from touchpad.json and images from an adjacent img folder
//you need to add the local ip of the machine you are trying to control via BTT.
//This will then be stored in a cookie so that you don't have to add it each time.
//A later version will have a proper implementation of tabs so that you can switch
to different sets of icons to trigger functions in BTT.
<html>
<head>
<style>
.html, body {
background-color: #1E1E1E;
color:#1f7695;
font-family:"Courier New";
}
.container {
margin:20px;
}
p {
padding:7px 0 7px 0;
}
p, input {
font-size: 20px;
}
input {
background-color: #2F171C;
color: #C0171C;
font-family:"Courier New";
margin-left: 43px;
padding:5px 0 5px 3px;
}
input:focus {
outline:none;
}
.card p {
color:#ffffff;
}
#menu {
grid-template-columns: repeat(auto-fill, minmax(90px, 1fr))!important;
gap: 15px!important;
margin-bottom:15px!important;
}
.cards {
display: grid;
grid-template-columns: repeat(auto-fill, minmax(100px, 2fr));
gap: 20px;
grid-auto-flow: dense;
margin:30px auto;
max-width:900px;
}
.active {
background-color: #5d5d5d;
}
.card {
width:65px;
height:65px;
border: 5px solid;
border-color:#151515;
background-color: #151515;
border-radius: 18px;
padding: 10px;
text-align: center;
}
.trigger {
width:85px!important;
height:85px!important;
}
.badge {
padding:0;
width:85px;
height:85px;
}
img {
position:relative;
width:100%;
height:100%;
vertical-align: middle;
margin:auto;
}
.hidden {
display:none;
}
.success {
animation: success 0.5s ;
animation-iteration-count: 1;
}
@keyframes success {
50% {border-color: #00B900;}
}
.fail {
animation: fail 0.5s ;
animation-iteration-count: 5;
}
@keyframes fail {
50% {border-color: #C0171C;}
}
/* The sidebar menu */
.sidebar {
height: 100%; /* 100% Full-height */
width: 0; /* 0 width - change this with JavaScript */
position: fixed; /* Stay in place */
z-index: 1; /* Stay on top */
top: 0;
left: 0;
background-color: #111; /* Black*/
overflow-x: hidden; /* Disable horizontal scroll */
padding-top: 60px; /* Place content 60px from the top */
transition: 0.5s; /* 0.5 second transition effect to slide in the sidebar */
}
/* The sidebar links */
.sidebar a {
padding: 8px 8px 8px 32px;
text-decoration: none;
font-size: 25px;
color: #818181;
display: block;
transition: 0.3s;
}
/* When you mouse over the navigation links, change their color */
.sidebar a:hover {
color: #f1f1f1;
}
/* Position and style the close button (top right corner) */
.sidebar .closebtn {
position: absolute;
top: 0;
right: 25px;
font-size: 36px;
margin-left: 50px;
}
/* Style page content - use this if you want to push the page content to the right when you open the side navigation */
#main {
transition: margin-left .5s; /* If you want a transition effect */
padding: 20px;
}
/* On smaller screens, where height is less than 450px, change the style of the sidenav (less padding and a smaller font size) */
@media screen and (max-height: 450px) {
.sidebar {padding-top: 15px;}
.sidebar a {font-size: 18px;}
}
</style>
</head>
<?php
$string = file_get_contents("touchpad.json");
$allcards = json_decode($string,true);
$menus = []; $cards = []; $x=0; $y=0;
foreach ($allcards as $card) {
//var_dump($card);
if ($card["type"] == "menu") {
//print("<p>menu found</p>");
$menus[$x] = [];
foreach($card as $key => $value) {
//echo $key." : ".$value."<br/>";
$menus[$x][$key] = $value;
}
$x++;
} else {
$cards[$y] = [];
foreach($card as $key => $value) {
//echo $key." : ".$value."<br/>";
$cards[$y][$key] = $value;
}
$y++;
}
unset($card);
}
?>
<body>
<div id="sideBar" class="sidebar hidden">
<a href="javascript:void(0)" class="closebtn" onclick="sidebarToggle()">&times;</a>
<a href="#">About</a>
<a href="#">Services</a>
<a href="#">Clients</a>
<a href="#">Contact</a>
</div>
<div id="container">
<p id="ipSelect">ip: <span id="currentIP" onclick="editIP(this);">NOT-SET</span></p>
<form id="ipForm" onsubmit="setIP()" class="hidden">
<input id="ipFormEntry" type="text">
</form>
<div class="cards" id="menu">
<?php
//count array length, setup counter
$x = 0; $arrLength = count($menus);
//print('<p> array length is '.$arrLength.'</p>');
while ($x < $arrLength) {
$ref = $menus[$x]["ref"];
$label = $menus[$x]["label"];
$img = $menus[$x]["img"];
$trigger = $menus[$x]["trigger"];
$behaviour = $menus[$x]["behaviour"];
$color = $menus[$x]["color"];
$bgcolor = $menus[$x]["bgcolor"];
$badge = $menus[$x]["badge"];
if ($badge =='badge') {
print '<div class="card '.$badge.'"><img onclick="callSwitch(\''.$trigger.'\');" src="img/'.$img.'" alt="'.$label.'"/></div>
';
} else {
print '<div class="card"><img onclick="callSwitch(\''.$trigger.'\');" src="img/'.$img.'" alt="'.$label.'"/></div>
';
}
$x++;
} ?>
</div>
<hr>
<div class="cards triggers hidden" id="powerpoint">
<?php
//count array length, setup counter
$x = 0; $arrLength = count($cards);
//print '<p> array length is '.$arrLength.'</p>';
while ($x < $arrLength) {
$ref = $cards[$x]["ref"];
$label = $cards[$x]["label"];
$img = $cards[$x]["img"];
$trigger = $cards[$x]["trigger"];
$behaviour = $cards[$x]["behaviour"];
$color = $cards[$x]["color"];
$bgcolor = $cards[$x]["bgcolor"];
/*if ($badge =='badge') {
//nothing
} else {
$badge ='';
}*/
print '<div id="'.$ref.'" style="color:#'.$color.'; background-color:#'.$bgcolor.'!important;" class="card '.$trigger.'"><img onclick="callTrigger(\''.$trigger.'\',\''.$ref.'\',);"src="img/'.$img.'" alt="'.$label.'"/></i></div>
';
$x++;
}
?>
</div>
<div class="cards triggers hidden" id="google_meet">
<div id="r46" style="color:#ffffff; background-color:#151515!important;" class="card ppFormat1"><img onclick="callTrigger('ppFormat1','r46',);" src="img/format.svg" alt="Format object"></div>
</div>
<div class="cards triggers hidden" id="sound">
<div id="r56" style="color:#ffffff; background-color:#151515!important;" class="card ppFormat1"><img onclick="callTrigger('ppFormat1','r56',);" src="img/format.svg" alt="Format object"></div>
</div>
<div class="cards triggers hidden" id="gmail">
<div id="r66" style="color:#ffffff; background-color:#151515!important;" class="card ppFormat1 success"><img onclick="callTrigger('ppFormat1','r66',);" src="img/format.svg" alt="Format object"></div>
</div>
<div class="cards triggers hidden" id="apps">
<div id="app-ip" style="color:#ffffff; background-color:#151515!important;" class="card ppFormat1 alert"><img onclick="sidebarToggle('sideBar','app-ip',);" src="img/basic_gunsight.svg" alt="Format object"></div>
<div id="r76" style="color:#ffffff; background-color:#151515!important;" class="card ppFormat1 alert"><img onclick="callTrigger('ppFormat1','r76',);" src="img/format.svg" alt="Format object"></div>
</div>
</div>
<script>
var defaultIP = "";
async function sidebarToggle(setting, r) {
document.querySelector('#'+setting).classList.toggle("hidden");
document.querySelector('#'+r).classList.toggle("success");
//document.querySelector('#'+r).classList.toggle("fail");
};
//define global var of newIP
async function callTrigger(trigger, r) {
url = 'https://' + defaultIP + ':56689/trigger_named/?shared_secret=verjope878vh_infoe7&trigger_name=' + trigger;
fetch(url, { signal: AbortSignal.timeout(300),
method:'GET', mode: 'cors', cache: 'no-cache', headers: {
'content-type':'application/x-www-form-urlencoded'
}})
.then(response => {
if (response.status == 200) {
console.log('The status was 200 - YES \n');
document.querySelector('#'+r).classList.toggle("success");
} else {
console.log('the status was NOT 200 \n');
document.querySelector('#'+r).classList.toggle("fail");
}
})
.catch(error => {
console.log('There was a Fetch problem: \n', error);
document.querySelector('#'+r).classList.toggle("fail");
});
};
async function callSwitch(i) {
document.querySelector('#'+i).classList.toggle("hidden");
};
AbortSignal.timeout ??= async function timeout(ms) {
const ctrl = new AbortController()
setTimeout(() => ctrl.close(), ms)
return ctrl.signal
}
editIP = function(iptext) {
t = iptext.innerText || iptext.textContent;
document.getElementById("ipSelect").classList.toggle("hidden");
document.getElementById("ipFormEntry").value = "";
document.getElementById("ipForm").classList.toggle("hidden");
document.getElementById("ipFormEntry").focus();
}
setIP = function() {
newIP = document.getElementById("ipFormEntry").value;
defaultIP = newIP;
document.querySelector("#ipForm").classList.toggle("hidden");
document.getElementById("currentIP").textContent = newIP;
document.querySelector("#currentIP").classList.toggle("hidden");
setCookie("TouchPad",newIP,365)
}
function setCookie(name,value,days) {
var expires = "";
if (days) {
var date = new Date();
date.setTime(date.getTime() + (days*24*60*60*1000));
expires = "; expires=" + date.toUTCString();
}
document.cookie = name + "=" + (value || "") + expires + "; path=/";
}
function getCookie(name) {
var nameEQ = name + "=";
var ca = document.cookie.split(';');
for(var i=0;i < ca.length;i++) {
var c = ca[i];
while (c.charAt(0)==' ') c = c.substring(1,c.length);
if (c.indexOf(nameEQ) == 0) return c.substring(nameEQ.length,c.length);
}
return null;
}
function eraseCookie(name) {
document.cookie = name +'=; Path=/; Expires=Thu, 01 Jan 1970 00:00:01 GMT;';
}
readCookie = function() {
saved = getCookie("TouchPad");
if (saved) {
document.getElementById("currentIP").textContent = saved;
defaultIP = saved;
} else {
defaultIP = "0";
}
}
window.onload = readCookie;
</script>
</body>
</html
Raw
touchpadSheet2json.gs
//This first file creates the json for the config file. The next file creates the UI.
// Functionality: get a new menu item in google sheets to turn a 2D table of data into formatted JSON
// To use: add this to google sheets as a new Apps Script file
//The json text then needs to be store in a config file along with the PHP script
//that will be parsed to generate the user interface for the client.
// Includes functions for exporting active sheet or all sheets as JSON object (also Python object syntax compatible).
// Tweak the makePrettyJSON_ function to customize what kind of JSON to export.
//I didn't write this myself
var FORMAT_ONELINE = 'One-line';
var FORMAT_MULTILINE = 'Multi-line';
var FORMAT_PRETTY = 'Pretty';
var LANGUAGE_JS = 'JavaScript';
var LANGUAGE_PYTHON = 'Python';
var STRUCTURE_LIST = 'List';
var STRUCTURE_HASH = 'Hash (keyed by "id" column)';
/* Defaults for this particular spreadsheet, change as desired */
var DEFAULT_FORMAT = FORMAT_PRETTY;
var DEFAULT_LANGUAGE = LANGUAGE_JS;
var DEFAULT_STRUCTURE = STRUCTURE_LIST;
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [
{name: "Export JSON for this sheet", functionName: "exportSheet"},
{name: "Export JSON for all sheets", functionName: "exportAllSheets"}
];
ss.addMenu("Export JSON", menuEntries);
}
function makeLabel(app, text, id) {
var lb = app.createLabel(text);
if (id) lb.setId(id);
return lb;
}
function makeListBox(app, name, items) {
var listBox = app.createListBox().setId(name).setName(name);
listBox.setVisibleItemCount(1);
var cache = CacheService.getPublicCache();
var selectedValue = cache.get(name);
Logger.log(selectedValue);
for (var i = 0; i < items.length; i++) {
listBox.addItem(items[i]);
if (items[1] == selectedValue) {
listBox.setSelectedIndex(i);
}
}
return listBox;
}
function makeButton(app, parent, name, callback) {
var button = app.createButton(name);
app.add(button);
var handler = app.createServerClickHandler(callback).addCallbackElement(parent);;
button.addClickHandler(handler);
return button;
}
function makeTextBox(app, name) {
var textArea = app.createTextArea().setWidth('100%').setHeight('200px').setId(name).setName(name);
return textArea;
}
function exportAllSheets(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
var sheetsData = {};
for (var i = 0; i < sheets.length; i++) {
var sheet = sheets[i];
var rowsData = getRowsData_(sheet, getExportOptions(e));
var sheetName = sheet.getName();
sheetsData[sheetName] = rowsData;
}
var json = makeJSON_(sheetsData, getExportOptions(e));
displayText_(json);
}
function exportSheet(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var rowsData = getRowsData_(sheet, getExportOptions(e));
var json = makeJSON_(rowsData, getExportOptions(e));
displayText_(json);
}
function getExportOptions(e) {
var options = {};
options.language = e && e.parameter.language || DEFAULT_LANGUAGE;
options.format = e && e.parameter.format || DEFAULT_FORMAT;
options.structure = e && e.parameter.structure || DEFAULT_STRUCTURE;
var cache = CacheService.getPublicCache();
cache.put('language', options.language);
cache.put('format', options.format);
cache.put('structure', options.structure);
Logger.log(options);
return options;
}
function makeJSON_(object, options) {
if (options.format == FORMAT_PRETTY) {
var jsonString = JSON.stringify(object, null, 4);
} else if (options.format == FORMAT_MULTILINE) {
var jsonString = Utilities.jsonStringify(object);
jsonString = jsonString.replace(/},/gi, '},\n');
jsonString = prettyJSON.replace(/":\[{"/gi, '":\n[{"');
jsonString = prettyJSON.replace(/}\],/gi, '}],\n');
} else {
var jsonString = Utilities.jsonStringify(object);
}
if (options.language == LANGUAGE_PYTHON) {
// add unicode markers
jsonString = jsonString.replace(/"([a-zA-Z]*)":\s+"/gi, '"$1": u"');
}
return jsonString;
}
function displayText_(text) {
var output = HtmlService.createHtmlOutput("<textarea style='width:100%;' rows='20'>" + text + "</textarea>");
output.setWidth(400)
output.setHeight(300);
SpreadsheetApp.getUi()
.showModalDialog(output, 'Exported JSON');
}
// getRowsData iterates row by row in the input range and returns an array of objects.
// Each object contains all the data for a given row, indexed by its normalized column name.
// Arguments:
// - sheet: the sheet object that contains the data to be processed
// - range: the exact range of cells where the data is stored
// - columnHeadersRowIndex: specifies the row number where the column names are stored.
// This argument is optional and it defaults to the row immediately above range;
// Returns an Array of objects.
function getRowsData_(sheet, options) {
var headersRange = sheet.getRange(1, 1, sheet.getFrozenRows(), sheet.getMaxColumns());
var headers = headersRange.getValues()[0];
var dataRange = sheet.getRange(sheet.getFrozenRows()+1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
var objects = getObjects_(dataRange.getValues(), normalizeHeaders_(headers));
if (options.structure == STRUCTURE_HASH) {
var objectsById = {};
objects.forEach(function(object) {
objectsById[object.id] = object;
});
return objectsById;
} else {
return objects;
}
}
// getColumnsData iterates column by column in the input range and returns an array of objects.
// Each object contains all the data for a given column, indexed by its normalized row name.
// Arguments:
// - sheet: the sheet object that contains the data to be processed
// - range: the exact range of cells where the data is stored
// - rowHeadersColumnIndex: specifies the column number where the row names are stored.
// This argument is optional and it defaults to the column immediately left of the range;
// Returns an Array of objects.
function getColumnsData_(sheet, range, rowHeadersColumnIndex) {
rowHeadersColumnIndex = rowHeadersColumnIndex || range.getColumnIndex() - 1;
var headersTmp = sheet.getRange(range.getRow(), rowHeadersColumnIndex, range.getNumRows(), 1).getValues();
var headers = normalizeHeaders_(arrayTranspose_(headersTmp)[0]);
return getObjects(arrayTranspose_(range.getValues()), headers);
}
// For every row of data in data, generates an object that contains the data. Names of
// object fields are defined in keys.
// Arguments:
// - data: JavaScript 2d array
// - keys: Array of Strings that define the property names for the objects to create
function getObjects_(data, keys) {
var objects = [];
for (var i = 0; i < data.length; ++i) {
var object = {};
var hasData = false;
for (var j = 0; j < data[i].length; ++j) {
var cellData = data[i][j];
if (isCellEmpty_(cellData)) {
continue;
}
object[keys[j]] = cellData;
hasData = true;
}
if (hasData) {
objects.push(object);
}
}
return objects;
}
// Returns an Array of normalized Strings.
// Arguments:
// - headers: Array of Strings to normalize
function normalizeHeaders_(headers) {
var keys = [];
for (var i = 0; i < headers.length; ++i) {
var key = normalizeHeader_(headers[i]);
if (key.length > 0) {
keys.push(key);
}
}
return keys;
}
// Normalizes a string, by removing all alphanumeric characters and using mixed case
// to separate words. The output will always start with a lower case letter.
// This function is designed to produce JavaScript object property names.
// Arguments:
// - header: string to normalize
// Examples:
// "First Name" -> "firstName"
// "Market Cap (millions) -> "marketCapMillions
// "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"
function normalizeHeader_(header) {
var key = "";
var upperCase = false;
for (var i = 0; i < header.length; ++i) {
var letter = header[i];
if (letter == " " && key.length > 0) {
upperCase = true;
continue;
}
if (!isAlnum_(letter)) {
continue;
}
if (key.length == 0 && isDigit_(letter)) {
continue; // first character must be a letter
}
if (upperCase) {
upperCase = false;
key += letter.toUpperCase();
} else {
key += letter.toLowerCase();
}
}
return key;
}
// Returns true if the cell where cellData was read from is empty.
// Arguments:
// - cellData: string
function isCellEmpty_(cellData) {
return typeof(cellData) == "string" && cellData == "";
}
// Returns true if the character char is alphabetical, false otherwise.
function isAlnum_(char) {
return char >= 'A' && char <= 'Z' ||
char >= 'a' && char <= 'z' ||
isDigit_(char);
}
// Returns true if the character char is a digit, false otherwise.
function isDigit_(char) {
return char >= '0' && char <= '9';
}
// Given a JavaScript 2d Array, this function returns the transposed table.
// Arguments:
// - data: JavaScript 2d Array
// Returns a JavaScript 2d Array
// Example: arrayTranspose([[1,2,3],[4,5,6]]) returns [[1,4],[2,5],[3,6]].
function arrayTranspose_(data) {
if (data.length == 0 || data[0].length == 0) {
return null;
}
var ret = [];
for (var i = 0; i < data[0].length; ++i) {
ret.push([]);
}
for (var i = 0; i < data.length; ++i) {
for (var j = 0; j < data[i].length; ++j) {
ret[j][i] = data[i][j];
}
}
return ret;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment