Set value to cell / Get value from cell.(Microsoft Excel Office Scripts)

03/19/2023

Introduces basic operations on cell values in Office scripts.

Contents

1. Operations
2. For those who want to learn Office script effectively
3. Links
- 3.1. Office Scripts Articles

Operations

Using script recordings will target the active sheet.

However, that would change the behavior depending on where the script is run, so it is recommended to specify the sheet name as well.

Set value to cell

The following code will set the value in any cell. (Change the red text accordingly)

workbook.getWorksheet("sheet name").getRange("cell address").setValue("value");

The following example sets "123" in cell D4 of the "Sheet1".

Getting a value from a cell

The following code will retrieve a value from any cell and store it in a variable. (Change the red text as necessary)

let variable = workbook.getWorksheet("sheet name").getRange("cell address").getValue();

The following example gets the value of cell D4 on the "Sheet1" and sets it to the variable val.

Transcribing from cell to cell

The following code will transcribe values from any cell to cell. (Red text should be changed accordingly)

let variable = workbook.getWorksheet("Transcription source sheet name").getRange("Transcription source cell address").getValue();
workbook.getWorksheet("Transcription Destination Sheet").getRange("Transcription Destination Cell Address").setValue(variable);

In the following example, the value of cell D4 of the "Sheet1" is retrieved and set to the variable val, and

The following example gets the value of D4 cell on the "Sheet1", sets it to the variable val, and then sets the value to D5 cell. (Transcription from D4 to D5)

The following will transcribe without variables.

workbook.getWorksheet("Transcription Destination sheet name").getRange("Transcription Destination cell address").setValue(
  workbook.getWorksheet("Transcription source Sheet").getRange("Transcription source
Cell Address").getValue()
);

The following will post an arbitrary calculation. (An error will occur if the source of posting is not a numerical value.)

let variable = workbook.getWorksheet("Transcription source sheet name").getRange("Transcription source cell address").getValue();
workbook.getWorksheet("Transcription destination sheet name").getRange("Transcription destination cell address").setValue(Number(variable) + number to add);

The following example takes the value in cell D4 of the Sheet1, sets it to the variable val, adds 25 to the value, and sets the value in cell D5. (Transcription from D4 to D5)

The Number function converts a string to a number.

If you do not use this function, the value will be treated as a string and an error will occur.

It is also possible without variables. (although it will be difficult to read)

Specified by row/column number, not cell address

If getRange("cell address") is changed as follows, it will be specified by number.

Note that in this specification, the first row and the first column start from 0.

getCell(row number - 1, column number - 1)

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.

Click here to buy!!