23415384980

Committed 22 Mar 2026 11:37PM UTC coverage: 95.227% (-0.2%) from 95.475%

Build # 23415384980

Build Type

push

github

Committed by

demmings

Commit Message

Load all sheets that match a wildcard as ONE table.

This allows multiple matching sheets (based on a sheet with wildcard character) to be loaded as one table.

For example, you have a transaction sheet which is divided up onto their own sheet for each calendar year.  If the table data input is specified as a string and a wildcard character is added so it matches all of the transaction sheets.  e.g.:

=gsSQL("Select sum(amount), year(transaction_date) from transactions where expense_category = ?1 and amount < 0 group by year(transaction_date)", "transactions", "Master Transactions*", true, "Utilities - Electricity")

So "Master Transactions*" will match my 'Master Transactions' and 'Master Transactions2019' and 'Master Transactions2020', and so on.  So when a new sheet is created for 2026, it will automatically be included in the results (as long as it matches the wildcard).

Coverage Stats

1597 of 1691 branches covered (94.44%)

Branch coverage included in aggregate %.

19 of 61 new or added lines in 2 files covered. (31.15%)

1 existing line in 1 file now uncovered.

13227 of 13876 relevant lines covered (95.32%)

280.77 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

78.23

/src/Select2Object.js

/*  *** DEBUG START ***

//  Remove comments for testing in NODE
import { GasSql } from "./Sql.js";
export { Select2Object };

//  *** DEBUG END ***/

/**
 * @classdesc - Executes a SELECT statement on sheet data.  Returned data will be any array of objects,
 * where each item is one row of data.  The property values in the object are the column names.
 * The column names will be in lower case.  If more than one table is referenced, the column name will be:
 * "table.column", otherwise it will just be the column name.  Spaces in the column name use the underscore, so
 * something like "Transaction Date" would be referenced as "transaction_date".
 */
class Select2Object {           // skipcq: JS-0128
    constructor() {
        this.tables = [];
        this.bindVariables = [];
        this.selectClass = null;
    }

    /**
     * 
     * @param {String} tableName - table name referenced in SELECT statement.
     * @param {*} data - double array or string.  If string it must reference A1 notation, named range or sheet name.
     * @returns {Select2Object}
     */
    addTableData(tableName, data) {
        const table = { tableName, data };
        this.tables.push(table);

        return this;
    }

    /**
     * If bind variables are used in SELECT statement, this are added here.
     * Ordering is important.  The first one added will be '?1' in the select, second is '?2' in select...
     * @param {any} bindVar 
     * @returns {Select2Object}
     */
    addBindVariable(bindVar) {
        this.bindVariables.push(bindVar);

        return this;
    }

    /**
     * When creating the object to be returned, rather than assign from an empty object '{}', a new instance
     * of this class is created instead.
     * @param {Object} className 
     * @returns {Select2Object}
     */
    selectToClass(className) {
        this.selectClass = className;

        return this;
    }

    /**
     * Query any sheet range using standard SQL SELECT syntax and return array of table info with column names as properties.
     * @example
     * gsSQL("select * from expenses where type = ?1")
     * 
     * @param {String} statement - SQL string 
     * @returns {Object[]} - array of object data.  
     */
    execute(statement) {     //  skipcq: JS-0128
        const parms = [];

        //  Add the table name and range.
        for (const tab of this.tables) {
            parms.push(tab.tableName, tab.data);
        }

        //  Add column output indicator.
        parms.push(true);   //  We want column names returned.

        //  Add bind data.
        for (const bind of this.bindVariables) {
            parms.push(bind);
        }

        const tableDataArray = GasSql.execute(statement, parms);

        if (tableDataArray === null || tableDataArray.length === 0) {
            return null;
        }

        //  First item in return array is an array of column names.
        const columnNames = Select2Object.cleanupColumnNames(tableDataArray[0]);

        return Select2Object.createTableObjectArray(columnNames, tableDataArray, this.selectClass);
    }

