Search code examples
google-apps-scriptgoogle-sheetscopy

Google Sheets - App Script (copyTo - cell content & format)

I have a tracker to count daily productivity.

This is appended to a weekly tracker, in which the sum of the two serves as a running total.

The weekly tracker is appended to a separate sheet to serve as historical data.

TRACKER

DATA

As you can see in the 'DATA' screenshot, the formula is being copied from K12:O12

  • see function copyTT / source.copyTo

I want to retain cell formatting, and copy only the cell data, not it's formula.

Script;

function mergeTT() { 

var ss = SpreadsheetApp.getActiveSpreadsheet (); 

var source = ss.getRange("NEW_TTRACKER!J3:O3"); 

var destSheet = ss.getSheetByName("NEW_TTRACKER"); 

var lastRow = destSheet.getRange("J7:J11").getValues().filter(String).length;

var destRange = destSheet.getRange(lastRow+7,10,1,6); 

source.copyTo(destRange, {contentsOnly: true});

var ws = ss.getSheetByName("NEW_TTRACKER")

ws.getRange('K3:O3').clearContent();

} 

function copyTT() { 

var ss = SpreadsheetApp.getActiveSpreadsheet (); 

var source = ss.getRange("NEW_TTRACKER!J5:O12"); 

var destSheet = ss.getSheetByName("NEW_TDATA");

var destRange = destSheet.getRange(destSheet.getLastRow()+1,1); 

source.copyTo(destRange, {contentsOnly: false});

var sheet = SpreadsheetApp.getActive().getSheetByName("NEW_TTRACKER"); 

sheet.getRange('J7:O11').clearContent(); 

}

Thanks in advance.

I've been attempting to get around it playing with different CopyPasteType properties and I'm really not getting anywhere.

Solution

  • About I've been attempting to get around it playing with different CopyPasteType properties, I think that your approach is correct. In your script, how about modifying your script as follows?

    From:

    source.copyTo(destRange, {contentsOnly: true});

    To:

    source.copyTo(destRange, { contentsOnly: true });
source.copyTo(destRange, SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false); // Added
    • By this modification, the format is also copied.

    Reference: