Excel Office Script - set a variable to a specific cell value

Question

Hi - I am using an Excel Workbook in the Microsoft Office 365 platform.

I am creating Microsoft Office Script to automate a process.

I would like to set a variable in the script to a value in the Excel Workbook from a specific sheet and cell.

Example:

In the Microsoft Office Script I have the variable "RowCounter01"

In my Workbook I have Sheet ("RDate") and in the Sheet a specific cell with a value (cell "J1").

How do I code the automated script to set the RowCounter01 variable to the value in RDate, cell J1 ?

If the value in J1 is 123 then RowCounter01 would be set to 123.

Any assistance is appreciated - thank you - jerryr125

Answer 1

Hi jerry,

To set the RowCounter01 variable to the value in RDate, cell J1 using Microsoft Office Script in Excel, you can use the following code:

// Get the value from cell J1 in the RDate sheet
let sheetName = "RDate"; 
let cellAddress = "J1"; 
let value = context.workbook.worksheets.getItem(sheetName).getRange(cellAddress).values[0][0];  

// Set the RowCounter01 variable to the retrieved value 
let RowCounter01 = value;

In the above code-

• The 'sheetName' variable is set to the name of the sheet you want to retrieve the value from.
• The 'cellAddress' variable is set to the cell address of the cell you want to retrieve the value from.
• The 'context.workbook.worksheets.getItem(sheetName)' function is used to get the specified sheet from the workbook.
• The 'getRange(cellAddress)' function is used to get the range of the specified cell.
• The 'values[0][0]' property is used to retrieve the value of the cell as a JavaScript array.
• And, the retrieved value is assigned to the 'RowCounter01' variable.

Note- Make sure to add the necessary Microsoft Office Script APIs and authentication code to your script before running this code.

Best Regards.