A Script to Simplify Creating a SO Table

Question

How to create a markdown table from Google sheets?

Wicket · Accepted Answer · 2025-03-12 14:21:22Z

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>

TheMaster · Accepted Answer · 2025-03-12 02:27:52Z

Here's a simple web app to create markdown.

Click "Run Snippet" and
Just copy data from Google sheets and paste in the top text area:

 <!DOCTYPE html>
<html>
  <head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width" />
    <style>
      textarea {
        width: 100%;
        height: 40vh;
      }
    </style>
    <title>1</title>
  </head>
  <body>
    <label
      >Paste Google sheets copied content here(at least 2 rows):
      <textarea
        id="input"
        placeholder="
    Category    Date    Amount
    Interest    1/1/2025    $5,000.00
    Tax 2/2/2025    $3,500.00"
      ></textarea>
    </label>
    <label>
      Output
      <textarea
        id="output"
        placeholder="
    Category|Date|Amount
    |-|-|-
    Interest|1/1/2025|$5,000.00
    Tax|2/2/2025|$3,500.00"
      ></textarea>
    </label>

    <script>
      const inputArea = document.querySelector('#input');
      inputArea.addEventListener('paste', handlePaste);
      inputArea.addEventListener('change', handleChange);
      async function handlePaste(e) {
        try {
          handleChange(e, e.clipboardData.getData('text/plain'));
        } catch (e) {}
      }
      async function handleChange(e, data = inputArea.value) {
        const num_cols = data.match(/(.*?\n)/)[0].split('\t').length;
        const o = data
          .replace(/^/gm, '|')
          .replace('\n', '\n' + '|-'.repeat(num_cols) + '\n')
          .replace(/\t/g, '|');
        document.querySelector('#output').value = o;
        try {
          await navigator.clipboard.writeText(o);
          alert('Data copied to clipboard!');
        } catch (e) {
          alert('Markdown generated. Copy from output below');
        }
      }
    </script>
  </body>
</html>

For eg, Google sheets data copy pasted above

Category    Date    Amount
Interest        $5,000.00
Tax 2/2/2025

will become

|Category|Date|Amount
|-|-|-
|Interest||$5,000.00
|Tax|2/2/2025|

which will render as

Category	Date	Amount
Interest		$5,000.00
Tax	2/2/2025

Make sure to paste the table after 2 empty lines for it to render properly.

Collectives™ on Stack Overflow

A Script to Simplify Creating a SO Table

2 Answers 2

Version 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Version 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related