Skip to content

Instantly share code, notes, and snippets.

@cwhittl

cwhittl/Code.gs

Last active July 25, 2018 17:13
Show Gist options
  • Save cwhittl/ee21ca67803e26899f7462c2bc0feaf4 to your computer and use it in GitHub Desktop.
Save cwhittl/ee21ca67803e26899f7462c2bc0feaf4 to your computer and use it in GitHub Desktop.
Download ZIP
Google Spreadsheet Convert to JSON
Raw
Code.gs
function doGet(e) {
var book = SpreadsheetApp.getActiveSpreadsheet();
var masterSheet = book.getSheetByName("New_072018");
// var json = convertSheet2Json(masterSheet);
var colStartIndex = 1;
var rowNum = 1;
var firstRange = masterSheet.getRange(1, 1, 1, masterSheet.getLastColumn());
var firstRowValues = firstRange.getValues();
var titleColumns = firstRowValues[0];
// after the second line(data)
var lastRow = masterSheet.getLastRow();
var ui = SpreadsheetApp.getUi();
var rowValues = [];
for(var rowIndex=2; rowIndex<=lastRow; rowIndex++) {
var colStartIndex = 1;
var rowNum = 1;
var range = masterSheet.getRange(rowIndex, colStartIndex, rowNum, masterSheet.getLastColumn());
var values = range.getValues();
rowValues.push(values[0]);
}
// create json
var json = [];
for(var i=0; i<rowValues.length; i++) {
var line = rowValues[i];
var jsontemp = new Object();
for(var j=0; j<titleColumns.length; j++) {
jsontemp[titleColumns[j]] = line[j];
}
json.push(jsontemp);
}
var t = HtmlService.createTemplateFromFile('index');
t.data = JSON.stringify(json);
var html = t.evaluate();
html.setTitle("Generated JSON here");
html.setWidth(500);
SpreadsheetApp.getUi().showSidebar(html);
}
function onOpen() {
SpreadsheetApp.getUi()
.createMenu("RC Stuff")
.addSeparator()
.addItem("Generate JSON", "doGet")
.addToUi();
}
Raw
index.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/highlight.js/9.6.0/styles/monokai-sublime.min.css">
<link rel="stylesheet" href="https://clipboardjs.com/bower_components/primer-css/css/primer.css">
<script src="https://cdnjs.cloudflare.com/ajax/libs/highlight.js/9.6.0/highlight.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/clipboard.js/1.5.12/clipboard.min.js"></script>
<script>
hljs.initHighlightingOnLoad();
</script>
</head>
<body>
<h3> Generated JSON</h3>
<div style="padding: 10px;margin-bottom:25px">
<button id="clipboardbtn" class="btn" data-clipboard-target="#foo">
<img class="clippy" src="https://clipboardjs.com/assets/images/clippy.svg" width="13" alt="Copy to clipboard">
</button><span style="margin-left: 5px">Click on the button to copy</span>
</div>
<pre><code class="json" id="foo"><?= data ?></code></pre>
<script>
//Tooltip functions
var btn = document.getElementById("clipboardbtn");btn.addEventListener('mouseleave',function(e){e.currentTarget.setAttribute('class','btn');e.currentTarget.removeAttribute('aria-label');});
function showTooltip(elem,msg){elem.setAttribute('class','btn tooltipped tooltipped-s');elem.setAttribute('aria-label',msg);}
function fallbackMessage(action){var actionMsg='';var actionKey=(action==='cut'?'X':'C');if(/iPhone|iPad/i.test(navigator.userAgent)){actionMsg='No support :(';}
else if(/Mac/i.test(navigator.userAgent)){actionMsg='Press ⌘-'+ actionKey+' to '+ action;}
else{actionMsg='Press Ctrl-'+ actionKey+' to '+ action;}
return actionMsg;
}
//Clipboard definition and success/error handlers
var clipboard = new Clipboard('#clipboardbtn');
clipboard.on('success',function(e){
//e.clearSelection();
showTooltip(e.trigger,'Copied!');});
clipboard.on('error',function(e){
showTooltip(e.trigger,fallbackMessage(e.action));});
</script>
</body>
</html>
@cwhittl
Copy link
Author

cwhittl commented Jul 21, 2018

Taken and updated from http://www.thesnoopybub.com/blog/google-spreadhseet-to-json/

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment