Scripting Samples

1. Enabling Scripting

To use Python scripting, you must have the Python package installed. Enabling Python scripting requires the same configuration steps as using Python UDF functions and formulas. Please refer to Installing Python and integrating it with GS-Calc for detailed instructions.

2. Creating Scripts

You can create scripts as global scripts, which are saved in the program settings and available to all workbooks, or as local scripts stored within a specific GS-Calc workbook, which are only available when that workbook is open.

To create these scripts, use the File > Application Scripts or File > Database Scripts commands.

The "Scripts" dialog box allows you to organize your scripts into subfolders, as well as copy, import, or export them.

View a sample "Scripts" dialog screen.

Notes:

If a global script is executed while no workbook is open, it should begin with one of the application Open(...) or New(..) functions.
If a workbook is already loaded, an executed script (whether global or local) automatically refers to that active workbook. Using the Open(...) functions with the same workbook path will have no effect.
Backslashes used in script text, primarily within file path parameters, must always be doubled. For example: f:\\my_folder\\file01.gsc.

3. Sample Index

Merging rows from multiple text files from a given folder to the current worksheet

import sys
from ctypes import *
CDLL("gsc2python39.dll")   # Python 3.9.13
import gscalc

wbook = gscalc.ActiveWorkbook();

merge = gscalc.CreateMergeParams();
merge.setPath("e:\\sample??.txt")
# or e.g. merge.setPath("e:\\my_folder\\some_text_file*.txt")
# or e.g. merge.setPath("e:\\my_folder\\some_text_file_01.txt")
# or e.g. merge.setPath("e:\\my_folder\\*.csv")
# or e.g. merge.setPath("e:\\my_folder\\")
# or e.g. merge.setPath(gscalc.GetFolder("Find folder", "e:\\"))
# or e.g. merge.setPath(gscalc.GetFilePath(false, "Merge from text file", "e:\\", "d:\\my_folder", ".csv"))
merge.setTable("")
merge.setFieldNames(False);

text = gscalc.CreateTextParams();
text.setSeparator(",")
text.setEncoding("utf8");

wbook.MergeRowsFromTextFile(merge, text);

Merging rows from multiple Excel XLSX files from a given folder to the current worksheet

import sys
from ctypes import *
CDLL("gsc2python39.dll")   # Python 3.9.13
import gscalc

wbook = gscalc.ActiveWorkbook();

var merge = gscalc.CreateMergeParams();
merge.setPath("e:\\my_folder\\some_text_file??.xlsx")

# no table name - merge rows from the default worksheet in each XLSX file
merge.setTable("")
merge.setFieldNames(False)

wbook.MergeRowsFromExcelFile(merge);
# or e.g. wbook.MergeRowsFromMySQLFile(merge);
# or e.g. wbook.MergeRowsFromFile(merge);

Importing tables from multiple text files from a given folder to the current workbook

wbook = gscalc.ActiveWorkbook();
wsheet = wbook.ActiveWorksheet();

text = gscalc.CreateTextParams();
text.setSeparator("|")
text.setEncoding("utf8");

wbook.MergeTableFromTextFile("e:\\sample??.txt", "", text);
# or e.g. wbook.MergeTableFromTextFile("e:\\my_folder\\some_text_file*.txt", "", text);
# or e.g. wbook.MergeTableFromTextFile("e:\\my_folder\\some_text_file_01.txt", "", text);
# or e.g. wbook.MergeTableFromTextFile("e:\\my_folder\\*.csv", "", text);
# or e.g. wbook.MergeTableFromTextFile("e:\\my_folder\\", "", text);
# or e.g. wbook.MergeTableFromTextFile("e:\\my_folder\\some_text_file??.txt", "", text);
# or e.g. wbook.MergeTableFromTextFile(gscalc.GetFolder("Find folder", "e:\\"), "", text);
# or e.g. wbook.MergeTableFromTextFile(gscalc.GetFilePath(false, "Merge from text file", "e:\\", "d:\\my_folder", ".csv"), "", text);

Importing tables from multiple Excel XLSX files from a given folder to the current workbook

wbook = gscalc.ActiveWorkbook();

wbook.MergeTableFromExcelFile("e:\\my_folder\\some_text_file??.xlsx", "");
# or e.g. wbook.MergeTableFromExcelFile("e:\\my_folder\\some_text_file*.xlsx", "");
# or e.g. wbook.MergeTableFromExcelFile("e:\\my_folder\\some_text_file_01.xlsx", "");
# or e.g. wbook.MergeTableFromExcelFile("e:\\my_folder\\*.csv", "");
# or e.g. wbook.MergeTableFromExcelFile("e:\\my_folder\\", "");
# or e.g. wbook.MergeTableFromExcelFile("e:\\my_folder\\some_text_file??.xlsx", "");
# or e.g. wbook.MergeTableFromExcelFile(gscalc.GetFolder("Find folder", "e:\\"), "");
# or e.g. wbook.MergeTableFromExcelFile(gscalc.GetFilePath(false, "Merge from Excel XLSX file", "e:\\", "d:\\my_folder", ".xlsx"), "");

Releasing open workbook (gsc/xlsx/txt/csv...) files to allow other programs to update them at the same time

