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

79.12

/src/TableData.js

/*  *** DEBUG START ***
//  Remove comments for testing in NODE

export { TableData };
import { SpreadsheetApp } from "./SqlTest.js";
import { ScriptSettings } from "./ScriptSettings.js";
import { Table } from "./Table.js";
import { CacheService, LockService, Utilities } from "../GasMocks.js";

class Logger {
    static log(msg) {
        console.log(msg);
    }
}
//  *** DEBUG END ***/

/** 
 * Interface for loading table data either from CACHE or SHEET. 
 * @class
 * @classdesc
 * * Automatically load table data from a **CACHE** or **SHEET** <br>
 * * In all cases, if the cache has expired, the data is read from the sheet. 
 * <br>
 * 
 * | Cache Seconds | Description |
 * | ---           | ---         |
 * | 0             | Data is not cached and always read directly from SHEET |
 * | <= 21600      | Data read from SHEETS cache if it has not expired |
 * | > 21600       | Data read from Google Sheets Script Settings |
 * 
 */
class TableData {       //  skipcq: JS-0128
    /**
    * Retrieve table data from SHEET or CACHE.
    * @param {String} namedRange - Location of table data.  Either a) SHEET Name, b) Named Range, c) A1 sheet notation.
    * @param {Number} cacheSeconds - 0s Reads directly from sheet. > 21600s Sets in SCRIPT settings, else CacheService 
    * @returns {any[][]}
    */
    static loadTableData(namedRange, cacheSeconds = 0) {
        if (namedRange === undefined || namedRange === "")
            return [];

        Logger.log(`loadTableData: ${namedRange}. Seconds=${cacheSeconds}`);

        return Table.removeEmptyRecordsAtEndOfTable(TableData.getValuesCached(namedRange, cacheSeconds));
    }

    /**
     * Reads a RANGE of values.
     * @param {String} namedRange 
     * @param {Number} seconds 
     * @returns {any[][]}
     */
    static getValuesCached(namedRange, seconds) {
        let cache = {};
        let cacheSeconds = seconds;

        if (cacheSeconds <= 0) {
            return TableData.loadValuesFromRangeOrSheet(namedRange);
        }
        else if (cacheSeconds > 21600) {
            cache = new ScriptSettings();
            if (TableData.isTimeToRunLongCacheExpiry()) {
                ScriptSettings.expire(false);
                TableData.setLongCacheExpiry();
            }
            cacheSeconds = cacheSeconds / 86400;  //  ScriptSettings put() wants days to hold.
        }
        else {
            cache = CacheService.getScriptCache();
        }

        let arrData = TableData.cacheGetArray(cache, namedRange);
        if (arrData !== null) {
            Logger.log(`Found in CACHE: ${namedRange}. Items=${arrData.length}`);
            return arrData;
        }

        Logger.log(`Not in cache: ${namedRange}`);

        arrData = TableData.lockLoadAndCache(cache, namedRange, cacheSeconds);

        return arrData;
    }

    /**
     * Is it time to run the long term cache expiry check?
     * @returns {Boolean}
     */
    static isTimeToRunLongCacheExpiry() {
        const shortCache = CacheService.getScriptCache();
        return shortCache.get("LONG_CACHE_EXPIRY") === null;
    }

    /**
     * The long term expiry check is done every 21,000 seconds.  Set the clock now!
     */
    static setLongCacheExpiry() {
        const shortCache = CacheService.getScriptCache();
        shortCache.put("LONG_CACHE_EXPIRY", 'true', 21000);
    }

    /**
     * In the interest of testing, force the expiry check.
     * It does not mean items in cache will be removed - just 
     * forces a check.
     */
    static forceLongCacheExpiryCheck() {
        const shortCache = CacheService.getScriptCache();
        if (shortCache.get("LONG_CACHE_EXPIRY") !== null) {
            shortCache.remove("LONG_CACHE_EXPIRY");
        }
    }

    /**
     * Reads a single cell.
     * @param {String} namedRange 
     * @param {Number} seconds 
     * @returns {any}
     */
    static getValueCached(namedRange, seconds = 60) {
        const cache = CacheService.getScriptCache();

        let singleData = cache.get(namedRange);

        if (singleData === null) {
            const ss = SpreadsheetApp.getActiveSpreadsheet();
            singleData = ss.getRangeByName(namedRange).getValue();
            cache.put(namedRange, JSON.stringify(singleData), seconds);
        }
        else {
            singleData = JSON.parse(singleData);
            const tempArr = [[singleData]];
            TableData.fixJSONdates(tempArr);
            singleData = tempArr[0][0];
        }

        return singleData;
    }

