More efficient way to find the last row in a specific column?
I am writing an application that will import columns from one sheet to another. The .getLastRow method only applies to the entire sheet, but cannot be used to get the last row of a column. There is a problem requesting this feature.
I wrote something using 2D Array library from people at Google Script Examples: https://sites.google.com/site/scriptsexamples/custom-methods/2d-arrays-library
I have a working version that finds the last row in a specific column, but I suspect it is rather inefficient.
function readRows() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var numRows = sheet.getLastRow();
var numColumns = sheet.getLastColumn();
var data = sheet.getRange(1, 1, numRows, numColumns).getValues();
//Get the Headers, Search for a value of the headers and index
var headerArray = sheet.getRange(1, 1, 1, numColumns).getValues();
var flip = ArrayLib.transpose(headerArray)
var search = "Greens";
var whereGreen = ArrayLib.indexOf(flip, 0, search);
//Get the value of the column with matching headers, and looks up Column length.
var values = sheet.getRange(1, whereGreen +1, numRows, 1).getValues();
//finds last value, makes string
for(; values[numRows - 1] == "" && numRows > 0; numRows--) {}
var lastValue = values[numRows - 1].toString();
//Indexes where the string is, which gives the value -1 of the last row in column.
var lastRowCol = ArrayLib.indexOf(values, 0, lastValue);
Logger.log(lastRowCol +1);
}
Can anyone help me move to the simplified version? I'm sure JavaScript can do this, but I am reasonably well versed in my knowledge in this department.
source to share
More efficient code can be reduced by reducing the number of calls to the spreadsheet service. The following code is much faster:
function readRows() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var data = sheet.getDataRange().getValues();
var numRows = data.length;
//Get the Headers, Search for a value of the headers and index
var headerRow = data[0];
var search = "Greens";
var whereGreen = headerRow.indexOf(search);
//finds last value, makes string
while( data[numRows - 1][whereGreen] == "" && numRows > 0 ) {
numRows--;
}
var lastValue = data[numRows - 1][whereGreen].toString();
Logger.log( 'Last row: '+ numRows );
Logger.log( 'Last value: '+ lastValue );
// Not clear what this does, what more information is needed?
//Indexes where the string is, which gives the value -1 of the last row in column.
//var lastRowCol = ArrayLib.indexOf(values, 0, lastValue);
// Logger.log(lastRowCol +1);
}
I replaced the for loop with a while loop, but that shouldn't make a big difference in efficiency, it makes it more readable.
source to share