Parsing JSON table: hiding a row, changing color of word

Hi all,

I am using jquery to convert a google spreadsheet to JSON that produces a HTML table.

I would like to:

  • hide one of the rows that has the phrase “E9” in it.
  • change the color of the phrase “C12” to red.

I have tried many scripts, but to no avail!

Here is the code I currently have.

Thank you very much!

<html>
<head>
  <script src="https://code.jquery.com/jquery-3.1.0.js"></script>
    <script>       
      //google spreadsheet
  var spData=null;function doData(a){spData=a.feed.entry}function drawCell(b,c,a){var d=$(a?"<th/>":"<td/>");b.append(d);d.append(c);return d}function drawRow(a,e,d){if(e==null){return null}if(e.length==0){return null}var b=$("<tr/>");if(d){b.addClass("head")}a.append(b);for(var f=0;f<e.length;f++){drawCell(b,e[f],((f==0)||d))}return b}function drawTable(a){var b=$("<table/>");a.append(b);return b}function readData(b){var f=spData;var d=drawTable(b);var e=[];var h=0;for(var c=0;c<f.length;c++){var a=f[c]["gs$cell"];var g=a["$t"];if(a.col==1){drawRow(d,e,(h==1));e=[];h++}e.push(g)}drawRow(d,e,(h==1))}$(document).ready(function(){readData($("#data"))});
  
   //Hide Row based on Phrase
    $("#data tr:contains('E9')").hide ();
      
      //change color of phrase: C12
      $('table').each(function(){
   $(this).html(
        $(this).html()
       .replace(
              /(?<!-)(\bC12\b)(?!([^<]+)?>)(?!-)/ig, 
             '<span style="color:green;">$1</span>'
        )
    );
});
        
  </script>
</head>
<body>
  
<script src="https://spreadsheets.google.com/feeds/cells/1LM353z3Q8EeYVC2Lpxta8p4U4QBP_ih8vzERA9dh_D4/1/public/values?alt=json-in-script&callback=doData">
  </script>
  
  <div class="box-table" id="spreadsheet" style="overflow-x:auto!important;">
<table id="data"></table>
</div>

</body>
</html>

Could you please provide us some (minimal) data, so that we can recreate a couple of lines of the table.

As it is, the regex you are using is throwing an error:

SyntaxError: invalid regexp group

You can see this in the console (hit F12 in your browser).

I would have thought to simply match the phrase “C12” you would need to do this:

.replace(/C12/g, '<span style="color:green;">C12</span>')

but I guess there is more going on that you have not alluded to in your question.

hi @James_Hibbard
here is the jsbin : https://jsbin.com/rafaguz/edit?html,output
and here is the json: https://spreadsheets.google.com/feeds/cells/1LM353z3Q8EeYVC2Lpxta8p4U4QBP_ih8vzERA9dh_D4/1/public/values?alt=json-in-script&callback=doData

i tried to have a go with that code you had but it didn’t work

The problem is that you are trying to manipulate the table before it has had any data inserted into it.

If I was you, I would check the docs and see if Google Spreadsheets offers any kind of onload event, and if they do, hook into that (maybe that callback parameter on the URL?)

But just for fun, I made you a poor man’s version of that, which queries the table for content, then manipulates it once the content is loaded.

This should work as intended:

<!doctype html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <title>Google sheets</title>
  </head>

  <body>
    <div class="box-table" id="spreadsheet">
      <table id="data"></table>
    </div>

    <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
    <script>
      var spData=null;function doData(a){spData=a.feed.entry}function drawCell(b,c,a){var d=$(a?"<th/>":"<td/>");b.append(d);d.append(c);return d}function drawRow(a,e,d){if(e==null){return null}if(e.length==0){return null}var b=$("<tr/>");if(d){b.addClass("head")}a.append(b);for(var f=0;f<e.length;f++){drawCell(b,e[f],((f==0)||d))}return b}function drawTable(a){var b=$("<table/>");a.append(b);return b}function readData(b){var f=spData;var d=drawTable(b);var e=[];var h=0;for(var c=0;c<f.length;c++){var a=f[c]["gs$cell"];var g=a["$t"];if(a.col==1){drawRow(d,e,(h==1));e=[];h++}e.push(g)}drawRow(d,e,(h==1))}$(document).ready(function(){readData($("#data"))});
    </script>
    <script src="https://spreadsheets.google.com/feeds/cells/1LM353z3Q8EeYVC2Lpxta8p4U4QBP_ih8vzERA9dh_D4/1/public/values?alt=json-in-script&callback=doData"></script>

    <script>
      function processTable(){
        $("#data tr:contains('E9')").hide ();

        $("table#data tr").children().each(function(){
          if(this.innerText === "B10"){
            $(this).html('<span style="color:green;">B10</span>');
          }
        });
      }

      function init(){
        const i = setInterval(()=>{
          const contentIsLoaded = $("table").children().length > 0;
          if (contentIsLoaded){
            // Content has loaded
            // Kill timers and kick things off
            clearInterval(i);
            clearTimeout(s);
            processTable();
          }
        }, 50);

        // It didn't load after 5 seconds
        // Something is probably broken
        const s = setTimeout(() => {
          clearInterval(i);
        }, 5000);
      }

      init();
    </script>
  </body>
</html>

So problem #1: The sheet in question does not contain the data “C12” in any location.
Problem #2: The regex is flawed, and can be replaced entirely by /\bC12\b/.
Problem #3: Timing of the execution is off.

As stupid as it sounds, the callback is failing because the script tag that loads the sheet data comes before the data storing element is defined.

Here’s my version of your file. I have changed C12 to B10 as Pullo did to actually show a change.

<html>
<head>
  <script src="https://code.jquery.com/jquery-3.1.0.js"></script>
    <script>
var spData=null;function doData(a){spData=a.feed.entry}function drawCell(b,c,a){var d=$(a?"<th/>":"<td/>");b.append(d);d.append(c);return d}function drawRow(a,e,d){if(e==null){return null}if(e.length==0){return null}var b=$("<tr/>");if(d){b.addClass("head")}a.append(b);for(var f=0;f<e.length;f++){drawCell(b,e[f],((f==0)||d))}return b}function drawTable(a){var b=$("<table/>");a.append(b);return b}function readData(b){var f=spData;var d=drawTable(b);var e=[];var h=0;for(var c=0;c<f.length;c++){var a=f[c]["gs$cell"];var g=a["$t"];if(a.col==1){drawRow(d,e,(h==1));e=[];h++}e.push(g)}drawRow(d,e,(h==1))};
function loadAndColor(data) {
  doData(data);
  readData($("#data"));
//Hide Row based on Phrase
 $("#data tr:contains('E9')").hide();
 //change color of phrase: C12
 $("#data td:contains('B10')").each(function() { $(this).html($(this).html().replace(/\bB10\b/ig,'<span style="color:green;">B10</span>'))});
};
  </script>
</head>
<body>
  <div class="box-table" id="spreadsheet" style="overflow-x:auto!important;">
<div id="data"></div>
<script src="https://spreadsheets.google.com/feeds/cells/1LM353z3Q8EeYVC2Lpxta8p4U4QBP_ih8vzERA9dh_D4/1/public/values?alt=json-in-script&callback=loadAndColor">
  </script>
</div>
</body>
</html>

@m_hutley works perfect! silly me not realising C12 was not in the file!

thanks also @James_Hibbard who also helped.

hopefully that it is helpful to others!

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.