Szybszy sposób znalezienia pierwszego pustego wiersza

To pytanie ma teraz więcej niż 12K odsłon - więc nadszedł czas na aktualizację, ponieważ charakterystyka wydajności nowych arkuszy jest inna niż wtedy, gdy Serge przeprowadził swoje wstępne testy .

Dobra wiadomość: wydajność jest znacznie lepsza!

Najszybszy:

Jak w pierwszym teście, odczyt danych arkusza tylko raz, a następnie działanie na tablicy, dało ogromną korzyść z wydajności. Co ciekawe, oryginalna funkcja Dona sprawowała się znacznie lepiej niż zmodyfikowana wersja testowana przez Serge ' a. (Wygląda na to, że while jest szybszy niż for, co nie jest logiczne.)

Średni czas wykonania na przykładowych danych wynosi tylko 38ms , w dół od poprzedniego 168ms.

// Don's array approach - checks first column only
// With added stopping condition & correct result.
// From answer https://stackoverflow.com/a/9102463/1677912
function getFirstEmptyRowByColumnArray() {
  var spr = SpreadsheetApp.getActiveSpreadsheet();
  var column = spr.getRange('A:A');
  var values = column.getValues(); // get all data in one call
  var ct = 0;
  while ( values[ct] && values[ct][0] != "" ) {
    ct++;
  }
  return (ct+1);
}

Wyniki testu:

Oto wyniki, podsumowane ponad 50 iteracji w arkuszu kalkulacyjnym z 100 wierszami x 3 kolumnami (wypełnionymi funkcją testową Serge ' a).

Nazwy funkcji pasują do kodu w skrypcie poniżej.

"pierwszy pusty wiersz"

Pierwotna Prośba polegała na znalezieniu pierwszego pustego wiersza. Żaden z poprzednich skryptów nie dostarcza tego. Wiele sprawdza tylko jedną kolumnę, co oznacza, że mogą dawać fałszywie pozytywne wyniki. Inne znajdują tylko pierwszy wiersz, który podąża za wszystkimi danymi, co oznacza, że puste wiersze w nieciągłych danych są pomijane.

Oto funkcja, która spełnia specyfikację. Został uwzględniony w testach, a podczas gdy wolniejszy niż Błyskawica-szybki sprawdzacz Jednokolumnowy, przyszedł z przyzwoitym 68ms, 50% premii za poprawną odpowiedź!

/**
 * Mogsdad's "whole row" checker.
 */
function getFirstEmptyRowWholeRow() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();
  var row = 0;
  for (var row=0; row<values.length; row++) {
    if (!values[row].join("")) break;
  }
  return (row+1);
}

Kompletny skrypt:

Jeśli chcesz powtórzyć testy lub dodać własną funkcję do miksu jako porównanie, po prostu weź cały skrypt i użyj go w arkuszu kalkulacyjnym.

/**
 * Set up a menu option for ease of use.
 */
function onOpen() {
  var menuEntries = [ {name: "Fill sheet", functionName: "fillSheet"},
                      {name: "test getFirstEmptyRow", functionName: "testTime"}
                     ];
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  sh.addMenu("run tests",menuEntries);
}

/**
 * Test an array of functions, timing execution of each over multiple iterations.
 * Produce stats from the collected data, and present in a "Results" sheet.
 */
function testTime() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.getSheets()[0].activate();
  var iterations = parseInt(Browser.inputBox("Enter # of iterations, min 2:")) || 2;

  var functions = ["getFirstEmptyRowByOffset", "getFirstEmptyRowByColumnArray", "getFirstEmptyRowByCell","getFirstEmptyRowUsingArray", "getFirstEmptyRowWholeRow"]

  var results = [["Iteration"].concat(functions)];
  for (var i=1; i<=iterations; i++) {
    var row = [i];
    for (var fn=0; fn<functions.length; fn++) {
      var starttime = new Date().getTime();
      eval(functions[fn]+"()");
      var endtime = new Date().getTime();
      row.push(endtime-starttime);
    }
    results.push(row);
  }

  Browser.msgBox('Test complete - see Results sheet');
  var resultSheet = SpreadsheetApp.getActive().getSheetByName("Results");
  if (!resultSheet) {
    resultSheet = SpreadsheetApp.getActive().insertSheet("Results");
  }
  else {
    resultSheet.activate();
    resultSheet.clearContents();
  }
  resultSheet.getRange(1, 1, results.length, results[0].length).setValues(results);

  // Add statistical calculations
  var row = results.length+1;
  var rangeA1 = "B2:B"+results.length;
  resultSheet.getRange(row, 1, 3, 1).setValues([["Avg"],["Stddev"],["Trimmed\nMean"]]);
  var formulas = resultSheet.getRange(row, 2, 3, 1);
  formulas.setFormulas(
    [[ "=AVERAGE("+rangeA1+")" ],
     [ "=STDEV("+rangeA1+")" ],
     [ "=AVERAGEIFS("+rangeA1+","+rangeA1+',"<"&B$'+row+"+3*B$"+(row+1)+","+rangeA1+',">"&B$'+row+"-3*B$"+(row+1)+")" ]]);
  formulas.setNumberFormat("##########.");

  for (var col=3; col<=results[0].length;col++) {
    formulas.copyTo(resultSheet.getRange(row, col))
  }

  // Format for readability
  for (var col=1;col<=results[0].length;col++) {
    resultSheet.autoResizeColumn(col)
  }
}

// Omiod's original function.  Checks first column only
// Modified to give correct result.
// question https://stackoverflow.com/questions/6882104
function getFirstEmptyRowByOffset() {
  var spr = SpreadsheetApp.getActiveSpreadsheet();
  var cell = spr.getRange('a1');
  var ct = 0;
  while ( cell.offset(ct, 0).getValue() != "" ) {
    ct++;
  }
  return (ct+1);
}

// Don's array approach - checks first column only.
// With added stopping condition & correct result.
// From answer https://stackoverflow.com/a/9102463/1677912
function getFirstEmptyRowByColumnArray() {
  var spr = SpreadsheetApp.getActiveSpreadsheet();
  var column = spr.getRange('A:A');
  var values = column.getValues(); // get all data in one call
  var ct = 0;
  while ( values[ct] && values[ct][0] != "" ) {
    ct++;
  }
  return (ct+1);
}

// Serge's getFirstEmptyRow, adapted from Omiod's, but
// using getCell instead of offset. Checks first column only.
// Modified to give correct result.
// From answer https://stackoverflow.com/a/18319032/1677912
function getFirstEmptyRowByCell() {
  var spr = SpreadsheetApp.getActiveSpreadsheet();
  var ran = spr.getRange('A:A');
  var arr = []; 
  for (var i=1; i<=ran.getLastRow(); i++){
    if(!ran.getCell(i,1).getValue()){
      break;
    }
  }
  return i;
}

// Serges's adaptation of Don's array answer.  Checks first column only.
// Modified to give correct result.
// From answer https://stackoverflow.com/a/18319032/1677912
function getFirstEmptyRowUsingArray() {
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  var ss = sh.getActiveSheet();
  var data = ss.getDataRange().getValues();
  for(var n=0; n<data.length ;  n++){
    if(data[n][0]==''){n++;break}
  }
  return n+1;
}

/**
 * Mogsdad's "whole row" checker.
 */
function getFirstEmptyRowWholeRow() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();
  var row = 0;
  for (var row=0; row<values.length; row++) {
    if (!values[row].join("")) break;
  }
  return (row+1);
}

function fillSheet(){
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  var ss = sh.getActiveSheet();
  for(var r=1;r<1000;++r){
    ss.appendRow(['filling values',r,'not important']);
  }
}

// Function to test the value returned by each contender.
// Use fillSheet() first, then blank out random rows and
// compare results in debugger.
function compareResults() {
  var a = getFirstEmptyRowByOffset(),
      b = getFirstEmptyRowByColumnArray(),
      c = getFirstEmptyRowByCell(),
      d = getFirstEmptyRowUsingArray(),
      e = getFirstEmptyRowWholeRow(),
      f = getFirstEmptyRowWholeRow2();
  debugger;
}

Jest już tam jako metoda getLastRow na arkuszu.

var firstEmptyRow = SpreadsheetApp.getActiveSpreadsheet().getLastRow() + 1;

Ref https://developers.google.com/apps-script/class_sheet#getLastRow

Widząc ten stary post z 5K odsłon najpierw sprawdziłem 'najlepsza odpowiedź' i byłem dość zaskoczony jego treścią... to był bardzo powolny proces ! wtedy poczułem się lepiej, gdy zobaczyłem odpowiedź Dona Kirkby ' ego, podejście array jest rzeczywiście znacznie bardziej efektywne !

