Daten aus mehreren Tabellenblättern zusammenfassen

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

Zielsetzungen

• Verstehen Sie, was die Lösung leisten kann.
• Was die Apps Script-Dienste innerhalb der Lösung leisten
• Richten Sie das Skript ein.
• Führen Sie das Skript aus.

Informationen zu dieser Lösung

Wenn Sie ähnlich strukturierte Daten auf mehreren Tabellenblättern einer Tabelle haben, z. B. Kundensupportmesswerte für Teammitglieder, können Sie diese benutzerdefinierte Funktion verwenden, um eine Zusammenfassung der einzelnen Tabellenblätter zu erstellen. Diese Lösung konzentriert sich auf Kundensupport-Tickets, Sie können sie jedoch an Ihre Bedürfnisse anpassen.

Funktionsweise

Die benutzerdefinierte Funktion getSheetsData() fasst die Daten aus jedem Tabellenblatt in der Tabelle anhand der Spalte Status eines Tabellenblatts zusammen. Das Skript ignoriert Tabellenblätter, die nicht in der Aggregation enthalten sein sollten, z. B. die Tabellenblätter ReadMe und Summary.

Apps Script-Dienste

Diese Lösung verwendet den folgenden Dienst:

• Tabellendienst: ruft die Tabellenblätter ab, die zusammengefasst werden müssen, und zählt die Anzahl der Elemente, die mit einem bestimmten String übereinstimmen. Anschließend fügt das Skript die berechneten Informationen in einen Bereich ein, der sich darauf bezieht, wo die benutzerdefinierte Funktion in der Tabelle aufgerufen wurde.

Voraussetzungen

Sie benötigen die folgenden Voraussetzungen, um dieses Beispiel verwenden zu können:

• Ein Google-Konto (Google Workspace-Konten erfordern möglicherweise die Administratorgenehmigung).
• Ein Webbrowser mit Zugang zum Internet.

Skript einrichten

Klicken Sie auf die Schaltfläche unten, um eine Kopie der Tabelle Benutzerdefinierte Funktion „Tabellendaten zusammenfassen“ 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. In dieser Zelle befindet sich die Funktion getSheetsData().
3. Rufen Sie eines der Tabellen mit den Eigentümern auf und aktualisieren Sie sie oder fügen Sie Daten hinzu. Sie können beispielsweise Folgendes tun:
   • Fügen Sie eine neue Zeile mit Beispiel-Ticketinformationen 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 nach Spalte D.
4. Rufen Sie das Tabellenblatt Zusammenfassung auf und sehen Sie sich die aktualisierte Übersichtstabelle 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, aktualisiert das Skript die Anzahl der Tickets und Status.
   • Falls Sie die Position der Spalte Status verschoben haben, funktioniert das Skript mit dem neuen Spaltenindex weiterhin wie vorgesehen.

Code ansehen

Klicken Sie unten auf Quellcode anzeigen, um den Apps Script-Code für diese Lösung zu sehen:

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

Abwandlung

Sie können die benutzerdefinierte Funktion beliebig ändern. Unten finden Sie eine optionale Ergänzung zum manuellen Aktualisieren der Ergebnisse benutzerdefinierter Funktionen.

Im Cache gespeicherte Ergebnisse aktualisieren

Im Gegensatz zu integrierten Funktionen speichert Google benutzerdefinierte Funktionen im Cache, um die Leistung zu optimieren. Wenn Sie also etwas innerhalb Ihrer benutzerdefinierten Funktion ändern, z. B. einen Wert, der berechnet wird, wird möglicherweise nicht sofort eine Aktualisierung erzwungen. Führen Sie die folgenden Schritte aus, um das Funktionsergebnis manuell zu aktualisieren:

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

Beitragende

Dieses Beispiel wird von Google mit Unterstützung von Google Developers-Experten verwaltet.

Nächste Schritte

• Benutzerdefinierte Funktionen in Google Tabellen
• Google Tabellen erweitern