JSON.FROMRANGE

Ich muss schon gestehen, die Excel-Funktion LAMBDA fasziniert mich weiterhin. Weil ich in einem meiner Projekte per VBA-Code aus einzelnen Zellbereichen JSON-Daten generieren musste, hatte ich mich gefragt, ob sich vielleicht das Konvertieren eines Zellbereichs in JSON-Daten auch nicht mit einer Formel realisieren liesse. Vorab: ja, das geht – mit einer LAMBDA-Funktion!

Dieser Artikel beschreibt, wie ich zu diesem Zweck meine benutzerdefinierte Excel-Funktion JSON.FROMRANGE implementiert habe. Um alle nachfolgend beschriebenen Schritte nachvollziehen zu können, ist ein Grundwissen zu der LAMBDA-Funktion empfehlenswert. Mein Artikel zur Einführung der LAMBDA-Funktion in Excel stellt die LAMBDA-Funktion und deren Fähigkeiten ausführlich vor.

Zudem wird JSON.FROMRANGE Gebrauch von zwei Hilfsfunktionen machen, die jeweils selbst LAMBDA-Funktionen sind. Die erste dieser Funktionen ist ISODATETIME, die im gleichnamigen Artikel genauer erläutert wird. Die zweite Hilfsfunktion ist eine in JSON.FROMRANGE integrierte, rekursive LAMBDA-Funktion, deren Prinzip ich im Artikel LAMBDA-Funktionen als Variable rekursiv verwenden beschrieben habe.

JSON

Die Dokumentation bei Mozilla definiert JSON als ein textbasierendes Datenformat angelehnt an die JavaScript Object Syntax und popularisiert durch Douglas Crockford. Auch wenn es der JavaScript Object Syntax ähnelt, ist es dennoch Javascript unabhängig. Derzeit unterstützen zahlreiche Programmierumgebungen JSON, sowohl lesend als auch schreibend.

Mittlerweile ist JSON so geläufig, dass etliche Schnittstellen (API) Abfragedaten als JSON zurückliefern. So wie beispielsweise Twitter, Google oder Facebook. Auch dieser WordPress-Blog hat eine solche Schnittstelle, die ich allerdings abgeschaltet habe. Wer übrigens zum Testen und Anschauen eine Fake-API benötigt, wird bei {JSON} Placeholder fündig.

JSON-Daten folgen einem standardisierten Schema und berücksichtigen hierbei auch verschiedene Datentypen. So werden Objekte und Array durch geschweifte und eckige Klammern dargestellt, die auch verschachtelt werden können. Feldnamen sind durch doppelte Anführungszeichen als Text ausgezeichnet, ebenso Daten vom Typ Text. Zahlen werden in englischer Notation aufgeführt und Boolsche Werte als true oder false. JSON kennt auch Nullwerte, die dann durch null gekennzeichnet werden.

Für den Fall, dass das doppelte Anführungszeichen in einem Text vorkommt, muss dieses maskiert werden, was durch das Voranstellen eines Backslashs erfolgt. Und, Zeilenumbrüche oder Tabulatoren sind durch \n und \t zu ersetzen.

Nachfolgend ein beispielhafter Aufbau einer JSON-Datei. Das Feld zur id ist eine Zahl, das Feld valid ein Wahrheitswert, lastlogin eine Datums- und Zeitangabe und die restlichen Felder Strings.

Eine Besonderheit bei JSON-Daten ist, dass es keinen explizit definierten Datentyp für Datums- und Zeitangaben gibt. Diese werden als Text dargestellt, wie in der obigen Abbidlung beispielhaft durch 2021-03-15T12:30:00. In der Regel hat sich jedoch die ISO-Norm ISO 8601 zur Darstellung von Datum- und Zeitangaben in JSON-Dateien durchgesetzt. Kurz zusammengefasst, besagt diese Norm, dass Datumsangaben in der Schreibweise JJJJ-MM-TT und Zeitangaben in der Schreibweise hh:mm:ss zu erfolgen haben. Wird ein Datum mit einer Zeit kombiniert, ist der Zeitangabe der Buchstabe T voranzustellen. Die ISO-Norm beinhaltet auch eine standardisierte Schreibweise zur Berücksichtigung von Zeitzonen. Bezieht sich die Zeit auf die koordinierte Weltzeit UTC, kann dies durch den Buchstaben Z nach der Uhrzeit markiert werden. Oder die Differenz zu UTC im Uhrzeitformat angegeben werden, zum Beispiel -02:00 oder +05:30.

