Diese Seite wurde von der Cloud Translation API übersetzt.

Daten aus mehreren Tabellenblättern zusammenfassen

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

Zielsetzungen

Informieren Sie sich über die Funktionsweise der Lösung.
Informationen zu den Aufgaben der Apps Script-Dienste in der Lösung
Richten Sie das Script ein.
Führen Sie das Skript aus.

Informationen zu dieser Lösung

Wenn Sie in einer Tabelle ähnliche Daten auf mehreren Tabellenblättern haben, z. B. Kundensupportmesswerte für Teammitglieder, können Sie mit dieser benutzerdefinierten Funktion eine Zusammenfassung der einzelnen Tabellenblätter erstellen. Diese Lösung konzentriert sich auf Kundensupporttickets. Sie können sie jedoch an Ihre Anforderungen anpassen.

Screenshot der Ausgabe der Funktion „getSheetsData“

Funktionsweise

Die benutzerdefinierte Funktion getSheetsData() fasst die Daten aus jeder Tabelle in der Tabelle basierend auf der Spalte Status zusammen. Das Script ignoriert Tabellen, die nicht in die Aggregation einbezogen werden sollen, z. B. die Tabellen ReadMe und Summary.

Apps Script-Dienste

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

Tabellendienst: Hiermit werden die Tabellen abgerufen, die zusammengefasst werden müssen, und die Anzahl der Elemente gezählt, die mit einer bestimmten Zeichenfolge übereinstimmen. Anschließend fügt das Script die berechneten Informationen einem Bereich hinzu, der relativ zu der Stelle ist, an der die benutzerdefinierte Funktion in der Tabelle aufgerufen wurde.

Vorbereitung

Für die Verwendung dieses Beispiels sind die folgenden Voraussetzungen erforderlich:

Ein Google-Konto (für Google Workspace-Konten ist möglicherweise die Administratorzustimmung erforderlich).
Einen Webbrowser mit Internetzugriff.

Script einrichten

Klicken Sie auf die Schaltfläche unten, um eine Kopie der Tabelle Benutzerdefinierte 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

Rufen Sie in der kopierten Tabelle das Tabellenblatt Zusammenfassung auf.
Klicken Sie auf Zelle A4. Die Funktion getSheetsData() befindet sich in dieser Zelle.
Rufen Sie eines der Eigentümerblätter auf und aktualisieren oder fügen Sie dem Blatt Daten hinzu. Sie können Folgendes versuchen:
- Fügen Sie eine neue Zeile mit Beispieldaten für Tickets hinzu.
- Ändern Sie in der Spalte Status den Status eines vorhandenen Tickets.
- Ändern Sie die Position der Spalte Status. Verschieben Sie beispielsweise in der Tabelle Owner1 die Spalte Status von Spalte C nach Spalte D.
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 anklicken, 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 Statuszahlen durch das Script aktualisiert.
- Wenn Sie die Position der Spalte Status verschoben haben, funktioniert das Script 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

Auf GitHub ansehen

// 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 finden 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 berechneten Wert, wird die Funktion möglicherweise nicht sofort aktualisiert. So aktualisieren Sie das Funktionsergebnis manuell:

Klicken Sie auf Einfügen > Kästchen, um einer leeren Zelle ein Kästchen hinzuzufügen.
Fügen Sie die Zelle mit dem Kästchen als Parameter der benutzerdefinierten Funktion hinzu, z. B. getSheetsData(B11).
Setzen Sie ein Häkchen in das Kästchen, um die Ergebnisse der benutzerdefinierten Funktion zu aktualisieren.

Beitragende

Dieses Beispiel wird von Google mithilfe von Google Developer Experts verwaltet.