Ranges. If you are a web developer, depending on the use case, the phrase can make you quiver. Read up on it if you have a chance. Ranges are somewhat obscure, especially if you want to do anything with them, for instance.... The latest project I'm working on is called "ForwardLink Protocol" I will avoid talking about that for now and just say, it is a reciprocal linking process that has to do with revisions in time and.... ranges.

The use case:

• User selects text on a page they find interesting (we'll call this the origin)
• That text is sent to their clipboard
• They then insert that text into a website they they are a contributer of (we'll call this a feeder)
• They text is smart enough to tell the origin that the feeder has cited a block of text and both the origin and feeder are notified that there is linkage between them
• A visitor to the feeder website sees a link and follows that to the origin where And this is the hard part the original block of text is highlighted with a link around it.
• Please note: The origin server is notified of the citing only after the article and copied text from the origin are created!

"So what is the problem?" you say? Four characters, "H.T.M.L". To put it lightly, all browsers modify the html the it has been sent at runtime, thus altering the original downloaded from the server. So back to ranges, how do I select a range? Embarrassingly as it may seem, I didn't have the answer.

• Position? - Nope, the page can change size depending on the browser and if the document is updated
• Object offset? - Read previous
• Text? - The user can select text between tags, partial tags, child to parent, there are no rules

So what is the solution?

Simple.... To ignore.

That is if we can sanitize the html down to just words, and do the same with the selected text ahhhhh, now we can compare them. But how? How can I strip a string of html and keep that position within an html document? That is what I thought about for a few hours... Remember jQuery.sheet? Jison to the rescue! We build a super simple parser to do the work for us. The parser can analyze words, html, and characters and return something in their place if needed, thus we don't need to change the whole document, just the parts that matter.

Here is how it works:

• the html body is analyzed into an array of words, a word is a group of characters that are either lowercase, uppercase, or numbers
• the phrase the was selected prior is analyzed in the same way that the html body was
• the html body words are then compared with the phrase words and I find the point at which they match up
• the html body is analyzed once more, this time we know where the words both start and end within the html body, and if a word is detected to be in that range it is encapsulated as the selected phrase or citation block

Success! And not only does it work well, but I haven't been able to find a place that it doesn't work, Internet Explorer, Firefox, Chrome. Now that isn't to say it doesn't have bugs, I'm sure it does, we are imperfect, but it works. Bending the rules feels so good, especially after hitting a few walls.

Check out the code for yourself

The down side to it is that an object in memory is not always the tag as text, many attributes can be changed, and once you run the analysis on the document dom object, all of this is lost. But you can't have everything I guess.

As you may or may not know, I've been working with Tiki wiki cms groupware ;), and one of the things I like to do when entering into a new project is take a fresh look at things and how they can be improved. Well recently this happen with Tiki Trackers. If you aren't familiar with the feature/concept, you basically create a place to store information, trackers have items, each item has a field, and each field can be any number values, from dates to images, to text. They are really neat, somewhat simple to explain but very complex to see in action, especially in the newest upcoming release of Tiki, version 8, which is where the "fun" begins.

So I was introduced to trackers about a year ago, and that is what started the fresh look stated at the outset. Trackers were really just designed to be used in a wiki page, sort of like an application layer found within the wiki text. Cool right? And that got me thinking, why can't we use trackers in applications? And then you'd be able to use app data in a wiki page, in a spreadsheet, or wherever. Sort of like portable plugins for tiki, not just wiki page plugins, but application plugins. So what's the problem? Well trackers weren't designed to be used in that way, at least not easily. But shouldn't they be? I mean isn't tracker/item/field the same as database/row/column? So thus started the journey... Tiki Plugable Applications... or "Tiki Apps".

The first problem I ran into was tracker storage, the way they are pushed together is very hard to reform them without querying each part of the trackers. But if you have a thousand items, and you need to query for every item, how many queries is that? Hum, lets see, 1 * 1000.... 1000 QUERIES!?!?! There had to be a better way! We need under 10 queries no matter the size.

Enter "Tracker Query Lib". So there was opposition for even creating such a beast, something that made trackers easily obtainable based on dates, item id, whatever, to a simple array that represents not only the tracker, but also the data you need, and something like this has a number of uses, auto complete, simple listings, grid view, tracker as a spreadsheet, date based reports, the list goes on. Another challenge was to use tracker and field names rather than just id, just made things easy to look at. I ended up using mysql group_concat function, which this never would have been possible without. So after much work, and steady commits it now exists in Tiki trunk (I think it actually made it into v 7 as an alpha), but it is really easy to use, and supports method chaining, let me demo:

$trklib = TikiLib::lib("trk");
TikiLib::lib("trkqry");

$myTracker = TrackerQueryLib::tracker("MyTracker")
    ->byName()
    ->query();

Or say you want to query something by date:

$trklib = TikiLib::lib("trk");
TikiLib::lib("trkqry");

$myTracker = TrackerQueryLib::tracker("MyTracker")
    ->start(strtotime("1/1/2011"))
    ->end(strtotime("12/31/2011"))
    ->byName()
    ->query();

And the returned array is like follows:

//standard query
    Array (
    [367] =>; Array //item repeats, key = itemId
        (
            [19] => 369 //item field values, key = fieldId
            [20] => 366
            [11] => internal
            [162] => Array //items list that repeats
                (
                    [0] => 176
                    [1] => Event Name
                )
        )
    )

//using byName() query
    Array (
    [367] =>; Array //item repeats, key = itemId
        (
            [FieldName1] => 369 //item field values, key = fieldId
            [FieldName2] => 366
            [FieldName3] => internal
            [FieldName4] => Array //items list that repeats
                (
                    [0] => 176
                    [1] => Event Name
                )
        )
    )

The arrays are a representation of the tracker. Easy to understand and use, like it should be. There are a bunch of other selectors you can use to narrow your query, it is understood that returning an entire tracker may not be in your websites best interest, but it really makes things much more friendly to the developer. What is even more exciting is that even though rendering tracker values does take some time, you can turn off render and see how fast the trackers query lib is, I don't have any numbers for you on that one as of this moment, but it is fast, perhaps we can visit that in another post.

I last left you in somewhat suspense with jQuery.sheet and the new parser... We are now at v2 RC 2, and leading getting closer to v2.

Get it here

What can you expect in v2?
Well, the new parser is at a point now where we can say it is done (but what is done in development?). I've migrated nearly all the events over to jQuery event types, called on the parent object, I will have the documentation updated shortly. But more than that I've had a chance not only to refine jQuery.sheet, but also it's premier integration... Found in Tiki wiki cms groupware, which is soon to be version 8 (where you will see these features in existence). In v2 I've really tried to work out many of the bugs for real world systems so that it is... usable ;).

Anyway, give it a download, and have fun with it!

Since I last brought up jQuery.sheet I was nearing the end of creating the new calculation engine. Well it is done. And working great I might add, and althought there will always be things that need to be added, changed, improved, it is very usable. In-fact, since we created the spreadsheet finance functions, we can basically copy and paste directly from a real spreadsheet many of the values, and jQuery.sheet parses them just fine. Mind you that the old syntax (which basically just javascript) still works fine, but will be on its way out to have a more unified feel.

BUT, I wanted to bring up an new and interesting feature that is in the works. parser.php. Yup, you guessed it, a SERVER side language parser for sheet. You see, JISON, the Bison implementation in Javascript has been progressing well, and since php and javascript are so similar, I thought, "Hey, lets write a parser for php!".

What does this mean for users? Imagine gaining power over HUGE spreadsheets, all of which can now be parsed at the server, rather than on the user side. So you can effectivley parse entire spreadsheets without even loading them up.

How does that help me? Well, imagine you have a widget on your webpage that runs through a bunch of formulas in a spreadsheet, that returns a great deal of math (SUM, AVG, PI*PI + RAND ;)... I know if it just math) and it gives you your bottom line for the month, now you can get all of that information, run it through the spreadsheet, and... perhaps return a chart based on that data. Without even openeing the spreadsheet... ;), and with nearly no calculation delay.

