[performance] Modo più veloce per trovare la prima riga vuota


Answers

Questa domanda ha ora più di 12.000 visualizzazioni , quindi è il momento di un aggiornamento, poiché le caratteristiche delle prestazioni dei nuovi fogli sono diverse rispetto a quando Serge ha eseguito i test iniziali .

Buone notizie: le prestazioni sono molto migliori su tutta la linea!

più veloce:

Come nel primo test, la lettura dei dati del foglio solo una volta, quindi operando sull'array, ha dato un enorme vantaggio in termini di prestazioni. È interessante notare che la funzione originale di Don è risultata molto migliore rispetto alla versione modificata testata da Serge. (Sembra che while è più veloce di for , che non è logico.)

Il tempo medio di esecuzione sui dati di esempio è di soli 38 ms , in calo rispetto ai 168 ms precedenti.

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

Risultati del test:

Ecco i risultati, riassunti in oltre 50 iterazioni in un foglio di calcolo con 100 righe x 3 colonne (riempite con la funzione di test di Serge).

I nomi delle funzioni corrispondono al codice nello script sottostante.

"Prima riga vuota"

La domanda originale era trovare la prima riga vuota. Nessuno degli script precedenti in realtà lo fornisce. Molti controllano solo una colonna, il che significa che possono dare risultati falsi positivi. Altri trovano solo la prima riga che segue tutti i dati, il che significa che le righe vuote nei dati non contigui vengono perse.

Ecco una funzione che soddisfa le specifiche. È stato incluso nei test e, anche se più lento del correttore fulmineo a colonna singola, è arrivato a 68ms di tutto rispetto, un premio del 50% per una risposta corretta!

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

Sceneggiatura completa:

Se si desidera ripetere i test o aggiungere la propria funzione al mix come confronto, basta prendere l'intero script e utilizzarlo in un foglio di calcolo.

/**
 * 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

Ho creato uno script che ogni poche ore aggiunge una nuova riga a un foglio di calcolo di Google Apps.

Questa è la funzione che ho fatto per trovare la prima riga vuota:

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

Funziona bene, ma quando si raggiungono circa 100 file, diventa molto lento, anche dieci secondi. Sono preoccupato che quando raggiungo migliaia di file, sarà troppo lento, forse andando in timeout o peggio. C'è un modo migliore?




Ho ottimizzato il codice ghoti fornito in modo che cercasse una cella vuota. Il confronto dei valori non ha funzionato su una colonna con testo (o non ho potuto capire come) invece ho usato isBlank (). Si noti che il valore è negato con! (davanti alla variabile r) quando guardo avanti poiché vuoi che aumenti finché non viene trovato uno spazio vuoto. Elaborando il foglio per dieci si vuole smettere di diminuire i quando si trova una cella che non è vuota (! Rimosso). Quindi, indietro il foglio di uno al primo vuoto.

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;



So che questo è un vecchio thread e ci sono stati alcuni approcci molto intelligenti qui.

Io uso la sceneggiatura

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

se ho bisogno della prima riga completamente vuota.

Se ho bisogno della prima cella vuota in una colonna, faccio quanto segue.

  • La mia prima fila è solitamente una riga del titolo.
  • La mia seconda riga è una riga nascosta e ogni cella ha la formula

    =COUNTA(A3:A)
    

    Dove A è sostituito con la lettera della colonna.

  • Il mio script legge questo valore. Questo aggiornamento è abbastanza veloce rispetto agli approcci di script.

C'è una volta che questo non funziona e cioè quando consento alle celle vuote di suddividere la colonna. Non ho ancora avuto bisogno di una correzione per questo, ho il sospetto che uno possa essere derivato da COUNTIF , o una funzione combinata o una delle tante altre incorporate.

EDIT: COUNTA fa fronte a celle vuote all'interno di un intervallo, quindi la preoccupazione per "una volta che questo non funziona" non è davvero una preoccupazione. (Potrebbe trattarsi di un nuovo comportamento con "nuovi fogli".)




Solo i miei due centesimi, ma lo faccio sempre. Scrivo solo i dati nella parte superiore del foglio. La data è invertita (l'ultima in alto), ma posso ancora farla fare quello che voglio. Il codice qui sotto memorizza i dati che riscuote dal sito di un agente immobiliare negli ultimi tre anni.

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

Questo blocco della funzione raschietto inserisce una riga sopra il n. 2 e scrive i dati lì. La prima riga è l'intestazione, quindi non la tocco. Non l'ho cronometrato, ma l'unica volta che ho un problema è quando il sito cambia.




Vedendo questo vecchio post con 5k view ho prima controllato la 'migliore risposta' e sono rimasto piuttosto sorpreso dal suo contenuto ... questo è stato davvero un processo molto lento! poi mi sono sentito meglio quando ho visto la risposta di Don Kirkby, l'approccio array è davvero molto più efficiente!

Ma quanto più efficiente?

Così ho scritto questo piccolo codice di test su un foglio di calcolo con 1000 righe e qui ci sono i risultati: (non male! ... non c'è bisogno di dire quale è quale ...)

ed ecco il codice che ho usato:

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

E il foglio di calcolo del test per provare da solo :-)

MODIFICARE :

Seguendo il commento di Mogsdad, dovrei menzionare che questi nomi di funzioni sono davvero una cattiva scelta ... Dovrebbe essere qualcosa come getLastNonEmptyCellInColumnAWithPlentyOfSpaceBelow() che non è molto elegante (vero?) Ma più accurato e coerente con ciò che effettivamente restituisce.

Commento:

Ad ogni modo, il mio punto era mostrare la velocità di esecuzione di entrambi gli approcci, e ovviamente lo ha fatto (non è vero? ;-)




Tengo un foglio di "manutenzione" in più, sui miei fogli di calcolo, dove tengo questi dati.

Per ottenere la prossima riga libera di un intervallo, esamino la cella pertinente. Posso ottenere istantaneamente il valore, perché il lavoro di ricerca del valore si verifica quando i dati vengono modificati.

La formula nella cella è in genere qualcosa di simile:

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

Il valore in A9 può essere impostato periodicamente su una riga vicina "sufficiente" alla fine.

Avvertenza : non ho mai controllato se questo è fattibile per enormi set di dati.




Related