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 estruturados semelhantes em várias páginas de uma planilha, como métricas de suporte ao cliente para membros da equipe, use essa função personalizada para criar um resumo de cada planilha. Essa solução se concentra em tíquetes de suporte ao cliente, mas é possível personalizá-la de acordo com suas necessidades.

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 as planilhas que não precisam ser incluídas na agregação, como as páginas ReadMe e Resumo.

Serviços do Apps Script

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

• Serviço de planilha: acessa as páginas 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 ao local onde a função personalizada foi chamada na planilha.

Pré-requisitos

Para usar essa amostra, você precisa dos seguintes pré-requisitos:

• Uma Conta do Google (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 Função personalizada dos dados da planilha. O projeto do Apps Script para esta 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á nesta célula.
3. Acesse uma das planilhas do proprietário e atualize ou adicione dados à planilha. Algumas ações que você pode tentar incluem:
   • Adicione uma nova linha com exemplos de informações do ingresso.
   • Na coluna Status, altere o status de um tíquete.
   • Mude a posição da coluna Status. Por exemplo, na página Owner1, mova a coluna Status da coluna C para a coluna D.
4. Acesse a planilha Resumo e revise a tabela de resumo atualizada que getSheetsData() criou a partir da célula A4. Talvez seja necessário marcar a caixa de seleção na linha 10 para atualizar os resultados em cache da função personalizada. O Google armazena em cache funções personalizadas para otimizar o desempenho.
   • Se você adicionou ou atualizou linhas, o script atualizará as contagens de tíquetes e status.
   • Se você moveu a posição da coluna Status, o script ainda funcionará conforme o esperado com o novo índice de coluna.

Revisar o código

Para analisar o código do Apps Script para esta solução, clique em Ver código-fonte abaixo:

Ver o código-fonte

Code.gs

solutions/custom-functions/summarize-sheets-data/Code.js

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

Modificações

Você pode editar a função personalizada o quanto quiser. Veja abaixo 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 funções personalizadas para otimizar o desempenho. Isso significa que, se você alterar algo na sua função personalizada, como um valor que está sendo calculado, talvez isso não force 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 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 de especialistas do Google Developers.

Próximas etapas

• Funções personalizadas nas Planilhas Google
• Estender as Planilhas Google