This is future stuff, but is starting now, so, in short... it is very exciting to see this type of thing happen.

With the translation of the parser into php comes a new variety of features that span outside of jQuery.sheet. For example, JISON is basically injected with a few arrays that tell the core what to do, the php parser will do the same thing. So lets say you want to create your own language, JISON takes your language and it is then pushed into JISON's core and you get the end result, the ability to parse your language user side. After creating the php version, we will be able to do the same thing, inject the core with your language and you have your language, but parsed at the server.... COOL!

From here we are fixing sheet bugs, and very shortly jQuery.sheet v2 will be released at the same time that Tiki wiki v7 will be released. I can't wait!

jQuery.s5.. What is it? Slideshows to put it simple. Easy to create, easy to manipulate, easy, easy, easy, and slick!

Recently the guys at TIki (more specifically Marc Laporte ) came to me to build a new slideshow system not only for Tiki, but for simple html markup in general.

The work was based around JQS5, which sounding similar is a different project. The goal was to build what had been created in JQS5, but make it even... well simpler.

We started out with the intention of making a solid integration on a new project, one with some vigor. Along the way we thought of a few things that a bit lacking in even complex applications, like powerpoint.

• Multi screen support
  • Why? Well, because you don't want to share all your secrets on the big projector, you don't want to give away all your secrets.
  • How? Open up jQuery.s5 in your favorit browser, then in the menu bar at the bottom, select notes. The notes will then allow you to see what you don't want to show everone else.
