Daten aus mehreren Tabellenblättern zusammenfassen

Programmierkenntnisse: Anfänger 
Dauer: 5 Minuten 
Projekttyp: Benutzerdefinierte Funktion

Ziele

  • Verstehen, was die Lösung bewirkt.
  • Informationen zur Funktionsweise der Apps Script-Dienste in der Lösung.
  • Richten Sie das Skript ein.
  • Führen Sie das Skript aus.

Informationen zu dieser Lösung

Wenn Sie in einer Tabelle ähnlich strukturierte Daten auf mehreren Blättern haben, zum Beispiel Messwerte des Kundensupports für einzelne Teammitglieder, können Sie mit dieser benutzerdefinierten Funktion eine Zusammenfassung für jedes Blatt erstellen. Diese Lösung wurde für Kundensupport-Tickets entwickelt, kann aber an Ihre Anforderungen angepasst werden.

Screenshot der Ausgabe der Funktion „getSheetsData“

Funktionsweise

Die benutzerdefinierte Funktion getSheetsData() fasst Daten aus jedem Tabellenblatt in der Tabelle basierend auf der Spalte Status eines Tabellenblatts zusammen. Das Skript ignoriert Tabellenblätter, die nicht in die Aggregation einbezogen werden sollen, z. B. die Tabellenblätter ReadMe und Summary.

Apps Script-Dienste

Für diese Lösung wird der folgende Dienst verwendet:

  • Tabellendienst: Ruft die zusammenzufassenden Tabellen ab und zählt die Anzahl der Elemente, die einer angegebenen Zeichenfolge entsprechen. Anschließend werden die berechneten Informationen einem Bereich hinzugefügt, der sich relativ dazu befindet, wo die benutzerdefinierte Funktion in der Tabelle aufgerufen wurde.

Vorbereitung

Für dieses Beispiel müssen die folgenden Voraussetzungen erfüllt sein:

  • Ein Google-Konto (für Google Workspace-Konten ist möglicherweise die Genehmigung durch den Administrator erforderlich).
  • Ein Webbrowser mit Internetzugriff.

Skript einrichten

Klicken Sie auf die Schaltfläche unten, um eine Kopie der Tabelle mit der benutzerdefinierten Funktion zum Zusammenfassen von Tabellendaten zu erstellen. Das Apps Script-Projekt für diese Lösung ist an die Tabelle angehängt.
Kopie erstellen

Skript ausführen

  1. Rufen Sie in der kopierten Tabelle das Tabellenblatt Zusammenfassung auf.
  2. Klicken Sie auf die Zelle A4. Die Funktion getSheetsData() befindet sich in dieser Zelle.
  3. Rufen Sie eines der Inhabertabellenblätter auf und aktualisieren oder fügen Sie Daten hinzu. Hier einige Aktionen, die Sie ausprobieren können:
    • Fügen Sie eine neue Zeile mit Beispielinformationen zum Ticket hinzu.
    • Ändern Sie in der Spalte Status den Status eines vorhandenen Tickets.
    • Ändern Sie die Position der Spalte Status. Verschieben Sie beispielsweise im Tabellenblatt Owner1 die Spalte Status von Spalte C in Spalte D.
  4. Rufen Sie das Tabellenblatt Zusammenfassung auf und sehen Sie sich die aktualisierte Zusammenfassungstabelle an, die getSheetsData() aus Zelle A4 erstellt hat. Möglicherweise müssen Sie das Kästchen in Zeile 10 aktivieren, um die im Cache gespeicherten Ergebnisse der benutzerdefinierten Funktion zu aktualisieren. Google speichert benutzerdefinierte Funktionen im Cache, um die Leistung zu optimieren.
    • Wenn Sie Zeilen hinzugefügt oder aktualisiert haben, werden die Ticket- und Statusanzahlen im Script aktualisiert.
    • Wenn Sie die Position der Spalte Status verschoben haben, funktioniert das Skript weiterhin wie vorgesehen mit dem neuen Spaltenindex.

Code ansehen

Wenn Sie den Apps Script-Code für diese Lösung ansehen möchten, klicken Sie unten auf Quellcode ansehen:

Quellcode ansehen

Code.gs

solutions/custom-functions/summarize-sheets-data/Code.js
// To learn how to use this script, refer to the documentation:
// https://developers.google.com/apps-script/samples/custom-functions/summarize-sheets-data

