Retrieving all values in a column

Question

I am new to google apps script and I was trying to get all the values in a particular column inside a sheet named "Items". I was able to create a loop to get to the last row that contains value but when I try to use the function, no data is retrieved. I tried console.log(values[lr][0]); inside the if clause and it outputs just fine.

Here's my code

function getAllItems()
{
 
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var locationSheet = ss.getSheetByName("Items");
  var values = locationSheet.getRange("Items!B2:B").getValues();
  
 for(var i = values.length - 1 ; i >= 0 ; i--){
    if (values[i][0] != null && values[i][0] != ""){
      lr = i + 1;
      values.sort();
      return values[lr][0];
    }
 }      
}

Answer 1

There are several ways to retrieve values from a column in Google Sheets.

The basics, getting the sheet

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName('Items');

SpreadsheetApp.getActiveSpreadsheet() works in bounded projects and add-ons. Spreadsheet.getSheetByName(name) works when the sheet name is known.

Getting the column values by using Sheet.getRange and an open reference using A1 notation

var values = sheet.getRange('B:B').getValues();

If your spreadsheet has blank rows at the bottom, in this case Range.getValues besides the column values, it will return an empty string for each blank rows.

Besides using Sheet.getRange with an open reference, it might be used with other reference types and using start row, start column, number of rows and number of columns.

Getting the column values by using Sheet.getRange and an open reference using A1 notation excluding empty strings

var values = sheet.getRange('B:B').getValues().filter(String);

Getting the column values by using Sheet.getDataRange and Array.prototype.map

var values = sheet.getDataRange().getValues().map(row => row[1]);

Only will return the values from the first row to the last row of the data range. The data range is determined from A1 to the last row and last column having values, i.e., if one column B have values from row 1 to row 10 and column C have values from row 4 to row 20, the data range reference is A1:C20, so values will contain the values from row 1 to row 20, showing empty strings for the blank cells.

Getting the column values by using Sheet.getDataRange, Array.prototype.splice and Array.prototype.getLastIndex

var values = sheet.getDataRange().getValues();
values.splice(values.findLastIndex(String) + 1);

Only will return the values from the first row to the last row of the column containing non empty strings. This might be helpful when having columns "of different sizes", as explained in the previous case. Please note that if there blank cells in between, an empty string will be included as value of these cells.

Notes:

1. Instead of Range.getValues you might use Range.getDisplayValues to get the strings with the values formatted as strings as they are displayed on Google Sheets cells. Both methods return the values structured as an Array of Arrays, this might be handy if you will be adding the values to another range, but if you want to add them to the execution logs you might want to format them in another way.
2. Please bear in mind that if the column content is very large, nowadays a Google Sheets spreadsheet could have up to 10 million cells and each cell could have upto 50k characters, the column content will be truncated when printed to the execution logs.

Related

• Get column from a two dimensional array

Resources

• Array

Answer 2

You don't need a loop for that (explanation in comments):

function getAllItems()
{
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var locationSheet = ss.getSheetByName("Items");
  var values = locationSheet.getRange("Items!B2:B").getValues().flat(); // 2D -> 1D array
  var filter_values = values.filter(r=>r!=''); // remove empty rows
  Logger.log(filter_values); // get the full list
  Logger.log(filter_values[filter_values.length-1]); // get the last value; 

  return filter_values[filter_values.length-1];     
}

Answer 3

Try this:

function getAllItems(){
  var ss= SpreadsheetApp.getActive();
  var sh = ss.getSheetByName("Items");
  var vs = sh.getRange("B2:B"+sh.getLastRow()).getValues();//all the values in column B
  return sh.getLastRow();//the last row with data
}

Or you can use:

function getColumnHeight(col, sh, ss) {
  var ss = ss || SpreadsheetApp.getActive();
  var sh = sh || ss.getActiveSheet();
  var col = col || sh.getActiveCell().getColumn();
  var rcA = [];
  if (sh.getLastRow()){ rcA = sh.getRange(1, col, sh.getLastRow(), 1).getValues().flat().reverse(); }
  let s = 0;
  for (let i = 0; i < rcA.length; i++) {
    if (rcA[i].toString().length == 0) {
      s++;
    } else {
      break;
    }
  }
  return rcA.length - s;
  //const h = Utilities.formatString('col: %s len: %s', col, rcA.length - s);
  //Logger.log(h);
  //SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(h).setWidth(150).setHeight(100), 'Col Length')
}

function getAllItems(){
  var ss= SpreadsheetApp.getActive();
  var sh = ss.getSheetByName("Items");
  var vs = sh.getRange("B2:B"+getColumnHeight(2,sh,ss).getValues();//all the values in column B
  return sh.getLastRow();//the last row with data
}

If you use filter() to filter out all of the nulls you may not get the desired result if one of the data elements is null.