    /**
     * Return column names in lower case and remove table name when only one table.
     * @param {String[]} cols 
     * @returns {String[]}
     */
    static cleanupColumnNames(cols) {
        const newColumns = cols.map(v => v.toLowerCase());
        const noTableColumns = [];
        const duplicateFieldMap = new Map();

        //  Is the field name listed more than once.
        //  ONLY if a field is duplicated is the table name qualifier included.
        for (const col of newColumns) {
            const splitColumn = col.split(".");
            const fld = (splitColumn.length > 1) ? splitColumn[1] : splitColumn[0];
            const counter = duplicateFieldMap.has(fld) ? duplicateFieldMap.get(fld) : 0;
            duplicateFieldMap.set(fld, counter + 1);
        }

        for (const col of newColumns) {
            const splitColumn = col.split(".");
            const fld = (splitColumn.length > 1) ? splitColumn[1] : splitColumn[0];
            if (duplicateFieldMap.get(fld) > 1) {
                noTableColumns.push(col);
            }
            else  {
                noTableColumns.push(fld);
            }
        }

        return noTableColumns;
    }

    /**
     * First row MUST be column names.
     * @param {any[][]} tableDataArray 
     * @returns {Object[]}
     */
    static convertTableArrayToObjectArray(tableDataArray) {
        //  First item in return array is an array of column names.
        const propertyNames = Select2Object.convertColumnTitleToPropertyName(tableDataArray[0]);

        return Select2Object.createTableObjectArray(propertyNames, tableDataArray);
    }

    /**
     * 
     * @param {Object[]} objectArray 
     * @param {String[]} columnTitles 
     * @param {Boolean} outputTitleRow
     * @returns {any[][]}
     */
    static convertObjectArrayToTableArray(objectArray, columnTitles, outputTitleRow = true) {
        const propertyNames = Select2Object.convertColumnTitleToPropertyName(columnTitles);
        const tableArray = [];

        if (outputTitleRow)
            tableArray.push(columnTitles);

        for (const objectRow of objectArray) {
            const row = [];

            for (const prop of propertyNames) {
                row.push(objectRow[prop]);
            }

            tableArray.push(row);
        }

        return tableArray;
    }

    /**
     * 
     * @param {Object} object 
     * @param {String[]} columnTitles 
     * @returns {String[]}
     */
    static convertObjectToArray(object, columnTitles) {
        const propertyNames = Select2Object.convertColumnTitleToPropertyName(columnTitles);
        const row = [];
        for (const prop of propertyNames) {
            row.push(object[prop]);
        }

        return row;
    }

    /**
     * Convert a sheet column name into format used for property name (spaces to underscore && lowercase)
     * @param {String[]} columnTitles 
     * @returns {String[]}
     */
    static convertColumnTitleToPropertyName(columnTitles) {
        const columnNames = [...columnTitles];
        const srcColumns = columnNames.map(col => col.trim()).map(col => col.toLowerCase()).map(col => col.replaceAll(' ', '_'));

        return srcColumns;
    }

    /**
     * Get column number - starting at 1 in object.
     * @param {Object} object 
     * @param {String} columnTitle 
     * @returns {Number}
     */
    static getColumnNumber(object, columnTitle) {
        const prop = Select2Object.convertColumnTitleToPropertyName([columnTitle])[0];
        let col = 1;
        for (const propName in object) {        // skipcq: JS-0051
            if (propName === prop) {
                return col;
            }
            col++;
        }

        return -1;
    }

    /**
     * 
     * @param {String[]} columnNames 
     * @param {any[]} tableDataArray 
     * @param {Object} SelectClass
     * @returns {Object[]}
     */
    static createTableObjectArray(columnNames, tableDataArray, SelectClass=null) {
        //  Create empty table record object.
        const emptyTableRecord = Select2Object.createEmptyRecordObject(columnNames);

        //  Create table array with record data stored in an object.
        const tableData = [];
        for (let i = 1; i < tableDataArray.length; i++) {
            let newRecord = {};
            if (SelectClass !== null) {
                newRecord = new SelectClass();
            }

            Object.assign(newRecord, emptyTableRecord);

            for (const [index, col] of columnNames.entries()) {
                newRecord[col] = tableDataArray[i][index];
            }

            tableData.push(newRecord);
        }

        return tableData;
    }

