A Script to Simplify Creating a SO Table

Question

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:

enter image description here

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>

enter image description here

Version 2

Leave a Comment Cancel reply