Namen aller Tabellen ausgeben
In diesem Tutorial und zugleich Snippet zeige ich wie Ihr mit der Google Tabellen Script Programmierung die Namen aller Tabellen ausgeben könnt. Ich habe das auch schon etwas erweitert, sodass man mit einem Klick auf einen Link direkt auf das entsprechende Tabellenblatt kommt.
Beschreibung:
In diesem Beispiel wird in dem Tabellenblatt "Tabellenübersicht" eine Liste erzeugt die aus den Namen aller Tabellenblättern besteht.
Wichtig: Das Tabellenblatt "Tabellenübersicht" muss natürlich existieren.
Um so ein Tabellenblatt zu erzeugen müsst Ihr entweder ein bestehendes Tabellenblatt umbenennen oder durch das Plus Symbol unten links in der Ecke ein neues Tabellenblatt erzeugen. Umbenennen müsst Ihr das dann natürlich trotzdem 😉
Ihr könnt aber im Script den Namen anpassen wenn Ihr das wollt.
Die Ausgabe beginnt in Zelle A1 und jeder weitere Eintrag wird dann eine Zeile tiefer hinzugefügt. Sollte außer der Übersichtstabelle eine weitere Tabelle vorhanden sein wird diese mit einem Link eingetragen.
Im oberen Abschnitt deklariere ich erst einmal alle Variablen mit "var" und greife auf das Tabellenblatt zu.
Der Name "Tabellenübersicht" kann hier natürlich angepasst werden.
var uebersichtsTabellenName="Tabellenübersicht";
Das Tabellenblatt mit dem Namen muss dann aber auch wirklich existieren.
Der Zugriff auf das Tabellenblatt wo die Seitennamen und Links eingefügt werden sollen erfolgt dann über:
var uebersichtstabelle=SpreadsheetApp.getActiveSpreadsheet().getSheetByName(uebersichtsTabellenName);
Die Tabellennamen kann ich über den nachfolgenden Code abrufen:
var tabellenBlaetter=SpreadsheetApp.getActiveSpreadsheet().getSheets();
Damit habe ich dann alle Tabellenblätter in "tabellenBlaetter" als Array gespeichert.
Die Namen aller Tabellen ausgeben
Anschließend wird das Array in einer Schleife abgearbeitet. Hier wird dann auch gleich überprüft ob die Übersichtstabelle dabei ist und entsprechend bei der Auflistung ausgeschlossen.
for(i=0; i< tabellenBlaetter.length; i++){
tabellenBlattName=tabellenBlaetter[i].getName();
tabellenBlattId=tabellenBlaetter[i].getSheetId();
tabellenBlattLink="=HYPERLINK(\"#gid="+tabellenBlattId+"\";\""+tabellenBlattName+"\")";
if(tabellenBlattName!=uebersichtsTabellenName)
uebersichtstabelle.getRange(counter++ ,1).setValue(tabellenBlattLink);
}
"length" gibt mir für das Array die Anzahl der Einträge zurück und damit kann ich die Schleife genauso oft durchlaufen.
Das [i] wird bei jedem Durchlauf um eins erhöht und greift dann immer auf den nächsten Datensatz des Arrays zu.
getName:
Holt mir den Tabellenname
getSheetId:
Gibt mir die ID der Tabelle damit ich über den Link später auch darauf zugreifen kann.
Um den Namen der Tabelle und den Link einzutragen habe ich folgenden Code verwendet:
tabellenBlattLink="=HYPERLINK(\"#gid="+tabellenBlattId+"\";\""+tabellenBlattName+"\")";
Hyperlink erzeugt dabei den Link in der Zelle. Diese Funktion steht übrigens im normalen Tabellenblatt auch ohne Programmierung zur Verfügung.
die Backslashes im Code werden benötigt um die Anführungszeichen speziell zu behandeln. Damit werden sie nicht im Code ausgeführt und geschützt und stehen dann erst bei der Ausgabe zur Verfügung (Zwar gerade etwas blöd erklärt aber so ungefähr müsst Ihr Euch das vorstellen).
Mit getRange greifen wir dann auf die Zelle zu und mit setValue tragen wir den Wert dann dort ein.
Vorher vorhandene Einträge löschen
Wenn Ihr Tabellenblätter löscht, dann sollen diese natürlich auch aus der Liste wieder verschwinden. Das habe ich mit folgender Zeile gelöst:
uebersichtstabelle.getRange("A:A").setValue("");
Hier werden dann alle Einträge aus der Spalte A schon vorher entfernt bevor das Array durchlaufen wird und.
Funktion aufrufen
Aufgerufen wird die Funktion mit namenAllerTabellen. Dieses Script könnt Ihr über Buttons (Zeichnungen oder Bilder) mit diesem Namen zuweisen.
Natürlich könnt Ihr auch automatische Aktualisierungen benutzen. Es gibt dafür zum Beispiel auch die Funktionen onOpen und onEdit.
Für den Anfang
Vorab ein Video welches Euch zeigt wie man ein einfaches Script programmieren kann und wie Ihr den Scripteditor aufrufen könnt:
Mein erstes Script programmieren mit GOOGLE Tabellen
Achtung:
Benutzt zum Üben eine separate Tabelle für Testzwecke. Dadurch gehen Euch keine Daten verloren.
Snippet: Namen aller Tabellen ausgeben mit Google Tabellen Script Programmierung
function namenAllerTabellen(){
// Die Tabelle wo die Tabellennamen rein geschrieben werden sollen
var uebersichtsTabellenName="Tabellenübersicht";
var uebersichtstabelle=SpreadsheetApp.getActiveSpreadsheet().getSheetByName(uebersichtsTabellenName);
var tabellenBlaetter=SpreadsheetApp.getActiveSpreadsheet().getSheets();
var tabellenBlattName="";
var tabellenBlattLink="";
var tabellenBlattId="";
// Nachfolgende Zeile löscht Einträge der Spalte A
uebersichtstabelle.getRange("A:A").setValue("");
var counter=1;
for(i=0; i< tabellenBlaetter.length; i++){
tabellenBlattName=tabellenBlaetter[i].getName();
tabellenBlattId=tabellenBlaetter[i].getSheetId();
tabellenBlattLink="=HYPERLINK(\"#gid="+tabellenBlattId+"\";\""+tabellenBlattName+"\")";
if(tabellenBlattName!=uebersichtsTabellenName)
uebersichtstabelle.getRange((counter++) ,1).setValue(tabellenBlattLink);
}
}
Die Benutzung der Scripte erfolgt natürlich wie immer auf eigene Gefahr.
Euch gefällt das Script und Ihr wollt Euch gerne bedanken?
Hier ist meine AMAZON – Wunschliste [HIER KLICKEN] 😉
Dies ist Teil der Serie: "Google Tabellen Script Programmierung"
Dieses Tutorial ist Teil einer Serie die als eine Art Datenbank für kleinere Snippets dient. Damit habt Ihr die Möglichkeit für Eure Projekte einen Teil des Codes hier direkt zu kopieren. Ihr könnt das dann wie ein Modul anwenden.
Die Liste aller Snippets findet Ihr hier:
Google Tabellen Script Programmierung
Das könnte Dich auch interessieren
- Aktive Zelle auslesen mit einem Google Tabellen Script
- DO WHILE SCHLEIFE (Google Tabellen Script) Snippet
- Daten anderer Tabellen kopieren (Google Tabellen Script)
- Daten mit Script an den Listenanfang verschieben (Google Tabellen Script)
- Einfügen oder löschen einzelner Zellen bei Google Tabellen
- FOR SCHLEIFE (Google Tabellen Script) Snippet
- Google Tabellen Script autorisieren
- Hallo Welt in Google Tabellen Script
- Kalender mit Tabelle verbinden / verknüpfen (Google Tabellen Script)
- Tutorials über Google Tabellen Script - Programmierung
- Vergleichen der Werte von Zellen mit Google Tabellen Script
- WHILE SCHLEIFE (Google Tabellen Script) Snippet
- Wertebereich einer Zelle mit Google Tabellen Script überprüfen
- Zeitstempel programmieren in Google Tabellen
- Übersicht der SCHLEIFEN (Google Tabellen Script) Snippet
- Übersichts-Liste der GOOGLE Tabellen Script Tutorials
21 Comments
vielen dank, werde das die nächsten tage testen. im moment keine zeit.
werde dann auch noch versuchen nur tabellenblätter auszulesen die ein gewisses kriterium entsprechen.
im meinen fall beginnen die tabellenblätter immer mit "GK" .
mal schauen ob ich das hin bekomme.
hab ein kleines dankeschön hinterlassen.
lg Alfred
Hi Alfred,
über das Dankeschön habe ich mich natürlich sehr gefreut 😉
Danke Dir.
Für die GK Tabellenblätter könntest Du folgendes vor die letzte IF Abrage setzen. Eigentlich kannst Du die "eine" Zeile mit der IF Abfrage sogar dann entfernen. Die letzte Zeile brauchst Du dann aber trotzdem.
var re=("GK+");
var regExp=new RegExp(re);
if(!tabellenBlattName.match(regExp))
continue;
Probier das mal aus wenn Du Zeit hast.
Viele Grüße
Chris
Mach ich, aber erst die nächsten Tage.
Rückmeldung kommt
Ja, mach das mal. Bin gespannt ob es klappt 😉
klappt einwandfrei.
jetzt kann ich mein Projekt weitermachen…..
werd aber noch einige videos von dir studieren müssen.
danke
Super. Freut mich zu hören dass es klappt 😉
Das mit dem Lernen anhand von einem Projekt ist immer gut. Das Programmieren macht auch richtig Spaß wenn man die Zeit dazu hat. Ich würde auch am Liebsten noch sehr viel mehr damit machen aber Toptorials ist leider nicht der einzige Kanal von mir 😉
Interessant wäre nämlich auch das Thema HTMLService mit doGet. Da könnte man dann seine eigenen HTML Formulare mit Google Tabellen kombinieren. Aber das ist dann schon etwas mehr Arbeit 😉
hallo chris, komme gut voran, aber bekomme dieses problem nicht weg.
=SUMMEWENN(GK!H$8:H$42;A3;GK!I$8:I$42)
diese funktion funktioniert, nur soll der bezug auf das Tabellenblatt GK! nicht per hand in der formel stehen sondern über eine zelle da ja der tabellennamen ändern oder neu dazukommen kann.
darum auch das skript zum auslesen der tabellennamen
in zelle b1 steht genau "GK!". bekomme aber den text nicht in die formel
=SUMMEWENN(indirekt("b1")H$8:H$42;A2;indirekt("b1")I$8:I$42) geht nicht.
was mache ich falsch?
gruß alfred
Hi Alfred,
versuche mal folgende Formel:
=SUMMEWENN(INDIREKT(VERKETTEN(B1;"H$8:H$42"));A3;INDIREKT(VERKETTEN(B1;"I$8:I$42")))
Viele Grüße
Chris
leider nicht.
gleiche problem auch bei sverweis usw….
irgendwie übernimmt er die zeichenkette nicht bei einer funktion
Wundert mich aber. Ich habe diese Funktion gerade bei mir getestet und ich bekam von der Tabelle "GK" die korrekte Summe zurück.
=SUMMEWENN(INDIREKT(VERKETTEN(B1;"H$8:H$42"));A3;INDIREKT(VERKETTEN(B1;"I$8:I$42")))In "Tabellenblatt1" habe ich die Formel eingetragen und in Zelle A3 stand dort ein Name zum Filtern.
ok….nochmals in ruhe testen
Hier schau mal:
https://toptorials.com/tests-und-downloads-google-tabellen-spreadsheets/
Ich habe mal eine neue Seite erstellt wo ich ab und an einige Tests hochladen werde. Hier habe ich jetzt auch gleich mal meinen Test zu Deiner Fragestellung hochgeladen.
Ich muss aber auch gleich dazu sagen dass ich nicht immer die Zeit dazu finden werde. Ich habe momentan zu viele Projekte am Laufen. Also können Antworten auch mal etwas länger dauern 😉
geht natürlich. hab auf die schnelle vorher einen falschen zellenbezug gehabt.
danke
Wunderbar 😉
es geht voran…dank deiner videos schon so mach kleines problem gelös.
jetzt aber habe ich eine frage.
ich benutze um den wert einer zelle um 1 zu erhöhen ein skript das auf einer grafik hinterlegt ist(kleines plus zeichen)
function gg1m(){
var ss=SpreadsheetApp.getActiveSheet();
var a=ss.getRange("p8").getValue();
a=a+1;
ss.getRange("p8").setValue(a)
}
klappt auch ganz gut…aber ich brauche das ganze 70 mal und auch 70 mal mit wert -1.
meine frage ist: geht das auch anders?
da das bild ja an einer x beliebigen stelle sein kann wird es nicht prüfen welche zelle im hintergrund ist.
gibt es eine andere möglichkeit skripte auszuführen ?
gruß Alfred
sollte es keine möglichkeit geben kann ich diese skripte relativ schnell mittels copy und paste erstellen, frage ist nur wie sich das auf die geschwindigkeit auswirkt
Du könntest es eventuell noch etwas anders machen. Du hast eine Dropdownliste und zwei Buttons oder zwei Dropdownlisten und einen Button.
In der einen Dropdownliste hast Du dann Zahlen von 1-70 in der anderen 1 und -1 (Falls Du nur einen Button benutzt). Die ausgelesene Zahl aus der ersten Dropdownliste benutzt Du um die Zelle entsprechend mit setValue neu zu berechnen und zwar mit dem Wert aus der zweiten Dropdownliste. Oder Du benutzt zwei funktionen. Eine die 1 einträgt und die andere die -1 einträgt. Dann brauchst Du nur eine Dropdownliste aber zwei Buttons.
Ansonsten eventuell noch eine Zelle auswählen. Die aktive Zelle im Script auslesen und dann kann ebenfalls mit einem Button genau die Zelle ausgelesen und erhöht werden.
Geschwindigkeit ist bei riesigen Datenmengen schon irgendwann ein Problem. Ich selbst würde bei großen Datenmengen vermutlich PHP und MySQL Datenbanken bevorzugen.
Ausführen der Scripte kann einmal über einen Button statt finden aber auch über sogenannte zeitlich gesteuerte Trigger:
https://toptorials.com/funktion-zeitlich-gesteuert-ausfuehren-google-tabellen-script/
Sowie über Events (onOpen, onEdit zum Beispiel):
https://toptorials.com/daten-sortieren-beim-oeffnen-google-tabellen-spreadsheets-script/
dropdown mache ich nicht. denke das dauert länger wie eingabe.
aber wie durch ein wunder hab ich was zusammenbasteln können was sogar funktioniert.
jetzt muss ich nur den namen anklicken und danach ein Plus oder – bild wo die skripte hinterlegt sind.
danke für die vorschläge
function plus() {
var qq=SpreadsheetApp.getActiveSheet();
var zeile=SpreadsheetApp.getActiveRange().getRow();
var spalte=SpreadsheetApp.getActiveRange().getColumn();
if (zeile>7) {
if (zeile7) {
if (zeile<43) {
if (spalte==5) {
var ww=qq.getRange(zeile, spalte+11).getValue()
ww=ww-1
qq.getRange(zeile, spalte+11).setValue(ww)
}
}
}
}
Ja, da gebe ich Dir recht. Schneller geht es mit Anklicken der Zeile und dann auf den Button.
Ist aber cool wie gut es voran geht. Ich kenne das ja bereits … man muss aufpassen dass man nicht süchtig wird.
Man bekommt ja immer wieder kleine Erfolgserlebnisse und die treiben einen richtig an.
Wenn ich mehr Zeit hätte würde ich auch gerne wieder viel mehr programmieren. Vor allem brennt mir unter den Fingern das Thema HTMLService wo man eigene Formulare erstellen kann die dann mit der Tabelle verbunden sind. Genauso mit externen Daten wie fetchURL und so weiter. Aber die Zeit ist einfach zu knapp 😉
Ich habe mal kurz das Script angeschaut. Du kannst ein paar Sachen verkürzen damit Du nicht so viele If Abfragen hast und so viele Klammern 😉
function plus() {
var qq=SpreadsheetApp.getActiveSheet();
var zeile=SpreadsheetApp.getActiveRange().getRow();
var spalte=SpreadsheetApp.getActiveRange().getColumn();
if (zeile>7 && zeile<43 && spalte==5) {
var ww=qq.getRange(zeile, spalte+11).getValue()
ww--;
qq.getRange(zeile, spalte+11).setValue(ww)
}}
Wobei ich bei if(zeile7) verwundert war. Ich denke das war ein Tippfehler oder es fehlt das Gleichheitszeichen. Wenn da ein Gleichheitszeichen hin muss kannst Du auch:
if (zeile>=7 && zeile<43 && spalte==5)
benutzen.
der hat mein artikel abgekürzt da er zu lange war. hat die letzten teilen nach oben geschoben. somit hat sich das minus skript über das plus skript gelegt.
darum hast du jetzt auch ww– geschrieben.
beim plus skript muss ich ww++ einfügen. etwas unglücklich gelaufen.
das war das ursprüngliche plus skript
function plus() {
var qq=SpreadsheetApp.getActiveSheet();
var zeile=SpreadsheetApp.getActiveRange().getRow();
var spalte=SpreadsheetApp.getActiveRange().getColumn();
if (zeile>7) {
if (zeile<43) {
if (spalte==5) {
var ww=qq.getRange(zeile, spalte+11).getValue()
ww=ww+1
qq.getRange(zeile, spalte+11).setValue(ww)
}
}
}
}
Wundert mich das der so einfach einen Teil kürzt. Aber das ist hier auch nur eine reine WORDPRESS Seite. Ich habe noch gar nicht ausprobiert was so alles möglich ist. Aber tausend Zeichen hätten eigentlich drin sein müssen (dachte ich zumindest). Aber so in zwei Teilen ging es ja auch 😉
Hallo Chris,
Dein Script funktioniert wunderbar. Dennoch habe ich ein kleines Problem. Ich benötige die Ausgabe in der Übersicht nicht in Zelle A1, sondern eine Zeile Darunter in A2. Kann man und wenn ja, wie, das lösen?
Auch wenn der Beitrag etwas älter ist, ich hoffe, du liest meinen Kommentar und kannst mir helfen.
Vielen Dank und viele Grüße, René