Daten anderer Tabellen kopieren (Google Tabellen Script)

Daten anderer Tabellen kopieren (Google Tabellen Script)

In diesem Tutorial zeige ich Euch wie Ihr mit der Google Tabellen Script-Programmierung Daten von mehreren Tabellen kopieren könnt. Über einen Button könnt Ihr das dann entsprechend immer gleich wieder aktualisieren. Falls Ihr andere Namen für die einzelnen Tabellenblätter benutzen solltet könnt Ihr das natürlich entsprechend anpassen. Statt Tabellenblatt1 gebt Ihr einfach den Namen ein der Eurem Tabellenblatt entspricht.



Im Bereich wo getLastRow()+2 steht werden zwischen den Daten Leerzeilen eingefügt. Wollt Ihr dazwischen keine Leerzeilen haben reicht es aus wenn Ihr nur +1 eingebt.
Falls Ihr die Daten dann auch später noch sortieren wollt könnt Ihr die letzte Zeile sheet1.sort(1) auskommentieren. Dafür müsst Ihr nur die zwei // entfernen.

Hier das Script zum Kopieren von Daten aus anderen Tabellen mit Google Tabellen Script:

Die Benutzung des Scripts geschieht wie immer auf eigene Gefahr 😉

Euch gefällt die Datei und Ihr wollt Euch unbedingt bedanken?
Hier ist meine AMAZON – Wunschliste [HIER KLICKEN] 😉

 

Das entsprechende Video-Tutorial mit der Anleitung findet Ihr hier:

 



Das könnte Dich auch interessieren

