I recently wanted a simple variable to display a bar chart for many different tables. Because the tables could have a variable number of rows and columns, I used the following code to build up a totals table so the chart could simply grab the x-axis from row 1 and the y-axis from row 2.

%CALC{$SET(vbar,|)}% %CALC{$WHILE($counter <= $LISTSIZE($LIST(R1:C2..R1:C999)),$SETHASH(totals,$T(R1:C$INT($counter+1)),$SUM(R2:C$INT($counter+1)..R999:C$INT($counter+1))))}% %TABLE{name="TotalsTable"}%
| %CALC{$LISTJOIN($GET(vbar),$HASH2LIST(totals,$key))}% |
| %CALC{$LISTJOIN($GET(vbar),$HASH2LIST(totals,$value))}% |

One problem is that I want to put this code in a variable so it can be reused, but when reused on the same page the hash contains items from the last table. I can't figure out how to clear the hash set or use a randomized name.

-- Michael Sprague - 2013-04-06

You do not need to specify the exact last row number for the ChartPlugin, any arbitrary large number will do. The ChartPlugin will use up to the actual last row.

You can clear a hash using $SETHASH(name). See SpreadSheetPlugin.

On numbering, use either a random, such as $INT($RAND(1000)), or set a counter and increment it, such as $SET(tablenr, 0), then $SETM(tablenr, +1) before each table.

-- Peter Thoeny - 2013-04-06

Thanks. That fixes my problem with the HASH. However, if I have a table of indeterminate rows and columns, can I get a range for the last table row with R999:C1..R999:C999?

-- Michael Sprague - 2013-04-07

Michael: Use $ROW() after a table to get the number of rows of the preceding table. Use $COLUMN() after a table to get the number of columns of the preceding table. I just enhanced the plugin for the latter feature. Wave TWiki is updated as well.

-- Peter Thoeny - 2013-04-08

The SpreadSheetPlugin has been updated several times since this blog post:

• Allow newlines and indent around functions and function parameters, which makes complex functions much more readable
• Allow newlines in triple-quoted strings
• Add FORMAT(CURRENY, ...) with support for currency symbol
• Add ADDLIST(), GETLIST(), SETLIST()
• Fix VALUE function bug with incorrect exponential number
• Ability to delete SET() variable
• Do not strip trailing spaces in SETIFEMPTY()
• Fix plural of month to months in FORMATTIMEDIFF()
• TRANSLATE() supporting double quote and apostrophe-quote (single quote) escape tokens
• Add short and compact format to FORMATTIMEDIFF()

-- Peter Thoeny - 2014-10-03

The latest SpreadSheetPlugin has a RANDOMSTRING() function, can be used to generate random passwords and license keys. With this and the new ExecutePlugin you can use TWiki as the GUI for license management.

-- Peter Thoeny - 2014-10-24