Resumir datos de varias hojas

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

Objetivos

• Comprende lo que hace la solución.
• Comprender lo que 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 estructurados 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 centra en los tickets de asistencia al cliente, pero puedes personalizarla para que se adapte a tus necesidades.

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 hoja de cálculo: Obtiene las hojas que deben resumirse 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 relacionado con el lugar en el que se llamó a la función personalizada en la hoja de cálculo.

Requisitos previos

Para usar esta muestra, necesitas 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 Resumir la función personalizada de los datos de la hoja de cálculo. El proyecto de Apps Script correspondiente a 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 de las entradas.
   • En la columna Status, cambia el estado de un ticket existente.
   • Cambia la posición de la columna Estado. Por ejemplo, en la hoja Owner1, mueve la columna Owner1 de la columna C a la columna D.
4. Ve a la hoja Resumen 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 de 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 actualiza los recuentos de tickets y estados.
   • Si moviste la posición de la columna Estado, la secuencia de comandos aún funciona según lo previsto con el nuevo índice de columna.

Revisa el código

Si deseas revisar el código de Apps Script para 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

Ver en GitHub

// 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, según tus necesidades. A continuación, se muestra una adición opcional para actualizar manualmente los resultados de las funciones personalizadas.

Actualizar 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 Expertos de Google Developers.

Próximos pasos

• Funciones personalizadas en Hojas de cálculo de Google
• Ampliación de Hojas de cálculo de Google