Reputation: 5
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];
}
}
}
Upvotes: 0
Views: 3326
Reputation: 38435
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:
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.Related
Resources
Upvotes: 3
Reputation: 64140
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.
Upvotes: 0
Reputation: 27400
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];
}
Upvotes: 2