More efficient way too look up the last row in a s

问题:

I'm writing an app that will import columns from one sheet to another. The .getLastRow method will only apply to the whole sheet, but can't be used to get last row of a column. There is an issue open requesting this feature.

I've written something with the help of the 2D Array library from the folks over at Google Script Examples: https://sites.google.com/site/scriptsexamples/custom-methods/2d-arrays-library

I've gotten a working version that finds the last row in a specific column, but I suspect it's rather ineffecient.

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 get to a streamlined version? I'm sure JavaScript could do it, but I'm rather light on my knowledge in that department.

回答1:

The code can be made more efficient 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 should not make much difference in efficiency, makes it a bit more readable.

回答2:

In terms of efficiency, this is about as close as you get to efficient, in my opinion. In terms of a cleaner solution, I can't seem to think of one right now. Will update if I think of anything.