google-apps-script google - Skript zum Zusammenfassen von nicht aktualisierenden Daten




tabellen befehle (6)

Ich habe eine Google-Tabelle mit Zeiterfassungsdaten. Es hat ein Blatt für jeden Monat, jedes Blatt ist eine Menge von sechs Spaltenblöcken, ein Block pro Client.

Ich habe ein Übersichtsblatt erstellt, das die Summe für jeden Kunden erhält und in einer Liste anzeigt:

function getClientTotals(sheetname, colcount)
{  
  colcount = colcount ? colcount : 6;
  var res;      
  var ss = SpreadsheetApp.openById('myid_goes_here');
  if(ss)
  {
    res = [];
    var totrow = ss.getRange(sheetname + '!A1:ZZ1').getValues()[0];
    for(var i = 0; i < totrow.length; i += colcount)
    {
      res.push([totrow[i], totrow[i + colcount - 1]]);
    }
  }   
  return res;
}

Ich habe dann meiner Übersichtsseite, die =getClientTotals($C$7,$C$8) enthält, eine Zelle hinzugefügt, die den =getClientTotals($C$7,$C$8) für den Monat und die Anzahl der Spalten für jeden Client (bei "Schema" =getClientTotals($C$7,$C$8) übergibt.

Das alles funktioniert gut, aber es wird nicht aktualisiert, wenn die Quelldaten geändert werden. Ich habe einen onEdit Trigger hinzugefügt. keine Freude. Es wird aktualisiert, wenn Sie zum Skript-Editor gehen und auf Speichern klicken, aber das ist nicht sinnvoll. Bin ich etwas vermisst?


Answers

Ich hatte ein ähnliches Problem beim Erstellen eines Dashboards für die Arbeit. Die obige Lösung von Chamil (nämlich die Verwendung der Funktion Sheet's Filter, die als Wert an eine Dummy-Variable in Ihrer Funktion übergeben wird) funktioniert gut, trotz des jüngsten Kommentars von Arsen. In meinem Fall habe ich eine Funktion verwendet, um einen Bereich zu überwachen, und konnte den Filter nicht für den gleichen Bereich verwenden, da er eine zirkuläre Referenz erstellt hat. Also hatte ich gerade eine Zelle (in meinem Fall E45 im Code unten), in der ich die Nummer jedesmal änderte, wenn ich meine Funktion aktualisieren wollte:

=myFunction("E3:E43","D44",filter(E45,1=1))

Wie Chamil angegeben hat, wird der Filter nicht im Skript verwendet:

function myFunction(range, colorRef, dummy) {
  variable 'dummy' not used in code here
}

Ihnen fehlt die anspruchsvolle Caching- Bug- Funktion. Es funktioniert so:

Google ist der Ansicht, dass alle Ihre benutzerdefinierten Funktionen nur von ihren Parameterwerten abhängen, um direkt ihr Ergebnis zurückzugeben (Sie können optional von anderen statischen Daten abhängig sein).

Unter dieser Voraussetzung können Sie Ihre Funktionen nur dann auswerten, wenn sich ein Parameter ändert. z.B

Nehmen wir an, wir haben den Text "10" in der Zelle B1, dann in einer anderen Zelle =myFunction(B1) wir =myFunction(B1)

myFunction wird ausgewertet und das Ergebnis abgerufen. Wenn Sie den Wert für die Zelle B1 auf "35" ändern, wird die benutzerdefinierte Funktion wie erwartet neu bewertet und das neue Ergebnis wird normal abgerufen. Wenn Sie nun die Zelle B1 wieder in die ursprüngliche "10" ändern, erfolgt keine erneute Auswertung, das ursprüngliche Ergebnis wird sofort aus dem Cache abgerufen.

Wenn Sie den Blattnamen als Parameter verwenden, um ihn dynamisch abzurufen und das Ergebnis zurückzugeben, brechen Sie die Caching-Regel.

Leider können Sie keine benutzerdefinierten Funktionen ohne diese erstaunliche Funktion haben. Sie müssen es also ändern, um die Werte direkt anstelle des Blattnamens zu erhalten, oder verwenden Sie keine benutzerdefinierte Funktion. Zum Beispiel könnten Sie einen Parameter in Ihrem Skript haben, der onEdit , wohin die Zusammenfassungen gehen sollen und einen onEdit sie bei onEdit Änderung aktualisiert.


eine andere Lösung für das Caching-Problem.

haben Sie eine Dummy-Variable in Ihrer Methode. bestehen

Filter(<the cell or cell range>,1=1)

als Wert für diesen Parameter.

z.B

=getValueScript("B1","B4:Z10", filter(B4:Z10,1=1))

Die Ausgabe des Filters wird nicht verwendet. es zeigt jedoch der Tabelle an, dass diese Formel für den Bereich B4: Z10 empfindlich ist.


Wie @ Brionius sagte, setzen Sie ein zusätzliches dinamisches Argument auf die Funktion. Wenn Sie now () verwenden, haben Sie möglicherweise Timeout-Probleme, die das Update ein wenig verlangsamen ...

cell A1 = int(now()*1000)
cell A2 = function(args..., A1)

In Anbetracht der von Henrique Abreu erläuterten Funktion können Sie die sofort einsatzbereite Tabellenfunktion QUERY verwenden , die SQL- ähnliche Abfrage häufig bei der Arbeit an Rohdaten verwendet und Daten als Zusammenfassung auf einer anderen Registerkarte abrufen. Ergebnisdaten werden aktualisiert. in Echtzeit nach der Änderung der Rohdaten.

Mein Vorschlag basiert auf der Tatsache, dass Ihr Skript keine Arbeit wie URL-Fetch, nur Datenbearbeitung, vorangetrieben hat, da ich ohne genaue Datenlesung keine genaue Lösung mit QUERY geben kann.

In Bezug auf das von Henrique Abreu erwähnte Cache-Feature (ich habe nicht genug Ruf, um direkt unter seiner Antwort zu kommentieren), habe ich getestet und festgestellt, dass:

  1. sieht aus, dass kein Cache funktioniert, das Skript der Testfunktion wird unten gezeigt:

    Funktionsaddierer (Basis) {Utilities.sleep (5000); Rückkehrbasis + 10; }

Anwenden dieses benutzerdefinierten Funktionsaddierers () in einem Blatt durch Aufrufen einer Zelle, und änderte dann diesen Zellenwert hin und her, jedes Mal, wenn ich die Lade-Nachricht und die Gesamtzeit mehr als 5 Sekunden sehen. Es könnte sich auf das in dieser GAS-Ausgabe erwähnte Update beziehen :

Dieses Problem wurde behoben. Benutzerdefinierte Funktionen in New Sheets sind jetzt kontextbezogen und zwischenspeichern Werte nicht so aggressiv.

  1. Das in diesem Thema erwähnte Problem bleibt bestehen, meine Tests legen nahe, dass Google-Blätter die benutzerdefinierte Funktion jedes Mal NUR WANN neu berechnen

    • Wert, der DIREKT von der Funktion aufgerufen wird, wird geändert.

    function getCellValue (Blattname, Zeile, Spalte) {var ss = SpreadsheetApp.getActiveSpreadsheet (); var sh = ss.getSheetByName (Blattname); return sh.getRange (row, col) .getValue (); }


    Eine Änderung eines beliebigen Werts in gelben Zellen führt zu einer Neuberechnung der benutzerdefinierten Funktion. Die Änderung der tatsächlichen Datenquellenwerte wird von der Funktion ignoriert.

    • Die Funktion, die die Position der Zelle enthält, wird im Blatt geändert. Ex. Einfügen / Entfernen einer Zeile / Spalte oberhalb oder links.

Apps Script ist (ziemlich) nur Javascript; plain-old JSON.parse ist die beste Option zum Analysieren von JSON in einer Objektdarstellung.

Sie können JSON.stringify auch verwenden, um ein Objekt in eine Zeichenfolgendarstellung zu serialisieren.







google-apps-script google-spreadsheet google-apps custom-function