Подтвердить что ты не робот

Определение последней строки в одном столбце

У меня есть лист с данными в столбцах A через H.

Мне нужно определить последнюю строку в столбце A, которая содержит данные (все смежные - без пробелов в данных/строках).

В других столбцах есть данные, у которых больше строк данных, чем столбец A, поэтому мне нужно выделить только столбец A. (И/или просто диапазон внутри col A).

Я могу сделать это на уровне электронных таблиц, используя

=COUNTA(A2:A100)

Однако во всех моих исследованиях для решения Google Apps Script все, что мне кажется, это требования к выполнению нескольких функций, охватывающих десятки строк кода, включая множество материалов i++... Что я мог сделать менее сложно путем смещения непосредственно из A1.

Может ли быть способ, специфичный для столбцов, для модификации этого метода?

var aLast = ss.getDataRange().getNumRows();

Если запутанный процесс - это то, что требуется, то пусть будет так. Но мне трудно представить (и еще труднее найти!) Более простое решение.

Кто-нибудь хочет просветить меня (или поп мой пузырь)?

4b9b3361

ОТВЕТЫ

Ответ 1

Как насчет использования трюка JavaScript?

var Avals = ss.getRange("A1:A").getValues();
var Alast = Avals.filter(String).length;

Я взял эту идею из этого ответа. Метод Array.filter() работает в массиве Avals, который содержит все ячейки в столбце A. Avals конструктор нативной функции, мы возвращаем только ненулевые элементы.

Это работает только для одного столбца; если диапазон содержит несколько столбцов, то результат filter() будет включать ячейки из всех столбцов и, таким образом, будет за пределами заполненных измерений диапазона.

Ответ 2

Хотя нет формулы straighforward, я могу подумать, она не требует десятков строк кода, чтобы узнать последнюю строку в столбце A. Попробуйте эту простую функцию. Используйте его в ячейке обычным способом, чтобы использовать какую-то другую функцию =CountColA()

function CountColA(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  for(var i = data.length-1 ; i >=0 ; i--){
    if (data[i][0] != null && data[i][0] != ''){
      return i+1 ;
    }
  }
}

Ответ 3

Вы можете сделать это, обратившись обратным образом. Начиная с последней строки в электронной таблице и поднимаясь до тех пор, пока вы не получите какую-то ценность. Это будет работать во всех случаях, даже если у вас есть несколько пустых строк. Код выглядит следующим образом:

var iLastRowWithData = lastValue('A');
function lastValue(column) {
  var iLastRow = SpreadsheetApp.getActiveSheet().getMaxRows();
  var aValues = SpreadsheetApp.getActiveSheet().getRange(column + "2:" + column + lastRow).getValues();

  for (; aValues[iLastRow - 1] == "" && iLastRow > 0; iLastRow--) {}
  return iLastRow;
}

Ответ 4

Это получит последнюю строку в листе, предполагая, что она основана на столбце A.

function getLastDataRow(sheet) {
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange("A" + lastRow);
  if (range.getValue() !== "") {
    return lastRow;
  } else {
    return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  }              
}

Это исправляет частично-правильный ответ @mrityunjay-pandey.

Чтобы расширить этот ответ, чтобы получить последнюю строку и столбец, мы можем использовать:

function columnToLetter(column) {
  var temp, letter = '';
  while (column > 0) {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

function letterToColumn(letter) {
  var column = 0, length = letter.length;
  for (var i = 0; i < length; i++) {
    column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
  }
  return column;
}

function getLastDataColumn(sheet) {
  var lastCol = sheet.getLastColumn();
  var range = sheet.getRange(columnToLetter(lastCol) + "1");
  if (range.getValue() !== "") {
    return lastCol;
  } else {
    return range.getNextDataCell(SpreadsheetApp.Direction.PREVIOUS).getColumn();
  }              
}

function getLastDataRow(sheet) {
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange("A" + lastRow);
  if (range.getValue() !== "") {
    return lastRow;
  } else {
    return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  }              
}

function run() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var [startRow, lastRow] = [2, getLastDataRow(sheet)];
  var [startCol, lastCol] = [1, getLastDataColumn(sheet)];
}

Ответ 5

Для очень больших таблиц это решение очень быстро:

function GoLastRow() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A:AC').createFilter();
  var criteria = SpreadsheetApp.newFilterCriteria().whenCellNotEmpty().build();
  var rg = spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(1, criteria).getRange();

  var row = rg.getNextDataCell (SpreadsheetApp.Direction.DOWN);  

  LastRow = row.getRow();

  spreadsheet.getActiveSheet().getFilter().remove();

  spreadsheet.getActiveSheet().getRange(LastRow+1, 1).activate();

};

Ответ 6

Я использовал getDataRegion

sheet.getRange(1, 1).getDataRegion(SpreadsheetApp.Dimension.ROWS).getLastRow()