Więc napisałem ten mały kod testowy na arkuszu kalkulacyjnym z 1000 wierszami i oto wyniki: (nieźle !... nie trzeba mówić, który z nich jest które...)

A oto kod, którego użyłem :

function onOpen() {
  var menuEntries = [ {name: "test method 1", functionName: "getFirstEmptyRow"},
                      {name: "test method 2 (array)", functionName: "getFirstEmptyRowUsingArray"}
                     ];
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  sh.addMenu("run tests",menuEntries);
}

function getFirstEmptyRow() {
  var time = new Date().getTime();
  var spr = SpreadsheetApp.getActiveSpreadsheet();
  var ran = spr.getRange('A:A');
  for (var i= ran.getLastRow(); i>0; i--){
    if(ran.getCell(i,1).getValue()){
      break;
    }
  }
  Browser.msgBox('lastRow = '+Number(i+1)+'  duration = '+Number(new Date().getTime()-time)+' mS');
}

function getFirstEmptyRowUsingArray() {
  var time = new Date().getTime();
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  var ss = sh.getActiveSheet();
  var data = ss.getDataRange().getValues();
  for(var n =data.length ; n<0 ;  n--){
    if(data[n][0]!=''){n++;break}
  }
  Browser.msgBox('lastRow = '+n+'  duration = '+Number(new Date().getTime()-time)+' mS');
}

function fillSheet(){
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  var ss = sh.getActiveSheet();
  for(var r=1;r<1000;++r){
    ss.appendRow(['filling values',r,'not important']);
  }
}

I arkusz testowy aby spróbować samemu: -)

EDIT:

Podążając za komentarzem Mogsdada, powinienem wspomnieć, że te nazwy funkcji są rzeczywiście złym wyborem... Powinno być coś w stylu getLastNonEmptyCellInColumnAWithPlentyOfSpaceBelow(), co nie jest zbyt eleganckie (prawda ?), ale dokładniejsze i bardziej spójne z tym, co faktycznie zwraca.

Komentarz:

w każdym razie, chodziło mi o to, aby pokazać szybkość wykonania obu podejść i oczywiście tak było (prawda ? ;-)

I dlaczego nie użyć appendRow ?

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
spreadsheet.appendRow(['this is in column A', 'column B']);

Wiem, że to stary wątek i było tu kilka bardzo sprytnych podejść.

Używam skryptu

var firstEmptyRow = SpreadsheetApp.getActiveSpreadsheet().getLastRow() + 1;

Jeśli potrzebuję pierwszego całkowicie pustego wiersza.

Jeśli potrzebuję pierwszej pustej komórki w kolumnie, wykonuję następujące czynności.

• mój pierwszy wiersz to zwykle wiersz tytułowy.

• Mój drugi rząd jest ukrytym rzędem i każda komórka ma wzór

  =COUNTA(A3:A)
  

  Gdzie A zastępuje się literą kolumny.

• Mój skrypt właśnie czyta to wartość. To aktualizuje się dość szybko w porównaniu do podejść skryptowych.

jest jeden raz, że to nie działa i wtedy pozwalam pustym komórkom rozbić kolumnę. Nie potrzebowałem do tego jeszcze poprawki, podejrzewam, że może ona pochodzić z COUNTIF, lub funkcji połączonej lub jednej z wielu innych wbudowanych.

Edytuj: COUNTA radzi sobie z pustymi komórkami w zakresie, więc troska o "jeden raz to nie działa" nie jest naprawdę. (Może to być nowe zachowanie z "nowymi arkuszami".)

Rzeczywiście getValues jest dobrą opcją, ale można użyć .funkcja długości, aby uzyskać ostatni wiersz.

 function getFirstEmptyRow() {
  var spr = SpreadsheetApp.getActiveSpreadsheet();
  var array = spr.getDataRange().getValues();
  ct = array.length + 1
  return (ct);
}

Trzymam dodatkowy arkusz "konserwacji", w moich arkuszach kalkulacyjnych, gdzie przechowuję takie dane.

Aby uzyskać następny wolny rząd zakresu, po prostu sprawdzam odpowiednią komórkę. Mogę uzyskać wartość natychmiast, ponieważ praca znalezienia wartości dzieje się, gdy dane są zmieniane.

Formuła w komórce jest zwykle czymś w rodzaju:

=QUERY(someSheet!A10:H5010, 
    "select min(A) where A > " & A9 & " and B is null and D is null and H < 1")

