Namen aller Tabellen ausgeben (Google Tabellen Script) Snippet

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";

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);

var uebersichtstabelle=SpreadsheetApp.getActiveSpreadsheet().getSheetByName(uebersichtsTabellenName);

Die Tabellennamen kann ich über den nachfolgenden Code abrufen:


var tabellenBlaetter=SpreadsheetApp.getActiveSpreadsheet().getSheets();

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);
}

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+"\")";

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("");

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);
    }
}

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

21 Kommentare

Alfred Bublik sagt:

13. Februar 2018 um 21:45 Uhr

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

Antworten
- Chris sagt:
  
  14. Februar 2018 um 7:35 Uhr
  
  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
  
  Antworten
Alfred Bublik sagt:

14. Februar 2018 um 9:36 Uhr

Mach ich, aber erst die nächsten Tage.
Rückmeldung kommt

Antworten
- Chris sagt:
  
  15. Februar 2018 um 15:31 Uhr
  
  Ja, mach das mal. Bin gespannt ob es klappt 😉
  
  Antworten
Alfred Bublik sagt:

15. Februar 2018 um 18:42 Uhr

klappt einwandfrei.
jetzt kann ich mein Projekt weitermachen…..
werd aber noch einige videos von dir studieren müssen.

danke

Antworten
- Chris sagt:
  
  15. Februar 2018 um 20:40 Uhr
  
  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 😉
  
  Antworten
Alfred Bublik sagt:

17. Februar 2018 um 13:32 Uhr

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

Antworten
- Chris sagt:
  
  17. Februar 2018 um 16:05 Uhr
  
  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
  
  Antworten
Alfred Bublik sagt:

17. Februar 2018 um 17:58 Uhr

leider nicht.
gleiche problem auch bei sverweis usw….
irgendwie übernimmt er die zeichenkette nicht bei einer funktion

Antworten
- Chris sagt:
  
  17. Februar 2018 um 18:08 Uhr
  
  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.
  
  Antworten
Alfred Bublik sagt:

17. Februar 2018 um 18:26 Uhr

ok….nochmals in ruhe testen

Antworten
- Chris sagt:
  
  17. Februar 2018 um 18:47 Uhr
  
  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 😉
  
  Antworten
Alfred Bublik sagt:

17. Februar 2018 um 22:09 Uhr

geht natürlich. hab auf die schnelle vorher einen falschen zellenbezug gehabt.

danke

Antworten
- Chris sagt:
  
  17. Februar 2018 um 22:29 Uhr
  
  Wunderbar 😉
  
  Antworten
Alfred Bublik sagt:

22. Februar 2018 um 3:55 Uhr

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

Antworten
- Chris sagt:
  
  22. Februar 2018 um 12:20 Uhr
  
  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/
  
  Antworten
Alfred Bublik sagt:

22. Februar 2018 um 17:21 Uhr

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)

}
}
}
}

Antworten
- Chris sagt:
  
  22. Februar 2018 um 23:44 Uhr
  
  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.
  
  Antworten
Alfred Bublik sagt:

22. Februar 2018 um 23:56 Uhr

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)

}
}
}
}

Antworten
- Chris sagt:
  
  23. Februar 2018 um 1:53 Uhr
  
  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 😉
  
  Antworten
René sagt:

22. November 2020 um 11:20 Uhr

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é

Antworten

Namen aller Tabellen ausgeben (Google Tabellen Script) Snippet