Resumir dados de várias planilhas

Nível de programação: iniciante
Duração: 5 minutos
Tipo de projeto: função personalizada

Objetivos

  • Entenda o que a solução faz.
  • Entenda o que os serviços do Apps Script fazem na solução.
  • Configure o script.
  • Execute o script.

Sobre esta solução

Se você tiver dados com estrutura semelhante em várias planilhas, como métricas de suporte ao cliente para membros da equipe, pode usar esta função personalizada para criar um resumo de cada planilha. O foco desta solução é em tíquetes de suporte ao cliente, mas você pode personalizá-la de acordo com suas necessidades.

Captura de tela da saída da função getSheetsData

Como funciona

A função personalizada, chamada getSheetsData(), resume os dados de cada página da planilha com base na coluna Status. O script ignora planilhas que não devem ser incluídas na agregação, como Leia-me e Resumo.

Serviços do Apps Script

Esta solução usa o seguinte serviço:

  • Serviço de planilha: recebe as planilhas que precisam ser resumidas e conta o número de itens que correspondem a uma string especificada. Em seguida, o script adiciona as informações calculadas a um intervalo relativo a onde a função personalizada foi chamada na planilha.

Pré-requisitos

Para usar esta amostra, você precisa atender aos seguintes pré-requisitos:

  • Uma Conta do Google (as contas do Google Workspace podem exigir a aprovação do administrador).
  • Um navegador da Web com acesso à Internet.

Configurar o script

Clique no botão abaixo para fazer uma cópia da planilha Resumir dados da planilha função personalizada. O projeto do Apps Script para essa solução está anexado à planilha.
Fazer uma cópia

Executar o script

  1. Na planilha copiada, acesse a página Resumo.
  2. Clique na célula A4. A função getSheetsData() está nessa célula.
  3. Acesse uma das planilhas do proprietário e atualize ou adicione dados a ela. Confira algumas ações que você pode tentar:
    • Adicione uma nova linha com informações de exemplo de um tíquete.
    • Na coluna Status, mude o status de um tíquete.
    • Mude a posição da coluna Status. Por exemplo, na planilha Owner1, mova a coluna Status da coluna C para a D.
  4. Acesse a planilha Resumo e revise a tabela de resumo atualizada que o getSheetsData() criou na célula A4. Talvez seja necessário marcar a caixa de seleção na linha 10 para atualizar os resultados armazenados em cache da função personalizada. O Google armazena em cache as funções personalizadas para otimizar a performance.
    • Se você adicionou ou atualizou linhas, o script atualiza as contagens de tíquetes e status.
    • Se você moveu a posição da coluna Status, o script ainda funciona como esperado com o novo índice de coluna.

Revisar o código

Para revisar o código do Apps Script dessa solução, clique em Ver código-fonte abaixo:

Acessar o código-fonte

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

Modificações

Você pode editar a função personalizada quantas vezes quiser para atender às suas necessidades. Abaixo está uma adição opcional para atualizar manualmente os resultados da função personalizada.

Atualizar resultados armazenados em cache

Ao contrário das funções integradas, o Google armazena em cache as funções personalizadas para otimizar o desempenho. Isso significa que, se você mudar algo na função personalizada, como um valor que está sendo calculado, talvez não seja possível forçar uma atualização imediatamente. Para atualizar o resultado da função manualmente, siga estas etapas:

  1. Para adicionar uma caixa de seleção a uma célula vazia, clique em Inserir > Caixa de seleção.
  2. Adicione a célula que tem a caixa de seleção como um parâmetro da função personalizada, por exemplo, getSheetsData(B11).
  3. Marque ou desmarque a caixa de seleção para atualizar os resultados da função personalizada.

Colaboradores

Esta amostra é mantida pelo Google com a ajuda dos Google Developer Experts.

Próximas etapas