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 a spreadsheet using Google apps script. I searched the internet and I found this link - sorry if this sounds stupid, but 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?

Disassemble

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
}

Eschatology

Is this really an efficient way of doing it, though? It seems so excessive to retrieve the whole data range and then one only needs one column. – Bolyard

It takes the same time to get all data or just a column data, so it really doesn't make any difference:-) you can easily check that in the execution transcript where all execution times are logged by service call. – Eschatology

This really helps me understand 2D arrays. Thanks – Morganite

There is huge difference in performance. getRange("A:A") is faster. – Caterinacatering

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.

Cesaria

IMO best answer for getting values from a column efficiently. Would be nice if this were a built in function. – Brahmanism

Answer 3

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

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

Enrollment

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
});

Lunisolar

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)

Canter

Answer 6

I think this is the best answer so far. Suppose you want the values of Column "D1:D"

var numrows = SpreadsheetApp.getActiveSpreadsheet().getDataRange().getNumRows();
var columnValues = sheet.getRange(1,4,numrows,1).getValues();

Pyrotechnics

Answer 7

Attempt to use Appscript to extract data from each column of Excel row by row.

function readRowAndColumnsOneByOne() {
    var values = SpreadsheetApp.getActiveSheet().getDataRange().getValues()
    var x = 0;
    for (n = 0; n < values.length; ++n) {
        for (j = 0; j < values[n].length - 1; ++j) {
            var cell = values[x][j]; // x is the index of the row starting from 0
            console.log("Number of rows " + n + " Col " + j + "- " + cell);
        }
        x++
    }
}

Sorgo