23416183381

Committed 23 Mar 2026 12:24AM UTC coverage: 95.239%. Remained the same

Build # 23416183381

Build Type

push

github

Committed by

demmings

Commit Message

String replaceAll() change.

Coverage Stats

1597 of 1691 branches covered (94.44%)

Branch coverage included in aggregate %.

0 of 2 new or added lines in 1 file covered. (0.0%)

13227 of 13874 relevant lines covered (95.34%)

280.81 hits per line

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

79.42

/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
                .replaceAll(/\?/g, ".")
                .replaceAll(/\*/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!
270	return TableData.loadMultipleSheets(tableNamedRange);	×
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✔
310	// Get ALL sheet names.	×
311	const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets().map((sh) => sh.getName());	×
312	// Get all sheet names that match wildcard	×
313	const matchingSheets = sheets.filter(x => TableData.wildcardMatchRegExp(x, rangeWildcard));	×
314	// Get sheet handle for matching sheets.	×
315	const matchingSheetHandles = matchingSheets.map((sh) => SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sh));	×
316	// Filter out invalid handles	×
317	const validSheetHandles = matchingSheetHandles.filter(x => x !== null);	×
318		×
319	const output = [];	×
320	let firstSheet = true;	×
321	for (const sheetHandle of validSheetHandles) {	×
322	const lastColumn = sheetHandle.getLastColumn();	×
323	const lastRow = sheetHandle.getLastRow();	×
324	const firstRow = firstSheet ? 1 : 2;	×
325	const sheetData = sheetHandle.getSheetValues(firstRow, 1, lastRow, lastColumn);	×
326	const cleanData = Table.removeEmptyRecordsAtEndOfTable(sheetData);	×
327	output.push(...cleanData);	×
328		×
329	firstSheet = false;	×
330	}	×
331		×
332	return output;	×
333	}	×
334		1✔
335	static wildcardMatchRegExp(text, pattern) {	1✔
336	// Convert wildcard pattern to a	×
337	// regular expression pattern	×
338	const regexPattern = new RegExp(	×
339	`^${pattern	×
NEW 340	.replaceAll(/\?/g, ".")	×
NEW 341	.replaceAll(/\/g, ".")}$`	×
342	);	×
343		×
344	// Test if the text matches the	×
345	// regular expression pattern	×
346	return regexPattern.test(text);	×
347	}	×
348		1✔
349	/**	1✔
350	* Takes array data to be cached, breaks up into chunks if necessary, puts each chunk into cache and updates status.	1✔
351	* @param {Object} cache	1✔
352	* @param {String} namedRange	1✔
353	* @param {Number} cacheSeconds	1✔
354	* @param {any[][]} arrData	1✔
355	*/	1✔
356	static cachePutArray(cache, namedRange, cacheSeconds, arrData) {	1✔
357	const cacheStatusName = TableData.cacheStatusName(namedRange);	3✔
358	const json = JSON.stringify(arrData);	3✔
359		3✔
360	// Split up data (for re-assembly on get() later)	3✔
361	let splitCount = (json.length / (100 * 1024)) * 1.3; // 1.3 - assumes some blocks may be bigger.	3✔
362	splitCount = Math.max(splitCount, 1);	3✔
363	const arrayLength = Math.ceil(arrData.length / splitCount);	3✔
364	const putObject = {};	3✔
365	let blockCount = 0;	3✔
366	let startIndex = 0;	3✔
367	while (startIndex < arrData.length) {	3✔
368	const arrayBlock = arrData.slice(startIndex, startIndex + arrayLength);	3✔
369	blockCount++;	3✔
370	startIndex += arrayLength;	3✔
371	putObject[`${namedRange}:${blockCount.toString()}`] = JSON.stringify(arrayBlock);	3✔
372	}	3✔
373		3✔
374	// Update status that cache is updated.	3✔
375	const lock = LockService.getScriptLock();	3✔
376	try {	3✔
377	lock.waitLock(100000); // wait 100 seconds for others' use of the code section and lock to stop and then proceed	3✔
378	} catch {	3!
379	throw new Error("Cache lock failed");	×
380	}	×
381	cache.putAll(putObject, cacheSeconds);	3✔
382	cache.put(cacheStatusName, TABLE.BLOCKS + blockCount.toString(), cacheSeconds);	3✔
383		3✔
384	Logger.log(`Writing STATUS: ${cacheStatusName}. Value=${TABLE.BLOCKS}${blockCount.toString()}. seconds=${cacheSeconds}. Items=${arrData.length}`);	3✔
385		3✔
386	lock.releaseLock();	3✔
387	}	3✔
388		1✔
389	/**	1✔
390	* Reads cache for range, and re-assembles blocks into return array of data.	1✔
391	* @param {Object} cache	1✔
392	* @param {String} namedRange	1✔
393	* @returns {any[][]}	1✔
394	*/	1✔
395	static cacheGetArray(cache, namedRange) {	1✔
396	let arrData = [];	6✔
397		6✔
398	const cacheStatusName = TableData.cacheStatusName(namedRange);	6✔
399	const cacheStatus = cache.get(cacheStatusName);	6✔
400	if (cacheStatus === null) {	6✔
401	Logger.log(`Named Range Cache Status not found = ${cacheStatusName}`);	3✔
402	return null;	3✔
403	}	3✔
404		3✔
405	Logger.log(`Cache Status: ${cacheStatusName}. Value=${cacheStatus}`);	3✔
406	if (cacheStatus === TABLE.LOADING) {	6!
407	return null;	×
408	}	×
409		3✔
410	const blockStr = cacheStatus.substring(cacheStatus.indexOf(TABLE.BLOCKS) + TABLE.BLOCKS.length);	3✔
411	if (blockStr !== "") {	3✔
412	const blocks = Number(blockStr);	3✔
413	for (let i = 1; i <= blocks; i++) {	3✔
414	const blockName = `${namedRange}:${i.toString()}`;	3✔
415	const jsonData = cache.get(blockName);	3✔
416		3✔
417	if (jsonData === null) {	3!
418	Logger.log(`Named Range Part not found. R=${blockName}`);	×
419	return null;	×
420	}	×
421		3✔
422	const partArr = JSON.parse(jsonData);	3✔
423	if (TableData.verifyCachedData(partArr)) {	3✔
424	arrData = arrData.concat(partArr);	3✔
425	}	3✔
426	else {	×
427	Logger.log(`Failed to verify named range: ${blockName}`);	×
428	return null;	×
429	}	×
430	}	3✔
431		3✔
432	}	3✔
433	Logger.log(`Just LOADED From CACHE: ${namedRange}. Items=${arrData.length}`);	3✔
434		3✔
435	// The conversion to JSON causes SHEET DATES to be converted to a string.	3✔
436	// This converts any DATE STRINGS back to javascript date.	3✔
437	TableData.fixJSONdates(arrData);	3✔
438		3✔
439	return arrData;	3✔
440	}	6✔
441		1✔
442	/**	1✔
443	* Dates retrieved from a JSON structure need to be converted to JS date.	1✔
444	* @param {any[][]} arrData	1✔
445	*/	1✔
446	static fixJSONdates(arrData) {	1✔
447	const ISO_8601_FULL = /^\d{4}-\d\d-\d\dT\d\d:\d\d:\d\d(\.\d+)?(([+-]\d\d:\d\d)\|Z)?$/i	13✔
448		13✔
449	for (const row of arrData) {	13✔
450	for (let i = 0; i < row.length; i++) {	85✔
451	const testStr = row[i];	685✔
452	if (ISO_8601_FULL.test(testStr)) {	685✔
453	row[i] = new Date(testStr);	72✔
454	}	72✔
455	}	685✔
456	}	85✔
457	}	13✔
458		1✔
459	/**	1✔
460	*	1✔
461	* @param {String} namedRange	1✔
462	* @returns {String}	1✔
463	*/	1✔
464	static cacheStatusName(namedRange) {	1✔
465	return namedRange + TABLE.STATUS;	15✔
466	}	15✔
467	}	1✔
468		1✔
469	const TABLE = {	1✔
470	STATUS: "__STATUS__",	1✔
471	LOADING: "LOADING",	1✔
472	BLOCKS: "BLOCKS="	1✔
473	}	1✔
474

demmings / gsSQL / 23416183381

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