Office Skript: Range aus Tabellenobjekt für Datenvalidierung

15.08.2025 17:56:13

Martin

Hallo an alle
Ich weiß nicht ob ich hier bei euch richtig bin weil die meisten Beiträge mit VBA zu tun haben und nicht mit Office Skript trotzdem wäre ich für Hilfe wirklich sehr dankbar.
VBA kann ich hier wegen den Sicherheitseinstellungen nicht verwenden.

Ich würde gern in Office Skript die Source der .dataValidation flexibel gestalten. Da sich die Anzahl der Zeilen und Spalten und auch die Position der Quelltabelle auf dem Tabellenblatt immer wieder ändern. Also ich brauch die Bereichsangabe der Spalte "Auswahl_Buchungsart" aus der Tabelle "tlb_Buchungsart". (Ich meine eine von den Intelligenten Tabellen ".listobjekt()" und in OS heißt es dann ".Table()" )
Das ist der Code ausschnitt um den es geht:

function main(workbook: ExcelScript.Workbook) {

// Aktives Arbeitsblatt abrufen

const selectedSheet = workbook.getWorksheet("Materialliste");

const tlb_Material = workbook.getTable("tlb_Material")

const tlb_Buchungsart = workbook.getTable("tlb_Buchungsart")



// Datendropdown für Spalte Buhungsart erstellen

dataValidation = tlb_Material.getColumn("Buchungsart").getRangeBetweenHeaderAndTotal().getDataValidation();

dataValidation.clear();

dataValidation.setIgnoreBlanks(true);

dataValidation.setPrompt({showPrompt: true, title: "", message: ""});

dataValidation.setErrorAlert({showAlert: true, title: "", message: "Eingabe nur über Ausfahlfeld möglich.", style: ExcelScript.DataValidationAlertStyle.stop});

dataValidation.setRule({ list: { inCellDropDown: true, source: "=Produkte!$K$2:$K10"}});

}

Die einzige möglichkeit die ich bis jetzt hinbekommen hab ist:

console.log("=" + tlb_Buchungsart.getColumn("Auswahl_Buchungsart").getRangeBetweenHeaderAndTotal().getAddress())

es erzeugt in der Konsole den String: =Produkte!K2:K10
Trag ich es jedoch bei der datenvalidierun als quelle

dataValidation.setRule({ list: { inCellDropDown: true, source: "=" + {tlb_Buchungsart.getColumn("Auswahl_Buchungsart").getRangeBetweenHeaderAndTotal().getAddress()} }});

Kommt mir ein Fehler.

Weis jemand einen effizienten weg wie ich an die Rang Koordinaten komme um sie direkt einzutragen. Das ganze scrippt erstellt recht viele Datenvalidierung, mit verschiedenen Quellen aus verschiedenen Spalten aus verschiedenen Tabellenblättern. Deswegen wäre es gut die Source der Datenvalidierungen direkt anhand des Tabellennamens und spalten namens einzutragen, sonst verliere ich den Überblick. Office Skript ist etwas mühsam ;)

Bin für jeden Vorschlag dankbar.

Grüße Martin

AW: Office Skript: Range aus Tabellenobjekt für Datenvalidierung

15.08.2025 18:21:56

Yal

Moin Martin,

ja, Office Skript sind noch sehr unterrepräsentiert in dem Forum.

Habe selber auch nur einen sehr bescheidenes Wissen darüber. Ich habe unter
https://learn.microsoft.com/de-de/office/dev/scripts/resources/samples/table-samples#dynamically-reference-table-values
entdeckt, dass man bei Variablen eine $ vor dem {..} setzen kann.

Dann der Weg über eine Variable wäre womöglich so:

const listQuelle = tlb_Buchungsart.getColumn("Auswahl_Buchungsart").getRangeBetweenHeaderAndTotal().getAddress();

dataValidation.setRule({ list: { inCellDropDown: true, source: '=${listQuelle}'}});

VG
Yal

AW: Office Skript: Range aus Tabellenobjekt für Datenvalidierung

15.08.2025 18:44:46

Yal

Moin,

ich einige Test gemacht. Folgendes Zwischenergebnis mit Tabelle tbl_Monat und Spalte Monat:

dataValidation.setRule({list: {inCellDropDown: true, source: "=INDIREKT(\"\"tbl_Monat[Monat]\"\")"}});

AW: Office Skript: Range aus Tabellenobjekt für Datenvalidierung

15.08.2025 23:15:30

Martin

Erstmal Danke für deine Antwort.
Eine Variable wäre naturlich eine möglichkeit und es funktioniert auch aber dann würde ich für jede Validierung eine separate variable anlegen. Und später kommt noch die Erstellung sehr vielen bedingten Formatierungen dazu.

Es muss doch irgendwie möglich sein es direkt aus der Methode heraus zu machen

 console.log("=" +{tlb_Buchungsart.getColumn("Auswahl_Buchungsart").getRangeBetweenHeaderAndTotal().getAddress())

;
Zeigt ja den richtigen Bereich in der Konsolen ausgabe, ich verstehe nicht warum es dann in der source zu einem Fehler führt. Glaub mir fehlen irgendwelche Anführungszeichen aber ich komm einfach nicht drauf wie ich es dort reinschreiben muss.

Grüße Martin

AW: Office Skript: Range aus Tabellenobjekt für Datenvalidierung

15.08.2025 23:36:17

Martin

Moin

Ich hätte zu dem Vorschlag mit der Indirekt Formel eine Frage:

source:"=INDIREKT(\"\"tbl_Monat[Monat]\"\")"}})

wozu dienen die ganzen slashes. Unter welchen Bedingungen setzt man die.

Vielleicht ist das ja mein Fehler. Ich verkettet ja "=" mit der Methode.

Hat jemand noch einen anderen Vorschlag?

Grüße

AW: Office Skript: Range aus Tabellenobjekt für Datenvalidierung

15.08.2025 23:54:51

Yal

Die Backslash sind sogenannte escape characters, die zwingen den " als solche genommen zu werden, und nicht als syntaxische Element.

Warum die dann trotzdem doppelt sind, ist mir rätzelhaft.

VG
Yal

AW: Office Skript: Range aus Tabellenobjekt für Datenvalidierung

16.08.2025 11:26:58

Luschi

Hallo Yal,

ja, Office Skript sind noch sehr unterrepräsentiert in dem Forum.

solange M$ engagierte Excel-Nutzer mit seiner falschen Konto-Politik von den neuen Excel-Technologien (Phyton, Office-Script) auschließt, kann das so auch nix werden,
Userbild

Gruß von Luschi
aus klein-Paris

Oha

16.08.2025 17:33:04

Yal

Hallo Luschi,

es war mir nicht bekannt/bewusst.
Ich bin in der Tat -noch- mit einem Firmen-Laptop unterwegs und wusste nicht, dass die Skripten nicht überall verfügbar sind.

Für Python-in-Excel hatte ich aber schon gesehen, dass es -trotz Firmenlizenz- in einer Zusatzlizenz inkl. Gebühren geht. Für mich ok. Ich mag Python und Excel, es muss daher nicht Python-in-Excel werden...

VG
Yal

Hmmmh!

17.08.2025 10:16:23

RPP63

Moin!
Meine 365er-Insider-Version:
Microsoft® Excel® für Microsoft 365 MSO (Version 2507 Build 16.0.19029.20136) 64 Bit
Dort gibt es ein Register "Automatisieren".
Mal ein Beispiel-Script:

/*

 * This script counts the empty rows on a given worksheet, and can return them to a Power Automate Flow.

 * This script does not affect any pre-existing data in the workbook.

 */

function main(

  workbook: ExcelScript.Workbook,

  sheetName: string = "Sheet1"

): number {

  // Get the worksheet named "Sheet1".

  const sheet = workbook.getWorksheet(sheetName);



  // Get the entire data range.

  const range = sheet.getUsedRange(true);



  // If the used range is empty, end the script.

  if (!range) {

    console.log(`No data on this sheet.`);

    return;

  }



  // Log the address of the used range.

  console.log(`Used range for the worksheet: ${range.getAddress()}`);



  // Look through the values in the range for blank rows.

  const values = range.getValues();

  let emptyRows = 0;

  for (let row of values) {

    let emptyRow = true;



    // Look at every cell in the row for one with a value.

    for (let cell of row) {

      if (cell.toString().length > 0) {

        emptyRow = false;

      }

    }



    // If no cell had a value, the row is empty.

    if (emptyRow) {

      emptyRows++;

    }

  }



  // Log the number of empty rows.

  console.log(`Total empty rows: ${emptyRows}`);



  // Return the number of empty rows for use in a Power Automate flow.

  return emptyRows;

}

Skript

17.08.2025 11:20:27

Eifeljoi 5

Hallo

Mit Skripts zu arbeiten ist eine sehr schöne Sache, was ich seit einem Jahr des öfter auch sehr gerne nutze, bringt den Vorteil man braucht kaum xlsm Format.
Doch leider kann man es nicht wie gewünscht mit allen Dingen nutzen wo man früher mit VBA rum gehandelt hat.
Zurzeit ackere ich dran eine PQ-Aktualisierung über Skript hin zubekommen, was nach meinen bisherigen nachlesen wohl angeblich nicht wie gewünscht funktionieren sollte, leider.

AW: Skript

17.08.2025 12:44:03

Martin

Hallo

Ich habe leider auch nur die Möglichkeit in der Arbeit deswegen kann ich meine erhaltene Antwort erst am Montag Test.

Also was die Möglichkeiten in Excel Scripte angeht. Sie sind leider noch vollkommen unterentwickelt. Es fehlen viele Methoden und Eigenschaften um die Excel Objekte zu beeinflussen. Teilweise sind es wirklich einfache Dinge bei die in VBA ganz selbstverständlich sind.

Bsp. Bei den Pivot Tabellen fehlt viel, es ist nicht möglich einer Pivot-Tabelle eine formatvorlage zuzuweisen. Das benutzerdefinierte Gruppieren von Pivot-Tabelle könnte ich bislang auch nicht enddecken.

Grüße

Das geht im Moment...

17.08.2025 13:24:10

Case

Moin, :-)

... wohl nur mit Power Automate: ;-)
https://learn.microsoft.com/de-de/office/dev/scripts/develop/power-automate-integration?tabs=run-script

Hier auch die Bedingungen: ;-)
https://learn.microsoft.com/de-de/office/dev/scripts/testing/platform-limits?tabs=business#platform-support
https://learn.microsoft.com/de-de/office/dev/scripts/testing/platform-limits?tabs=home#platform-support

Es geht wohl nicht mit einem privaten Office/OneDrive.

Das Menü "Automatisieren" sieht bei mir so aus (kein automatisches Ausführen):
Userbild

Servus
Case

AW: Office Skript: Range aus Tabellenobjekt für Datenvalidierung

15.08.2025 23:29:45

Yal

Dann direkt aber mit dem $ und die einfache Hochkomma:

dataValidation.setRule({list: {inCellDropDown: true, source: '=${tlb_Buchungsart.getColumn("Auswahl_Buchungsart").getRangeBetweenHeaderAndTotal().getAddress())}'}});

Variable man man weirder verwenden, wenn man sie mit let anstatt const instanziert.

VG
Yal

AW: Office Skript: Range aus Tabellenobjekt für Datenvalidierung

18.08.2025 10:15:15

Martin

Guten Morgen

Also ich hab jetzt alle Vorschläge ausprobiert:

Versuch mit Indirekt Formel: Fehler: DataValidation setRule: Das Argument ist ungültig, fehlt oder hat ein falsches Format

//Versuch mit Indirekt Formel:

	//dataValidation.setRule({ list: { inCellDropDown: true, source: "=INDIREKT(\"\"tlb_Buchungsart[Auswahl_Buchungsart]\"\")" } })

;

Versuch mit über eine Variable: Fehler: DataValidation setRule: Das Argument ist ungültig, fehlt oder hat ein falsches Format

//Versuch mit über eine Variable:

	//const listQuelle = tlb_Buchungsart.getColumn("Auswahl_Buchungsart").getRangeBetweenHeaderAndTotal().getAddress();

	//dataValidation.setRule({ list: { inCellDropDown: true, source: '=${listQuelle}' } });

Versuch ohne Variable direkt über die Methode (mein Eigentliches Ziel):Fehler: DataValidation setRule: Das Argument ist ungültig, fehlt oder hat ein falsches Format

//Versuch mit ohne Variable direkt über die Methode (mein  Eigentliches Ziel):

	//dataValidation.setRule({ list: { inCellDropDown: true, source: '=${tlb_Buchungsart.getColumn("Auswahl_Buchungsart").getRangeBetweenHeaderAndTotal().getAddress())}' } });

Am liebsten würde ich es direkt über die Methode in die Source der DatenValidierung übergeben, so wie im letzten versuch aber irgendwie stimmt meine Syntax scheinbar nicht. Hat jemand eine Idee woran es liegt?? Danke für die Hilfe.

Grüße Martin

AW: Office Skript: Range aus Tabellenobjekt für Datenvalidierung

18.08.2025 12:28:11

Yal

Hallo Martin,

unter
https://learn.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.datavalidationrule?view=office-scripts#excelscript-excelscript-datavalidationrule-list-member
wird ein Beispiel gezeigt, wie eine Variable vom Typ ExcelScript.ListDataValidation (wiederverwendbar, weil mit "let" instanziert) befüllt wird, dann eine Vairable vom Typ ExcelScript.DatavalidationRule, bevor diese den SetRule übergeben wird.

Ich denke die Typisierung ist notwendig. Office Skript basiert auf TypeScript, die Microsoft Version von JavaScript. Die besondere Unterschied ist eben das strenge Nutzen von Typen.

VG
Yal

AW: Office Skript: Range aus Tabellenobjekt für Datenvalidierung

19.08.2025 09:03:27

Martin

Hallo Moin

Danke Yal. Das bsp. Script aus dem link funktioniert , ich werde es nur noch für meinen bedarf anpassen und eventuell eine Funktion draus machen.
Sollte glaub ich keinen sehr Großen Performens unterschieb verursachen. Weist du zufällig ob es bei der Daten Validierung eine Obergrenze an begriffen/Zeichen gibt die man direkt in die Validierung eintragen kann?

An die Möglichkeit meine Begriffe aus der Quelltabelle auszulesen und dann alle direkt als ein langer ";" getrennter String in die source der Validierung zu schreiben hab ich noch gar nicht gedacht, muss ich gestehen.

Bsp aus dem Link von Yal

/**

 * This script creates a dropdown selection list for a cell.

 * It uses the existing values of the selected range as the choices for the list.

 */

function main(workbook: ExcelScript.Workbook) {

    // Get the values for data validation.

    const selectedRange = workbook.getSelectedRange();

    const rangeValues = selectedRange.getValues();



    // Convert the values into a comma-delimited string.

    let dataValidationListString = "";

    rangeValues.forEach((rangeValueRow) => {

        rangeValueRow.forEach((value) => {

            dataValidationListString += value + ",";

        });

    });



    // Clear the old range.

    selectedRange.clear(ExcelScript.ClearApplyTo.contents);



    // Apply the data validation to the first cell in the selected range.

    const targetCell = selectedRange.getCell(0, 0);

    const dataValidation = targetCell.getDataValidation();



    // Set the content of the dropdown list.

    let validationCriteria: ExcelScript.ListDataValidation = {

        inCellDropDown: true,

        source: dataValidationListString

    };

    let validationRule: ExcelScript.DataValidationRule = {

        list: validationCriteria

    };

    dataValidation.setRule(validationRule);

}

Grüße Martin

Das funktioniert...

18.08.2025 14:56:33

Case

Moin Martin, :-)

... bei mir: ;-)
https://www.herber.de/bbs/user/178629.xlsx

In G4:G7 wird die Gültigkeitsliste erstellt. ;-)
Das geht auch ohne Indirekt. Hätte aber den Nachteil, dass du das Skript immer dann ausführen musst, wenn sich die Tabelle ändert (mehr/weniger Zeilen). ;-)
Das Skript ist in einem Textfeld - ist aber getestet und funktioniert bei mir. ;-)

Servus
Case

AW: Das funktioniert...

19.08.2025 10:07:35

Martin

Hallo

Als ich bin auf folgende Fehler meinerseits Draufgekommen. Case hat recht, die =INDIREKT() funktioniert. Aber nur die englische Version davon also =INDIRECT(). Ich hatte die deutsche verwendet. Muss dem nach so ausschauen:
//source über Indirect Formel:

dataValidation.setRule({ list: { inCellDropDown: true, source: `=INDIRECT("tlb_Buchungsart[Auswahl_Buchungsart]")`}});

Die Variante über ne Variable Funktioniert auch ebenfalls das direckte übergeben aus der Methode herau. Es muss aber ein rechtes Hochkomma verwendet werden. Und zwar das neben der Delete taste, nicht das über der Raute (hat bei mir zum Fehler geführt).

//Erzeugt einen String mit der Rangadresse in A1 Schreibweise "nicht Absolut" 

`=${tlb_Buchungsart.getColumn("Auswahl_Buchungsart").getRangeBetweenHeaderAndTotal().getAddress()}` } });

//Instanziierung einer Variable an die einen String mit der Rangadresse in A1 Schreibweise "nicht Absolut" übergeben wird.

const listQuelle = tlb_Buchungsart.getColumn("Auswahl_Buchungsart").getRangeBetweenHeaderAndTotal().getAddress();



// Weiterverwendung der Variable in der Source der Daten Validierung, Achtung Zelladressen in der Variable sind nicht ABSOLUT 

//daher kommt es nicht zum gewünschten Ergebnis

dataValidation.setRule({ list: { inCellDropDown: true, source: `=${listQuelle}` } });

Die Validierung benötigt einen absoluten Zelladresse, sonst verrutscht der Bereich wenn sie auf mehrere Zeilen angewendet wird.
In meinem fall sollte in der Validierung =Produkte!K2:K8 stehen, das tut es auch. Aber eben nur in der ersten Zeile. In der Bspw. 14 Zeile steht dann schon =Produkte!K12:K18 und das ist außerhalb der Quelltabelle.

Leider gibt offenbar in Excel Script, für den Moment, keine Möglichkeit mit getAdress() die Eigenschaft als absoluten Wert auszugeben. Ich hab es zumindest nicht finden können und es würde mich auch nicht wundern da mir ähnliches beim Arbeiten mit dem Pivot Objekt von Excel Script auch schon aufgefallen ist. Es wurde viele wichtige Methoden und Eigenschaften von Microsoft einfach vergessen. Office Scripts ist meiner Meinung nach noch viel zu unausgereift. (das ist natürlich nur die Meinung eines Leihen)

Danke an Alle die mir bei meinem Vorhaben bislang geholfen haben.
Grüße Martin

(M)man(n) kann das auch...

19.08.2025 10:54:24

Case

Moin Martin, :-)

... Absolut setzen, wenn du den Bereich ohne Indirekt setzten möchtest. Brauchst du ein Beispiel? ;-)

Servus
Case

AW: (M)man(n) kann das auch...

19.08.2025 17:33:25

Martin

Hallo Case

Also ein Bsp wäre wirklich sehr Toll. Ich hab in Office Skript gesucht und gesucht und keine Möglichkeit gefunden den wert absolut zu setzen. ich setz mal eine Testdatei rein.
der Code in der Datei ist mal der jetzige stand. was die daten Validierung angeht.
https://www.herber.de/bbs/user/178631.xlsx

Jetzt bin ich wirklich gespannt, weil die getAdress() Methode gibt keine absoluten werte aus. Ich hab daher angefangen eine Funktion zu schreiben die $ in den String setzt, häng nur im Moment damit.

Danke und Grüße
Martin

Im Anhang sind...

19.08.2025 23:08:43

Case

Moin Martin, :-)

... zwei Beispiele (natürlich getestet). Einmal ohne Funktion und einmal mit Funktion - also zum wiederholten Aufrufen. ;-)
https://www.herber.de/bbs/user/178632.xlsx

Es gibt leider noch kein "range.getAddress(true, true)". ;-)

Aber Office Skript wird ja immer weiterentwickelt. ;-)

Servus
Case

AW: Im Anhang sind...

20.08.2025 11:29:41

Martin

Hallo Case

Vielen Dank, funktioniert bestens. Vor allem die Funktion werde ich für verschiedens gut gebrauchen können.

Grüße Martin

Office Skript: Range aus Tabellenobjekt für Datenvalidierung

Forumthread: Office Skript: Range aus Tabellenobjekt für Datenvalidierung

21
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Office Skript: Range aus Tabellenobjekt für Datenvalidierung

Forumthread: Office Skript: Range aus Tabellenobjekt für Datenvalidierung

21 Beiträge zum Forumthread Beiträge zu diesem Forumthread

21
Beiträge zum Forumthread

Beiträge zu diesem Forumthread