I have a script that will copy the value from a source cell to a target cell, but I am unable to figure out how to ignore a blank cell from being copied to the target cell. I need to have the source ignore copying the blank value into the target cell.
This is where I am currently at:
function CopyCellsCQF() { // Copies cells from bottom of sheet to individual selected cells
var ss3 = SpreadsheetApp.getActiveSpreadsheet();
ss3.getRange('CQF!B60').copyTo(ss3.getRange('CQF!J7'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); //Age #1
ss3.getRange('CQF!C60').copyTo(ss3.getRange('CQF!W7'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); //Age #2
ss3.getRange('CQF!D60').copyTo(ss3.getRange('CQF!H11:K11'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); //Amount
ss3.getRange('CQF!F60').copyTo(ss3.getRange('CQF!E8:L8'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); //Email #1
ss3.getRange('CQF!G60').copyTo(ss3.getRange('CQF!C6:J6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); //Name #1
ss3.getRange('CQF!H60').copyTo(ss3.getRange('CQF!D7:H7'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); //DOB #1
ss3.getRange('CQF!I60').copyTo(ss3.getRange('CQF!L6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); //Sex #1
ss3.getRange('CQF!R60').copyTo(ss3.getRange('CQF!L6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); //Sex #1 }
As you can see, the last two lines of the code below have 2 different sources but have the same target cell. Only I60 or R60 will have a value at one time. They never both will have a value in it at the same time. As it is now if I60 has a value and R60 is blank, the script will write the value into L6 from I60 and then the Blank value from R60 will overwrite and place a blank value into L6.
Forgive me if this sounds confusing. Any help will be much appreciated. Thank you
You can treat each exception scenario separately.
If CQF!I60 is NOT empty, copy that value to CQF!L6, if CQF!R60 is NOT empty, copy that value to CQF!L6.
…
var sex1 = ss3.getRange('CQF!I60').getValue();
var sex2 = ss3.getRange('CQF!R60').getValue();
if (sex1.trim().length > 0){ //if sex1 is not empty…
//copy sex1 to target cell
ss3.getRange('CQF!L6').setValue(sex1);
};
if (sex2.trim().length > 0){ //if sex2 is not empty…
//copy sex2 to target cell ()
ss3.getRange('CQF!L6').setValue(sex2);
};
CQF!I60 and CQF!R60 have values, CQF!L6 will be overwritten. However that might be intentional depending on your use case.