A Script to Simplify Creating a SO Table
How to create a markdown table from Google sheets?
Solution
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:
Version 2
This version auto generates Column Letters and Row numbers which I find are useful in situations where you have no headers in your data because it helps to provide a frame for understanding where the table is located. Anyway play with it. It's not hard to figure out.
GS:
function showRedactTableDialog() {
var userInterface = HtmlService.createHtmlOutputFromFile('redacttable').setWidth(400).setHeight(250);
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 redactableDataTable(obj) {
Logger.log(JSON.stringify(obj));
const { datarange, redactrange, headers, rows, redactstring, align, aligntext } = obj;
const ss = SpreadsheetApp.getActive();
const sh = ss.getActiveSheet();
const drg = sh.getRange(datarange);
const vA = drg.getDisplayValues().map((r => {
r.forEach(c => {
c.replace(/\r\n/, ' ')
});
return r;
}));
//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
});
});
});
}
Logger.log(headers);
if (headers && headers.length > 0) {
vA.unshift(headers.split(','));
}
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';
if(rows && rows.length > 0) {
tsv = dlm + tsv + dlm + ':---:';
}
//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
if(rows && rows.length > 0) {
let rA = rows.split(",");
vA.forEach(function (r, i) { if (i > 0) { tsv += '\r\n'; } tsv += dlm + rA[i] + dlm; r.forEach(function (c, j) { if (j > 0) { tsv += dlm; } tsv += c; }); tsv += dlm; });
} else {
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(800);
SpreadsheetApp.getUi().showModelessDialog(ui, 'Table Markdown');
}
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>
<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 /><input type="text" id= "hdrs" name="headers" placeholder="Enter Column Header Separated by Comma" size="30" /><input type="button" id="autocol" value="auto" onClick="autoCols();" />
<br /><input type="text" id= "rows" name="rows" placeholder="Enter Row Numbers Separated by Comma" size="30" /><input type="button" id="autorow" value="auto" onClick="autoRows();" />
<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);
}
function autoCols() {
let s = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
google.script.run
.withSuccessHandler((obj) => {
console.log(JSON.stringify(obj));
let hs = s.slice(obj.col - 1,obj.col + obj.width -1);
document.getElementById("hdrs").value = hs.split("").join(',');
})
.getUpperLeft();
}
function autoRows() {
google.script.run
.withSuccessHandler((obj) => {
let rs = obj.row;
document.getElementById("rows").value = Array.from(new Array(obj.height).keys(),x => x + rs).join(",");
})
.getUpperLeft();
}
console.log('My Code');
</script>
</body>
</html>
- Sum a number of range values; process and add to array
- Fill Google Form from Other Google Form Responses
- Downloading a PDF version of the open spreadsheet in Google Drive via scripts
- exporting spreadsheet to pdf then saving the file in google drive
- Convert a Markdown text file into a Google Document
- A Script to Simplify Creating a SO Table
- Archive a row after a dropdown status is selected on multiple sheets
- Get Google Sheet by ID?
- Google Sheets macro to copy a line of data to next empty row on another sheet
- Google Sheets - Auto border to a Dynamic Data
- Error 'Action Not Allowed' When Copying Content with a Vertical Line in Google Docs
- Remove an attachment of a Gmail email with Google Apps Script
- Google Apps Script switching html files
- How can I determine if a respondent's email address is unique in a list?
- How to make a simple authentication in Google Forms using Google apps script?
- Can a google-apps-script be written to download a csv file from spreadsheet directly to a local hard drive with an onEdit trigger?
- API call to directory.members.insert failed with error: Resource Not Found: groupKey
- Apps Script Delete Image
- DO NOT send notifications when share a file
- App Script to download specific cells in Google Sheet to PDF
- Export Google Docs comments into Google Sheets, along with highlighted text?
- Add users to Google Group using Google Apps Script
- How to force auto complete in app script editor for ambiguous variables?
- Dynamic Library Import for Maps JavaScript API not working in Apps script
- Error copying 2d array contents to Google Sheets column
- Auto-Resize column in Google Sheet
- autoResizeColumns Takes Too Long to Process
- autoResizeColumn works incorrectly not works as expected
- How to parse JSON response in Google App Script?
- Create an average line for Google Charts