Anforderungen

Nehmen wir mal an, es liegt uns folgend abgebildete Tabelle vor. Die Spaltenüberschriften bzw. Kopfzeile befindet sich in Zeile 1 und darunter die Daten. Die Tabelle weist pro Spalte teilweise unterschiedliche Datentypen auf.

Wenn dieser Bereich in JSON-Daten konvertiert wird, müsste das Ergebnis wie folgt abgebildet aussehen. Die Spaltenüberschriften werden zu Eigenschaften, die pro JSON-Datensatz wiederholt werden. Die Spalten für den Vornamen und Nachnamen sind als Strings darzustellen, das Alter als Zahl, die Verifizierung als Wahrheitswert und das Login als Datums- und Zeitangabe in Textform.

Eine LAMBDA-Funktion muss somit die verschiedenen Datentypen berücksichtigen. Bestimmte Sonderzeichen, wie Zeilenumbrüche in Texten müssen entweder maskiert oder durch eine definierte Zeichfolge ersetzt werden. Das trifft zum Beispiel auf doppelte Anführungszeichen in Texten zu, die durch \“ ersetzt werden müssen, oder dem Zeichen 10 (Zeilenumbruch), was durch \n zu ersetzen ist.

In einer meiner ersten Versionen bei der Entwicklung der LAMBDA-Funktionen hatte ich die Idee, den in einer Zelle hinterlegten Datentyp automatisch pro Zelle zu scannen. Das klappt zwar ganz gut mit Zellen, die einen Text, eine Zahl oder einen Wahrheitswert beinhalten. Jedoch nicht mehr, wenn es um ein Datum oder eine Zeit geht bzw. eine Kombination von beiden. Excel stellt zwar die Funktion ZELLE zur Verfügung, die über das Argument Format einige der Zellformate erkennt, darunter auch Datums- und Zeitformate, jedoch ist diese hier quasi nutzlos. Denn ZELLE gibt das Ergebnis sprachabhängig zurück. So liefert beispielsweise ZELLE("Format";A1) für eine Zelle mit dem Zellformat h:mm in Deutsch U4, in Englisch D9, in Französisch H4, in Türkisch Z4 oder in Finnisch A4 zurück.

Damit die LAMBDA-Funktion JSON.FROMRANGE nun eine korrekte Konvertierung gewährleisten kann, ist es somit sinnvoll, dass die LAMBDA-Funktion weiss, welchen Datentyp welche Spalte hat. Es ergibt sich also als Definition der Funktion:

=JSON.FROMRANGE(Header;Data;Types)

Das erste Argument Header definiert die Kopfzeile, die aus nur einer Zeile bestehen darf. Das zweite Argument Data entspricht dem Bereich zu den Daten und das dritte Argument entspricht einer Definition der Datentypen pro Spalte. Als Datentypen kommen in Frage:

1 : Text
2 : Zahl
3 : Datum
4 : Zeit
5 : Datum und Zeit
6 : Wahrheitswert

Bezogen auf unser zuvor abgebildetes Beispiel, ändert sich dieses nun wie folgt, wo dann in Zeile 1 die Datentypen, in Zeile 2 die Kopfzeile und in den Zeilen 3 und 4 der Datenbereich aufgeführt ist.

Nicht immer sind in Spalten jedoch Datums- und Zeitangaben enthalten. Daher kann das Argument Types auch optional sein und eine automatisierte Datentyperkennung für Texte, Zahlen und Boolesche Werte erfolgen. Eine solche automatisierte Erkennnung würde dann über die Angaben in der ersten Datenzeile erfolgen. Wären bei Auslassung des Arguments Types in den Spalten trotzdem Datums- und/oder Zeitangaben enthalten, würden diese als Zahl erkannt werden.

Einige Beispielaufrufe der Funktion JSON.FROMRANGE:

=JSON.FROMRANGE(A2:E2;A3:E4;A1:E1)
=JSON.FROMRANGE(A2:E2;A3:E4;)
=JSON.FROMRANGE(A2:E2;A3:E4;{1;1;2;6;5})
=JSON.FROMRANGE({"A";"B"};{100;200};{2;2})

Generell ist noch anzumerken, dass JSON.FROMRANGE erwartet, dass pro Spalte alle Einträge in den Zeilen des Datenbereichs denselben Datentyp haben. Gemischte Datentypen, wie z.B. ein Text in den Zellen A3 bis A10 und anschließend Zahlen in den Zellen A11 bis A20 würden alle als Text im JSON abgelegt werden.