    /**
     * Check if data from cache is in error.
     * @param {any[][]} arrData 
     * @returns {Boolean}
     */
    static verifyCachedData(arrData) {
        let verified = true;

        for (const rowData of arrData) {
            for (const fieldData of rowData) {
                if (fieldData === "#ERROR!") {
                    Logger.log("Reading from CACHE has found '#ERROR!'.  Re-Loading...");
                    verified = false;
                    break;
                }
            }
        }

        return verified;
    }

    /**
     * Checks if this range is loading elsewhere (i.e. from another call to custom function)
     * @param {String} namedRange
     * @returns {Boolean} 
     */
    static isRangeLoading(cache, namedRange) {
        let loading = false;
        const cacheData = cache.get(TableData.cacheStatusName(namedRange));

        if (cacheData !== null && cacheData === TABLE.LOADING) {
            loading = true;
        }

        Logger.log(`isRangeLoading: ${namedRange}. Status: ${loading}`);

        return loading;
    }

    /**
     * Retrieve data from cache after it has loaded elsewhere.
     * @param {Object} cache 
     * @param {String} namedRange 
     * @param {Number} cacheSeconds - How long to cache results.
     * @returns {any[][]}
     */
    static waitForRangeToLoad(cache, namedRange, cacheSeconds) {
        const start = Date.now();
        let current = Date.now();

        Logger.log(`waitForRangeToLoad() - Start: ${namedRange}`);
        while (TableData.isRangeLoading(cache, namedRange) && (current - start) < 10000) {
            Utilities.sleep(250);
            current = Date.now();
        }
        Logger.log("waitForRangeToLoad() - End");

        let arrData = TableData.cacheGetArray(cache, namedRange);

        //  Give up and load from SHEETS directly.
        if (arrData === null) {
            Logger.log(`waitForRangeToLoad - give up.  Read directly. ${namedRange}`);
            arrData = TableData.loadValuesFromRangeOrSheet(namedRange);

            if (TableData.isRangeLoading(cache, namedRange)) {
                //  Other process probably timed out and left status hanging.
                TableData.cachePutArray(cache, namedRange, cacheSeconds, arrData);
            }
        }

        return arrData;
    }

    /**
     * Read range of value from sheet and cache.
     * @param {Object} cache - cache object can vary depending where the data is stored.
     * @param {String} namedRange 
     * @param {Number} cacheSeconds 
     * @returns {any[][]} - data from range
     */
    static lockLoadAndCache(cache, namedRange, cacheSeconds) {
        //  Only change our CACHE STATUS if we have a lock.
        const lock = LockService.getScriptLock();
        try {
            lock.waitLock(100000); // wait 100 seconds for others' use of the code section and lock to stop and then proceed
        } catch {
            throw new Error("Cache lock failed");
        }

        //  It is possible that just before getting the lock, another process started caching.
        if (TableData.isRangeLoading(cache, namedRange)) {
            lock.releaseLock();
            return TableData.waitForRangeToLoad(cache, namedRange, cacheSeconds);
        }

        //  Mark the status for this named range that loading is in progress.
        cache.put(TableData.cacheStatusName(namedRange), TABLE.LOADING, 15);
        lock.releaseLock();

        //  Load data from SHEETS.
        const arrData = TableData.loadValuesFromRangeOrSheet(namedRange);

        Logger.log(`Just LOADED from SHEET: Item Count=${arrData.length}`);

        TableData.cachePutArray(cache, namedRange, cacheSeconds, arrData);

        return arrData;
    }

