copying data results in "invisible" cells that can also *lose data*?!

[Edward Wu]

I'm not sure this actually has to do with GAS, but it only seems to happen with data that's been copied via script, and it only happens *sometimes*.

I use a general "copy data" script that I put in a Library. The full code is below and I really don't think there's anything "unusual" about it.

I use the copy routine a lot to archive data. Nothing fancy is going on. But here's an example of what happens *sometimes* to *some* cells.

(And this only happens to cells with numeric values, as far as I can tell)

After a copy job, I'll look at the destination GS and notice what appears to be empty cells where there should be numeric data.

But if you select those cells, you'll see that they *do* contain a number in the formula bar.

No, changing font/cell colors don't affect the visibility at all, and there's zero conditional formatting. And no, doing "clear formatting" doesn't make it visible either. And no, we're not using fancy fonts...just plain ol' Arial.

But changing the cell Format to e.g. Automatic or Currency or Number "reveals" the data.

It doesn't seem to matter what the cell format was set to *before* data was copied into the cell.

Here's the kicker...if you set the cell format to plain text, the data is *removed*. Yes, you can *lose data* by changing its format.

But if you change the cell format to Automatic, *then* change it to plain text, the data "stays" and is visible.

(The reason it needs to be plain text is because we have some data with a leading zero that needs to be preserved. Plain text preserves the zero.)

Has anyone else experienced anything like this, and/or is there anything weird about my code? All I'm really doing is getValues and setValues.

(and for what it's worth, this happens even when I'm *not* copying formatting...I'm just copying straight data)

/**

* Copies data from the source to the target

* @param {Object} parameters The parameters for the copy function

* @property {Object} [sourceSpreadsheet=thisGs] The source SpreadSheet to copy from

* @property {string} sourceSheetName The name of the source Sheet

* @property {Object} [targetSpreadsheet=thisGs] The target SpreadSheet to copy to

* @property {string} targetSheetName The name of the target Sheet

* @property {number} [startingRow=1] The row to start copying from

* @property {number} [endingRow] The row to stop copying to

* @property {string} [evalColLetter=1st 10 cols] Use this column to determine the number of rows to copy. Usually, this is the column that has complete info in every row.

* @property {string} sourceRangeStartLetter The starting Col letter to copy from

* @property {string} sourceRangeEndLetter The ending Col letter to copy from

* @property {string} targetRangeStartLetter The starting Col letter to copy to

* @property {boolean} isReplaceData true = *replace* data on target & clear the target range when copying. false = *append* data

* @property {string} [formulasOnly=false] true = copy *only* formulas. false = convert formulas to static values. "mix" = combine static *and* formulas

* @property (boolean) [copyFormatting=false] true = copy formatting. false = don't copy formatting

* @property {number} [filterColNumber] Copy *only* rows where Col Number has data

*

* @return {String} sourceNumberOfRows Number of rows copied

*/

function copyData(parameters) {

let { sourceSpreadsheet,

sourceSheetName,

targetSpreadsheet,

targetSheetName,

startingRow,

endingRow,

evalColLetter,

sourceRangeStartLetter,

sourceRangeEndLetter,

targetRangeStartLetter,

isReplaceData,

formulasOnly,

copyFormatting,

filterColNumber } = parameters;

// set defaults for missing parameters

sourceSpreadsheet = sourceSpreadsheet ? sourceSpreadsheet : thisGs;

targetSpreadsheet = targetSpreadsheet ? targetSpreadsheet : thisGs;

startingRow = startingRow ? startingRow : 1;

formulasOnly = formulasOnly ? formulasOnly : false;

copyFormatting = copyFormatting ? copyFormatting : false;

let dataToCopy = "";

let ss = sourceSpreadsheet.getSheetByName(sourceSheetName);

let ts = targetSpreadsheet.getSheetByName(targetSheetName);

let sourceRangeStartColNumber = ss.getRange(sourceRangeStartLetter + "1").getColumn();

let sourceRangeEndColNumber = ss.getRange(sourceRangeEndLetter + "1").getColumn();

let sourceNumberOfCols = sourceRangeEndColNumber - sourceRangeStartColNumber + 1;

let targetRangeStartColNumber = ss.getRange(targetRangeStartLetter + "1").getColumn();

let sourceLastRow;

let targetFirstEmptyRow;

if (endingRow) {

sourceLastRow = +endingRow;

targetFirstEmptyRow = sourceLastRow;

} else {

sourceLastRow = getLastRowOfColUpTo1st10(ss, evalColLetter);

targetFirstEmptyRow = ts.getLastRow() + 1;

}

let sourceNumberOfRows = sourceLastRow - startingRow + 1;

if (sourceNumberOfRows > 0) {

let sourceToGet = sourceRangeStartLetter + startingRow + ":" + sourceRangeEndLetter + sourceLastRow;

if (formulasOnly === "mix") {

dataToCopy = getValuesAndFormulas(ss, sourceToGet);

}

else if (formulasOnly == true) {

dataToCopy = ss.getRange(sourceToGet).getFormulas();

}

else {

dataToCopy = ss.getRange(sourceToGet).getValues();

}

if (filterColNumber) {

dataToCopy = keepOnlyRowsWithDataInCol(dataToCopy, filterColNumber);

sourceNumberOfRows = dataToCopy.length;

}

if (isReplaceData) {

ts.getRange(startingRow, targetRangeStartColNumber, targetFirstEmptyRow, sourceNumberOfCols).clearContent();

targetFirstEmptyRow = startingRow;

}

if (copyFormatting === true) {

let sBG = ss.getRange(sourceToGet).getBackgrounds();

let sFC = ss.getRange(sourceToGet).getFontColors();

let sFF = ss.getRange(sourceToGet).getFontFamilies();

let sFL = ss.getRange(sourceToGet).getFontLines();

let sFSz = ss.getRange(sourceToGet).getFontSizes();

let sFSt = ss.getRange(sourceToGet).getFontStyles();

let sFW = ss.getRange(sourceToGet).getFontWeights();

let sHA = ss.getRange(sourceToGet).getHorizontalAlignments();

let sVA = ss.getRange(sourceToGet).getVerticalAlignments();

let sNF = ss.getRange(sourceToGet).getNumberFormats();

let sWR = ss.getRange(sourceToGet).getWraps();

ts.getRange(targetFirstEmptyRow, targetRangeStartColNumber, sourceNumberOfRows, sourceNumberOfCols).setValues(dataToCopy)

.setBackgrounds(sBG)

.setFontColors(sFC)

.setFontFamilies(sFF)

.setFontLines(sFL)

.setFontSizes(sFSz)

.setFontStyles(sFSt)

.setFontWeights(sFW)

.setHorizontalAlignments(sHA)

.setVerticalAlignments(sVA)

.setNumberFormats(sNF)

.setWraps(sWR);

} else {

ts.getRange(targetFirstEmptyRow, targetRangeStartColNumber, sourceNumberOfRows, sourceNumberOfCols).setValues(dataToCopy);

}

}

return sourceNumberOfRows;

}