JSON.FROMRANGE

Beginnen wir mit dem Aufbau der LAMBDA-Funktion. In einem ersten Schritt definieren wir die Variablen, die innerhalb der Funktion zu Zwischenberechnungen und Validierungen dienen sollen. Ein Pseudocode als LET-Formel sieht zunächst wie folgt aus:

=LET(Header;A2:E2;
     Data;A3:E4;
     Types;A1:E1;
     S;Array mit zu ersetzenden Zeichen
     F;Rekursive Funktion zum Ersetzen von Zeichen
     R;Anzahl der Datenzeilen
     C;Anzahl der Spalten
     X;Hilfsvariable für Spalten
     Y;Hilfsvariable für Zeilen
     T;Array mit den Datentypen
       Berechnung vom Ergebnis)

Nebenbei, dies habe ich mir nicht mal eben so ausgedacht, das hat schon etwas Zeit in Anspruch genommen, bis ich die Variablendefinition so festgelegt hatte, wie oben aufgeführt. Fangen wir zunächst mit den einfacheren Sachen an: die Variablen R, C, X und Y. Die Variable R soll die Anzahl der Datenzeilen festlegen. Dadurch ergibt sich für R:

R=ZEILEN(Data)

Die Bereiche zu der Kopfzeile und den Daten sollten beide die gleiche Anzahl an Spalten aufweisen, damit die LAMBDA-Funktion korrekt rechnet. Da die Bereichsangabe zu den Datentypen optional ist, berücksichtigen wir dies hier nicht. Um nun einer versehentlichen Angabe unterschiedlich breiter Bereiche vorzubeugen, berechnen wir das Minimum der Spaltenanzahl aus den zwei Bereichen. Das wäre dann:

C=MIN(SPALTEN(Header);SPALTEN(Data))

Die Variablen X und Y sollen Sequenzen darstellen, weshalb deren Definition recht einfach ist:

X=SEQUENZ(1;C)
Y=SEQUENZ(R)

Schauen wir uns nun die Variable T an, die die Datentypen pro Spalte darstellen soll. Die Formel sieht wie folgt aus:

T=WENN(ISTFEHLER(--INDEX(Types;1;X));
       WENN(ISTTEXT(INDEX(Data;1;X));1;
       WENN(ISTZAHL(INDEX(Data;1;X));2;
       WENN(ISTLOG(INDEX(Data;1;X));6;1)));
       WENN((INDEX(Types;1;X)>0)*(INDEX(Types;1;X)<7);INDEX(Types;1;X);1))

Was passiert in der Formel? In der ersten WENN-Abfrage wird über die Formel INDEX(Types;1;X) ein Zugriff auf das Element X ersten Spalte des über das Argument Types übergebenen Zellbereichs versucht. Das doppelte Minus versucht gleichzeitig eine Umwandlung in eine Zahl vorzunehmen. Ist der Zugriff erfolgreich, wird geprüft, ob der Wert innerhalb der erlaubten Werte 1 und 6 ist. Scheitert der Zugriff, zum Beispiel weil Types an der Stelle X keine Zahl enthält, wird versucht den Datentyp aus der ersten Zeile des Datenbereichs zu ermittlen. Dazu werden die Funktionen ISTTEXT, ISTZAHL und ISTLOG verwendet. Der Zugriff auf das Element X zu den Typen scheitert übrigens auch, wenn das Argument Types leer gelassen wurde, womit wir dann die optionale Angabe abgefangen hätten.

Kommen wir nun zu den Variablen S und F in der LET-Formel. Wie bereits erwähnt, müssen einige Zeichen, wie Anführungszeichen oder Zeilenumbrüche in Textausgaben des JSON-Formats maskiert oder durch andere Zeichen ersetzt werden. Ein Zellwert, der beispielsweise Test"Test enthalten würde, müsste in der JSON-Datei als "Text\"Test" erscheinen.

Die Variable S erzeugt ein statisches Array, dass eben diese Zeichen in dem zweispaltigen Array ablegt. Die Formel zu der Variable S berücksichtigt hierbei das doppelte Anführungszeichen, das Tabulator-Zeichen sowie einen Zeilenvorschub und einen Wagrenrücklauf - respektive im ASCI-Code die Werte 34, 9, 10 und 13.

