'Apps script array being written to when read from
First a quick explanation; I'm working on a Google Sheets script (Apps script), but this part of the code:
Logger.log("#1 store[6]: "+store[6]);
emptyCells[0] = store[rowNr][0]; //Adds shelf name to template
Logger.log("#2 store[6]: "+store[6]);
Will generate these logs:
Jan 25, 2022, 6:56:38 PM Info #1 store[6]: H3(R3),,,,,,,,,,
Jan 25, 2022, 6:56:38 PM Info #2 store[6]: H6(R3),,,,,,,,,,
"Store" should not be changed?
Explanation; I store the whole sheet in a two dimensional array called "store", it looks like this:
[H1(R3), ABC123, , , , , 8.0, 8.0, 8.0, 8.0, ],
[H2(R3), GKH456, , , , , , , , , ],
[H3(R3), , , 3.0, , , , , , , ],
[H4(R3), DEF123, , , , , , , , , ],
[H5(R3), CDE123, , , , , , , , , ],
[H6(R3), , , , , , 2.0, , , , ],
...
The relevant code looks like this
function clearEmpty(store){ //Removes info from empty shelves. "store" = array of whole sheet
var emptyCells = ["", "", "", "", "", "", "", "", "", "", ""]; //Template for an empty row (needs shelf name)
var splicedShelves = []; //To splice off shelf names from store (to "emptyCells")
var emptyRows = [] //To store empty row places
var removedShelves = []; //To store names of cleared shelves
var rowNr;
emptyRows = getEmptyShelves(store); //Function return the row numbers of the empty rows
//returned this:
//[6.0, 9.0, 11.0, 12.0, 23.0, 29.0, 30.0, 31.0, 33.0, 34.0, 38.0, 42.0, 47.0, 49.0, 57.0, 58.0,
// 59.0, 60.0, 62.0, 63.0, 67.0, 78.0, 88.0, 90.0, 92.0, 94.0, 95.0, 96.0, 107.0]
Logger.log("emptyRows: "+emptyRows);
for (let i = 0; i < emptyRows.length; i++){ //Loops through emptyRows
rowNr = emptyRows[i]; //Extract row number
splicedShelves = store[rowNr].slice(1); //Remove shelf name
if (splicedShelves.every(element => element == "") === false){ //Check that row is not completely empty
removedShelves.push(store[rowNr][0]); //Saves cleared shelf name
Logger.log("rowNr: "+rowNr);
Logger.log("#1 store[6]: "+store[6]);
emptyCells[0] = store[rowNr][0]; //Adds shelf name to template
Logger.log("#2 store[6]: "+store[6]);
Logger.log("emptyCells: "+emptyCells);
Logger.log("store[rowNr]: "+store[rowNr])
store[rowNr] = emptyCells; //Adds empty template with shelf name
}
}
The logs looks like this
Jan 25, 2022, 7:33:21 PM Info emptyRows: 6,9,11,12,23,29,30,31,33,34,38,42,47,49,57,58,59,60,62,63,67,78,88,90,92,94,95,96,107
Jan 25, 2022, 7:33:21 PM Info rowNr: 6
Jan 25, 2022, 7:33:21 PM Info #1 store[6]: H3(R3),,,3,,,,,,,
Jan 25, 2022, 7:33:21 PM Info #2 store[6]: H3(R3),,,3,,,,,,,
Jan 25, 2022, 7:33:21 PM Info store[rowNr]: H3(R3),,,3,,,,,,,
Jan 25, 2022, 7:33:21 PM Info emptyCells: H3(R3),,,,,,,,,,
Jan 25, 2022, 7:33:21 PM Info rowNr: 9
Jan 25, 2022, 7:33:21 PM Info #1 store[6]: H3(R3),,,,,,,,,,
Jan 25, 2022, 7:33:21 PM Info #2 store[6]: H6(R3),,,,,,,,,,
Jan 25, 2022, 7:33:21 PM Info emptyCells: H6(R3),,,,,,,,,,
Jan 25, 2022, 7:33:21 PM Info store[rowNr]: H6(R3),,,,,,2,,,,
Jan 25, 2022, 7:33:21 PM Info rowNr: 11
Jan 25, 2022, 7:33:21 PM Info #1 store[6]: H6(R3),,,,,,,,,,
Jan 25, 2022, 7:33:21 PM Info #2 store[6]: H8(R3),,,,,,,,,,
Jan 25, 2022, 7:33:21 PM Info emptyCells: H8(R3),,,,,,,,,,
Jan 25, 2022, 7:33:21 PM Info store[rowNr]: H8(R3),,,,,,2,,,,
Jan 25, 2022, 7:33:21 PM Info rowNr: 42
Jan 25, 2022, 7:33:21 PM Info #1 store[6]: H8(R3),,,,,,,,,,
Jan 25, 2022, 7:33:21 PM Info #2 store[6]: H39(R2),,,,,,,,,,
Jan 25, 2022, 7:33:21 PM Info emptyCells: H39(R2),,,,,,,,,,
Jan 25, 2022, 7:33:21 PM Info store[rowNr]: H39(R2),,,6,,,,,,,
The cleared rows should look like this:
#6 store[6]: H3(R3),,,,,,,,,,
#9 store[9]: H6(R3),,,,,,,,,,
#11 store[11]: H8(R3),,,,,,,,,,
#42 store[42]: H39(R2),,,,,,,,,,
But they look like this
#6 store[6]: H39(R2),,,,,,,,,,
#9 store[9]: H39(R2),,,,,,,,,,
#11 store[11]: H39(R2),,,,,,,,,,
#42 store[42]: H39(R2),,,,,,,,,,
Okay, the code works like this: it gets all rows without registration number (like ABC123):
emptyRows = getEmptyShelves(store); //Function return the row numbers of the empty rows
Returned this:
[6.0, 9.0, 11.0, 12.0, 23.0, 29.0, 30.0, 31.0, 33.0, 34.0, 38.0, 42.0, 47.0, 49.0, 57.0, 58.0, 59.0, 60.0, 62.0, 63.0, 67.0, 78.0, 88.0, 90.0, 92.0, 94.0, 95.0, 96.0, 107.0]
Then it copies the row but without the shelf name (like H3(R3)):
splicedShelves = store[rowNr].slice(1); //Remove shelf name
If any element in the row is not empty it selects that row and saves the shelf name (like H1(R3) to the template:
emptyCells[0] = store[rowNr][0]; //Adds shelf name to template
Then it stores the template in "store" at the stated row:
store[rowNr] = emptyCells; //Adds empty template with shelf name
The problem is that
emptyCells[0] = store[rowNr][0];
For some reason writes to store also! I have It is the second time arrays have behaved strange for me. Is this a bug or am I missing something? Any input is much appreciated!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
