Scripting: samples

Registering scripting interfaces

To be able to use scripting, you must register the scripting interfaces in the Windows registry database. To do this, run GS-Calc as an administrator (e.g. right-click GS-Calc shortcut/icon and on the context menu choose "Run as administrator") and use the Settings > Register Scripting Interfaces command.
Similarly, if you no longer need scripting, you can use the Settings > Unregister Scripting Interfaces command to remove all registry entries added earlier. Uninstalling GS-Calc will unregister those interfaces as well.

Creating scripts

You can create your scripts either as global scripts saved in the program settings and available to all databases or you can create scripts stored in a given GS-Calc workbook and available only after you open that workbook.
To create these scripts use the "File > Application Scripts" and "File > Database Scripts" commands.

The "(...) Scripts" dialog box enables you to organize your scripts in subfolders, test them to locate errors, copy/import/export them etc.

Sample "Scripts" screen.

Notes:

To add the following sample scripts to your GS-Calc, click File > Application Scripts, then in the displayed dialog box click New > JScript and copy/paste the script text - it'll be saved automatically in your global program settings.
To execute the added scripts, open that dialog box and click Run or assign some Ctrl+Shift+... shortcut to execute it using the keyboard.

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


var wbook = GSCalc.ThisWorkbook();

var merge = GSCalc.CreateMergeParams();
merge.path = "e:\\my_folder\\some_text_file??.txt";
// or e.g. merge.path = "e:\\my_folder\\some_text_file*.txt";
// or e.g. merge.path = "e:\\my_folder\\some_text_file_01.txt";
// or e.g. merge.path = "e:\\my_folder\\*.csv";
// or e.g. merge.path = "e:\\my_folder\\";
// or e.g. merge.path = GSCalc.GetFolder("Find folder", "e:\\");
// or e.g. merge.path = GSCalc.GetFilePath(false, "Merge from text file", "e:\\", "d:\\my_folder", ".csv")
merge.table = "";
merge.fieldNames = false;

var text = GSCalc.CreateTextParams();
text.separator = ",";
text.encoding = "utf8";
// or text.encoding = "windows";
text.autoFitColumns = false;

wbook.MergeRowsFromTextFile(merge, text);

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


var wbook = GSCalc.ThisWorkbook();

var merge = GSCalc.CreateMergeParams();
merge.path = "e:\\my_folder\\some_text_file??.xlsx";
// or e.g. merge.path = "e:\\my_folder\\some_text_file*.xlsx";
// or e.g. merge.path = "e:\\my_folder\\some_text_file_01.xlsx";
// or e.g. merge.path = "e:\\my_folder\\*.xlsx";
// or e.g. merge.path = "e:\\my_folder\\";
// or e.g. merge.path = GSCalc.GetFolder("Find folder", "e:\\");
// or e.g. merge.path = GSCalc.GetFilePath(false, "Merge from text file", "e:\\", "d:\\my_folder", ".xlsx")

// no table name - merge rows from the default worksheet in each XLSX file
merge.table = "";
merge.fieldNames = 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


var wbook = GSCalc.ThisWorkbook();

var text = GSCalc.CreateTextParams();
text.separator = ",";
text.encoding = "utf8";
// or text.encoding = "windows";
text.autoFitColumns = false;

wbook.MergeTableFromTextFile("e:\\my_folder\\some_text_file??.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


var wbook = GSCalc.ThisWorkbook();

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


var wbook = GSCalc.ThisWorkbook();

var filePath = wbook.Release();
GSCalc.MessageBox("Closed file: " + filePath, "ok", 1, "information");

// You can keep on editing the workbook as it's already loaded. You have to use the Attach() method to open/connect it again
// if you want to save it or otherwise the "Save File As" message box will be displayed.

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


var wbook = GSCalc.ThisWorkbook();

if (wbook.AttachFile("") == 1)
{
   // 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
   wbook.Reload();
}

// After checking and reloading you can release it again
wbook.Release();

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

var wbook = GSCalc.ThisWorkbook();

// get the first "child" element in the specified folder - in this case: the main folder 
// if path.length = 0 then there is no more worksheets or subfolders
var path = wbook.GetFirstTreeItem("\\");

// iterate through direct "child" elements of the specified folder (ignoring nested subfolders)
// to find the 10th worksheet
var counter = 0;

while (path.length)
{
	if (path.charAt(path.length - 1) == '\\')
	{
		// the terminating '\\' means it's a folder
		GSCalc.MessageBox("folder - " + path, "ok", 1, "information");
	}
	else
	{
		// worksheet
		if (++counter == 10)
		{
			break;
		}
	}
	path = GSBase.GetNextDatabaseItem(path);
}

if (path.length)
{
	// activate the 10th worksheet
	wbook.SetActiveWorksheet(path);
}

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

var wbook = GSCalc.ThisWorkbook();

var folders = new Array(10);
var i = 0;

var treeItem = wbook.GetFirstTreeItem("");

do
{
	if ( wbook.IsFolder(treeItem) )
	{
		GSCalc.MessageBox("Folder: " + treeItem, "ok", 1, "information");
		if ( !wbook.IsFolderEmpty(treeItem) )
		{
			folders[i++] = treeItem;
			treeItem = wbook.GetFirstTreeItem(treeItem);
			continue;
		}
	}
	else
	if ( wbook.IsWorksheet(treeItem) )
	{
		GSCalc.MessageBox("Worksheet: " + treeItem, "ok", 1, "information");
	}

	treeItem = wbook.GetNextTreeItem(treeItem);
	if ( treeItem == "" && i > 0 )
	{
		treeItem = wbook.GetNextTreeItem(folders[--i]);
	}
} while ( treeItem );

Editing worksheets

try
{
	var wbook = GSCalc.OpenWorkbook("d:\\sample.gsc", "");
	var wsheet = wbook.GetActiveWorksheet();

	wbook.SelectTreeItem("formulas");

	// display amount in "b9" as thousands of dollars

	var formatParams = wsheet.CreateFormatParams();
	formatParams.SetCurrencyFormat(0, "$ 1.1", "$", false, false, 1);

	wsheet.selectedRange = "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.InsertData("b10", "=fv(0.65%, 36, -500, -5500, 0)", true);
	wbook.WaitForUpdate();

	// repeat the formatting

	wsheet.selectedRange = "b10";
	wsheet.SetCellFormat(formatParams);

	// save all as a new encrypted file, specifying no path to display the "Save" dialog box

	wbook.SetFilePassword(true, "blowfish", "", "dk3zPi");
	wbook.SaveAs("");
}
catch(e)
{
	GSCalc.MessageBox(e, "ok", 1, "error");
}

Adding worksheets and folders

try
{
	// delete the "sample4" worksheet from the "2d charts" folder

	var wbook = GSCalc.OpenWorkbook("d:\\sample.gsc", "");
	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();
}
catch(e)
{
	GSCalc.MessageBox(e, "ok", 1, "error");
}

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

var textParams = GSCalc.CreateTextParams();
textParams.separator = ",";
textParams.quotingSymbol = "\"";
textParams.encoding = "utf8";
var wbook = GSCalc.OpenTextFile("d:\\test456.txt", false, textParams);
wbook.SaveAs("d:\\test456.gsc");