    /**
     * Read sheet data into double array.
     * @param {String} namedRange - named range, A1 notation or sheet name
     * @returns {any[][]} - table data.
     */
    static loadValuesFromRangeOrSheet(namedRange) {
        let tableNamedRange = namedRange;
        let output = [];

        try {
            Logger.log(`Getting Range of Values: ${tableNamedRange}`);
            const sheetNamedRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(tableNamedRange);

            if (sheetNamedRange === null) {
                //  This may be a SHEET NAME, so try getting SHEET RANGE.
                if (tableNamedRange.startsWith("'") && tableNamedRange.endsWith("'")) {
                    tableNamedRange = tableNamedRange.substring(1, tableNamedRange.length - 1);
                }

                if (tableNamedRange.indexOf("*") !== -1) {
                    return TableData.loadMultipleSheets(tableNamedRange);
                }

                let sheetHandle = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tableNamedRange);

                //  Actual sheet may have spaces in name.  The SQL must reference that table with
                //  underscores replacing those spaces.
                if (sheetHandle === null && tableNamedRange.includes("_")) {
                    tableNamedRange = tableNamedRange.replaceAll('_', " ");
                    sheetHandle = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tableNamedRange);
                }

                if (sheetHandle === null) {
                    throw new Error(`Invalid table range specified:  ${tableNamedRange}`);
                }

                const lastColumn = sheetHandle.getLastColumn();
                const lastRow = sheetHandle.getLastRow();
                output = sheetHandle.getSheetValues(1, 1, lastRow, lastColumn);
            }
            else {
                // @ts-ignore
                output = sheetNamedRange.getValues();
                Logger.log(`Named Range Data Loaded: ${tableNamedRange}. Items=${output.length}`);
            }
        }
        catch {
            throw new Error(`Error reading table data: ${tableNamedRange}`);
        }

        return output;
    }

    /**
     * First sheet loaded includes first row, every other sheet loaded starts from row 2.
     * All sheets in wildcard MUST be formatted the same.
     * @param {String} rangeWildcard - Sheet name with wildcard character '*'
     * @returns {any[][]}
     */
    static loadMultipleSheets(rangeWildcard) {
        //  Get ALL sheet names.
        const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets().map((sh) => sh.getName());
        //  Get all sheet names that match wildcard
        const matchingSheets = sheets.filter(x => TableData.wildcardMatchRegExp(x, rangeWildcard));
        //  Get sheet handle for matching sheets.
        const matchingSheetHandles = matchingSheets.map((sh) => SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sh));
        //  Filter out invalid handles
        const validSheetHandles = matchingSheetHandles.filter(x => x !== null);

        const output = [];
        let firstSheet = true;
        for (const sheetHandle of validSheetHandles) {
            const lastColumn = sheetHandle.getLastColumn();
            const lastRow = sheetHandle.getLastRow();
            const firstRow = firstSheet ? 1 : 2;
            const sheetData = sheetHandle.getSheetValues(firstRow, 1, lastRow, lastColumn);
            const cleanData = Table.removeEmptyRecordsAtEndOfTable(sheetData);
            output.push(...cleanData);

            firstSheet = false;
        }

        return output;
    }

    static wildcardMatchRegExp(text, pattern) {
        // Convert wildcard pattern to a 
        // regular expression pattern
        const regexPattern = new RegExp(
            "^" +
            pattern
                .replace(/\?/g, ".")
                .replace(/\*/g, ".*") +
            "$"
        );

        // Test if the text matches the
        // regular expression pattern
        return regexPattern.test(text);
    }

    /**
     * Takes array data to be cached, breaks up into chunks if necessary, puts each chunk into cache and updates status.
     * @param {Object} cache 
     * @param {String} namedRange 
     * @param {Number} cacheSeconds 
     * @param {any[][]} arrData 
     */
    static cachePutArray(cache, namedRange, cacheSeconds, arrData) {
        const cacheStatusName = TableData.cacheStatusName(namedRange);
        const json = JSON.stringify(arrData);

        //  Split up data (for re-assembly on get() later)
        let splitCount = (json.length / (100 * 1024)) * 1.3;    // 1.3 - assumes some blocks may be bigger.
        splitCount = Math.max(splitCount, 1);
        const arrayLength = Math.ceil(arrData.length / splitCount);
        const putObject = {};
        let blockCount = 0;
        let startIndex = 0;
        while (startIndex < arrData.length) {
            const arrayBlock = arrData.slice(startIndex, startIndex + arrayLength);
            blockCount++;
            startIndex += arrayLength;
            putObject[`${namedRange}:${blockCount.toString()}`] = JSON.stringify(arrayBlock);
        }

        //  Update status that cache is updated.
        const lock = LockService.getScriptLock();
        try {
            lock.waitLock(100000); // wait 100 seconds for others' use of the code section and lock to stop and then proceed
        } catch {
            throw new Error("Cache lock failed");
        }
        cache.putAll(putObject, cacheSeconds);
        cache.put(cacheStatusName, TABLE.BLOCKS + blockCount.toString(), cacheSeconds);

        Logger.log(`Writing STATUS: ${cacheStatusName}. Value=${TABLE.BLOCKS}${blockCount.toString()}. seconds=${cacheSeconds}. Items=${arrData.length}`);

        lock.releaseLock();
    }

    /**
     * Reads cache for range, and re-assembles blocks into return array of data.
     * @param {Object} cache 
     * @param {String} namedRange 
     * @returns {any[][]}
     */
    static cacheGetArray(cache, namedRange) {
        let arrData = [];

        const cacheStatusName = TableData.cacheStatusName(namedRange);
        const cacheStatus = cache.get(cacheStatusName);
        if (cacheStatus === null) {
            Logger.log(`Named Range Cache Status not found = ${cacheStatusName}`);
            return null;
        }

        Logger.log(`Cache Status: ${cacheStatusName}. Value=${cacheStatus}`);
        if (cacheStatus === TABLE.LOADING) {
            return null;
        }

        const blockStr = cacheStatus.substring(cacheStatus.indexOf(TABLE.BLOCKS) + TABLE.BLOCKS.length);
        if (blockStr !== "") {
            const blocks = Number(blockStr);
            for (let i = 1; i <= blocks; i++) {
                const blockName = `${namedRange}:${i.toString()}`;
                const jsonData = cache.get(blockName);

                if (jsonData === null) {
                    Logger.log(`Named Range Part not found. R=${blockName}`);
                    return null;
                }

                const partArr = JSON.parse(jsonData);
                if (TableData.verifyCachedData(partArr)) {
                    arrData = arrData.concat(partArr);
                }
                else {
                    Logger.log(`Failed to verify named range: ${blockName}`);
                    return null;
                }
            }

        }
        Logger.log(`Just LOADED From CACHE: ${namedRange}. Items=${arrData.length}`);

        //  The conversion to JSON causes SHEET DATES to be converted to a string.
        //  This converts any DATE STRINGS back to javascript date.
        TableData.fixJSONdates(arrData);

        return arrData;
    }

    /**
     * Dates retrieved from a JSON structure need to be converted to JS date.
     * @param {any[][]} arrData 
     */
    static fixJSONdates(arrData) {
        const ISO_8601_FULL = /^\d{4}-\d\d-\d\dT\d\d:\d\d:\d\d(\.\d+)?(([+-]\d\d:\d\d)|Z)?$/i

        for (const row of arrData) {
            for (let i = 0; i < row.length; i++) {
                const testStr = row[i];
                if (ISO_8601_FULL.test(testStr)) {
                    row[i] = new Date(testStr);
                }
            }
        }
    }

    /**
     * 
     * @param {String} namedRange 
     * @returns {String}
     */
    static cacheStatusName(namedRange) {
        return namedRange + TABLE.STATUS;
    }
}