You can continue editing the workbook after releasing it because it is already loaded into memory. However, you must use the AttachFile() method to reconnect the file before saving; otherwise, the "Save File As" dialog will be displayed.

wbook = gscalc.ActiveWorkbook()
wsheet = wbook.ActiveWorksheet()

filePath = wbook.ReleaseFile();
gscalc.MessageBox("Closed file: " + filePath, "ok", 1, "information");

Attaching a released workbook file (gsc/xlsx/txt/csv...) to check for changes and enable saving that file

wbook = gscalc.ActiveWorkbook();
wsheet = wbook.ActiveWorksheet();

if wbook.AttachFile("") == 1 :
   wbook.Reload()
   # An empty parameter (file path) indicates that we're attaching a file from the recent ReleaseFile().
   # AttachFile() returns 1 if the file was modified after you used ReleaseFile().
   # In that case, you might want to reload it to see the changes.

# After checking and reloading, you can release it again
wbook.ReleaseFile();

Activating the n-th worksheet in the current workbook (ignoring subfolders)

wbook = gscalc.ActiveWorkbook();
wsheet = wbook.ActiveWorksheet();

# Get the first "child" element in the specified folder - in this case: the main folder 
# If path.length = 0 then there are no more worksheets or subfolders
path = wbook.GetFirstTreeItem("");

counter = 0;

# Iterate through direct "child" elements of the specified folder (ignoring nested subfolders)
# to find the 3rd worksheet

while path != "" :
    if (wbook.IsFolder(path)) :
        gscalc.MessageBox("folder - " + path, "ok", 1, "information");
    else : counter = counter + 1
    if counter == 3:
        break;
    path = wbook.GetNextTreeItem(path);

if path != "" :
    # activate the 3rd worksheet
    wbook.SetActiveWorksheet(path);

Iterating through the entire tree of subfolders of the current workbook (max. 10 nesting levels)

wbook = gscalc.ActiveWorkbook();
wsheet = wbook.ActiveWorksheet();

folders = list()

treeItem = wbook.GetFirstTreeItem("");

while True :
    if wbook.IsFolder(treeItem) :
        gscalc.MessageBox("Folder: " + treeItem, "ok", 1, "information");
        if not wbook.IsFolderEmpty(treeItem) :
            folders.append(treeItem)
            treeItem = wbook.GetFirstTreeItem(treeItem)
            continue
    elif wbook.IsWorksheet(treeItem) :
        gscalc.MessageBox("Worksheet: " + treeItem, "ok", 1, "information");

    treeItem = wbook.GetNextTreeItem(treeItem);
    if treeItem == "" and len(folders) > 0 :
        treeItem = wbook.GetNextTreeItem(folders.pop());
    if treeItem == "":
        break;

Editing worksheets

wbook = gscalc.ActiveWorkbook();
wsheet = wbook.ActiveWorksheet();

wbook.SelectTreeItem("formulas");

# display amount in "b9" as thousands of dollars

formatParams = wsheet.CreateFormattingParams();
formatParams.SetCurrencyFormat("0", "$ 1.1", "$", False, False, 1);

wsheet.setSelectedRange("b9")
wsheet.SetCellFormat(formatParams);

# If the window is to be updated before the script terminates, UpdateWindow() must be called
wsheet.UpdateWindow(); 

# insert a new formula in b10
wsheet.InsertFormula("b10", "=fv(0.65%, 36, -500, -5500, 0)");
wbook.WaitForUpdate();

# repeat the formatting
wsheet.setSelectedRange("b10")
wsheet.SetCellFormat(formatParams);

# Save as a new encrypted file. Specifying no path displays the "Save" dialog box.
wbook.SetFilePassword(True, "blowfish", "", "dk3zPi");
wbook.SaveAs("");

Adding worksheets and folders

wbook = gscalc.OpenWorkbook("e:\\sample3.gsc", "dk3zPi");
wbook.SelectTreeItem("2d charts\\sample4");
wbook.DeleteTreeItem();

# insert a new "general" folder as the last folder of the tree
wbook.SelectTreeItem("");
wbook.InsertFolder("general");

# move the folder to the beginning of the tree
wbook.MoveTreeItem("general", wbook.GetFirstTreeItem(""));

# move the "formulas" and "data types" worksheets to "general"
wbook.MoveTreeItem("data types", "general");
wbook.MoveTreeItem("formulas", "general");

# insert a new worksheet as the last one in "general"
wbook.SelectTreeItem("general");
wbook.InsertWorksheet("more formulas");

wbook.Save();
wbook.Close();

Opening a text file and saving it to new *.gsc file

textParams = gscalc.CreateTextParams();
textParams.setSeparator("|")
textParams.setQuotingSymbol("\"")
textParams.setEncoding("utf8");
wbook = gscalc.OpenTextFile("e:\\sample1.txt", False, textParams);
wbook.SaveAs("d:\\some_test_file.gsc");

Reading / inserting a sparse matrix

from ctypes import *
CDLL("gsc2python39.dll")
import gscalc2

wbook = gscalc2.ActiveWorkbook();
wsheet = wbook.ActiveWorksheet()

m1, m2, m3 = wsheet.GetSparseMatrix("c4:g1000", 0)

wsheet.InsertSparseMatrix("k4", m1, m2, m3)