Copy a Range to another spreadsheet with data validations, formats etc-CodePudding

I managed to copy values and formulas from one range to another spreadsheet range (of the same size).

I need to copy formats, fonts, colors, borders, data validations etc to the new range in another spreadsheet.

Is it possible?

CodePudding user response：

There are actually multiple ways to do this with built-in methods rather than manually. The easiest one is to use copyTo(). This copies everything you mentioned from a source range to a destination. A simple sample of how it works:

    //Replicate rangeA on rangeB

    let ss = SpreadsheetApp.getActiveSpreadsheet();
    let source = ss.getSheetByName("Sheet1")
    let destination = ss.getSheetByName("Sheet2");
    
    let rangeA = source.getRange("A1:A5");
    let rangeB = destination.getRange("A1");
    
    rangeA.copyTo(rangeB);

As mentioned in the documentation, only the top-left cell position in the destination range is relevant, so you only need to specify a single cell and the method will just expand the copied data accordingly.

In case that you need to narrow down what you need to copy there are other methods to just copy certain parts, for formatting you can use copyFormatToRange(), for just values you can use copyValuesToRange(), for data validations you first need to get them with getDataValidations() and then set them separately with setDataValidations(). Formulas work similarly with getFormulas() to setFormulas(), and so on.

I suggest you check the documentation for more code samples. The Range object actually has a lot of useful methods to manipulate ranges:

Class Range