How to get cell value in Google Sheets using apps script

There are different ways to get the cell values. This article shows you different ways to get cell values from the Google Sheet. Some of the methods (like getCell(row, col) ) will be easier if you want to iterate through the cells.

Get selected cell value in your Google Sheet Script

First, let us add a menu item to your Google sheet so that it is easy for us to try the functions.

function onOpen() 
  var ui = SpreadsheetApp.getUi();
      .addItem('get current', 'getCurrentCellValue')

Now we have to add getCurrentCellValue() function. This function will get the value of the currently selected cell and then show it in a message box.

function getCurrentCellValue()
  var vv = SpreadsheetApp.getActiveSheet().getActiveCell().getValue();
  SpreadsheetApp.getUi().alert("The active cell value is "+vv);

In order to make it a little more informative, let us show the cell’s A1 notation and then its value.

function getCurrentCellValue()
  var cell = SpreadsheetApp.getActiveSheet().getActiveCell();
  var a1 = cell.getA1Notation();
  var val = cell.getValue();
  SpreadsheetApp.getUi().alert("The active cell "+a1+" value is "+val);

Get Cell by row and column

You can get a a range by using this function sheet.getRange(row,col)
In order to get the value, use the getValue() function. example:

var row = 5;
var col = 2;

var value = SpreadsheetApp.getActiveSheet().getRange(row, col).getValue();

Another example

var value = SpreadsheetApp.getActiveSheet().getRange(12, 2).getValue();

Get Cell by A1 notation and then get its value

Getting value using A1 notation is also similar.

First, you have to get the range. Then do getValue on the range.


var value = SpreadsheetApp.getActiveSheet().getRange('B12').getValue();

Or in two steps:

var range = SpreadsheetApp.getActiveSheet().getRange('B12');
var value = range.getValue();

Sample Sheet:
See the sample sheet here

Make a copy and go to Tools → Script editor to make changes.

Full script code:

Leave a Reply 0 comments

Leave a Reply: