Resumir datos de varias hojas

Nivel de programación: Principiante
Duración: 5 minutos
Tipo de proyecto: Función personalizada

Objetivos

  • Comprende qué hace la solución.
  • Comprende qué hacen los servicios de Apps Script dentro de la solución.
  • Configura la secuencia de comandos.
  • Ejecuta la secuencia de comandos.

Acerca de esta solución

Si tienes datos con estructuras similares en varias hojas de una hoja de cálculo, como métricas de asistencia al cliente para los miembros del equipo, puedes usar esta función personalizada para crear un resumen de cada hoja. Esta solución se enfoca en los tickets de asistencia al cliente, pero puedes personalizarla para adaptarla a tus necesidades.

Captura de pantalla del resultado de la función getSheetsData

Cómo funciona

La función personalizada, llamada getSheetsData(), resume los datos de cada hoja de la hoja de cálculo según la columna Estado de la hoja. La secuencia de comandos ignora las hojas que no deben incluirse en la agregación, como las hojas ReadMe y Summary.

Servicios de Apps Script

En esta solución, se usa el siguiente servicio:

  • Servicio de hojas de cálculo: Obtiene las hojas que se deben resumir y cuenta la cantidad de elementos que coinciden con una cadena especificada. Luego, la secuencia de comandos agrega la información calculada a un rango relativo a la ubicación en la que se llamó a la función personalizada en la hoja de cálculo.

Requisitos previos

Para usar esta muestra, necesitas cumplir con los siguientes requisitos previos:

  • Una Cuenta de Google (es posible que las cuentas de Google Workspace requieran la aprobación del administrador)
  • Un navegador web con acceso a Internet

Configura la secuencia de comandos

Haz clic en el siguiente botón para crear una copia de la hoja de cálculo de la función personalizada para resumir datos de hojas de cálculo. El proyecto de Apps Script para esta solución se adjunta a la hoja de cálculo.
Crear una copia

Ejecuta la secuencia de comandos:

  1. En la hoja de cálculo que copiaste, ve a la hoja Resumen.
  2. Haz clic en la celda A4. La función getSheetsData() se encuentra en esta celda.
  3. Ve a una de las hojas del propietario y actualiza o agrega datos a la hoja. Estas son algunas acciones que puedes probar:
    • Agrega una fila nueva con información de muestra del ticket.
    • En la columna Estado, cambia el estado de un ticket existente.
    • Cambia la posición de la columna Estado. Por ejemplo, en la hoja Owner1, mueve la columna Estado de la columna C a la columna D.
  4. Ve a la hoja Summary y revisa la tabla de resumen actualizada que getSheetsData() creó a partir de la celda A4. Es posible que debas marcar la casilla de verificación en la fila 10 para actualizar los resultados almacenados en caché de la función personalizada. Google almacena en caché las funciones personalizadas para optimizar el rendimiento.
    • Si agregaste o actualizaste filas, la secuencia de comandos actualizará los recuentos de tickets y estados.
    • Si moviste la posición de la columna Estado, la secuencia de comandos seguirá funcionando según lo previsto con el nuevo índice de columna.

Revisa el código

Para revisar el código de Apps Script de esta solución, haz clic en Ver código fuente a continuación:

Ver el código fuente

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

Modificaciones

Puedes editar la función personalizada tantas veces como quieras para que se adapte a tus necesidades. A continuación, se muestra una adición opcional para actualizar manualmente los resultados de las funciones personalizadas.

Actualiza los resultados almacenados en caché

A diferencia de las funciones integradas, Google almacena en caché las funciones personalizadas para optimizar el rendimiento. Esto significa que, si cambias algo en tu función personalizada, como un valor que se está calculando, es posible que no se fuerce una actualización de inmediato. Para actualizar el resultado de la función de forma manual, sigue estos pasos:

  1. Para agregar una casilla de verificación a una celda vacía, haz clic en Insertar > Casilla de verificación.
  2. Agrega la celda que tiene la casilla de verificación como parámetro de la función personalizada, por ejemplo, getSheetsData(B11).
  3. Marca o desmarca la casilla de verificación para actualizar los resultados de la función personalizada.

Colaboradores

Google mantiene esta muestra con la ayuda de los Google Developer Experts.

Próximos pasos