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> HTML:
I use datatables.net's jquery plugin and they have a process for allowing you to alter the content before it's shown. I prefer to do it on the server but I do have some cases where I've used javascript and it works fine. Have you trialled that plugin?
basically i dont really want to edit it server side because i will port that data to several sites, and then i want to use a code in one column to show or hide only certain data on different pages....
working now thanks to @m_hutley over at sitepoint forums <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: B10 $("#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> Code (markup):
Sorry I'm late to the party.. but DAMN... and people wonder why I say jQuery is fat bloated halfwitted incompetent TRASH!?!? -- nothing like using as much if not more code than needed whilst crapping out outmoded innerHTML methodology, pointless onload crap, and a host of other 'hurr durrz eyes cans has teh intarwebsies" development mental midgetry! Rather than hard coding your elements for colouration and removal one at a time in post processing, I would modify the JSON parsing script to not only do it on the fly, but to include the means to pass it MULTIPLE values for colouration! Then we kick jQuery to the curb and actually bother using the DOM to build the table... whilst at it might not be a bad idea to make sure your TH also have the proper SCOPE applied to them. <!DOCTYPE html><html><head><meta charset="utf-8"> </head><body> <div id="data"></div> <script> var googleSheetParser = { appendToId : 'data', colorValues : { 'B5' : 'blue', 'H6' : 'green', 'C12' : 'red' }, excludeRowsContaining : [ 'E9' ] }; (function(d) { // SIF to isolate scope and reduce call overhead to document function make(tagName, parent, content) { var e = d.createElement(tagName); if (content) e.appendChild(d.createTextNode(content)); if (parent) { if ('object' !== typeof parent) parent = d.getElementById(parent); parent.appendChild(e); } return e; } googleSheetParser.parse = function(data) { var table = make('table', this.appendToId), thead = make('thead', table), tbody = make('tbody', table), colWidth = 0, tr; for (var i = 0, entry; entry = data.feed.entry[i]; i++) { var cell = entry['gs$cell']; if (cell.col == 1) tr = make('tr', i == 0 ? thead : tbody); if (tr) { if (this.excludeRowsContaining.indexOf(cell['$t']) !== -1) { tr.parentNode.removeChild(tr); tr = false; } else { var value = cell['$t']; cellElement = make( cell.col == 1 || cell.row == 1 ? 'th' : 'td', tr, value ); if (cell.row == 1) cellElement.scope = 'col'; else if (cell.col == 1) cellElement.scope = 'row'; if (this.colorValues[value]) cellElement.style.color = this.colorValues[value]; } } } } })(document); </script> <script src="https://spreadsheets.google.com/feeds/cells/1LM353z3Q8EeYVC2Lpxta8p4U4QBP_ih8vzERA9dh_D4/1/public/values?alt=json-in-script&callback=googleSheetParser.parse"></script> <!-- At this point you could actually change the values of appendToId, colorValues, and excludeRowsContaining to call this again to insert another sheet into another element with new rules. --> </body></html> Code (markup): Might look like more code -- it isn't, it's just verbose, since if we minify JUST the SIF... var googleSheetParser = { appendToId : 'data', colorValues : { 'B5' : 'blue', 'H6' : 'green', 'C12' : 'red' }, excludeRowsContaining : [ 'E9' ] }; (function(f){function c(d,a,c){d=f.createElement(d);c&&d.appendChild(f.createTextNode(c));a&&("object"!==typeof a&&(a=f.getElementById(a)),a.appendChild(d));return d}googleSheetParser.parse=function(d){var a=c("table",this.appendToId),f=c("thead",a);a=c("tbody",a);for(var e,g=0,b;b=d.feed.entry[g];g++)if(b=b.gs$cell,1==b.col&&(e=c("tr",0==g?f:a)),e)if(-1!==this.excludeRowsContaining.indexOf(b.$t))e.parentNode.removeChild(e),e=!1;else{var h=b.$t;cellElement=c(1==b.col||1==b.row?"th":"td",e,h);1==b.row? cellElement.scope="col":1==b.col&&(cellElement.scope="row");this.colorValues[h]&&(cellElement.style.color=this.colorValues[h])}}})(document); Code (markup): It's 817 bytes.. that 'working' version from your last post is 1006 bytes to do the same thing... well apart from the fact this can trap multiple conditions quickly and easily, works directly on the DOM instead of derping things out as markup, properly fills in the SCOPE attributes, properly builds separate thead and tbody, etc, etc... But yeah, tell me again how jQuery made that 'simpler' or 'easier' for you. Note, if this were for actual production use, I'd be passing classes for the TD's that match the data, and not style.color. Say WHY it's getting colour then control that stuff from the stylesheet. Also those rows that only have one TH may need to be trapped to not have scope but instead have all the following rows point to it for headers="", but that's getting a bit complicated for something that -- since this is entirely scripting built -- is already telling accessibility to go f*** itself.