performance - ultima - vba excel trovare prima riga vuota




Modo più veloce per trovare la prima riga vuota (9)

È già presente come metodo getLastRow sul foglio.

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

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

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?


E perché non usare appendRow ?

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

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;

Ho un problema simile. In questo momento è una tabella con molte centinaia di righe e mi aspetto che aumenti di molte migliaia. (Non ho visto se un foglio di lavoro di Google gestirà decine di migliaia di righe, ma ci arriverò alla fine.)

Ecco cosa sto facendo.

  1. Avanzare attraverso la colonna per centinaia, fermarsi quando sono su una fila vuota.
  2. Torna indietro attraverso la colonna di decine, cercando la prima riga non vuota.
  3. Avanzare attraverso la colonna di uno, cercando la prima riga vuota.
  4. Restituisce il risultato.

Ciò dipende ovviamente dall'avere contenuti contigui. Non è possibile avere righe vuote casuali. O almeno, se lo fai, i risultati saranno sub-ottimali. E puoi regolare gli incrementi se pensi che sia importante. Questi funzionano per me, e trovo che la differenza di durata tra i passi di 50 e passi di 100 sia trascurabile.

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

Questo è molto più veloce di ispezionare ogni cella dall'alto. E se ti capita di avere altre colonne che estendono il tuo foglio di lavoro, potrebbe essere più veloce di ispezionare ogni cella anche dal basso.


In effetti i getValues ​​sono una buona opzione, ma puoi usare la funzione .length per ottenere l'ultima riga.

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

L'utilizzo di indexOf è uno dei modi per ottenere ciò:

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

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? ;-)







google-spreadsheet