What's the function to get a value of a cell?

LORENZO CORAZZA 0
Viewed 44

At first i tought the function was

spreadsheet.getValue('A1')

But it doesn't seem to work. Is it possible to execute loops or operations with a similar function? if not, how can i store the value in a variable?

I know this is a really basic question but i can't find anything in the documentation. I'm really new to this tipe of language.

google-sheets google-apps-script
2 Answers

To get value of A1, you need to getRange() first.

console.info(
  SpreadsheetApp.getActive() //returns class Spreadsheet
   .getSheetByName('Sheet1') //returns class Sheet
   .getRange('A1')  //returns class Range
   .getValue() //returns type string or number or date according to the cell's type
)
TheMaster0

There are several ways to get a cell value from Google Sheets. First you should be aware that Google Apps Script uses JavaScript as programming language. Google Apps Script has the Spreadsheet Service. This service includes several classes, enums and methods that makes easy to handle Google Sheets spreadsheets.

Below there are 5 samples that shows some of the ways to get cell value. The first 4 samples use a Range object, the 5th sample doesn't use a Range object.

/**
 * Get a value using A1 (cell using A1 notation) style reference from the first sheet
 */
function sample1(){
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const range = spreadsheet.getRange('A1');
  const value = range.getValue();
  console.log(value);
}

/**
 * Get a value using Sheet1!A1 (sheet name and cell using A1 notation) style reference
 */
function sample2(){
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const range = spreadsheet.getRange('Sheet2!A1');
  const value = range.getValue();
  console.log(value);
}

/**
 * Get a value using Sheet1!R1C1 (sheet name and cell using R1C1 notation) style reference
 */
function sample3(){
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const range = spreadsheet.getRange('Sheet2!R1C1');
  const value = range.getValue();
  console.log(value);
}

/**
 * Get a value by using a Sheet and Range objects.  
 * This sample uses Spreadsheet.getSheetByName to get a Sheet object.
 */
function sample4(){
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getSheetByName('Sheet2');
  const range = sheet.getRange('A1');
  const value = range.getValue();
  console.log(value);
}

NOTE: Besides using A1 and R1C1 notations, with getRange, it's also possible to getRange(1,1) (the first parameter is the row, the second the column, using 1 based indexes)

/**
 * Get a cell value from the first sheet by using JavaScript Arrays
 * The parameters of getSheetValues are start row, start column, 
 * number of rows, and number of columns
 * Please bear in mind that getSheetValues uses 1 for the first row and 
 * column but JavaScript Arrays use 0 for the first Array member
 */
function sample5(){
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const values = spreadsheet.getSheetValues(1,1,1,1); 
  const value = values[0][0];
  console.log(value);
}

Resources

userH4H60
Related