• Timing
  • Why? Because it is everything.
  • How? In the notes view you won't only see.. well, notes, but also, when playing, a complete countdown of the show, your current timing on the current slide, and the overall time.
• Muli-menu navigation
  • Why? Manitaining focus isn't easy, and well, this isn't difficult to do.
  • How? In notes view, use the menu to navigate around just as you would at the bottom of the regular slide view, simple.
• jQuery UI Themeroller Support
  • Why? Who wants to design their own slide, we know what we want, a slick view, with pics on one side, and text on the other.
  • How? Well this one is a Tiki only feature, but can be easily replicated with theme support.

So after everything is said and done, you get something like this:
jQuery.s5 Demo Showing Off Tiki Wiki CMS Groupware

But what is nice about jQuery.s5 + Tiki 7, which is going to be out soon and coordinated with the v1 release of jQuery.s5 (or Tiki Slideshow for both projects working together) is you can apply very simple wiki syntax to regular text, and then click slideshow at the bottom of the page. You don't even have to design it, it just is a slideshow.

Pretty cool huh?

Well, the last time I posted I was just brimming with new ideas, and now those ideas have materialized, or, uh, well I mean they exist . The new calculations engine is now about 99% done, with a few bugs that are being taken care of in due time. Here what can be expected:

• Flexible
• Fast
• Value safe
• Feature rich "this" owner
• 100% safe environment to create new functions -example

var mySheet = jQuery('#myObj').sheet({calculations: {MYFUNCTION: function() {}}});
  jQuery.sheet.fn.MyOtherFunction = function() {};

Another big problem that we cleared up with some ease was that of referencing the cell from the function. You may think to yourself "But why would I need that", well lookup functions are a start, and then there are inputs, which need to preserve their value after calculations... the list goes on, the thing is, we needed it and now you can interact with the formula function's owner (or cell) by use of "this" inside a function.
Example:

var mySheet = jQuery('#myObj').sheet({calculations: {MYFUNCTION: function() {
    return this.sheet + ',' + this.row + ',' + this.col;
  }});

Cool right? The list of accessible will be made when v2.0.0 is released, but this new type of functionality will really create new ways of extending your data, your way.

If you done any research on web based spreadsheets, you've most likely seen TrimPath Spreadsheet - which is actually where jQuery.sheet started. jQuery.sheet has changed time and time again, and now is a totally different animal, at least all but 1 part.

One of the strengths and weaknesses of how TrimPath Spreadsheet operates is how it calculates cell formulas. The "engine" (which is called the "calculationsEngine" or "cE" in jQuery.sheet) uses the "eval" feature in javascript to parse cell values, and it works well, but presents some security issues. This is something I've done hours and hours of research on, but touches a part of javascript that is so unused, that information on it is rather scarce.

So for jQuery.sheet v2, we wanted to eliminate eval. What are the possible methods? I first tried some very intricate Regex, but to no avail. In a spreadsheet, some cell values are built from formulas, and those values can be built from formulas.

=SUM(SUM(SUM(SUM(SUM(SUM(A1) + A2) + A3))))

With this crude example, we see that there are many parentheses, both opening, and closing. Regex for javascript is dumb. It has no since of keeping track of open and closing characters. At least not in a intelligent way.

So we would need a powerful parser engine. Something that was very stable, very fast, and something that has been proven to work in the past.

I presented this question to Douglas Crockford. Doug is a great javascripter, and has been for some time. In-fact he wrote JSLint - The JavaScript Code Quality Tool.

The answer? Funny enough, was actually discovered in 1973. Before javascript's inception....

Enter: Top Down Operator Precedence

Top Down Operator Precedence, is basically a programmatic language parser. The example found here parses javascript, with javascript (I know, loop right?). Interestingly enough, after about 20 minutes of tweaking, I was about to parse formulas that will be used in jQuery.sheet.

I'm still geeking out over this way cool example of what is even possible with javascript. Now we just need to build a nestable evaluator for the parser, and we're set. When you get a change, read that article, there is some real history there, and something we could all learn a little about.

Today jQuery.sheet has reached a new level of stability and interaction that is putting it a step above the rest.

What to expect in this release:
So many code revisions! jQuery.sheet now supports multi instance, multi sheet, new SWEET charts with gRaphaël.

Much faster, many memory leaks fixed, user interface fixes. If you want to see some jQuery that will make your mouth drop, YOU'VE GOT TO SEE THIS!

• Added documentation in code for developers
• Added enduser documentation, because not everyone using it is a developer ;)
• FIXES all over the place!
• Added documentation in code for developers
• Added file jquery.sheet.advancedfn.js for advanced mathematic calculations
• added setting: "autoAddCells" - Auto add cells (enter or tab) at end of sheet
• Added a finance library for calculations
• Tons of fixes
• Udated documentation
• Better cross-browser support

