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
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Ă©