19122082321

Committed 06 Nov 2025 01:43AM UTC coverage: 95.352% (+0.03%) from 95.319%

Build # 19122082321

Build Type

push

github

Committed by

demmings

Commit Message

SELF JOIN lint changes - Sonarcube

Not all suggestions implemented as many caused issues.  Others are just not supported in the current implementation of Javascript in Google Apps Script.

Coverage Stats

1589 of 1680 branches covered (94.58%)

Branch coverage included in aggregate %.

248 of 253 new or added lines in 8 files covered. (98.02%)

13141 of 13768 relevant lines covered (95.45%)

280.51 hits per line

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

82.38

/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;
    }

    /**
     * For updating a sheet VALUE that may be later read from cache.
     * @param {String} namedRange 
     * @param {any} singleData 
     * @param {Number} seconds 
     */
    static setValueCached(namedRange, singleData, seconds = 60) {
        const ss = SpreadsheetApp.getActiveSpreadsheet();
        ss.getRangeByName(namedRange).setValue(singleData);
        let cache = null;

        if (seconds === 0) {
            return;
        }
        else if (seconds > 21600) {
            cache = new ScriptSettings();
        }
        else {
            cache = CacheService.getScriptCache();
        }
        cache.put(namedRange, JSON.stringify(singleData), seconds);
    }

    /**
     * For updating a sheet array that may be later read from cache.
     * @param {String} namedRange 
     * @param {any[][]} arrData 
     * @param {Number} seconds 
     */
    static setValuesCached(namedRange, arrData, seconds = 60) {
        const cache = CacheService.getScriptCache();

        const ss = SpreadsheetApp.getActiveSpreadsheet();
        ss.getRangeByName(namedRange).setValues(arrData);
        cache.put(namedRange, JSON.stringify(arrData), seconds)
    }

    /**
     * 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 (e) {
            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);
                }
                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 (ex) {
            throw new Error(`Error reading table data: ${tableNamedRange}`);
        }

        return output;
    }

    /**
     * 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 (e) {
            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	* For updating a sheet VALUE that may be later read from cache.	1✔
143	* @param {String} namedRange	1✔
144	* @param {any} singleData	1✔
145	* @param {Number} seconds	1✔
146	*/	1✔
147	static setValueCached(namedRange, singleData, seconds = 60) {	1✔
148	const ss = SpreadsheetApp.getActiveSpreadsheet();	×
149	ss.getRangeByName(namedRange).setValue(singleData);	×
150	let cache = null;	×
151		×
152	if (seconds === 0) {	×
153	return;	×
154	}	×
155	else if (seconds > 21600) {	×
156	cache = new ScriptSettings();	×
157	}	×
158	else {	×
159	cache = CacheService.getScriptCache();	×
160	}	×
161	cache.put(namedRange, JSON.stringify(singleData), seconds);	×
162	}	×
163		1✔
164	/**	1✔
165	* For updating a sheet array that may be later read from cache.	1✔
166	* @param {String} namedRange	1✔
167	* @param {any[][]} arrData	1✔
168	* @param {Number} seconds	1✔
169	*/	1✔
170	static setValuesCached(namedRange, arrData, seconds = 60) {	1✔
171	const cache = CacheService.getScriptCache();	×
172		×
173	const ss = SpreadsheetApp.getActiveSpreadsheet();	×
174	ss.getRangeByName(namedRange).setValues(arrData);	×
175	cache.put(namedRange, JSON.stringify(arrData), seconds)	×
176	}	×
177		1✔
178	/**	1✔
179	* Check if data from cache is in error.	1✔
180	* @param {any[][]} arrData	1✔
181	* @returns {Boolean}	1✔
182	*/	1✔
183	static verifyCachedData(arrData) {	1✔
184	let verified = true;	3✔
185		3✔
186	for (const rowData of arrData) {	3✔
187	for (const fieldData of rowData) {	75✔
188	if (fieldData === "#ERROR!") {	675!
189	Logger.log("Reading from CACHE has found '#ERROR!'. Re-Loading...");	×
190	verified = false;	×
191	break;	×
192	}	×
193	}	675✔
194	}	75✔
195		3✔
196	return verified;	3✔
197	}	3✔
198		1✔
199	/**	1✔
200	* Checks if this range is loading elsewhere (i.e. from another call to custom function)	1✔
201	* @param {String} namedRange	1✔
202	* @returns {Boolean}	1✔
203	*/	1✔
204	static isRangeLoading(cache, namedRange) {	1✔
205	let loading = false;	3✔
206	const cacheData = cache.get(TableData.cacheStatusName(namedRange));	3✔
207		3✔
208	if (cacheData !== null && cacheData === TABLE.LOADING) {	3!
209	loading = true;	×
210	}	×
211		3✔
212	Logger.log(`isRangeLoading: ${namedRange}. Status: ${loading}`);	3✔
213		3✔
214	return loading;	3✔
215	}	3✔
216		1✔
217	/**	1✔
218	* Retrieve data from cache after it has loaded elsewhere.	1✔
219	* @param {Object} cache	1✔
220	* @param {String} namedRange	1✔
221	* @param {Number} cacheSeconds - How long to cache results.	1✔
222	* @returns {any[][]}	1✔
223	*/	1✔
224	static waitForRangeToLoad(cache, namedRange, cacheSeconds) {	1✔
NEW 225	const start = Date.now();	×
NEW 226	let current = Date.now();	×
227		×
228	Logger.log(`waitForRangeToLoad() - Start: ${namedRange}`);	×
229	while (TableData.isRangeLoading(cache, namedRange) && (current - start) < 10000) {	×
230	Utilities.sleep(250);	×
NEW 231	current = Date.now();	×
232	}	×
233	Logger.log("waitForRangeToLoad() - End");	×
234		×
235	let arrData = TableData.cacheGetArray(cache, namedRange);	×
236		×
237	// Give up and load from SHEETS directly.	×
238	if (arrData === null) {	×
239	Logger.log(`waitForRangeToLoad - give up. Read directly. ${namedRange}`);	×
240	arrData = TableData.loadValuesFromRangeOrSheet(namedRange);	×
241		×
242	if (TableData.isRangeLoading(cache, namedRange)) {	×
243	// Other process probably timed out and left status hanging.	×
244	TableData.cachePutArray(cache, namedRange, cacheSeconds, arrData);	×
245	}	×
246	}	×
247		×
248	return arrData;	×
249	}	×
250		1✔
251	/**	1✔
252	* Read range of value from sheet and cache.	1✔
253	* @param {Object} cache - cache object can vary depending where the data is stored.	1✔
254	* @param {String} namedRange	1✔
255	* @param {Number} cacheSeconds	1✔
256	* @returns {any[][]} - data from range	1✔
257	*/	1✔
258	static lockLoadAndCache(cache, namedRange, cacheSeconds) {	1✔
259	// Only change our CACHE STATUS if we have a lock.	3✔
260	const lock = LockService.getScriptLock();	3✔
261	try {	3✔
262	lock.waitLock(100000); // wait 100 seconds for others' use of the code section and lock to stop and then proceed	3✔
263	} catch (e) {	3!
264	throw new Error("Cache lock failed");	×
265	}	×
266		3✔
267	// It is possible that just before getting the lock, another process started caching.	3✔
268	if (TableData.isRangeLoading(cache, namedRange)) {	3!
269	lock.releaseLock();	×
270	return TableData.waitForRangeToLoad(cache, namedRange, cacheSeconds);	×
271	}	×
272		3✔
273	// Mark the status for this named range that loading is in progress.	3✔
274	cache.put(TableData.cacheStatusName(namedRange), TABLE.LOADING, 15);	3✔
275	lock.releaseLock();	3✔
276		3✔
277	// Load data from SHEETS.	3✔
278	const arrData = TableData.loadValuesFromRangeOrSheet(namedRange);	3✔
279		3✔
280	Logger.log(`Just LOADED from SHEET: Item Count=${arrData.length}`);	3✔
281		3✔
282	TableData.cachePutArray(cache, namedRange, cacheSeconds, arrData);	3✔
283		3✔
284	return arrData;	3✔
285	}	3✔
286		1✔
287	/**	1✔
288	* Read sheet data into double array.	1✔
289	* @param {String} namedRange - named range, A1 notation or sheet name	1✔
290	* @returns {any[][]} - table data.	1✔
291	*/	1✔
292	static loadValuesFromRangeOrSheet(namedRange) {	1✔
293	let tableNamedRange = namedRange;	4✔
294	let output = [];	4✔
295		4✔
296	try {	4✔
297	Logger.log(`Getting Range of Values: ${tableNamedRange}`);	4✔
298	const sheetNamedRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(tableNamedRange);	4✔
299		4✔
300	if (sheetNamedRange === null) {	4✔
301	// This may be a SHEET NAME, so try getting SHEET RANGE.	1✔
302	if (tableNamedRange.startsWith("'") && tableNamedRange.endsWith("'")) {	1✔
303	tableNamedRange = tableNamedRange.substring(1, tableNamedRange.length - 1);	1✔
304	}	1✔
305	let sheetHandle = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tableNamedRange);	1✔
306		1✔
307	// Actual sheet may have spaces in name. The SQL must reference that table with	1✔
308	// underscores replacing those spaces.	1✔
309	if (sheetHandle === null && tableNamedRange.includes("_")) {	1!
NEW 310	tableNamedRange = tableNamedRange.replaceAll('_', " ");	×
311	sheetHandle = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tableNamedRange);	×
312	}	×
313		1✔
314	if (sheetHandle === null) {	1!
315	throw new Error(`Invalid table range specified: ${tableNamedRange}`);	×
316	}	×
317		1✔
318	const lastColumn = sheetHandle.getLastColumn();	1✔
319	const lastRow = sheetHandle.getLastRow();	1✔
320	output = sheetHandle.getSheetValues(1, 1, lastRow, lastColumn);	1✔
321	}	1✔
322	else {	3✔
323	// @ts-ignore	3✔
324	output = sheetNamedRange.getValues();	3✔
325	Logger.log(`Named Range Data Loaded: ${tableNamedRange}. Items=${output.length}`);	3✔
326	}	3✔
327	}	4✔
328	catch (ex) {	4!
329	throw new Error(`Error reading table data: ${tableNamedRange}`);	×
330	}	×
331		4✔
332	return output;	4✔
333	}	4✔
334		1✔
335	/**	1✔
336	* Takes array data to be cached, breaks up into chunks if necessary, puts each chunk into cache and updates status.	1✔
337	* @param {Object} cache	1✔
338	* @param {String} namedRange	1✔
339	* @param {Number} cacheSeconds	1✔
340	* @param {any[][]} arrData	1✔
341	*/	1✔
342	static cachePutArray(cache, namedRange, cacheSeconds, arrData) {	1✔
343	const cacheStatusName = TableData.cacheStatusName(namedRange);	3✔
344	const json = JSON.stringify(arrData);	3✔
345		3✔
346	// Split up data (for re-assembly on get() later)	3✔
347	let splitCount = (json.length / (100 * 1024)) * 1.3; // 1.3 - assumes some blocks may be bigger.	3✔
348	splitCount = Math.max(splitCount, 1);	3✔
349	const arrayLength = Math.ceil(arrData.length / splitCount);	3✔
350	const putObject = {};	3✔
351	let blockCount = 0;	3✔
352	let startIndex = 0;	3✔
353	while (startIndex < arrData.length) {	3✔
354	const arrayBlock = arrData.slice(startIndex, startIndex + arrayLength);	3✔
355	blockCount++;	3✔
356	startIndex += arrayLength;	3✔
357	putObject[`${namedRange}:${blockCount.toString()}`] = JSON.stringify(arrayBlock);	3✔
358	}	3✔
359		3✔
360	// Update status that cache is updated.	3✔
361	const lock = LockService.getScriptLock();	3✔
362	try {	3✔
363	lock.waitLock(100000); // wait 100 seconds for others' use of the code section and lock to stop and then proceed	3✔
364	} catch (e) {	3!
365	throw new Error("Cache lock failed");	×
366	}	×
367	cache.putAll(putObject, cacheSeconds);	3✔
368	cache.put(cacheStatusName, TABLE.BLOCKS + blockCount.toString(), cacheSeconds);	3✔
369		3✔
370	Logger.log(`Writing STATUS: ${cacheStatusName}. Value=${TABLE.BLOCKS}${blockCount.toString()}. seconds=${cacheSeconds}. Items=${arrData.length}`);	3✔
371		3✔
372	lock.releaseLock();	3✔
373	}	3✔
374		1✔
375	/**	1✔
376	* Reads cache for range, and re-assembles blocks into return array of data.	1✔
377	* @param {Object} cache	1✔
378	* @param {String} namedRange	1✔
379	* @returns {any[][]}	1✔
380	*/	1✔
381	static cacheGetArray(cache, namedRange) {	1✔
382	let arrData = [];	6✔
383		6✔
384	const cacheStatusName = TableData.cacheStatusName(namedRange);	6✔
385	const cacheStatus = cache.get(cacheStatusName);	6✔
386	if (cacheStatus === null) {	6✔
387	Logger.log(`Named Range Cache Status not found = ${cacheStatusName}`);	3✔
388	return null;	3✔
389	}	3✔
390		3✔
391	Logger.log(`Cache Status: ${cacheStatusName}. Value=${cacheStatus}`);	3✔
392	if (cacheStatus === TABLE.LOADING) {	6!
393	return null;	×
394	}	×
395		3✔
396	const blockStr = cacheStatus.substring(cacheStatus.indexOf(TABLE.BLOCKS) + TABLE.BLOCKS.length);	3✔
397	if (blockStr !== "") {	3✔
398	const blocks = Number(blockStr);	3✔
399	for (let i = 1; i <= blocks; i++) {	3✔
400	const blockName = `${namedRange}:${i.toString()}`;	3✔
401	const jsonData = cache.get(blockName);	3✔
402		3✔
403	if (jsonData === null) {	3!
404	Logger.log(`Named Range Part not found. R=${blockName}`);	×
405	return null;	×
406	}	×
407		3✔
408	const partArr = JSON.parse(jsonData);	3✔
409	if (TableData.verifyCachedData(partArr)) {	3✔
410	arrData = arrData.concat(partArr);	3✔
411	}	3✔
412	else {	×
413	Logger.log(`Failed to verify named range: ${blockName}`);	×
414	return null;	×
415	}	×
416	}	3✔
417		3✔
418	}	3✔
419	Logger.log(`Just LOADED From CACHE: ${namedRange}. Items=${arrData.length}`);	3✔
420		3✔
421	// The conversion to JSON causes SHEET DATES to be converted to a string.	3✔
422	// This converts any DATE STRINGS back to javascript date.	3✔
423	TableData.fixJSONdates(arrData);	3✔
424		3✔
425	return arrData;	3✔
426	}	6✔
427		1✔
428	/**	1✔
429	* Dates retrieved from a JSON structure need to be converted to JS date.	1✔
430	* @param {any[][]} arrData	1✔
431	*/	1✔
432	static fixJSONdates(arrData) {	1✔
433	const ISO_8601_FULL = /^\d{4}-\d\d-\d\dT\d\d:\d\d:\d\d(\.\d+)?(([+-]\d\d:\d\d)\|Z)?$/i	13✔
434		13✔
435	for (const row of arrData) {	13✔
436	for (let i = 0; i < row.length; i++) {	85✔
437	const testStr = row[i];	685✔
438	if (ISO_8601_FULL.test(testStr)) {	685✔
439	row[i] = new Date(testStr);	72✔
440	}	72✔
441	}	685✔
442	}	85✔
443	}	13✔
444		1✔
445	/**	1✔
446	*	1✔
447	* @param {String} namedRange	1✔
448	* @returns {String}	1✔
449	*/	1✔
450	static cacheStatusName(namedRange) {	1✔
451	return namedRange + TABLE.STATUS;	15✔
452	}	15✔
453	}	1✔
454		1✔
455	const TABLE = {	1✔
456	STATUS: "__STATUS__",	1✔
457	LOADING: "LOADING",	1✔
458	BLOCKS: "BLOCKS="	1✔
459	}	1✔
460

demmings / gsSQL / 19122082321

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