    /**
     * Creates an empty object where each column name is a property in the object.
     * @param {String[]} columnNames 
     * @returns {Object}
     */
    static createEmptyRecordObject(columnNames) {
        //  Create empty table record object.
        const dataObject = {};
        for (const col of columnNames) {
            dataObject[col] = '';
        }

        dataObject.get = function (columnTitle) {
            const prop = Select2Object.convertColumnTitleToPropertyName([columnTitle])[0];
            return this[prop];
        };

        dataObject.set = function (columnTitle, value) {
            const prop = Select2Object.convertColumnTitleToPropertyName([columnTitle])[0];
            this[prop] = value;
        }

        return dataObject;
    }
}

1	/* * DEBUG START *	1✔
2		1✔
3	// Remove comments for testing in NODE	1✔
4	import { GasSql } from "./Sql.js";	1✔
5	export { Select2Object };	1✔
6		1✔
7	// * DEBUG END */	1✔
8		1✔
9	/**	1✔
10	* @classdesc - Executes a SELECT statement on sheet data. Returned data will be any array of objects,	1✔
11	* where each item is one row of data. The property values in the object are the column names.	1✔
12	* The column names will be in lower case. If more than one table is referenced, the column name will be:	1✔
13	* "table.column", otherwise it will just be the column name. Spaces in the column name use the underscore, so	1✔
14	* something like "Transaction Date" would be referenced as "transaction_date".	1✔
15	*/	1✔
16	class Select2Object { // skipcq: JS-0128	1✔
17	constructor() {	1✔
18	this.tables = [];	3✔
19	this.bindVariables = [];	3✔
20	this.selectClass = null;	3✔
21	}	3✔
22		1✔
23	/**	1✔
24	*	1✔
25	* @param {String} tableName - table name referenced in SELECT statement.	1✔
26	* @param {*} data - double array or string. If string it must reference A1 notation, named range or sheet name.	1✔
27	* @returns {Select2Object}	1✔
28	*/	1✔
29	addTableData(tableName, data) {	1✔
30	const table = { tableName, data };	5✔
31	this.tables.push(table);	5✔
32		5✔
33	return this;	5✔
34	}	5✔
35		1✔
36	/**	1✔
37	* If bind variables are used in SELECT statement, this are added here.	1✔
38	* Ordering is important. The first one added will be '?1' in the select, second is '?2' in select...	1✔
39	* @param {any} bindVar	1✔
40	* @returns {Select2Object}	1✔
41	*/	1✔
42	addBindVariable(bindVar) {	1✔
43	this.bindVariables.push(bindVar);	1✔
44		1✔
45	return this;	1✔
46	}	1✔
47		1✔
48	/**	1✔
49	* When creating the object to be returned, rather than assign from an empty object '{}', a new instance	1✔
50	* of this class is created instead.	1✔
51	* @param {Object} className	1✔
52	* @returns {Select2Object}	1✔
53	*/	1✔
54	selectToClass(className) {	1✔
NEW 55	this.selectClass = className;	×
56		×
57	return this;	×
58	}	×
59		1✔
60	/**	1✔
61	* Query any sheet range using standard SQL SELECT syntax and return array of table info with column names as properties.	1✔
62	* @example	1✔
63	* gsSQL("select * from expenses where type = ?1")	1✔
64	*	1✔
65	* @param {String} statement - SQL string	1✔
66	* @returns {Object[]} - array of object data.	1✔
67	*/	1✔
68	execute(statement) { // skipcq: JS-0128	1✔
69	const parms = [];	3✔
70		3✔
71	// Add the table name and range.	3✔
72	for (const tab of this.tables) {	3✔
73	parms.push(tab.tableName, tab.data);	5✔
74	}	5✔
75		3✔
76	// Add column output indicator.	3✔
77	parms.push(true); // We want column names returned.	3✔
78		3✔
79	// Add bind data.	3✔
80	for (const bind of this.bindVariables) {	3✔
81	parms.push(bind);	1✔
82	}	1✔
83		3✔
84	const tableDataArray = GasSql.execute(statement, parms);	3✔
85		3✔
86	if (tableDataArray === null \|\| tableDataArray.length === 0) {	3!
87	return null;	×
88	}	×
89		3✔
90	// First item in return array is an array of column names.	3✔
91	const columnNames = Select2Object.cleanupColumnNames(tableDataArray[0]);	3✔
92		3✔
93	return Select2Object.createTableObjectArray(columnNames, tableDataArray, this.selectClass);	3✔
94	}	3✔
95		1✔
96	/**	1✔
97	* Return column names in lower case and remove table name when only one table.	1✔
98	* @param {String[]} cols	1✔
99	* @returns {String[]}	1✔
100	*/	1✔
101	static cleanupColumnNames(cols) {	1✔
102	const newColumns = cols.map(v => v.toLowerCase());	3✔
103	const noTableColumns = [];	3✔
104	const duplicateFieldMap = new Map();	3✔
105		3✔
106	// Is the field name listed more than once.	3✔
107	// ONLY if a field is duplicated is the table name qualifier included.	3✔
108	for (const col of newColumns) {	3✔
109	const splitColumn = col.split(".");	11✔
110	const fld = (splitColumn.length > 1) ? splitColumn[1] : splitColumn[0];	11!
111	const counter = duplicateFieldMap.has(fld) ? duplicateFieldMap.get(fld) : 0;	11✔
112	duplicateFieldMap.set(fld, counter + 1);	11✔
113	}	11✔
114		3✔
115	for (const col of newColumns) {	3✔
116	const splitColumn = col.split(".");	11✔
117	const fld = (splitColumn.length > 1) ? splitColumn[1] : splitColumn[0];	11!
118	if (duplicateFieldMap.get(fld) > 1) {	11✔
119	noTableColumns.push(col);	2✔
120	}	2✔
121	else {	9✔
122	noTableColumns.push(fld);	9✔
123	}	9✔
124	}	11✔
125		3✔
126	return noTableColumns;	3✔
127	}	3✔
128		1✔
129	/**	1✔
130	* First row MUST be column names.	1✔
131	* @param {any[][]} tableDataArray	1✔
132	* @returns {Object[]}	1✔
133	*/	1✔
134	static convertTableArrayToObjectArray(tableDataArray) {	1✔
135	// First item in return array is an array of column names.	×
136	const propertyNames = Select2Object.convertColumnTitleToPropertyName(tableDataArray[0]);	×
137		×
138	return Select2Object.createTableObjectArray(propertyNames, tableDataArray);	×
139	}	×
140		1✔
141	/**	1✔
142	*	1✔
143	* @param {Object[]} objectArray	1✔
144	* @param {String[]} columnTitles	1✔
145	* @param {Boolean} outputTitleRow	1✔
146	* @returns {any[][]}	1✔
147	*/	1✔
148	static convertObjectArrayToTableArray(objectArray, columnTitles, outputTitleRow = true) {	1✔
149	const propertyNames = Select2Object.convertColumnTitleToPropertyName(columnTitles);	×
150	const tableArray = [];	×
151		×
152	if (outputTitleRow)	×
153	tableArray.push(columnTitles);	×
154		×
155	for (const objectRow of objectArray) {	×
156	const row = [];	×
157		×
158	for (const prop of propertyNames) {	×
159	row.push(objectRow[prop]);	×
160	}	×
161		×
162	tableArray.push(row);	×
163	}	×
164		×
165	return tableArray;	×
166	}	×
167		1✔
168	/**	1✔
169	*	1✔
170	* @param {Object} object	1✔
171	* @param {String[]} columnTitles	1✔
172	* @returns {String[]}	1✔
173	*/	1✔
174	static convertObjectToArray(object, columnTitles) {	1✔
175	const propertyNames = Select2Object.convertColumnTitleToPropertyName(columnTitles);	×
176	const row = [];	×
177	for (const prop of propertyNames) {	×
178	row.push(object[prop]);	×
179	}	×
180		×
181	return row;	×
182	}	×
183		1✔
184	/**	1✔
185	* Convert a sheet column name into format used for property name (spaces to underscore && lowercase)	1✔
186	* @param {String[]} columnTitles	1✔
187	* @returns {String[]}	1✔
188	*/	1✔
189	static convertColumnTitleToPropertyName(columnTitles) {	1✔
190	const columnNames = [...columnTitles];	×
191	const srcColumns = columnNames.map(col => col.trim()).map(col => col.toLowerCase()).map(col => col.replaceAll(' ', '_'));	×
192		×
193	return srcColumns;	×
194	}	×
195		1✔
196	/**	1✔
197	* Get column number - starting at 1 in object.	1✔
198	* @param {Object} object	1✔
199	* @param {String} columnTitle	1✔
200	* @returns {Number}	1✔
201	*/	1✔
202	static getColumnNumber(object, columnTitle) {	1✔
203	const prop = Select2Object.convertColumnTitleToPropertyName([columnTitle])[0];	×
204	let col = 1;	×
205	for (const propName in object) { // skipcq: JS-0051	×
206	if (propName === prop) {	×
207	return col;	×
208	}	×
209	col++;	×
210	}	×
211		×
212	return -1;	×
213	}	×
214		1✔
215	/**	1✔
216	*	1✔
217	* @param {String[]} columnNames	1✔
218	* @param {any[]} tableDataArray	1✔
219	* @param {Object} SelectClass	1✔
220	* @returns {Object[]}	1✔
221	*/	1✔
222	static createTableObjectArray(columnNames, tableDataArray, SelectClass=null) {	1✔
223	// Create empty table record object.	3✔
224	const emptyTableRecord = Select2Object.createEmptyRecordObject(columnNames);	3✔
225		3✔
226	// Create table array with record data stored in an object.	3✔
227	const tableData = [];	3✔
228	for (let i = 1; i < tableDataArray.length; i++) {	3✔
229	let newRecord = {};	10✔
230	if (SelectClass !== null) {	10!
NEW 231	newRecord = new SelectClass();	×
UNCOV 232	}	×
233		10✔
234	Object.assign(newRecord, emptyTableRecord);	10✔
235		10✔
236	for (const [index, col] of columnNames.entries()) {	10✔
237	newRecord[col] = tableDataArray[i][index];	38✔
238	}	38✔
239		10✔
240	tableData.push(newRecord);	10✔
241	}	10✔
242		3✔
243	return tableData;	3✔
244	}	3✔
245		1✔
246	/**	1✔
247	* Creates an empty object where each column name is a property in the object.	1✔
248	* @param {String[]} columnNames	1✔
249	* @returns {Object}	1✔
250	*/	1✔
251	static createEmptyRecordObject(columnNames) {	1✔
252	// Create empty table record object.	3✔
253	const dataObject = {};	3✔
254	for (const col of columnNames) {	3✔
255	dataObject[col] = '';	11✔
256	}	11✔
257		3✔
258	dataObject.get = function (columnTitle) {	3✔
259	const prop = Select2Object.convertColumnTitleToPropertyName([columnTitle])[0];	×
260	return this[prop];	×
261	};	3✔
262		3✔
263	dataObject.set = function (columnTitle, value) {	3✔
264	const prop = Select2Object.convertColumnTitleToPropertyName([columnTitle])[0];	×
265	this[prop] = value;	×
266	}	×
267		3✔
268	return dataObject;	3✔
269	}	3✔
270	}	1✔

demmings / gsSQL / 23415384980

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous