get value in one column in spreadsheet using google apps script

Question

I want to get a string value -to compare it later on with an if condition- from only one column in spreadsheet using Google apps script. I searched the internet and I found this link - sorry if this sounds stupid, I am new to Google apps scripts - https://developers.google.com/apps-script/class_spreadsheet

var values = SpreadsheetApp.getActiveSheet().getRange(2, 3, 6, 4).getValues(); 

I guess that must be helpful, the only problem is that the column I want to get the values from is dynamic so how do I set the range of this column

Answer 1

if you use simply :

var values = SpreadsheetApp.getActiveSheet().getDataRange().getValues()

You will get a 2 Dimension array of all the data in the sheet indexed by rows and columns.

So to get the value in column A, row1 you use values[0][0] , values[1][0] for columnA, row 2, values[0][2] for column C row1, etc...

If you need to iterate in a for loop (in a single column) :

for(n=0;n<values.length;++n){
var cell = values[n][x] ; // x is the index of the column starting from 0
}

If you need to iterate in a for loop (in a single row) :

for(n=0;n<values[0].length;++n){
var cell = values[x][n] ; // x is the index of the row starting from 0
}

Answer 2

Here is the script I use to get the values in a dynamic column:

var SS = SpreadsheetApp.getActiveSheet()
var Avals = SS.getRange("A1:A").getValues();
var numberOfValues = Avals.filter(String).length;
var RangeVals = SS.getRange(1,1,numberOfValues).getValues();

I've never had to change which starting row based based on a dynamic changing starting point though. Would be interested in seeing how that would be done.

Here is a similar post. Another example here.

Answer 3

Suppose you want all rows/cells in column A using getRange(a1Notation):

  var values = SpreadsheetApp.getActiveSheet().getRange("A:A").getValues();

Answer 4

much easier way to loop through the rows and get a column value.. hope that helps

var values = SpreadsheetApp.getActiveSheet().getDataRange().getValues();

values.forEach( function(row) {
  row[4] // column index as 4
});

Answer 5

All of the mentioned functions below in this answer will output a 1 dimensional array, which I feel will be slightly more convenient to operate with.

Solution 1:

Just another way of obtaining a column or a row:

function getColumn(activeSheet, columnIndex) {
  return activeSheet.getRange(1, columnIndex)
    .getDataRegion(SpreadsheetApp.Dimension.ROWS)
    .getValues()
    .flat();
}

function getRow(activeSheet, rowIndex) {
  return activeSheet.getRange(rowIndex, 1)
    .getDataRegion(SpreadsheetApp.Dimension.COLUMNS)
    .getValues()[0];
}

Note: This will only fetch data which are in the adjacent cells in the given direction.

Example: 1, 2, 3, blank, 4, blank, 5 ----- 4 and 5 will be skipped. Also, see getDataRegion(dimension).This is an ideal solution if you have no empty cells within the data region.

Solution 2:

Extending @itazzad's solution,

// JS object containing indices as keys and capital letters as value.
const INDEX_ALPHABET = Array.from(new Array(26))
  .reduce((accumulatedObj, _, index) =>
    (accumulatedObj[index + 1] = String.fromCharCode(index + 65), accumulatedObj), {});

// uses a1 notation
function getColumn(activeSheet, columnIndex) {
  var columnA1Notation = INDEX_ALPHABET[columnIndex]
  return activeSheet.getRange(columnA1Notation + ':' + columnA1Notation)
    .getValues()
    .flat();
}

// uses a1 notation
function getRow(activeSheet, rowIndex) {
  return activeSheet.getRange(rowIndex + ':' + rowIndex) 
    .getValues()[0];
}

See getRange(a1Notation). I was not sure of what R1C1 notation and A1 notation meant, but this article helped a bit.

Solution 3:

If you believe getValues() might be a expensive call and increases in time execution as the Range increases in size, then check this out. Basically it gets the last row or column and uses it to reduce the Range without omitting any relevant data.

function getRow(activeSheet, rowIndex) {
  var lastColumn = activeSheet.getDataRange().getLastColumn()
  return activeSheet.getRange(rowIndex, 1, 1, lastColumn)
    .getValues()[0]
}

function getColumn(activeSheet, columnIndex) {
  var lastRow = activeSheet.getDataRange().getLastRow()
  return activeSheet.getRange(1, columnIndex, lastRow, 1)
    .getValues()
    .flat()
}

Note: If you want to get a slice of a row or column, check out getRange(row, column, numRows, numColumns)