S=WAHL(SEQUENZ(4;2);
  ZEICHEN(34);"\"&ZEICHEN(34);
  ZEICHEN(9);"\t";
  ZEICHEN(10);"\n";
  ZEICHEN(13);"\r");

Die Funktion JSON.FROMRANGE muss für jede Zelle aus dem Datenbereich, die Text enthält, die in der ersten Spalten der Variable bzw. Array S aufgeführten Zeichen durch die Werte in der zweiten Spalte ersetzen. Dafür lässt sich eine Funktion vorsehen, die als Pseudocode wie folgt aussieht:

F(Value;Map)

Die Funktion soll dann im Text Value jedes Vorkommen des Zeichens Map[N,1] durch Map[N,2] ersetzen, wo N den Zeilen der Mapping-Tabelle Map entspricht. Bezogen auf unser zuvor definiertes Array S mit den zu ersetzenden Texten, müsste die Funktion 4-mal aufgerufen werden:

R1=F(Value;S) mit N = 4
R2=F(R1   ;S) mit N = 3
R3=F(R2   ;S) mit N = 2
R4=F(R3   ;S) mit N = 1

Warum habe ich mit dem letzten Element N gleich 4 angefangen? Weil es dann einfacher ist, ein Abbruchkriterium zu definieren. Denn spätestens nachdem N gleich 1 abgearbeitet wurde, kann die Rekursion beendet werden.

Nun stellt sich dass Problem, wie kann ich der Funktion sagen, dass sie den Wert von N bei jedem Durchlauf um eins verringern muss? Wenn das Array Map bei jedem Durchlauf um die letzte Zeile gekürzt würde, könnte ich die Excel-Funktion ZEILEN verwenden und würde dabei N automatisch reduzieren. Zum Glück steht die Excel-Funktion SEQUENZ zur Verfügung, die aus einem Array ein Teil-Array extrahieren kann. Die rekursive Funktion definiert sich somit wie folgt:

F=LAMBDA(Value;Map;
  LET(N;ZEILEN(Map);
      R;WECHSELN(Value;INDEX(Map;N;1);INDEX(Map;N;2));
      WENN(N<2;R;F(R;INDEX(Map;SEQUENZ(N-1);SEQUENZ(1;2))))))

Die Variable C ermittelt die Zeilenanzahl des an F übergebenen Arrays mit der Mapping-Tabelle, was dann der letzten Zeile des Arrays entspricht. Die Variable R beinhaltet dann das Ergebnis des Austauschs der Zeichen in Value. Danach wird geprüft, ob N kleiner 2 ist. Wenn ja, wird die Rekursion beendet und ansonsten die Funktion F erneut aufgerufen. Dabei werden wiederum R und das Array Map und die letzte Zeile gekürzt übergeben.

Somit ist nun die Ersetzungsfunktion vollständig. Ich hätte nun zwei Möglichkeiten: entweder die Funktion F als eigenständige LAMBDA-Funktion im Namensmanager, beispielsweise als TEXT.REPLACE, abzulegen, oder direkt in JSON.FROMRANGE als Variable zu verwenden. Ich hatte mich für letzteres entschieden - auch weil ich selber neugierig war, ob das funktioniert.

Damit das jedoch klappt, ist der Gültigkeitsbereich von Variablen zu berücksichtigen, indem der Funktionname selbst an die Funktion übergeben wird. Wie genau das passiert, habe ich in meinem Artikel LAMBDA-Funktionen als Variable rekursiv verwenden detaillert beschrieben. Berücksichtige ich dies in der LAMBDA-Definition für JSON.FROMRANGE, sieht dies wie folgt aus:

F=LAMBDA(F;V;M;
  LET(N;ZEILEN(M);
      R;WECHSELN(V;INDEX(M;N;1);INDEX(M;N;2));
      WENN(N<2;R;F(F;R;INDEX(M;SEQUENZ(N-1);SEQUENZ(1;2))))))

Fassen wir nun die bisherigen Schritte zu unserer LET-Formel zusammen:

