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

CodePudding user response：

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

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.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.

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.

CodePudding user response：

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

CodePudding user response：

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.