Cells copy/paste.(Microsoft Excel Office Scripts)

03/25/2023

Japanese version.

Instructions on how to copy/paste cells in Office scripts.

Operations

Using the script record will target the active sheet, though,but the behavior changes depending on where it is launched,
It is recommended that you specify the name of the sheet. (Red text indicates variable areas).

cellRangeToPasteTo.copyFrom(sourceRange, copyType, skipBlanks, transpose)

All parameters can be omitted except for the parameter "sourceRange".
The paste destination is a cell range, but a single cell is also acceptable.
The rules for pasting are the same as for copying and pasting in Excel.
The following example copies the entire contents of cells A1:A5 to cell E5 on all Test sheets.

function main(workbook: ExcelScript.Workbook) {
  workbook.getWorksheet("Test").getRange("E5").copyFrom(
    workbook.getWorksheet("Test").getRange("A1:A5")
  );
}
Specified by getRange
function main(workbook: ExcelScript.Workbook) {
  workbook.getWorksheet("Test").getRange("E5").copyFrom("A1:A5");
}
Specified by cell address

Parameters

sourceRange

The first parameter of the copyFrom method and cannot be omitted.

Specifies the address of the cell range from which to copy.

copyType

If false, normal pasting will occur. If omitted, it is false.

For example, if the source cell is blank and the destination cell has a value, if false, the source cell is overwritten with blanks, and if true, the destination cell's value remains unchanged.

Set valueDescription
ExcelScript.RangeCopyType.allNormal paste.
This is the setting when omitted.
ExcelScript.RangeCopyType.formatsOnly the format is pasted.
ExcelScript.RangeCopyType.formulasPaste only formulas.
ExcelScript.RangeCopyType.valuesPaste only the value.

skipBlanks

It is the third parameter of the copyFrom method and is optional.

If true, if the source cell has no value, it will be ignored (if there is only a format, it will also be ignored).

(If there is only formatting, that is also ignored.)

If false, normal pasting will occur. If omitted, it is false.

For example, if the source cell is blank and the destination cell has a value, if false, the source cell is overwritten with blanks, and if true, the destination cell's value remains unchanged.

transpose

It is the fourth parameter of the copyFrom method and is optional.

If true, the matrix is swapped.

If false, normal pasting is performed. If omitted, it is false.

For those who want to learn Office script effectively

The information on this site is now available in an easy-to-read e-book format.

Or Kindle Unlimited (unlimited reading).

You willl discover how to about basic operations.

By the end of this book, you will be equipped with the knowledge you need to use Excel Office Script to streamline your workflow.

Links

Office Scripts Articles