4049202619

Build # 4049202619

Build Type

push

github

Committed by cdemmigs

Commit Message

readme update.

Run Details

1254 of 1328 branches covered (94.43%)

Branch coverage included in aggregate %.

9725 of 10121 relevant lines covered (96.09%)

505.61 hits per line

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

83.27

/src/TableData.js

//  Remove comments for testing in NODE
//
/*  *** DEBUG START ***
export { TableData };
import { CacheService, LockService, SpreadsheetApp, Utilities } from "./SqlTest.js"; 
import { ScriptSettings } from "./ScriptSettings.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 (typeof namedRange === 'undefined' || namedRange === "")
            return [];

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

        let tempData = TableData.getValuesCached(namedRange, cacheSeconds)

        tempData = tempData.filter(e => e.join().replace(/,/g, "").length);

        return tempData;
    }

    /**
     * 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()) {
                cache.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);
    }

    /**
     * 
     * @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 = new Date().getTime();
        let current = new Date().getTime();

        Logger.log(`waitForRangeToLoad() - Start: ${namedRange}`);
        while (TableData.isRangeLoading(cache, namedRange) && (current - start) < 10000) {
            Utilities.sleep(250);
            current = new Date().getTime();
        }
        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(10000); // wait 10 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: ${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 {
            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);
                }
                const 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();
            }
        }
        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 = splitCount < 1 ? 1 : splitCount;
        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(10000); // wait 10 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 = parseInt(blockStr, 10);
            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;
    }

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

demmings / gsSQL / 4049202619

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