Wartość w A9 może być okresowo ustawiana na jakiś wiersz, który jest blisko "wystarczająco" do końca.

Zastrzeżenie : nigdy nie sprawdzałem, czy jest to opłacalne dla dużych zbiorów danych.

Mam podobny problem. W tej chwili jest to Tabela z wieloma setkami wierszy, i spodziewam się, że wzrośnie do wielu wielu tysięcy. (Nie widziałem, czy arkusz kalkulacyjny Google obsłuży dziesiątki tysięcy wierszy, ale w końcu się tam dostanę.)

1. krok do przodu przez kolumnę o setki, zatrzymaj się, gdy jestem w pustym wierszu.
2. krok wstecz przez kolumnę o dziesiątki, szukając pierwszego niepustego wiersza.
3. krok do przodu przez kolumnę po jedynkach, szukając pierwszego pustego wiersza.
4. zwraca wynik.

Zależy to oczywiście od posiadania sąsiadujących ze sobą treści. Nie ma tam żadnych przypadkowych pustych linii. A przynajmniej, jeśli to zrobisz, wyniki będą nieoptymalne. I możesz dostroić przyrosty, jeśli uważasz, że to ważne. Działają one dla mnie i uważam, że różnica w czasie trwania między krokami 50 i krokami 100 jest znikoma.

function lastValueRow() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var r = ss.getRange('A1:A');
  // Step forwards by hundreds
  for (var i = 0; r.getCell(i,1).getValue() > 1; i += 100) { }
  // Step backwards by tens
  for ( ; r.getCell(i,1).getValue() > 1; i -= 10) { }
  // Step forwards by ones
  for ( ; r.getCell(i,1).getValue() == 0; i--) { }
  return i;
}

Podkręciłem kod dostarczony przez ghoti tak, że szukał pustej komórki. Porównywanie wartości nie działało na kolumnie z tekstem (lub nie mogłem dowiedzieć się, jak) zamiast tego użyłem isblank (). Zauważ, że wartość jest negowana ! (przed zmienną r) gdy patrząc w przyszłość, ponieważ chcesz i zwiększyć aż pusty jest znaleziony. Praca arkusza przez dziesięć chcesz przestać zmniejszać i kiedy znajdziesz komórkę, która nie jest pusta (! usunięto). Następnie z powrotem do arkusza o jeden do pierwszego puste.

function findRow_() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.setActiveSheet(ss.getSheetByName("DAT Tracking"));
  var r = ss.getRange('C:C');
  // Step forwards by hundreds
  for (var i = 2; !r.getCell(i,1).isBlank(); i += 100) { }
  // Step backwards by tens
  for ( ; r.getCell(i,1).isBlank(); i -= 10) { }
  // Step forwards by ones
  for ( ; !r.getCell(i,1).isBlank(); i++) { }
  return i;

W końcu mam rozwiązanie jednoliniowe.

var sheet = SpreadsheetApp.getActiveSpreadsheet();
var lastEmptyOnColumnB = sheet.getRange("B1:B"+sheet.getLastRow()).getValues().join(",").replace(/,,/g, '').split(",").length;

Tylko moje dwa centy, ale robię to cały czas. Po prostu zapisuję dane na górze arkusza. To data odwrócona (najnowsza na górze), ale nadal mogę go zmusić do zrobienia tego, co chcę. Poniższy kod przechowuje dane, które skrobnie z witryny pośrednika w ciągu ostatnich trzech lat.

var theSheet = SpreadsheetApp.openById(zSheetId).getSheetByName('Sheet1');
theSheet.insertRowBefore(1).getRange("A2:L2").setValues( [ zPriceData ] );

Ten fragment funkcji skrobaka wstawia wiersz powyżej # 2 i zapisuje tam dane. Pierwszy wiersz to nagłówek, więc tego nie dotykam. Nie zmierzyłem czasu, ale mam problem tylko wtedy, gdy zmiany na stronie.

Użycie indexOf jest jednym ze sposobów, aby to osiągnąć:

function firstEmptyRow() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var rangevalues = sh.getRange(1,1,sh.getLastRow(),1).getValues(); // Column A:A is taken
  var dat = rangevalues.reduce(function (a,b){ return a.concat(b)},[]); // 
 2D array is reduced to 1D//
  // Array.prototype.push.apply might be faster, but unable to get it to work//
  var fner = 1+dat.indexOf('');//Get indexOf First empty row
  return(fner);
  }