GS-Calc full changelog

[+] New functions:

playSoundIf(path, type, if_condition, loops, volume)
====================================================
Plays mp3 and wav files. The parameters are as follows:

path:
Specifies the path of the file to play; if 'type' is 1, 'path'
is assumed to be an external disk file, for example:
e:music_file1.mp3
If 'type' is 0, 'path' is assumed to be a full worksheet path
(in the current workbook) of a file inserted with
the 'Insert > File' command, for example:
folder1music_file1.mp3
music_file2.wav

type:
Determines how to interpret the 'path' parameters as explained
above.

if_condition:
A given file is played only if the 'if_condition' evaluates
to a non-zero value.

loops:
Specifies the number of times a given file is to be played
in a loop;
if it's -1, it'll be played in an endless loop.

volume:
Specifies the volume for the played *.mp3 file. This can
be a value from 0 to 1000.

Music files are played immediately after all the formulas
are evaluated/updated
and the updating process is completed.
Thus you can use such a function with 'if_condition' set to a fixed
value of 1 to signal the end of updating.

If there are multiple files to play, there are played one after
another.
To stop playing, simply press ESC or use
the 'Tools > Cancel Update' command (even though updating at this
point updating is already successfully completed.

=playSoundIf("e:music_file1.mp3", 1, true, 1, 40)
=playSoundIf("folder1music_file1.mp3", 0, true, 100, 80)


messageIf(message, type, if_condition, reference-as-text)
=========================================================

Displays a message box with the specified 'message', the 'OK'
and 'Cancel' buttons.

message:
Any text to display.

type:
1 - a message box with 'information' icon;
2 - a message box with the warning icon;
3 - the question icon;

if_condition:
A given message is played only if 'if_condition' evaluates
to a non-zero value.

reference (as text):
A text string representing an optional cell/range reference with
a full worksheet path; if the parameter is not empty and if you
click the 'OK' button in the displayed message box, GS-Calc will
jump/scroll to the specified cell/range.

Messages are displayed immediately after all the formulas are
evaluated/updated and the updating process is completed.
Thus you can use such a function with 'if_condition' set to a fixed
value of 1 to signal the end of updating.

=messageIf("need to inspect this cell", 1, isError(folder2sheet2!P251), "folder2sheet2!P251")
=messageIf("need to inspect this cells", 1, isError(S120), "S121:S200")


imageIf(if_condition, path_true, path_false, type)
==================================================

Displays the specified image in a cell (overflowing the cell
boundaries if necessary). The parameters are as follows:

if_condition:
If 'if_condition' evaluates to a non-zero value, the 'path_true'
image is displayed, otherwise the 'path_false'.

path_true / path_false:
Specify the image file paths. If 'type' is 1, 'path_true' and
'path_false' are assumed to be external disk files, for example:
e:music_file1.png
if 'type' is 0, 'path_true' and 'path_false' are assumed to be
names of files (in the current workbook) inserted with
the 'Insert > Image' command, for example:
image_file1.jpg
image_file2.gif
The list of currently inserted images can be managed either
by clicking the Set Cell Background Image button on the toolbar
or by clicking the Image button in the Cell Style Properties
dialog box.

type:
Determines how to interpret the 'path_true' and 'path_false'
parameters as explained above.

Images are displayed/re-drawn immediately after all the formulas are
evaluated/updated and the updating process is completed.

=imageIf(P251 > 0, "e:image_file1.png", "e:image_file2.png", 1)
=imageIf(S10:S120 = 0, "music_file1.jpg", "image_file2.gif", 0)


[+] If you use the sumIf() or countIf() functions for large data sets,
by default they will now use fast binary searching for ranges
specified as columns that include at least 16384 rows. For such
ranges the functions will behave VLOOKUP/MATCH functions in the
binary fast mode.
You can change that row limit in the saved settings.xml
configuration file modifying the if-calc-treshold value. If you set
some value larger than the maximum number of rows, it'll
effectively turn off fast binary searching for these two functions.
The typical location of this file is:
C:Usersyour-nameAppDataLocalGS-Calc
If you choose a portable setup, the file will be in the folder you
specify as the target setup folder for all files.

[+] A number of help sections have been updated or added:

Entering formulas
https://citadel5.com/help/gscalc/formulas_enter.htm
Solver functions
https://citadel5.com/help/gscalc/solver_functions.htm
Ensuring high performance
https://citadel5.com/help/gscalc/performance.htm
Opening and saving files: Text files
https://citadel5.com/help/gscalc/text_files.htm
Output functions
https://citadel5.com/help/gscalc/output_functions.htm

[+] The number of rows has been increased to 32 millions (33,554,432).
[+] Text/CSV loading times has been decreased by 30-40%.
[+] More options to split text/CSV files with more than 32 million rows to multiple sheets.
[+] Auto-calculations for multiple selections

Conditional cell formatting - the setFormatIf(reference, action, if_condition, format_true, format_false) formula

Formats a given cell or a range of cells. The numeric action parameter specifies which formatting attributes to set or clear (restore defaults).

Usually, it's convenient to keep all the formatting functions in a separate worksheet. A single cell selection/value can control the appearances of other worksheets.

The function can be applied both to existing cells and empty regions without any previous formatting (and in this scenario the 1st update can be slightly slower).
There are no limitations for the number or complexity concerning this function. You can use millions of conditionally formatted cells or regions same as any other formulas and nested IFs.

If the reference argument specifies entire columns or rows, the formatting action will be applied respectively to column and row styles. The formatting layers are as follows: a table style < column styles < row styles < cell styles (and optionally colors defined in custom style patterns). Cell styles overwrite all other styles.

The format_true and format_false parameters be -1 to restore the default cell state for a given attribute or the following:

1 - numeric style (style index relative to the toolbar style list or the name)
2 - custom style pattern (custom style pattern like $#,##0.00)
3 - font size (4 to 256)
4 - font name (string)
6 - bold font (0 | 1)
7 - italic font (0 | 1)
8 - underline font (0 | 1)
9 - strikeout font (0 | 1)
11 - font color (RGB numeric value or a RGB string like "#FFEE00" or color name like "green")
14 - horizontal alignment (0 - left, 1 - center, 2 - right)
15 - vertical alignment (0 - top, 1 - center, 2 - bottom)
16 - wrap text (0 | 1)
17 - horizontal indent (a number of pixels 0 to 64)
18 - vertical indent (a number of pixels 0 to 64)
19 - text rotation (a value in degrees between -180 to 180)
20 - cell borders (a string "position width style color" like "bottom 1px solid blue" or "all 1pt dotted #FF0000" or "dotted red")
21 - borders around selection (a string "width style color" like "1px solid blue" or "thin solid #FF0000" or "dotted red")
22 - cell background color (RGB numeric value or a RGB string like "#FFEE00" or color name like "green")
25 - don't print / print cells (0 | 1)
26 - hyperlink (0 | 1)
27 - cell with filters used by the filter() formula (0 | 1)
28 - cell with image lists (identified by built-in names, like "star start star-half") (0 | 1)
29 - area in-cell chart (with the data series in cells pointed by the cell alignment, e.g. to the left) (0 | 1)
30 - column in-cell chart (0 | 1)
31 - bar in-cell chart (0 | 1)
32 - apply all 1-31 attributes at once as a named custom style (strings specified for custom styles in the "Format < Custom Cell Styles" dialog box
The 32nd action type sets all possible cell formatting/style attributes at once. Custom cell styles are added to a workbook with the Format < Custom Cell Styles dialog box and are saved in a workbook. Custom cell styles in a workbook can be saved to or loaded from a global GS-Calc cache using the Save As Default App Styles and Load Default App Styles commands in the Custom Cell Styles dialog box.

* instantly mass-modify/add/remove functions in formulas or some
parameters/arguments used by functions in large workbooks without
the risk of making mistakes in long formulas,

* replace existing functions with your own ones,

* replace some Excel-specific functions from the Excel latest version
with GS-Calc functions,

* converting formulas from any spreadsheet software using
its own extensions, for example replace in xlsx files some recently
added Excel functions that are not implement in e.g. Excel 2019,

* Changes concerning the xlsx support. Formulas are read and saved without conversions.
Output xlsx workbook files can exceed 4GB if the underlying zip format is left as zip64.

* The GS-Base *.gsb file is supported directly and the *.gsb files are loaded as data
table collections and save in the simple GS-Base file format.

* The file formats list in the "Open File" dialog box has been reorganized in
accordance with the above.

* The "Recently Used Files and Folders" window now includes dates in the "Recent Folders"
section. They show when a given folder was accessed for the last time.

* Bug fixes.

A new list of recently used files and folders displayed
as a separate split window. It displays file sizes, modification
dates and states.
You can assign one of many available built-in icons to each file
or folder.
Both lists (of files and folders) can include up to 50 items each
and new items are added in a cyclic manner. Both lists can
be locked to prevent any new additions or re-ordering.

SQLite *.db file format support.
MySQL *.sql file format support.
New scripting functions for the above file formats.
Reporting if an opened text file exceeded 12 millions rows or 16K/1M columns.

[+] The number of columns has been increased from 4K to 16,384.
[+] You can load text files with up to 1 million columns.
GS-Calc will automatically create multiple sheets based
on the maximum number of columns per sheet (that you can freely
change to anything from 1 to 16,384).
[+] By default, if you choose
the "Text | Text Archive (*.txt, *.csv, *tsv, *.zip)" filter in
the "Save File" dialog box to save a given *.gsc workbook as text,
only the current sheet is saved as a standalone text file.
If you want to save all sheets at once, you need to specify a file
name with the *.zip extension. This will tell GS-Base to save all
created text files to one ZIP64 text archive (along with a short
XML file that specifies used text parameters so you won't have to
re-enter them when opening that ZIP64 file in the future).
When saving ZIP text archives the options to split sheets based on
the number of rows is inactive.

New scripting methods, for example, for mass-merging records and tables from gsc/text/CSV/Excel xlsx files.

In-cell charts. You can create virtually unlimited number of in-cell mini-charts with a single click.