• Home
  • Features
  • Pricing
  • Docs
  • Announcements
  • Sign In

demmings / gsSQL / 23415384980

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

push

github

demmings
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).

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
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✔
STATUS · Troubleshooting · Open an Issue · Sales · Support · CAREERS · ENTERPRISE · START FREE · SCHEDULE DEMO
ANNOUNCEMENTS · TWITTER · TOS & SLA · Supported CI Services · What's a CI service? · Automated Testing

© 2026 Coveralls, Inc