21 Kommentare

  1. Meik sagt:

    Hey 🙂

    Schöne Tutorials und gut erklärt.
    Danke dafür!

    Hat sehr geholfen 🙂

    • Meik sagt:

      Ach eine Frage noch.
      Kann man vielleicht auch sagen, dass er nur den nackten Text kopieren und einfügen soll und keine Formeln?
      Das bereitet mir aktuell noch Probleme und ich finde in den Script-Hilfen noch nichts passendes 🙁
      Cih habe iene Formel, die mitkopiert wird, diese bezieht sich dann aber nicht mehr auf das original Tabellenblatt und schmeisst daher einen Fehler raus. am einfachsten wäre natürlich, wenn er direkt nur als Text/Zahl kopiert und einfügt.
      Hat das schon mal jemand gemacht?

    • Chris sagt:

      Gern geschehen und ich danke Dir Meik für das positive Feedback 😉

  2. Julia sagt:

    Hey Chris,

    dein Script war schonmal sehr hilfreich (zumal ich leider noch nicht scripten kann) aber ein Anliegen habe ich.
    Ich habe im Tabellenblatt1 sehr viele Zeilen mit verschiedenen Status und Bearbeitern. Jetzt müsste das Script erkennen ob der Bearbeiter bspw. Max UND Status (per Drop-Down auswählbar) "in Bearbeitung", "DD-Versand" ODER "zur Abrechnung" ist und es mir dann in das Tabellenblatt "Max" kopieren.

    Wäre super wenn du mir da helfen könntest!

    • Chris sagt:

      Hi Julia,
      kommt natürlich darauf an wie Du das ganze aufbauen willst. Es gibt sicher mehrere Möglichkeiten das umzusetzen. Man könnte von der Startseite eine Auswahlliste (Drop-Down) der Mitarbeiter nehmen und daneben einen Button wo man dann rauf klickt und dann das Script ausgeführt wird. Oder man hat den Button in der jeweiligen Seite des Mitarbeiters und hier wird nur der Tabellenname ausgelesen und ebenfalls das Script über den Tabellennamen ausgeführt. Hier sparst Du Dir sozusagen die Auswahl über das Auswahlfeld weil der Name ja schon im Tabellennamen enthalten ist.
      Anschließend wird die Übersichtsliste (wo für jeden Mitarbeiter der Status über das Drop-Down-Feld vorhanden ist) eingelesen. Hier beispielsweise 2 Spalten (NAME, STATUS) beim Durchlaufen des Arrays was Du ausgelesen hast kannst Du dann mit einer IF Abfrage die Bedingungen festlegen wann kopiert werden soll (NAME des Mitarbeiters, „in Bearbeitung“, „DD-Versand“ ODER „zur Abrechnung“). Innerhalb des IF Bereichs wird dann das ganze kopiert und vorher eventuell der Bereich gelöscht oder nur hinten angehangen mit einem Zeitstempel.
      Ein Tutorial welches vieles von dem zeigt was Du benötigst steckt hier:
      https://toptorials.com/zellen-vergleichen-markieren-array-google-tabellen-script/

      • Julia sagt:

        Hi Chris,

        nachdem ich lange rumprobiert habe, komme ich auf folgendes:

        function RZscript(){

        var sheet2=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Tim");
        sheet2.clear(); // Tabellenblatt Tim leeren
        var sheet1=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Projektstatus 2");

        var startzeile=2; //Zeile ab der gesucht werden soll
        var endzeile=sheet1.getLastRow(); //Spalte in der die Suche beendet wird

        // Name nachdem gesucht werden soll

        var Name="Tim";

        // Spaltenindex festlegen in dem nach dem Namen gesucht werden soll

        var suchSpaltenIndexN=5;

        //Projekte holen in denen der Name übereinstimmt

        var projekteN=sheet1.getRange(startzeile, suchSpaltenIndexN , endzeile, 1).getValues();

        // Status nachdenen gesucht werden soll

        var Status=sheet1.getRange("O2:O4");

        // Spaltenindex festlegen in dem nach dem Status gesucht werden soll
        var suchSpaltenIndexS="7";

        //Projekte holen in denen der Status übereinstimmt
        var projekteS=sheet1.getRange(startzeile, suchSpaltenIndexS , endzeile , 1).getValues();

        // Ziel festlegen
        var Zielzeile=sheet2.getRange(1,1);

        if(projekteN==Name); {
        if(projekteS==Status); {
        sheet1.getRange(startzeile,endzeile).copyTo(Zielzeile);
        }
        }

        }

        ——————-
        Es ist komplett debuggt, dennoch tut sich nichts beim ausführen.
        Die Zeile mit dem copyTo bereitet mir Kopfzerbrechen, da ich nicht weiß wie ich angeben soll dass ich NUR die Zeilen möchte in denen beide Bedingungen zutreffen.

        Danke für den Hinweis auf dein anderes Tutorial!

        • Chris sagt:

          Ich glaube der Fehler liegt beim Status.
          var Status=sheet1.getRange(„O2:O4″);
          und
          if(projekteS==Status); {
          Ich würde erst einmal alles weg Kommentieren was mit dem Status zu tun hat um zu sehen ob das bis dahin funktioniert. Bei Projekte und Status vergleichst Du anscheinend zwei Arrays. Hier musst Du vermutlich dann noch eine verschachtelte Schleife einbauen um die beiden Arrays mit einander zu vergleichen. Ich habe gerade nochmal geschaut aber eine sogenannte InArray Funktion habe ich bei Google Script jetzt nicht gesehen. Also bleiben Dir nur die Schleifen.
          Eine Möglichkeit dafür sieht dann in etwa so aus:
          https://stackoverflow.com/questions/41379965/google-apps-script-find-a-value-in-an-array
          Der Rest sieht, für mich ok aus auch wenn Du diese If Abfrage kombinieren könntest. Aber es geht ja auch so und ist manchmal übersichtlicher 😉

  3. Alfred Bublik sagt:

    Hi Chris.

    kann man den Befehl getlastrow eingrenzen, so das er z.b.erst ab spalte 10 die letzte reihe sucht.

    lg

    • Chris sagt:

      Das könntest Du über eine IF Abfrage machen. Das sieht dann in etwa so aus:

      function getLastRowExtended(){
      var ss=SpreadsheetApp.getActiveSpreadsheet();
      var lastRow=ss.getLastRow();
      var lastRowExtended;
      var firstRowToSearch=10; // Erste Zeile ab der gestartet werden soll.

      lastRowExtended=lastRow;
      if(lastRow< =firstRowToSearch) lastRowExtended=firstRowToSearch; ss.getRange("F1").setValue(lastRowExtended); }

      • Alfred Bublik sagt:

        das konnte ich nie umsetzen und einbauen. hab mir schon überlegt das problem anders zu lösen. problen ist das bei spalte 9 400 einträge sind und bei spalte 12 nur 15. es soll aber bei spalte 12 fortlaufend daten angehängt werden. da würde ja evtl eine schleife auch irgendwie gehen die so lange durchlaufen wird bis das erste frei feld in spalte 12 gefunden wird. mit last row komm ich immer ganz nach unten von spalte 9.

        function spcopy(){

        var qq=SpreadsheetApp.getActiveSheet();
        var zeile=SpreadsheetApp.getActiveRange().getRow();
        var spalte=SpreadsheetApp.getActiveRange().getColumn();
        var alledaten=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("datenblatt");
        if (zeile>7 && zeile<43 && spalte==32){
        var ww=qq.getActiveCell().getValue();
        qq.getActiveRange().copyTo(alledaten.getRange(alledaten.getLastRow()+1,9), {contentsOnly:true});
        }
        }

        damit kopier ich in spalte 9.

        ansonsten läuft alles stabil.

        gruß

        • Chris sagt:

          Wenn es in der Spalte keine Lücken gibt und die Einträge sauber untereinander stehen könntest Du folgendes machen.
          Schau Dir mal dieses Beispiel-Script an:

          function myFunction() {
          var qq=SpreadsheetApp.getActiveSheet();
          var AlleZellenDerSpalte = qq.getRange("L1:L").getValues();
          var ErsteFreieZelleDerSpalte = (AlleZellenDerSpalte.filter(String).length+1);
          qq.getRange("L"+ ErsteFreieZelleDerSpalte).setValue("Mein Eintrag in Zeile" + ErsteFreieZelleDerSpalte);
          }

          Ansonsten musst Du Dir wirklich etwas mit einer Schleife einfallen lassen. Das kann aber je nach Menge der Einträge länger dauern. Am Besten den ganzen Bereich auslesen und dann das Array nach Text durchsuchen lassen.
          Viele Grüße
          Chris

          • Alfred Bublik sagt:

            perfekt. habs hinbekommen

            function Gildencopy(){

            var qq=SpreadsheetApp.getActiveSheet();

            var zeile=SpreadsheetApp.getActiveRange().getRow();
            var spalte=SpreadsheetApp.getActiveRange().getColumn();
            var alledaten = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("datenblatt");
            if (zeile>8 && zeile<37 && spalte==2){
            var ww=qq.getActiveCell().getValue();
            var AlleZellenDerSpalte = alledaten.getRange("L1:L").getValues();
            var ErsteFreieZelleDerSpalte = (AlleZellenDerSpalte.filter(String).length+1);
            alledaten.getRange(ErsteFreieZelleDerSpalte, 12).setValue(ww)

            werde das öfters brauchen und auf den befehl lastrow verzichten

            vielen dank

            wenn mal lust hast schick ich dir ein link zu mein programm. besteht ja zu 80% aus deinen codes lol

          • Chris sagt:

            Super 😉 Du musst aber darauf achten dass es nur funktioniert wenn die Werte hintereinander eingetragen werden. Wenn Lücken dazwischen sind (also wenn eine oder mehrere Zeilen dazwischen leer sein sollten), können Daten überschrieben werden.
            Den Link kannst Du mir schicken. Ich werde mir Dein Werk dann gerne mal anschauen 😉

  4. Michael sagt:

    Hallo zusammen, ich habe dazu auch mal eine frage

    in Tabellenblatt 1 werden laufend Werte eingetragen, gelöscht und überschrieben etc.
    Ich hätte daher gerne zu jedem Eintrag der gemacht wird, in Tabellenblatt2, eine Kopie.

    Sodass ich in Tabelle 2 quasi eine Endlosliste erhalte, von allen Einträgen die in Tabelle 1 gemacht wurden.

    Ist das möglich und wenn ja wie? bin was Script und/oder Macros angeht nicht wirklich fit 🙂

    Wäre nice wenn Du mir da einen Tipp geben kannst.

    VG
    Michael

  5. Patrick Böttcher-Exner sagt:

    Hallo Chris,

    Wäre es möglich dieses Script auch auf unterschiedliche Tabellen auszubreiten? Also Tabellenblatt1 von Tabelle a + Tabellenblatt1 von Tabelle b + Tabellenblatt1 von Tabelle c, etc… Ich müsste wahrscheinlich mit Importrange erst einmal auf die anderen Tabellen zugreifen, aber wo würde ich das einbinden ?

    LG und Danke

  6. Michael sagt:

    Hallo Chris und vielen Dank für die tollen Tutorials!
    Ich hab hab aber derzeit eine für mich absolut unlösbare Aufgabe…

    Ich habe für eine Sportveranstaltung eine mittlerweile sehr gute Tabelle angelegt für die Erfassung der Personen und der gebuchten Workshop´s
    Jetzt hätte ich aber gerne eine Art Formular, die mir für eine Person die entsprechenden Daten anzeigt was alles gebucht wurde.
    Soweit ja auch noch kein Problem…

    Allerdings möchte ich jetzt in diesem Tabellenblatt Daten ändern können, die dann in die RAW-Daten zurückgeschrieben werden um wieder die Berechnungen wie Preise etc. anzustoßen.

    Für Excel habe ich was mit VBA gefunden, aber gibt es da auch eine Möglichkeit in Sheets um wechselseitige Aktualisierungen zu generieren?
    Wenn du weitere Infos brauchst, lass ich dir diese sehr gerne zukommen. Über deine Hilfe wäre ich sehr dankbar!

    • Chris sagt:

      Hallo Michael,
      ich könnte mir vorstellen das es mit der Google Script Programmierung machbar ist. Wenn ich so etwas programmieren müsste, würde ich ID-Nummern verwenden und eventuell eindeutige Bezeichner. Das ist natürlich immer abhängig davon was Du alles machen willst. Auf jeden Fall muss man sich aber vorher schon einmal Gedanken machen was so in Zukunft vielleicht noch dazu kommen könnte. Änderungen sind später wesentlich schwieriger durchzuführen. Wenn das ganze extrem groß werden könnte, würde ich sogar eher zu einer PHP und MySQL Lösung tendieren. Ansonsten mit ID-Nummern arbeiten die man über ein Script dann immer einfacher zuordnen kann. Namen bringen nichts, da diese doppelt vorkommen könnten. Du kannst das über ein Script lösen oder in dem Formular einen Link zu der RAW-Datei oder der Zelle setzen um es dann dort direkt zu ändern.
      Bei der Variante mit der Programmierung würde ich vermutlich versuchen auf Nummer sicher zu gehen und nach einer Änderung die entsprechenden Zellen nochmal zurückkopieren oder vergleichen lassen um zu sehen ob die richtigen Daten jetzt dort vorhanden sind.
      Letztendlich hättest Du dann ein Formular mit allen Daten und wenn Du etwas geändert hast kannst Du auf einen Button klicken und alle Daten werden dann wieder zurück übertragen. Jede Zelle wird ausgelesen und durch die Position ist klar um welche Daten es sich handelt. Über die ID kannst Du dann die Zeile, Zelle oder das Tabellenblatt suchen (je nachdem was Du alles machen willst) und die Daten dann übertragen.
      Beispiele:
      https://toptorials.com/zelle-kopieren-google-tabellen-script/
      https://toptorials.com/zelle-anderes-dokument-kopieren-google-tabellen-script/

      https://toptorials.com/textinhalt-vergleichen-markieren-google-tabellen-spreadsheets-script/
      https://toptorials.com/link-zur-zelle-einer-anderen-tabelle-google-tabellen/

      Viele Grüße
      Chris

  7. Dirk Bitter sagt:

    Hallo Chris! (und alle die hier her gefolgt sind)

    Ich bin mein Problem jetzt anders angegangen. Da bei ist auch ein interessanter Script raus gekommen.
    Aber es ist noch nicht perfekt! Darum fange ich an der Stelle an wo ich noch Verbesserung sehe und zur Verdeutlichung für die Interessierten ((oder auch dich)) hinten dran das Vorhaben. Brauchst u.U. also nicht alles lesen 😉

    Also ich habe jetzt auf Blatt-2 und Blatt-3 jeweils in die Zelle(A1) ='Blatt-1'!A1 geschrieben und aufgezogen. (Auf Blatt-2 bis Z100 und Blatt-3 bis Z200 um auch neue Zeilen und Spalten von Blatt-1 zu bekommen).
    Jetzt habe ich schon mal die Inhalte da wo sie hin sollen.

    Dann kommt mein Script für die Übernahme des Formats von Blatt-1:

    function onEdit(){
    FormatÜbertragen();
    }

    function FormatÜbertragen(){

    var Blatt1=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Blatt-1");
    var Blatt2=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Blatt-2");
    var Blatt3=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Blatt-3");

    Blatt1.autoResizeColumns(1, 26);
    Blatt1.setColumnWidth(1, 36);

    Blatt1.getRange('A1:Z100').copyFormatToRange(Blatt2,1,26,1,100);
    Blatt2.autoResizeColumns(1, 26);
    Blatt2.setColumnWidth(1, 36);

    Blatt1.getRange('A101:Z300').copyFormatToRange(Blatt3,1,26,1,200);
    Blatt3.autoResizeColumns(1, 26);
    Blatt3.setColumnWidth(1, 36);

    }

    Nur>

    Warum kann ich hinter .copyFormatToRange nur Zahlen und so anders bezogen verwenden? Ich fände es wie in getRange übersichtlicher.
    (anders bezogen heisst ersteSpalte,letzteSpalte,ersteZeile,letzteZeile anstatt ersteSpalteersteZeile(A1):letzeSpalteletzteZeile(Z300)

    BlattX.autoResizeColumns(1, 26); würde ich lieber (A, Z) nennen!?
    BlattX.setColumnWidth(1, 36); würde ich lieber (A, 36) nennen!? und 36 was? Millimeter?

    PS: Habe ich aus TestMacros und deinen Toptorials zusammengesponnen. Auf Blatt-2 SpalteAZeile1(A1) bis SpalteZZeile100(Z100) soll demnach SpalteAZeile1(A1) bis SpalteZZeile100(Z100) aus Blatt-1 als Duplikat.
    > Und auf Blatt-3 soll in SpalteAZeile1(A1) bis SpalteZZeile100(Z100) dann der auf Blatt-1 weiter unten liegende Bereich SpalteAZeile101(A101) bis SpalteZZeile300(Z300)

    Es soll da bei auch die Formatierung (Rahmen,Hintergrund,Fett,usw.) komplett übernommen werden so dass Änderungen am Format auf Blatt-1 automatisch auf Blatt-2und3 entsprechend der Tabellenteile angepasst werden sollen.
    Die Inhalte >ohne Formatierung< übertrage ich einfach über "ist =" also z.B. mit der Formel auf Blatt-2 in Feld-A1: [=Blatt-1!A1] und auf Blatt-2 in Feld A1: [=Blatt-1!A101]
    Umgesetzt auf alle Felder verteilt und angepasst. (Automatisch durch Aufziehen)

    PS: Ein Script mit copyTo funktioniert NICHT da die Beziehungen da bei verloren gehen!

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.