Обратите внимание, что это зависит от непрерывности данных (согласно запросу OP).

Ответ 7

Чтобы получить количество столбцов или последний индекс столбца:

var numColumns = sheet.getLastColumn()

Чтобы получить число строк или индекс последней строки:

var numRows = sheet.getLastRow()

где

var sheet = SpreadsheetApp.getActiveSheet()

Ответ 8

Это может быть еще один способ передвижения. Возможно, вам придется поиграть с кодом в соответствии с вашими потребностями

    function fill() {
      var spreadsheet = SpreadsheetApp.getActive();
      spreadsheet.getRange('a1').activate();
      var lsr = spreadsheet.getLastRow();
      lsr=lsr+1;
      lsr="A1:A"+lsr;

      spreadsheet.getActiveRange().autoFill(spreadsheet.getRange(lsr), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
};

Ответ 9

var Direction=SpreadsheetApp.Direction;
var aLast =ss.getRange("A"+(ss.getLastRow()+1)).getNextDataCell(Direction.UP).getRow();

Ответ 10

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("SheetName");
var lastRow = sheet.getRange("DefinedRangeName").getLastRow();

Вышеприведенный код не работает так же, как получение последней строки из листа. Он просто возвращает последнюю строку диапазона, пустой или нет. Это не ответ на этот вопрос.

Ответ 11

лично у меня была похожая проблема, и я пошел примерно так:

function getLastRowinColumn (ws, column) {
  var page_lastrow = ws.getDataRange().getNumRows();
  var last_row_col = 0
  for (i=1; i<=page_lastrow;i++) {
    if (!(spread.getRange(column.concat("",i)).isBlank())) {last_row_col = i};
  }
  return last_row_col
}

Он ищет количество строк в ws и проходит по каждой ячейке в вашем столбце. Когда он находит непустую ячейку, он обновляет позицию этой ячейки в переменной last_row_col. Он имеет то преимущество, что позволяет вам иметь несмежные столбцы и при этом знать последнюю строку (при условии, что вы проходите через весь столбец).

Ответ 12

Никогда не поздно опубликовать альтернативный ответ, я надеюсь. Вот фрагмент моей Находки последней Клетки. Меня в первую очередь интересует скорость. На БД, которую я использую с 150 000 строк, этой функции потребовалось (в среднем) 0,087 секунды, чтобы найти решение по сравнению с элегантным JS-решением @Mogsdad выше, которое занимает (в среднем) 0,53 с для тех же данных. Оба массива были предварительно загружены перед вызовом функции. Он использует рекурсию для выполнения бинарного поиска. Для 100 строк 000+ вы должны обнаружить, что для возврата результата требуется не более 15-20 прыжков.

Я оставил в журнале звонки, чтобы вы могли сначала проверить его в консоли и посмотреть, как он работает.

/* @OnlyCurrentDoc */

function myLastRow() {

var ss=SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var colArray = ss.getRange('A1:A').getDisplayValues();  // Change to relevant column label and put in Cache
var TestRow=ss.getLastRow();
var MaxRow=ss.getMaxRows(); 

Logger.log ('TestRow = %s',TestRow);
Logger.log ('MaxRow = %s',MaxRow);

var FoundRow=FindLastRow(TestRow,MaxRow);

Logger.log ('FoundRow = %s',FoundRow);    

function FindLastRow(v_TestRow,v_MaxRow) {

/* Some housekeeping/error trapping first
* 1) Check that LastRow doesn't = Max Rows. If so then suggest to add a few lines as this
* indicates the LastRow was the end of the sheet.
* 2) Check it not a new sheet with no data ie, LastRow = 0 and/or cell A1 is empty.
* 3) A return result of 0 = an error otherwise any positive value is a valid result.
*/

 return   !(colArray[0][0]) ? 1                   // if first row is empty then presume it a new empty sheet
        :!!(colArray[v_TestRow][0]) ? v_TestRow   // if the last row is not empty then column A was the longest
        : v_MaxRow==v_TestRow ? v_TestRow         // if Last=Max then consider adding a line here to extend row count, else
        : searchPair(0,v_TestRow);                // get on an find the last row
}

function searchPair(LowRow,HighRow){

var BinRow = ((LowRow+HighRow)/2)|0;   // force an INT to avoid row ambiguity

Logger.log ('LowRow/HighRow/BinRow = %s/%s/%s',LowRow, HighRow, BinRow);

/*   Check your log. You shoud find that the last row is always found in under 20 hops.
 *   This will be true whether your data rows are 100 or 100,000 long.
 *   The longest element of this script is loading the Cache (ColArray)
 */

 return   (!(colArray[BinRow-1][0]))^(!(colArray[BinRow][0])) ? BinRow
        : (!(colArray[BinRow-1][0]))&(!(colArray[BinRow][0])) ? searchPair(LowRow,BinRow-1)
        : (!!(colArray[BinRow-1][0]))|(!!(colArray[BinRow][0])) ? searchPair(BinRow+1,HighRow)
        : false;   // Error
 }
}

/* The premise for the above logic is that the binary search is looking for a specific pairing, <Text/No text>
 * on adjacent rows.  You said there are no gaps so the pairing <No Text/Text> is not tested as it irrelevant.
 * If the logic finds <No Text/No Text> then it looks back up the sheet, if it finds <Text/Text> it looks further
 * down the sheet.  I think you'll find this is quite fast, especially on datasets > 100,000 rows.
 */

Ответ 13

Я попытался написать 3 следующих функции, вы можете проверить их на разные случаи. Вот данные, которые я протестировал:

enter image description here

Функция getLastRow1 и getLastRow2 вернут 0 для столбца B Функция getLastRow3 вернет 1 для столбца B

Зависит от вашего случая, вы настроите их для своих нужд.

function getLastRow1(sheet, column) {
  var data = sheet.getRange(1, column, sheet.getLastRow()).getValues();

  while(typeof data[data.length-1] !== 'undefined'
     && data[data.length-1][0].length === 0){ 
    data.pop();
  }
  return data.length;
}

function test() {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet6');
  Logger.log('Cách 1');
  Logger.log("Dòng cuối cùng của cột A là: " + getLastRow1(sh, 1));
  Logger.log("Dòng cuối cùng của cột B là: " + getLastRow1(sh, 2));
  Logger.log("Dòng cuối cùng của cột C là: " + getLastRow1(sh, 3));
  Logger.log("Dòng cuối cùng của cột D là: " + getLastRow1(sh, 4));
  Logger.log("Dòng cuối cùng của cột E là: " + getLastRow1(sh, 5));
  Logger.log('Cách 2');  
  Logger.log("Dòng cuối cùng của cột A là: " + getLastRow2(sh, 1));
  Logger.log("Dòng cuối cùng của cột B là: " + getLastRow2(sh, 2));
  Logger.log("Dòng cuối cùng của cột C là: " + getLastRow2(sh, 3));
  Logger.log("Dòng cuối cùng của cột D là: " + getLastRow2(sh, 4));
  Logger.log("Dòng cuối cùng của cột E là: " + getLastRow2(sh, 5));
  Logger.log('Cách 3');
  Logger.log("Dòng cuối cùng của cột A là: " + getLastRow3(sh, 'A'));
  Logger.log("Dòng cuối cùng của cột B là: " + getLastRow3(sh, 'B'));
  Logger.log("Dòng cuối cùng của cột C là: " + getLastRow3(sh, 'C'));
  Logger.log("Dòng cuối cùng của cột D là: " + getLastRow3(sh, 'D'));
  Logger.log("Dòng cuối cùng của cột E là: " + getLastRow3(sh, 'E'));

}

function getLastRow2(sheet, column) {
  var lr = sheet.getLastRow();
  var data = sheet.getRange(1, column, lr).getValues();

  while(lr > 0 && sheet.getRange(lr , column).isBlank()) {
    lr--;
  }

  return lr;
}

function getLastRow3(sheet, column) {
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange(column + lastRow);
  if (range.getValue() !== '') {
    return lastRow;
  } else {
    return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  } 
}

Ответ 14

Обновление от решения Mogsdad,

var Avals = ss.getRange("A1:A").getValues();
var Alast = Avals.filter(function(r){return r[0].length>0});

Ответ 15

Я переписал функции getLastRow/getLastColumn, чтобы указать индекс строки или индекс столбца.

function get_used_rows(sheet, column_index){
      for (var r = sheet.getLastRow(); r--; r > 0) {
        if (sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getCell(r, column_index).getValue() != ""){
          return r;
          break;
        }
      }
    }

function get_used_cols(sheet, row_index){
  for (var c = sheet.getLastColumn(); c--; c > 0) {
    if (sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getCell(row_index, c).getValue() != ""){
      return c;
      break;
    }
  }
}

Ответ 16

Здесь альтернативный способ решения этого. Он использует while цикл, но учитывает пустые промежутки между рядами.

function getLastRow (column) {
  var iLastRow = ss.getActiveSheet().getMaxRows();
  var aValues = ss.getActiveSheet().getRange(column + ":" + column).getValues();
  var row = "";
  while(row == ""){
    row = aValues[iLastRow-1];
    iLastRow--;
  }
  return iLastRow;
}

Ответ 17

Я использую getDataRange() за которым следует getNumRows(). Первая функция

Возвращает диапазон, соответствующий размерам, в которых присутствуют данные

и вторая функция

Возвращает количество строк в этом диапазоне.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var ws = ss.getActiveSheet();
var lastRow = ws.getDataRange().getNumRows();

PS Я надеюсь, что это работает для всех случаев.