Developers:
Download the plugin here
Project Home, Documentation, Everything Else
(We're still updating the website to v1.2.0, please stand by ;) )

End Users:
For those of us that don't want to integrate jQuery.sheet, but do want to use it, Tiki Wiki CMS Groupware has helped put together an excellent spreadsheet system based around jQuery.sheet.
Download it here
Documentation

In the weeks and months to come, if you have any questions, I would love to answer them through the Project Forum if possible.

What to expect for the future of jQuery.sheet:
There are many new features that are in the works for jQuery.sheet.

• A new calculation engine, with stored types (we'll get rid of those pesky problems with cell values like: "555-555-5555", which it thinks is math)
• Better cell-bars with handles
• Lockable cells
• Draggable cells
• Copy/Past cells

New Developments:
Recently I've started developing with the team at Tiki Wiki CMS Groupware, to bring a tighter integration of jQuery.sheet to their fine product. One of the features we are working toward is Spreadsheet Trackers.
In Tiki, a Tracker is basically a user-side database. A database that can be edited from the website user interface... Sound familar? They are like spreadsheets, but they can be skinned, and they don't handle formulas (at least not yet ;) ).
By making them load inside a spreadsheet, we can start to do all kinds of cool things. For instance, imagine you have an ecommerce website driven by Tiki & Trackers, and you want to list all relevant orders made during the month... You can write this report without hiring a DBA by using a simple web based spreadsheet - jQuery.sheet ;). In addition, all of this information will be searchable, a rare feature for spreadsheets. This eliminates the need for duplicate information, which is what you would run into if you used Google's or Microsoft's Spreadsheet products.

Thanks for your support and interest in jQuery.sheet - The web based spreadsheet plugin.

Robert Plummer
Developer - Visual Interop Development llc.

So many code revisions! jQuery.sheet now supports multi instance, multi sheet, new SWEET charts with gRaphaël. Much faster, many memory leaks fixed, user interface fixes. If you want to see some jQuery that will make your mouth drop, YOU'VE GOT TO SEE THIS!

Get it here: Incorrect parameter fileId

Here are some more updates:

• Added documentation in code for developers
• Added enduser documentation, because not everyone using it is a developer ;)
• FIXES all over the place!
• Added documentation in code for developers
• Added
• added setting: "autoAddCells" - Auto add cells (enter or tab) at end of sheet
• added setting: "minSize" - Makes sheets always start at a certain size, default -minSize: {rows: 10: cols: 10} added.
• added setting: "caseInsensitive" - Makes formulas case insensitive formulas like =sum(a1) or =SUM(A1)
• added setting: "autoFiller" - Adds the little corner at the bottom right of cells so you can drag values around
• added setting: "calculations" - So you can have your own calculations
• added setting: "fnClose" - called on sheet close
• added setting: "fnAfterCellEdit" - called just after cell edit
• added setting: "fnSwitchSheet" - called when a spreadsheet tab is clicked and a new sheet is visible
• added setting: "fnPaneScroll" - called when a spreadsheet is being scrolled

Charts, you love them, you hate them. They seem to really add something to a presentation though when you use the right information in them. The charting with jQuery.sheet has been nice, but lacked that certain something that makes it practical, that makes it "POP". Starting today jQuery.sheet drops support for jGcharts and now uses gRaphaël to build them entirely.

Demo

Raphaël—JavaScript Library
gRaphaël—Charting JavaScript Library