/*
Copyright 2022 Google LLC

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    https://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/

/**
 * Gets summary data from other sheets. The sheets you want to summarize must have columns with headers that match the names of the columns this function summarizes data from.
 * 
 * @return {string} Summary data from other sheets.
 * @customfunction
 */

// The following sheets are ignored. Add additional constants for other sheets that should be ignored.
const READ_ME_SHEET_NAME = "ReadMe";
const PM_SHEET_NAME = "Summary";

/**
 * Reads data ranges for each sheet. Filters and counts based on 'Status' columns. To improve performance, the script uses arrays 
 * until all summary data is gathered. Then the script writes the summary array starting at the cell of the custom function.
 */
function getSheetsData() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheets = ss.getSheets();
  let outputArr = [];

  // For each sheet, summarizes the data and pushes to a temporary array.
  for (let s in sheets) {
    // Gets sheet name.
    let sheetNm = sheets[s].getName();
    // Skips ReadMe and Summary sheets.
    if (sheetNm === READ_ME_SHEET_NAME || sheetNm === PM_SHEET_NAME) { continue; }
    // Gets sheets data.
    let values = sheets[s].getDataRange().getValues();
    // Gets the first row of the sheet which is the header row.
    let headerRowValues = values[0];
    // Finds the columns with the heading names 'Owner Name' and 'Status' and gets the index value of each.
    // Using 'indexOf()' to get the position of each column prevents the script from breaking if the columns change positions in a sheet.
    let columnOwner = headerRowValues.indexOf("Owner Name");
    let columnStatus = headerRowValues.indexOf("Status");
    // Removes header row.
    values.splice(0,1);
    // Gets the 'Owner Name' column value by retrieving the first data row in the array.
    let owner = values[0][columnOwner];
    // Counts the total number of tasks.
    let taskCnt = values.length;
    // Counts the number of tasks that have the 'Complete' status.
    // If the options you want to count in your spreadsheet differ, update the strings below to match the text of each option.
    // To add more options, copy the line below and update the string to the new text.
    let completeCnt = filterByPosition(values,'Complete', columnStatus).length;
    // Counts the number of tasks that have the 'In-Progress' status.
    let inProgressCnt = filterByPosition(values,'In-Progress', columnStatus).length;
    // Counts the number of tasks that have the 'Scheduled' status.
    let scheduledCnt = filterByPosition(values,'Scheduled', columnStatus).length;
    // Counts the number of tasks that have the 'Overdue' status.
    let overdueCnt = filterByPosition(values,'Overdue', columnStatus).length;
    // Builds the output array.
    outputArr.push([owner,taskCnt,completeCnt,inProgressCnt,scheduledCnt,overdueCnt,sheetNm]);
  }
  // Writes the output array.
  return outputArr;
}

/**
 * Below is a helper function that filters a 2-dimenstional array.
 */
function filterByPosition(array, find, position) {
  return array.filter(innerArray => innerArray[position] === find);
}

Änderungen

Sie können die benutzerdefinierte Funktion nach Bedarf bearbeiten. Unten sehen Sie eine optionale Ergänzung, mit der Sie die Ergebnisse benutzerdefinierter Funktionen manuell aktualisieren können.

Im Cache gespeicherte Ergebnisse aktualisieren

Im Gegensatz zu integrierten Funktionen werden benutzerdefinierte Funktionen von Google im Cache gespeichert, um die Leistung zu optimieren. Wenn Sie also etwas in Ihrer benutzerdefinierten Funktion ändern, z. B. einen Wert, der berechnet wird, wird möglicherweise nicht sofort eine Aktualisierung erzwungen. So aktualisieren Sie das Funktionsergebnis manuell:

  1. Klicken Sie auf Einfügen > Kästchen, um einer leeren Zelle ein Kästchen hinzuzufügen.
  2. Fügen Sie die Zelle mit dem Kästchen als Parameter der benutzerdefinierten Funktion hinzu, z. B. getSheetsData(B11).
  3. Aktivieren oder deaktivieren Sie das Kästchen, um die Ergebnisse der benutzerdefinierten Funktion zu aktualisieren.

Beitragende

Dieses Beispiel wird von Google mit Unterstützung von Google Developer Experts verwaltet.

Nächste Schritte