[performance] Schnellere Möglichkeit, die erste leere Zeile zu finden



Answers

Diese Frage hat nun mehr als 12.000 Aufrufe. Es ist also Zeit für ein Update, da die Leistungsmerkmale von New Sheets anders sind als bei den ersten Tests von Serge .

Gute Nachrichten: Leistung ist auf der ganzen Linie viel besser!

Schnellste:

Wie im ersten Test ergab das einmalige Lesen der Daten des Blatts und das anschließende Arbeiten mit dem Array einen enormen Leistungsvorteil. Interessanterweise schnitt Dons ursprüngliche Funktion viel besser ab als die modifizierte Version, die Serge getestet hatte. (Es scheint, dass while schneller ist als for , was nicht logisch ist.)

Die durchschnittliche Ausführungszeit für die Beispieldaten beträgt nur 38 ms gegenüber den vorherigen 168 ms .

// 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);
}

Testergebnisse:

Hier sind die Ergebnisse, zusammengefasst über 50 Iterationen in einer Tabelle mit 100 Zeilen x 3 Spalten (gefüllt mit der Testfunktion von Serge).

Die Funktionsnamen stimmen mit dem Code im folgenden Skript überein.

"Erste leere Reihe"

Die ursprüngliche Frage war, die erste leere Zeile zu finden. Keines der vorherigen Skripte liefert das tatsächlich. Viele überprüfen nur eine Spalte, was bedeutet, dass sie falsch positive Ergebnisse liefern können. Andere finden nur die erste Zeile, die allen Daten folgt, was bedeutet, dass leere Zeilen in nicht zusammenhängenden Daten verpasst werden.

Hier ist eine Funktion, die die Spezifikation erfüllt. Es wurde in die Tests einbezogen, und obwohl es langsamer war als der blitzschnelle Einspalten-Checker, kam es mit respektablen 68 ms, eine 50% Prämie für eine korrekte Antwort!

/**
 * 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);
}

Vollständiges Skript:

Wenn Sie die Tests wiederholen oder eine eigene Funktion zum Vergleich hinzufügen möchten, nehmen Sie einfach das gesamte Skript und verwenden Sie es in einer Tabelle.

/**
 * 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;
}
Question

Ich habe ein Skript erstellt, das alle paar Stunden eine neue Zeile zu einer Google Apps-Tabelle hinzufügt.

Dies ist die Funktion, die ich gemacht habe, um die erste leere Zeile zu finden:

function getFirstEmptyRow() {
  var spr = SpreadsheetApp.getActiveSpreadsheet();
  var cell = spr.getRange('a1');
  var ct = 0;
  while ( cell.offset(ct, 0).getValue() != "" ) {
    ct++;
  }
  return (ct);
}

Es funktioniert gut, aber wenn es ungefähr 100 Zeilen erreicht, wird es wirklich langsam, sogar zehn Sekunden. Ich bin besorgt, dass wenn es Tausende von Zeilen erreicht, es zu langsam sein wird, vielleicht in Timeout oder schlimmer. Gibt es einen besseren Weg?




Ich besitze ein extra "Wartungsblatt" auf meinen Tabellen, wo ich solche Daten aufbewahre.

Um die nächste freie Zeile eines Bereichs zu erhalten, untersuche ich einfach die relevante Zelle. Ich kann den Wert sofort erhalten, da die Suche nach dem Wert geschieht, wenn die Daten geändert werden.

Die Formel in der Zelle ist normalerweise etwas wie:

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

Der Wert in A9 kann periodisch auf eine Zeile gesetzt werden, die nahe genug am Ende ist.

Vorbehalt : Ich habe nie geprüft, ob dies für große Datenmengen geeignet ist.




Nur meine zwei Cent, aber ich mache das die ganze Zeit. Ich schreibe einfach die Daten auf die Oberseite des Blattes. Es ist Datum umgekehrt (spät oben), aber ich kann es immer noch tun, was ich will. Der folgende Code speichert Daten, die in den letzten drei Jahren von der Website eines Maklers gelöscht wurden.

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

Dieser Teil der Scraper-Funktion fügt eine Zeile über # 2 ein und schreibt die Daten dort. Die erste Zeile ist die Kopfzeile, also berühre ich das nicht. Ich habe es nicht geplant, aber das einzige Mal, dass ich ein Problem habe, ist, wenn sich die Seite ändert.




Ich weiß, dass dies ein alter Faden ist und es gab hier sehr clevere Ansätze.

Ich benutze das Skript

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

wenn ich die erste komplett leere Reihe brauche.

Wenn ich die erste leere Zelle in einer Spalte brauche, mache ich folgendes.

  • Meine erste Zeile ist normalerweise eine Titelzeile.
  • Meine zweite Reihe ist eine versteckte Reihe und jede Zelle hat die Formel

    =COUNTA(A3:A)
    

    Wo A durch den Spaltenbuchstaben ersetzt wird.

  • Mein Skript liest gerade diesen Wert. Dies wird relativ schnell im Vergleich zu Skriptansätzen aktualisiert.

Es gibt eine Zeit, die dies nicht funktioniert und das ist, wenn ich leeren Zellen erlaube, die Spalte aufzulösen. Ich habe dafür noch keine Korrektur benötigt, ich vermute, dass man von COUNTIF oder einer kombinierten Funktion oder einer der vielen anderen Funktionen abgeleitet werden kann.

EDIT: COUNTA bewältigt leere Zellen in einem Bereich, so dass die Sorge um das "Einmal, das das nicht funktioniert", nicht wirklich ein Problem ist. (Dies könnte ein neues Verhalten bei "new Sheets" sein.)




Als ich diesen alten Post mit 5k Views sah, überprüfte ich zuerst die "beste Antwort" und war ziemlich überrascht von seinem Inhalt ... das war wirklich ein sehr langsamer Prozess! dann fühlte ich mich besser, als ich Don Kirkbys Antwort sah, der Array-Ansatz ist in der Tat viel effizienter!

Aber wie viel effizienter?

Also habe ich diesen kleinen Testcode in einer Tabelle mit 1000 Zeilen geschrieben und hier sind die Ergebnisse: (nicht schlecht! ... keine Notwendigkeit zu sagen, welches ist was ...)

und hier ist der Code, den ich benutzt habe:

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']);
  }
}

Und die Testtabelle , um es selbst zu versuchen :-)

EDIT:

Nach Mogsdads Kommentar sollte ich erwähnen, dass diese Funktionsnamen in der Tat eine schlechte Wahl sind ... Es sollte etwas wie getLastNonEmptyCellInColumnAWithPlentyOfSpaceBelow() das nicht sehr elegant ist (ist es?), Aber genauer und kohärenter mit dem, was es tatsächlich zurückgibt.

Kommentar :

Wie auch immer, mein Punkt war es, die Geschwindigkeit der Ausführung beider Ansätze zu zeigen, und es hat es offensichtlich getan (nicht wahr?) ;-)




Ich habe den Code ghoti so optimiert, dass er nach einer leeren Zelle suchte. Das Vergleichen von Werten funktionierte nicht in einer Spalte mit Text (oder ich konnte nicht herausfinden, wie), stattdessen habe ich isBlank () verwendet. Beachten Sie, dass der Wert mit negiert wird! (vor der Variable r), wenn Sie vorwärts schauen, da Sie wollen, dass ich bis ein Leerzeichen gefunden wird. Wenn Sie das Blatt um zehn aufarbeiten, möchten Sie aufhören, i zu verringern, wenn Sie eine Zelle finden, die nicht leer ist (! Entfernt). Dann fädle das Blatt um eins zum ersten Blatt zurück.

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;



Related