Edit

Cut, copy, and paste ranges using the Excel JavaScript API

  • Article

This article provides code samples that cut, copy, and paste ranges using the Excel JavaScript API. For the complete list of properties and methods that the Range object supports, see Excel.Range class.

Note

The Excel JavaScript API doesn't have a "Cell" object or class. Instead, the Excel JavaScript API defines all Excel cells as Range objects. An individual cell in the Excel UI translates to a Range object with one cell in the Excel JavaScript API. A single Range object can also contain multiple contiguous cells. See Work with cells using the Excel JavaScript API to learn more.

Copy and paste

The Range.copyFrom method replicates the Copy and Paste actions of the Excel UI. The destination is the Range object that copyFrom is called on. The source to be copied is passed as a range or a string address representing a range.

The following code sample copies the data from A1:E1 into the range starting at G1 (which ends up pasting into G1:K1).

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    // Copy everything from "A1:E1" into "G1" and the cells afterwards ("G1:K1").
    sheet.getRange("G1").copyFrom("A1:E1");
    await context.sync();
});

Range.copyFrom has three optional parameters.

copyFrom(sourceRange: Range | RangeAreas | string, copyType?: Excel.RangeCopyType, skipBlanks?: boolean, transpose?: boolean): void;

copyType specifies what data gets copied from the source to the destination.

  • Excel.RangeCopyType.formulas transfers the formulas in the source cells and preserves the relative positioning of those formulas' ranges. Any non-formula entries are copied as-is.
  • Excel.RangeCopyType.values copies the data values and, in the case of formulas, the result of the formula.
  • Excel.RangeCopyType.formats copies the formatting of the range, including font, color, and other format settings, but no values.
  • Excel.RangeCopyType.all (the default option) copies both data and formatting, preserving cells' formulas if found.

skipBlanks sets whether blank cells are copied into the destination. When true, copyFrom skips blank cells in the source range. Skipped cells will not overwrite the existing data of their corresponding cells in the destination range. The default is false.

transpose determines whether or not the data is transposed, meaning its rows and columns are switched, into the source location. A transposed range is flipped along the main diagonal, so rows 1, 2, and 3 will become columns A, B, and C.

The following code sample and images demonstrate this behavior in a simple scenario.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    // Copy a range, omitting the blank cells so existing data is not overwritten in those cells.
    sheet.getRange("D1").copyFrom("A1:C1",
        Excel.RangeCopyType.all,
        true, // skipBlanks
        false); // transpose
    // Copy a range, including the blank cells which will overwrite existing data in the target cells.
    sheet.getRange("D2").copyFrom("A2:C2",
        Excel.RangeCopyType.all,
        false, // skipBlanks
        false); // transpose
    await context.sync();
});

Data before range is copied and pasted

Data in Excel before range's copy method has been run.

Data after range is copied and pasted

Data in Excel after range's copy method has been run.

Cut and paste (move) cells

The Range.moveTo method moves cells to a new location in the workbook. This cell movement behavior works the same as when cells are moved by dragging the range border or when taking the Cut and Paste actions. Both the formatting and values of the range are moved to the location specified as the destinationRange parameter.

The following code sample moves a range with the Range.moveTo method. Note that if the destination range is smaller than the source, it will be expanded to encompass the source content.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    sheet.getRange("F1").values = [["Moved Range"]];

    // Move the cells "A1:E1" to "G1" (which fills the range "G1:K1").
    sheet.getRange("A1:E1").moveTo("G1");
    await context.sync();
});

See also