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:

More Related Contents:

Leave a Comment Cancel reply