GS-Calc 23 coming soon

GS-Calc GS-Calc Updates
1

GS-Calc 23 adds and significantly improves compatibility with
Google Sheets along with several new functions, improves data
exchange with Excel 365 using the *.ods format and includes numerous
changes/fixes concerning that file format.

  • GS-Calc 23 let you use (read/write) Google Sheet (as *.ods)
    templates/workbooks directly for most files preserving exactly
    how they look and work. Some specific files might require minor
    adjustments as outlined below in the “tips” section.

  • The Google Sheet SPARKLINE() function has been added.
    In addition to its default parameters:
    SPARKLINE - Google Docs Editors Help
    GS-Calc adds also the “interval” and “axisstyle” parameters so the Google “Monthly budget” template can look like this after adding them to that template:

1236×808 54.1 KB

  • A significant number of changes/fixes have been added to saving/loading charts using the Open Document *.ods format to ensure compatibility with Sheets.

  • The AVERAGEIF() functions has been added.
    Although all the older versions already include the AVERAGEIFS() function the former ensures better compatibility with Sheets.

  • The HSTACK() and VSTACK() functions have been added.
    HSTACK function - Google Docs Editors Help

  • The IFERROR() function have been added.

  • The UNIQUE() function has been modified to ensure compatibility with Google Sheets: (1) the default one now returns the unique values (not indices), (2) the empty cells are included in the results (and counted if the corresponding parameters is specified), (3) it includes previously omitted time optimization if the entire 32-million-cell column is specified as a parameter.

  • Functions returning array/matrices function return empty cells for those input cells that were already empty to ensure compatibility with Google Sheets (when e.g. the IFBLANK() function is used to check the results).

  • Saving Excel formulas in the *.ods files have been improved to ensure they are correctly loaded by Google Sheets and Excel 365. This also required adding an additional save option to let you specify whether a give file is being saved for Google Sheet or Excel.

466×463 9.2 KB

  • The FIND()/REPLACE() functions which already use regular expressions
    have been improved. You can now specify which capturing group (1,2 or 3)
    the FIND() function should return e.g. to easily extract folders, file
    names or file extensions from file paths.
=replace("(.)a+\d{1,3}", "abc aa0102", 1, "\1", 1)    returns 'abc 2'
=replace("(ab)", "abcdef ghijk abb123", 1, "\u\1", 1)    returns "ABcdef ghijk ABb123"

To find/extract the extension:

find("(\.[^.]+)$", "c:\file.txt", 1, 32+SEARCH::RegExStr)

To find/extract a folder from a file path:

=find("^(.*[\\\/])?(\.*.*?)(\.[^.]+?|)$", "c:\folder1\file.txt", 1, 32+SEARCH::RegExStr)
=find("^(.*[\\\/])?(\.*.*?)(\.[^.]+?|)$", "c:\folder1\file.txt", 1, 32+SEARCH::RegExStr)

To find/extract the file name without the extension and path:

=find("^(.*[\\\/])?(\.*.*?)(\.[^.]+?|)$", "c:\file.txt", 1, 64+SEARCH::RegExStr)

To remove duplicated words in field1:

=replace("\b(\w+)(?:\W+\1\b)+", field1, 1, "\1", 1)

To create abbreviations consisting of first letters of words:

=replace("(\b\w)(\w*(\W+|\z))", field1, 1, "\1", 1)

To create abbreviations consisting of first letters of words leaving full numbers:

=replace("(\b\w(\d*))(\w*(\W+|\z))", field1, 1, "\1", 1)

To mask IP addresses partially (e.g. 11.12.13.114 to 11.12.13.*):

=replace("\b((\d{1,3}\.){3,3})\d{1,3}\b", field1, 1, "\1\*", 1)

Tips

The "Monthly Budget" Google Sheet template uses the SPARKLINE()
function with a cell embedded in an array string:

=SPARKLINE(D17,{"charttype","column";"ymin", 0; "ymax",MAX(D17:E17);"firstcolor","#334960"})

GS-Calc requires array string to be constant like:

=SPARKLINE(D17,{"charttype","column";"ymin", 0; "ymax",2000;"firstcolor","#334960"})

so you can either enter a constant value, specify this list entirely as a reference to a sheet range: =SPARKLINE(D17,F1:G4) or use the VSTACK()/HSTACK() functions.
----
Formulas returning arrays/matrices saved by Google Sheets omit the
table:number-matrix-columns-spanned and table:number-matrix-rows-spanned
attributes that are used to denote spilling formulas.
After opening such files in GS-Calc, for example the last “Summary” sheet in
the “Annual Budget” Google template, you need to update (e.g. F9)
it first then delete all cells from the “spilled” areas below/after the
formulas returning the #FILL error (and let GS-Calc re-create them automatically).

983×565 40.5 KB

--