=LET(Header;A2:E2;
     Data;A3:E4;
     Types;A1:E1;
     S;WAHL(SEQUENZ(4;2);
       ZEICHEN(34);"\"&ZEICHEN(34);
       ZEICHEN(9);"\t";
       ZEICHEN(10);"\n";
       ZEICHEN(13);"\r");
     F;LAMBDA(F;V;M;
       LET(N;ZEILEN(M);
           R;WECHSELN(V;INDEX(M;N;1);INDEX(M;N;2));
             WENN(N<2;R;F(F;R;INDEX(M;SEQUENZ(N-1);SEQUENZ(1;2))))));
     R;ZEILEN(Data);
     C;MIN(SPALTEN(Header);SPALTEN(Data));
     X;SEQUENZ(1;C);
     Y;SEQUENZ(R);
     T;WENN(ISTFEHLER(--INDEX(Types;1;X));
       WENN(ISTTEXT(INDEX(Data;1;X));1;
       WENN(ISTZAHL(INDEX(Data;1;X));2;
       WENN(ISTLOG(INDEX(Data;1;X));6;1)));
       WENN((INDEX(Types;1;X)>0)*(INDEX(Types;1;X)<7);INDEX(Types;1;X);1));
       Berechnung vom Ergebnis)

Was jetzt noch fehlt, ist die Berechnung vom Ergebnis. Dazu muss der Datenbereich zeilenweise durchlaufen werden und pro Spalte mit dem aus der Kopfzeile zu entnehmenden Eigenschaftsnamen ausgezeichnet werden. Einzelne Eingeschaft-Wert-Paare sind durch ein Komma und die Datensätze durch geschweifte Klammern zu trennen. Ist mehr als eine Zeile im Datenbereich enthalten, sind die JSON-Daten als Array zu kennzeichnen. Und, es sind die Datentypen zu berücksichtigen. Die Formel zur Berechnung des Ergebnisses sieht wie folgt aus:

=WENN(R>1;"["&"{";"{")&
 TEXTKETTE(ZEICHEN(34)&""&INDEX(Header;1;X)&ZEICHEN(34)&":"&" "&
 WENN(ISTLEER(INDEX(Data;Y;X));"null";
 WAHL(T;ZEICHEN(34)&F(F;INDEX(Data;Y;X);S)&ZEICHEN(34);
        WECHSELN(""&WENNFEHLER(--INDEX(Data;Y;X);0);",";".");
        ZEICHEN(34)&ISODATETIME(INDEX(Data;Y;X);1;0;0)&ZEICHEN(34);
        ZEICHEN(34)&ISODATETIME(INDEX(Data;Y;X);2;0;0)&ZEICHEN(34);
        ZEICHEN(34)&ISODATETIME(INDEX(Data;Y;X);3;0;0)&ZEICHEN(34);
        WENN(WENNFEHLER(--INDEX(Data;Y;X);0);"true";"false")))&
 WENN(X<C;",";WENN(Y<R;"}"&","&"{";"")))&
 WENN(R>1;"}"&"]";"}")

Die Funktion TEXTKETTE durchläuft die Bereich spaltenweise von links nach rechts und zeilenweise von oben nach unten. Was dann ermöglicht, die Zellwerte der Kopfzeile den Zellwerten des Datenbereichs zuzuordnen und gleichzeitig für die Zellwerte aus dem Datenbereich anhand der Excel-Funktion WAHL zu den unterschiedlichen Datentypen zu verzweigen.

Für die Umwandlung von Datums- und/oder Zeitangaben nach ISO 8601 wird die LAMBDA-Funktion ISODATETIME aufgerufen, die im gleichnamigen Artikel beschrieben habe.

Damit ist die LAMBDA-Funktion vollständig. Es verbleibt, diese dann als JSON.FROMRANGE oder, falls erwünscht, unter einem anderen Namen im Namensmanager abzulegen. Die Funktion lautet somit:

