Excel Tables are a nice feature. They size automatically and they are easy to reference. Google Sheets doesn't have that and it can be difficult to find data on a sheet, where there are blank rows, or perhaps multiple tables of data in the same sheet. This is a particular problem for add-ons such as Chord Snip, Sankey Snip and Dicers which have to make a best guess as to where the important data on a sheet is, and to react to changes in position or shape dynamically and automatically. I'll be implementing this technique in each of those add-ons shortly.
This can be used on both server and client side.
If you're looking for the new feature for tolerating a certain amount of blankness in a table, it's written up at the end of this post.
sheet.getDataRange() is fine if you have one table starting at row 1 column 1, but lets say you have a mess like this on a sheet, and you want to separate all this into discrete data tables.
This post is about how to easily do that, to select the data from the various table, and to dynamically track a table as it resizes or moves.
The code is in the cUseful library. Here's the key, and it's also on github
In its simplest form, findTableBlocks works like this. It will analyze all the blocks of data it can find on a sheet and return where they are. It also selects one of the blocks as the one you probably want, and gives you the values and other info on what's there.
Here's what an App using this can do. In this example, I have an onEdit function which continually assesses the sheet and changes the formatting for each block of data. It picks one of the blocks as the likely table (more on how to tweak that later), and maintains the sheet like this as data is modified by the user. You can see that it has highlighted each table, and selected the one named 'a wide table' as the one you probably want.
The code for doing this is super simple...
Let's look in a little more detail at the result of calling findTablesBlocks(values)
Of course you can examine the blocks and decide for yourself which is the most tableworthy, but you can also provide some options to have it done automatically, the code below will select the 3rd block as the most tableworthy.
The mode can be 'cells' or 'position'. cells uses the number of cells in the block, and position the position of the block in the sheet. The rank is used to say which one to pick. A rank of 0 means pick the table in the last position, or the one with the most cells, so the default is equivalent to, this - which will pick the biggest table.
I've added a new feature to be able to include tables with some blank rows and columns.
Let's say you have a sheet like this.
By default, findTableBlocks will consider this as 4 separate tables, and will return this as the selected table description when called like this
However it is possible that blank rows and columns have been inserted for formatting purposes, and that this should instead be considered as a single table.The options rowTolerance and columnTolerance can be used to specified how many blank rows and columns can be tolerated before a table is considered to be a separate entity. This tolerance only applies to embedded blank rows and columns. Leading and trailing blank rows and columns are never included in a table, regardless of the row and column tolerance settings. Here's how to deal with tables with some embedded blank rows and columns.
The cUseful library contains this and many other things posted around this site.
Here's the key, and it's also on github
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Google Apps Scripts snippets >