Table Utility

This is a utility to assist users in creating tables in Stack Overflow Markdown. In addition, it also allows the user to redact private information within those ranges.

Google Apps Script Code

redact.gs

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

redacttable.html

<!DOCTYPE html>

<html>

<head>

<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>

<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 /><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>

<br /><input type="text" id="rs" name="redactstring" size="15" />Redact String

</form>

$(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>

</head>

<body>

$(function(){

google.script.run

.withSuccessHandler((robj)=>{

$("#tsv").val(robj.tsv);

$("#rngs").val(robj.rngs);

}).redactableDataTable(obj);

});

</script>

</body>

</html>

The Menu and Command

function onOpen() {

menu();

}

function menu() {

SpreadsheetApp.getUi().createMenu('My Tools')

.addItem('Redactable Table','showRedactTableDialog')

.addToUi();

}