=LAMBDA(Header;Data;Types;
 LET(S;WAHL(SEQUENZ(4;2);
            ZEICHEN(34);"\"&ZEICHEN(34);
            ZEICHEN(9);"\t";
            ZEICHEN(10);"\n";
            ZEICHEN(13);"\r");
     F;LAMBDA(F;V;M;
              LET(N;ZEILEN(M);
                  R;WECHSELN(V;INDEX(M;N;1);INDEX(M;N;2));
                  WENN(N<2;R;F(F;R;INDEX(M;SEQUENZ(N-1);SEQUENZ(1;2))))));
     R;ZEILEN(Data);
     C;MIN(SPALTEN(Header);SPALTEN(Data));
     X;SEQUENZ(1;C);
     Y;SEQUENZ(R);
     T;WENN(ISTFEHLER(--INDEX(Types;1;X));
       WENN(ISTTEXT(INDEX(Data;1;X));1;
       WENN(ISTZAHL(INDEX(Data;1;X));2;
       WENN(ISTLOG(INDEX(Data;1;X));6;1)));
       WENN((INDEX(Types;1;X)>0)*(INDEX(Types;1;X)<7);INDEX(Types;1;X);1));
       WENN(R>1;"["&"{";"{")&
       TEXTKETTE(ZEICHEN(34)&""&
       INDEX(Header;1;X)&ZEICHEN(34)&":"&" "&
       WENN(ISTLEER(INDEX(Data;Y;X));"null";
       WAHL(T;ZEICHEN(34)&F(F;INDEX(Data;Y;X);S)&ZEICHEN(34);
              WECHSELN(""&WENNFEHLER(--INDEX(Data;Y;X);0);",";".");
              ZEICHEN(34)&ISODATETIME(INDEX(Data;Y;X);1;0;0)&ZEICHEN(34);
              ZEICHEN(34)&ISODATETIME(INDEX(Data;Y;X);2;0;0)&ZEICHEN(34);
              ZEICHEN(34)&ISODATETIME(INDEX(Data;Y;X);3;0;0)&ZEICHEN(34);
              WENN(WENNFEHLER(--INDEX(Data;Y;X);0);"true";"false")))&
       WENN(X<C;",";WENN(Y<R;"}"&","&"{";"")))&
       WENN(R>1;"}"&"]";"}")))

Für mein englischsprachiges Publikum folgend die Übersetzung via meinem Excel Formel Übersetzer.

=LAMBDA(Header,Data,Types,
 LET(S,CHOOSE(SEQUENCE(4,2),
              CHAR(34),"\"&CHAR(34),
              CHAR(9),"\t",
              CHAR(10),"\n",
              CHAR(13),"\r"),
     F,LAMBDA(F,V,M,
              LET(N,ROWS(M),
                  R,SUBSTITUTE(V,INDEX(M,N,1),INDEX(M,N,2)),
                  IF(N<2,R,F(F,R,INDEX(M,SEQUENCE(N-1),SEQUENCE(1,2)))))),
     R,ROWS(Data),
     C,MIN(COLUMNS(Header),COLUMNS(Data)),
     X,SEQUENCE(1,C),
     Y,SEQUENCE(R),
     T,IF(ISERROR(--INDEX(Types,1,X)),
       IF(ISTEXT(INDEX(Data,1,X)),1,
       IF(ISNUMBER(INDEX(Data,1,X)),2,
       IF(ISLOGICAL(INDEX(Data,1,X)),6,1))),
       IF((INDEX(Types,1,X)>0)*(INDEX(Types,1,X)<7),INDEX(Types,1,X),1)),
       IF(R>1,"["&"{","{")&
       CONCAT(CHAR(34)&""&
       INDEX(Header,1,X)&CHAR(34)&":"&" "&
       IF(ISBLANK(INDEX(Data,Y,X)),"null",
       CHOOSE(T,CHAR(34)&F(F,INDEX(Data,Y,X),S)&CHAR(34),
                SUBSTITUTE(""&IFERROR(--INDEX(Data,Y,X),0),",","."),
                CHAR(34)&ISODATETIME(INDEX(Data,Y,X),1,0,0)&CHAR(34),
                CHAR(34)&ISODATETIME(INDEX(Data,Y,X),2,0,0)&CHAR(34),
                CHAR(34)&ISODATETIME(INDEX(Data,Y,X),3,0,0)&CHAR(34),
                IF(IFERROR(--INDEX(Data,Y,X),0),"true","false")))&
       IF(X<C,",",IF(Y<R,"}"&","&"{","")))&
       IF(R>1,"}"&"]","}")))

Fazit

Mit der Funktion JSON.FROMRANGE lassen sich sehr einfach aus einem Zellbereich JSON-Daten erzeugen. Und es wurde aufgezeigt, dass sich innerhalb einer LAMBDA-Funktion weitere, auch rekursive, LAMBDA-Funktionen als Hilfsfunktion verwenden lassen.

Bei JSON.FROMRANGE ist allerdings zu beachten, dass auf Grund der Excel-Limitierung auf 32.767 Zeichen pro Zelle, die Anzahl der Zeilen im Datenbereich begrenzt ist. Beispielsweise ist nach dem hier verwendeten Beispiel etwa nach 320 Zeilen Schluß, wenn ich die zweite Zeile nach unten kopiere.

Happy Exceling :-)