How to Get the Range to Set Value of a Cell In Google Apps Script – With Examples

Cell range refers to the address of the cell in Google Sheets.

You can get the range to set value of a Cell in Google Apps Script using the SpreadsheetApp.getActiveSheet().getRange(‘F2’) method.

Basic Example

The following code sets the value of the cell F2 with the value Value to Set in the currently active Google sheet.

SpreadsheetApp.getActiveSheet().getRange('F2').setValue('Value To Set');

This tutorial explains the different methods to get a range of Cells from Google Sheets to set values in Google Apps Script. The range can either be a single cell or multiple cells.

Using GetRange And Cell Name

The getRange() method returns the range of the cells as specified using the A1 notation or R1C1 notation.

• The A1 notation is the direct reference of the Cell Address- For example, A1 is the first row and column of the sheet.
• The R1C1 notation defines a cell with a string that contains the starting and ending cell coordinates using row numbers and column numbers.

Code

The following code demonstrates how to

• Get the range of the Cell F2 using the A1 notation
• Set the value using the setValue() method

SpreadsheetApp.getActiveSheet().getRange('F2').setValue('Hello');

Output

Using GetRange And Cell Indexes(numbers)

This section demonstrates how to get the range using the row index, the column index, and the getRange() method.

Parameters

• Row Number – 1-based Index of the row. Use 1 to specify the first row and 2 to specify the second row, and so on
• Column Number – 1-based Index of the column. Use 1 to specify the first column and 2 to specify the second column, and so on

Code

The following code gets the cell from the second row, and the third column and sets its value to Test.

SpreadsheetApp.getActiveSheet().getRange(2,3).setValue('Test');

Output

Get a Range of Multiple Cells

This section teaches you how to get a range of multiple cells and set their value.

To get the range of multiple cells,

• Pass the range to the getRange() method. For example, A2:C2 to get the cell from A2 until C2
• Set their value using the setValues() method
• The number of values you pass to the setValues() method must be equal to the number of cells you get using the getRange() method

Code

The following code demonstrates how to get three cells from A2 to C2 and set the value to them.

 var values = [
    ["Cell Value 1", "Cell Value 2", "Cell Value 3"]
  ];

  var range = SpreadsheetApp.getActiveSheet().getRange("A2:C2");

  range.setValues(values);

Output

GetRange From Another Spreadsheet

To get the range of cells from another spreadsheet,

• Open another spreadsheet using its id
• Get the desired sheet using the getSheetByName() method
• Use the getRange() method to get the range of cells
• Set the value using the setValue() method

Code

SpreadsheetApp.openById(sheet_id).getSheetByName(Sheet_name).getRange("B2").setValue("VALUE");

Using GetActiveRange

To set the value of the currently active cell,

• Get the active cell using the getActiveRange() method
• Use the setValue() method

Code

SpreadsheetApp.getActiveRange().setValue('hello');

Additional Resources

• How to Check if a Cell is Empty in Google Apps Script(Google Sheets)