const TABLE = {
    STATUS: "__STATUS__",
    LOADING: "LOADING",
    BLOCKS: "BLOCKS="
}


1	/* * DEBUG START *	1✔
2	// Remove comments for testing in NODE	1✔
3		1✔
4	export { TableData };	1✔
5	import { SpreadsheetApp } from "./SqlTest.js";	1✔
6	import { ScriptSettings } from "./ScriptSettings.js";	1✔
7	import { Table } from "./Table.js";	1✔
8	import { CacheService, LockService, Utilities } from "../GasMocks.js";	1✔
9		1✔
10	class Logger {	1✔
11	static log(msg) {	1✔
12	console.log(msg);	38✔
13	}	38✔
14	}	1✔
15	// * DEBUG END */	1✔
16		1✔
17	/**	1✔
18	* Interface for loading table data either from CACHE or SHEET.	1✔
19	* @class	1✔
20	* @classdesc	1✔
21	* * Automatically load table data from a CACHE or SHEET <br>	1✔
22	* * In all cases, if the cache has expired, the data is read from the sheet.	1✔
23	* <br>	1✔
24	*	1✔
25	* \| Cache Seconds \| Description \|	1✔
26	* \| --- \| --- \|	1✔
27	* \| 0 \| Data is not cached and always read directly from SHEET \|	1✔
28	* \| <= 21600 \| Data read from SHEETS cache if it has not expired \|	1✔
29	* \| > 21600 \| Data read from Google Sheets Script Settings \|	1✔
30	*	1✔
31	*/	1✔
32	class TableData { // skipcq: JS-0128	1✔
33	/**	1✔
34	* Retrieve table data from SHEET or CACHE.	1✔
35	* @param {String} namedRange - Location of table data. Either a) SHEET Name, b) Named Range, c) A1 sheet notation.	1✔
36	* @param {Number} cacheSeconds - 0s Reads directly from sheet. > 21600s Sets in SCRIPT settings, else CacheService	1✔
37	* @returns {any[][]}	1✔
38	*/	1✔
39	static loadTableData(namedRange, cacheSeconds = 0) {	1✔
40	if (namedRange === undefined \|\| namedRange === "")	7✔
41	return [];	7!
42		7✔
43	Logger.log(`loadTableData: ${namedRange}. Seconds=${cacheSeconds}`);	7✔
44		7✔
45	return Table.removeEmptyRecordsAtEndOfTable(TableData.getValuesCached(namedRange, cacheSeconds));	7✔
46	}	7✔
47		1✔
48	/**	1✔
49	* Reads a RANGE of values.	1✔
50	* @param {String} namedRange	1✔
51	* @param {Number} seconds	1✔
52	* @returns {any[][]}	1✔
53	*/	1✔
54	static getValuesCached(namedRange, seconds) {	1✔
55	let cache = {};	7✔
56	let cacheSeconds = seconds;	7✔
57		7✔
58	if (cacheSeconds <= 0) {	7✔
59	return TableData.loadValuesFromRangeOrSheet(namedRange);	1✔
60	}	1✔
61	else if (cacheSeconds > 21600) {	6✔
62	cache = new ScriptSettings();	3✔
63	if (TableData.isTimeToRunLongCacheExpiry()) {	3✔
64	ScriptSettings.expire(false);	2✔
65	TableData.setLongCacheExpiry();	2✔
66	}	2✔
67	cacheSeconds = cacheSeconds / 86400; // ScriptSettings put() wants days to hold.	3✔
68	}	3✔
69	else {	3✔
70	cache = CacheService.getScriptCache();	3✔
71	}	3✔
72		6✔
73	let arrData = TableData.cacheGetArray(cache, namedRange);	6✔
74	if (arrData !== null) {	7✔
75	Logger.log(`Found in CACHE: ${namedRange}. Items=${arrData.length}`);	3✔
76	return arrData;	3✔
77	}	3✔
78		3✔
79	Logger.log(`Not in cache: ${namedRange}`);	3✔
80		3✔
81	arrData = TableData.lockLoadAndCache(cache, namedRange, cacheSeconds);	3✔
82		3✔
83	return arrData;	3✔
84	}	7✔
85		1✔
86	/**	1✔
87	* Is it time to run the long term cache expiry check?	1✔
88	* @returns {Boolean}	1✔
89	*/	1✔
90	static isTimeToRunLongCacheExpiry() {	1✔
91	const shortCache = CacheService.getScriptCache();	3✔
92	return shortCache.get("LONG_CACHE_EXPIRY") === null;	3✔
93	}	3✔
94		1✔
95	/**	1✔
96	* The long term expiry check is done every 21,000 seconds. Set the clock now!	1✔
97	*/	1✔
98	static setLongCacheExpiry() {	1✔
99	const shortCache = CacheService.getScriptCache();	2✔
100	shortCache.put("LONG_CACHE_EXPIRY", 'true', 21000);	2✔
101	}	2✔
102		1✔
103	/**	1✔
104	* In the interest of testing, force the expiry check.	1✔
105	* It does not mean items in cache will be removed - just	1✔
106	* forces a check.	1✔
107	*/	1✔
108	static forceLongCacheExpiryCheck() {	1✔
109	const shortCache = CacheService.getScriptCache();	1✔
110	if (shortCache.get("LONG_CACHE_EXPIRY") !== null) {	1✔
111	shortCache.remove("LONG_CACHE_EXPIRY");	1✔
112	}	1✔
113	}	1✔
114		1✔
115	/**	1✔
116	* Reads a single cell.	1✔
117	* @param {String} namedRange	1✔
118	* @param {Number} seconds	1✔
119	* @returns {any}	1✔
120	*/	1✔
121	static getValueCached(namedRange, seconds = 60) {	1✔
122	const cache = CacheService.getScriptCache();	12✔
123		12✔
124	let singleData = cache.get(namedRange);	12✔
125		12✔
126	if (singleData === null) {	12✔
127	const ss = SpreadsheetApp.getActiveSpreadsheet();	2✔
128	singleData = ss.getRangeByName(namedRange).getValue();	2✔
129	cache.put(namedRange, JSON.stringify(singleData), seconds);	2✔
130	}	2✔
131	else {	10✔
132	singleData = JSON.parse(singleData);	10✔
133	const tempArr = [[singleData]];	10✔
134	TableData.fixJSONdates(tempArr);	10✔
135	singleData = tempArr[0][0];	10✔
136	}	10✔
137		12✔
138	return singleData;	12✔
139	}	12✔
140		1✔
141	/**	1✔
142	* Check if data from cache is in error.	1✔
143	* @param {any[][]} arrData	1✔
144	* @returns {Boolean}	1✔
145	*/	1✔
146	static verifyCachedData(arrData) {	1✔
147	let verified = true;	3✔
148		3✔
149	for (const rowData of arrData) {	3✔
150	for (const fieldData of rowData) {	75✔
151	if (fieldData === "#ERROR!") {	675!
152	Logger.log("Reading from CACHE has found '#ERROR!'. Re-Loading...");	×
153	verified = false;	×
154	break;	×
155	}	×
156	}	675✔
157	}	75✔
158		3✔
159	return verified;	3✔
160	}	3✔
161		1✔
162	/**	1✔
163	* Checks if this range is loading elsewhere (i.e. from another call to custom function)	1✔
164	* @param {String} namedRange	1✔
165	* @returns {Boolean}	1✔
166	*/	1✔
167	static isRangeLoading(cache, namedRange) {	1✔
168	let loading = false;	3✔
169	const cacheData = cache.get(TableData.cacheStatusName(namedRange));	3✔
170		3✔
171	if (cacheData !== null && cacheData === TABLE.LOADING) {	3!
172	loading = true;	×
173	}	×
174		3✔
175	Logger.log(`isRangeLoading: ${namedRange}. Status: ${loading}`);	3✔
176		3✔
177	return loading;	3✔
178	}	3✔
179		1✔
180	/**	1✔
181	* Retrieve data from cache after it has loaded elsewhere.	1✔
182	* @param {Object} cache	1✔
183	* @param {String} namedRange	1✔
184	* @param {Number} cacheSeconds - How long to cache results.	1✔
185	* @returns {any[][]}	1✔
186	*/	1✔
187	static waitForRangeToLoad(cache, namedRange, cacheSeconds) {	1✔
188	const start = Date.now();	×
189	let current = Date.now();	×
190		×
191	Logger.log(`waitForRangeToLoad() - Start: ${namedRange}`);	×
192	while (TableData.isRangeLoading(cache, namedRange) && (current - start) < 10000) {	×
193	Utilities.sleep(250);	×
194	current = Date.now();	×
195	}	×
196	Logger.log("waitForRangeToLoad() - End");	×
197		×
198	let arrData = TableData.cacheGetArray(cache, namedRange);	×
199		×
200	// Give up and load from SHEETS directly.	×
201	if (arrData === null) {	×
202	Logger.log(`waitForRangeToLoad - give up. Read directly. ${namedRange}`);	×
203	arrData = TableData.loadValuesFromRangeOrSheet(namedRange);	×
204		×
205	if (TableData.isRangeLoading(cache, namedRange)) {	×
206	// Other process probably timed out and left status hanging.	×
207	TableData.cachePutArray(cache, namedRange, cacheSeconds, arrData);	×
208	}	×
209	}	×
210		×
211	return arrData;	×
212	}	×
213		1✔
214	/**	1✔
215	* Read range of value from sheet and cache.	1✔
216	* @param {Object} cache - cache object can vary depending where the data is stored.	1✔
217	* @param {String} namedRange	1✔
218	* @param {Number} cacheSeconds	1✔
219	* @returns {any[][]} - data from range	1✔
220	*/	1✔
221	static lockLoadAndCache(cache, namedRange, cacheSeconds) {	1✔
222	// Only change our CACHE STATUS if we have a lock.	3✔
223	const lock = LockService.getScriptLock();	3✔
224	try {	3✔
225	lock.waitLock(100000); // wait 100 seconds for others' use of the code section and lock to stop and then proceed	3✔
226	} catch {	3!
227	throw new Error("Cache lock failed");	×
228	}	×
229		3✔
230	// It is possible that just before getting the lock, another process started caching.	3✔
231	if (TableData.isRangeLoading(cache, namedRange)) {	3!
232	lock.releaseLock();	×
233	return TableData.waitForRangeToLoad(cache, namedRange, cacheSeconds);	×
234	}	×
235		3✔
236	// Mark the status for this named range that loading is in progress.	3✔
237	cache.put(TableData.cacheStatusName(namedRange), TABLE.LOADING, 15);	3✔
238	lock.releaseLock();	3✔
239		3✔
240	// Load data from SHEETS.	3✔
241	const arrData = TableData.loadValuesFromRangeOrSheet(namedRange);	3✔
242		3✔
243	Logger.log(`Just LOADED from SHEET: Item Count=${arrData.length}`);	3✔
244		3✔
245	TableData.cachePutArray(cache, namedRange, cacheSeconds, arrData);	3✔
246		3✔
247	return arrData;	3✔
248	}	3✔
249		1✔
250	/**	1✔
251	* Read sheet data into double array.	1✔
252	* @param {String} namedRange - named range, A1 notation or sheet name	1✔
253	* @returns {any[][]} - table data.	1✔
254	*/	1✔
255	static loadValuesFromRangeOrSheet(namedRange) {	1✔
256	let tableNamedRange = namedRange;	4✔
257	let output = [];	4✔
258		4✔
259	try {	4✔
260	Logger.log(`Getting Range of Values: ${tableNamedRange}`);	4✔
261	const sheetNamedRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(tableNamedRange);	4✔
262		4✔
263	if (sheetNamedRange === null) {	4✔
264	// This may be a SHEET NAME, so try getting SHEET RANGE.	1✔
265	if (tableNamedRange.startsWith("'") && tableNamedRange.endsWith("'")) {	1✔
266	tableNamedRange = tableNamedRange.substring(1, tableNamedRange.length - 1);	1✔
267	}	1✔
268		1✔
269	if (tableNamedRange.indexOf("*") !== -1) {	1!
NEW 270	return TableData.loadMultipleSheets(tableNamedRange);	×
NEW 271	}	×
272		1✔
273	let sheetHandle = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tableNamedRange);	1✔
274		1✔
275	// Actual sheet may have spaces in name. The SQL must reference that table with	1✔
276	// underscores replacing those spaces.	1✔
277	if (sheetHandle === null && tableNamedRange.includes("_")) {	1!
278	tableNamedRange = tableNamedRange.replaceAll('_', " ");	×
279	sheetHandle = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tableNamedRange);	×
280	}	×
281		1✔
282	if (sheetHandle === null) {	1!
283	throw new Error(`Invalid table range specified: ${tableNamedRange}`);	×
284	}	×
285		1✔
286	const lastColumn = sheetHandle.getLastColumn();	1✔
287	const lastRow = sheetHandle.getLastRow();	1✔
288	output = sheetHandle.getSheetValues(1, 1, lastRow, lastColumn);	1✔
289	}	1✔
290	else {	3✔
291	// @ts-ignore	3✔
292	output = sheetNamedRange.getValues();	3✔
293	Logger.log(`Named Range Data Loaded: ${tableNamedRange}. Items=${output.length}`);	3✔
294	}	3✔
295	}	4✔
296	catch {	4!
297	throw new Error(`Error reading table data: ${tableNamedRange}`);	×
298	}	×
299		4✔
300	return output;	4✔
301	}	4✔
302		1✔
303	/**	1✔
304	* First sheet loaded includes first row, every other sheet loaded starts from row 2.	1✔
305	* All sheets in wildcard MUST be formatted the same.	1✔
306	* @param {String} rangeWildcard - Sheet name with wildcard character '*'	1✔
307	* @returns {any[][]}	1✔
308	*/	1✔
309	static loadMultipleSheets(rangeWildcard) {	1✔
NEW 310	// Get ALL sheet names.	×
NEW 311	const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets().map((sh) => sh.getName());	×
NEW 312	// Get all sheet names that match wildcard	×
NEW 313	const matchingSheets = sheets.filter(x => TableData.wildcardMatchRegExp(x, rangeWildcard));	×
NEW 314	// Get sheet handle for matching sheets.	×
NEW 315	const matchingSheetHandles = matchingSheets.map((sh) => SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sh));	×
NEW 316	// Filter out invalid handles	×
NEW 317	const validSheetHandles = matchingSheetHandles.filter(x => x !== null);	×
NEW 318		×
NEW 319	const output = [];	×
NEW 320	let firstSheet = true;	×
NEW 321	for (const sheetHandle of validSheetHandles) {	×
NEW 322	const lastColumn = sheetHandle.getLastColumn();	×
NEW 323	const lastRow = sheetHandle.getLastRow();	×
NEW 324	const firstRow = firstSheet ? 1 : 2;	×
NEW 325	const sheetData = sheetHandle.getSheetValues(firstRow, 1, lastRow, lastColumn);	×
NEW 326	const cleanData = Table.removeEmptyRecordsAtEndOfTable(sheetData);	×
NEW 327	output.push(...cleanData);	×
NEW 328		×
NEW 329	firstSheet = false;	×
NEW 330	}	×
NEW 331		×
NEW 332	return output;	×
NEW 333	}	×
334		1✔
335	static wildcardMatchRegExp(text, pattern) {	1✔
NEW 336	// Convert wildcard pattern to a	×
NEW 337	// regular expression pattern	×
NEW 338	const regexPattern = new RegExp(	×
NEW 339	"^" +	×
NEW 340	pattern	×
NEW 341	.replace(/\?/g, ".")	×
NEW 342	.replace(/\/g, ".") +	×
NEW 343	"$"	×
NEW 344	);	×
NEW 345		×
NEW 346	// Test if the text matches the	×
NEW 347	// regular expression pattern	×
NEW 348	return regexPattern.test(text);	×
NEW 349	}	×
350		1✔
351	/**	1✔
352	* Takes array data to be cached, breaks up into chunks if necessary, puts each chunk into cache and updates status.	1✔
353	* @param {Object} cache	1✔
354	* @param {String} namedRange	1✔
355	* @param {Number} cacheSeconds	1✔
356	* @param {any[][]} arrData	1✔
357	*/	1✔
358	static cachePutArray(cache, namedRange, cacheSeconds, arrData) {	1✔
359	const cacheStatusName = TableData.cacheStatusName(namedRange);	3✔
360	const json = JSON.stringify(arrData);	3✔
361		3✔
362	// Split up data (for re-assembly on get() later)	3✔
363	let splitCount = (json.length / (100 * 1024)) * 1.3; // 1.3 - assumes some blocks may be bigger.	3✔
364	splitCount = Math.max(splitCount, 1);	3✔
365	const arrayLength = Math.ceil(arrData.length / splitCount);	3✔
366	const putObject = {};	3✔
367	let blockCount = 0;	3✔
368	let startIndex = 0;	3✔
369	while (startIndex < arrData.length) {	3✔
370	const arrayBlock = arrData.slice(startIndex, startIndex + arrayLength);	3✔
371	blockCount++;	3✔
372	startIndex += arrayLength;	3✔
373	putObject[`${namedRange}:${blockCount.toString()}`] = JSON.stringify(arrayBlock);	3✔
374	}	3✔
375		3✔
376	// Update status that cache is updated.	3✔
377	const lock = LockService.getScriptLock();	3✔
378	try {	3✔
379	lock.waitLock(100000); // wait 100 seconds for others' use of the code section and lock to stop and then proceed	3✔
380	} catch {	3!
381	throw new Error("Cache lock failed");	×
382	}	×
383	cache.putAll(putObject, cacheSeconds);	3✔
384	cache.put(cacheStatusName, TABLE.BLOCKS + blockCount.toString(), cacheSeconds);	3✔
385		3✔
386	Logger.log(`Writing STATUS: ${cacheStatusName}. Value=${TABLE.BLOCKS}${blockCount.toString()}. seconds=${cacheSeconds}. Items=${arrData.length}`);	3✔
387		3✔
388	lock.releaseLock();	3✔
389	}	3✔
390		1✔
391	/**	1✔
392	* Reads cache for range, and re-assembles blocks into return array of data.	1✔
393	* @param {Object} cache	1✔
394	* @param {String} namedRange	1✔
395	* @returns {any[][]}	1✔
396	*/	1✔
397	static cacheGetArray(cache, namedRange) {	1✔
398	let arrData = [];	6✔
399		6✔
400	const cacheStatusName = TableData.cacheStatusName(namedRange);	6✔
401	const cacheStatus = cache.get(cacheStatusName);	6✔
402	if (cacheStatus === null) {	6✔
403	Logger.log(`Named Range Cache Status not found = ${cacheStatusName}`);	3✔
404	return null;	3✔
405	}	3✔
406		3✔
407	Logger.log(`Cache Status: ${cacheStatusName}. Value=${cacheStatus}`);	3✔
408	if (cacheStatus === TABLE.LOADING) {	6!
409	return null;	×
410	}	×
411		3✔
412	const blockStr = cacheStatus.substring(cacheStatus.indexOf(TABLE.BLOCKS) + TABLE.BLOCKS.length);	3✔
413	if (blockStr !== "") {	3✔
414	const blocks = Number(blockStr);	3✔
415	for (let i = 1; i <= blocks; i++) {	3✔
416	const blockName = `${namedRange}:${i.toString()}`;	3✔
417	const jsonData = cache.get(blockName);	3✔
418		3✔
419	if (jsonData === null) {	3!
420	Logger.log(`Named Range Part not found. R=${blockName}`);	×
421	return null;	×
422	}	×
423		3✔
424	const partArr = JSON.parse(jsonData);	3✔
425	if (TableData.verifyCachedData(partArr)) {	3✔
426	arrData = arrData.concat(partArr);	3✔
427	}	3✔
428	else {	×
429	Logger.log(`Failed to verify named range: ${blockName}`);	×
430	return null;	×
431	}	×
432	}	3✔
433		3✔
434	}	3✔
435	Logger.log(`Just LOADED From CACHE: ${namedRange}. Items=${arrData.length}`);	3✔
436		3✔
437	// The conversion to JSON causes SHEET DATES to be converted to a string.	3✔
438	// This converts any DATE STRINGS back to javascript date.	3✔
439	TableData.fixJSONdates(arrData);	3✔
440		3✔
441	return arrData;	3✔
442	}	6✔
443		1✔
444	/**	1✔
445	* Dates retrieved from a JSON structure need to be converted to JS date.	1✔
446	* @param {any[][]} arrData	1✔
447	*/	1✔
448	static fixJSONdates(arrData) {	1✔
449	const ISO_8601_FULL = /^\d{4}-\d\d-\d\dT\d\d:\d\d:\d\d(\.\d+)?(([+-]\d\d:\d\d)\|Z)?$/i	13✔
450		13✔
451	for (const row of arrData) {	13✔
452	for (let i = 0; i < row.length; i++) {	85✔
453	const testStr = row[i];	685✔
454	if (ISO_8601_FULL.test(testStr)) {	685✔
455	row[i] = new Date(testStr);	72✔
456	}	72✔
457	}	685✔
458	}	85✔
459	}	13✔
460		1✔
461	/**	1✔
462	*	1✔
463	* @param {String} namedRange	1✔
464	* @returns {String}	1✔
465	*/	1✔
466	static cacheStatusName(namedRange) {	1✔
467	return namedRange + TABLE.STATUS;	15✔
468	}	15✔
469	}	1✔
470		1✔
471	const TABLE = {	1✔
472	STATUS: "__STATUS__",	1✔
473	LOADING: "LOADING",	1✔
474	BLOCKS: "BLOCKS="	1✔
475	}	1✔
476

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