A Script to Simplify Creating a SO Table
Solution 1:
This code allows you to copy data from your spreadsheet, redact it, align each column independently and then post it in to SO with the appropriate markdown to make a nice looking table.
The Code:
redact.gs:
function onOpen() {
menu();
}
function menu() {
SpreadsheetApp.getUi().createMenu('My Tools')
.addItem('Authenticate','authenticate')
.addItem('Redactable Table','showRedactTableDialog')
.addToUi();
}
function authenticate() {
//no nothing
}
function getCSVDataRange() {
const ss=SpreadsheetApp.getActive();
const sh=ss.getActiveSheet();
const rg=sh.getActiveRange();
const cols=rg.getWidth();
const datarange=rg.getA1Notation();
return {datarange:datarange,columns:cols};
}
function getRedactRangeList() {
const ss=SpreadsheetApp.getActive();
const sh=ss.getActiveSheet();
const rgA=sh.getActiveRangeList().getRanges();
const redactrange=rgA.map(function(rg,i){return rg.getA1Notation();}).join(',');
return {redactrange:redactrange};
}
function showRedactTableDialog() {
var userInterface=HtmlService.createHtmlOutputFromFile('redacttable').setWidth(400).setHeight(200);
const h=userInterface.getHeight();
const w=userInterface.getWidth();
const title='Redactable Data Table';
userInterface.append(Utilities.formatString('<div id="dim">w:%s,h:%s</div>',w,h));
SpreadsheetApp.getUi().showModelessDialog(userInterface, title);
}
function getPresets() {
return {datarange:'',redactrange:'',delimiter:',',redactstring:'Redacted'};
}
function getTablePresets() {
return {datarange:'',redactrange:'',align:'c',redactstring:'Redacted',aligntext:""};
}
function testrdtable() {
redactableDataTable({"redactrange":"","cols":"3","col":"3","align":"l","aligntext":"rrr","datarange":"A1:C4","redactstring":"Redacted"})
}
function redactableDataTable(obj) {
Logger.log(JSON.stringify(obj));
const {datarange,redactrange,redactstring,align,aligntext}=obj;
const ss=SpreadsheetApp.getActive();
const sh=ss.getActiveSheet();
const drg=sh.getRange(datarange);
const vA=drg.getValues();
//new parameters
const dlm='|';
const dlmrow={l:':---',c:':---:',r:'---:'};
const aline=(aligntext.length>0)?aligntext:align;
if(redactrange) {
const rgA1=redactrange.split(',');
//Logger.log(rgA1);
const rgA=rgA1.map(function(A1,i){
return sh.getRange(A1);
});
const rowStart=drg.getRow();
const colStart=drg.getColumn();
//const rowEnd=drg.getRow()+drg.getHeight()-1;
//const colEnd=drg.getColumn()+drg.getWidth()-1;
rgA.forEach(function(rg,k){
var v=rg.getDisplayValues();
let row=rg.getRow();
let col=rg.getColumn();
v.forEach(function(r,i){
r.forEach(function(c,j){
vA[row-rowStart+i][col-colStart+j]=redactstring;//redact string
});
});
});
}
var tsv='';
var hdr=[vA.shift()];
//header row
hdr.forEach(function(r,i){tsv+=dlm;r.forEach(function(c,j){if(j>0)tsv+=dlm;tsv+=c;});tsv+=dlm;});
tsv+='\r\n';
//delimiter row
hdr.forEach(function(r,i){tsv+=dlm;r.forEach(function(c,j){if(j>0)tsv+=dlm;tsv+=dlmrow[aline[j%aline.length]];});tsv+=dlm;});
tsv+='\r\n';
//data table
vA.forEach(function(r,i){if(i>0){tsv+='\r\n';}tsv+=dlm;r.forEach(function(c,j){if(j>0){tsv+=dlm;}tsv+=c;});tsv+=dlm;});
let s=`Data:${datarange} - Redact:${redactrange}`;
var html=Utilities.formatString('<body><input type="button" value="Exit" onClick="google.script.host.close();" /><br /><textarea rows="1" cols="150" id="rngs">%s</textarea><br /><textarea rows="30" cols="150" id="tsv">%s</textarea></body>',s,tsv);
html+='<br /><input type="button" value="Exit" onClick="google.script.host.close();" />';
console.log(html);
var ui=HtmlService.createHtmlOutput(html).setWidth(1200);
SpreadsheetApp.getUi().showModelessDialog(ui, 'Table Markdown');
}
html code:
redacttable.hmtl:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
<style>
select,
input {
margin: 2px 5px 2px 0;
font-size: 12px;
}
#cols {
margin: 2px 5px 2px 5px;
font-size: 12px;
}
.bold {
font-weight: "bold";
}
</style>
</head>
<body>
<form name="form">
<br /><input type="text" id="dtrg" name="datarange" placeholder="Select Data Range" size="20" readonly /><input type="button" value="Data" onClick="getDataRange();" title="Select Data Range." /><input type="text" id="cols" name="cols" size="2" readonly />Cols
<br /><input type="text" id="rdrg" name="redactrange" placeholder="Select Redact Ranges" size="20" readonly /><input type="button" value="Redact" onClick="getRedactRangelist();" title="Select Redact Rangelist." />
<br /><span class="bold">Alignment</span>
<br /><select name="align"><option value="l">left</option><option value="c">center</option><option value="r">right</option></select>
<input type="text" id="alntxt" name="aligntext" placeholder="Align all columns with r,c,or l only" size="25" oninput="getLength();" /><input type="text" name="col" id="col" size="2" readonly />
<br /><input type="text" id="rs" name="redactstring" size="15" />Redact String
<br /><input type="button" value="Submit" onClick="processForm(this.parentNode);" />
</form>
<script>
$(function(){
google.script.run
.withSuccessHandler(function(obj){
if(obj.datarange) {$('#dtrg').val(obj.datarange);}
if(obj.redactrange) {$('#rdrg').val(obj.redactrange);}
if(obj.align) {$('#aln').val(obj.align);}
if(obj.redactstring) {$('#rs').val(obj.redactstring);}
if(obj.aligntext){$('$alntxt').val(obj.aligntext);}
})
.getTablePresets();
});
function getLength() {
let s=$('#alntxt').val();
let all='rlc';
if(!all.includes(s[s.length-1])){
$('#alntxt').val(s.slice(0,-1));
}
$('#col').val($('#alntxt').val().length);
}
function getDataRange() {
google.script.run
.withSuccessHandler(function(obj){
$('#dtrg').val(obj.datarange);
$('#cols').val(obj.columns);
})
.getCSVDataRange();
}
function getRedactRangelist() {
google.script.run
.withSuccessHandler(function(obj){
$('#rdrg').val(obj.redactrange);
})
.getRedactRangeList();
}
function processForm(form) {
google.script.run.redactableDataTable(form);
}
console.log('My Code');
</script>
</body>
</html>
tableMarkdown.html:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>';
</head>
<body>
<input type="button" value="Exit" onClick="google.script.host.close();" /><br />
<textarea rows="1" cols="150" id="rngs"></textarea><br /><textarea rows="30" cols="150" id="tsv"></textarea>
<br /><input type="button" value="Exit" onClick="google.script.host.close();" />
<script>
$(function(){
google.script.run
.withSuccessHandler((robj)=>{
$("#tsv").val(robj.tsv);
$("#rngs").val(robj.rngs);
}).redactableDataTable(obj);
});
</script>
</body>
</html>
This script is also available here: https://sites.google.com/view/googlappsscript/table-utility
Demo:
