Суммировать данные из нескольких листов

Уровень владения программированием : начинающий
Продолжительность : 5 минут
Тип проекта : Пользовательская функция

Цели

• Поймите, для чего предназначено это решение.
• Разберитесь, что делают службы Apps Script в рамках данного решения.
• Настройте скрипт.
• Запустите скрипт.

Об этом решении

Если у вас есть данные аналогичной структуры на нескольких листах электронной таблицы, например, показатели работы службы поддержки клиентов для членов команды, вы можете использовать эту пользовательскую функцию для создания сводки по каждому листу. Это решение ориентировано на обработку заявок в службу поддержки клиентов, но вы можете настроить его под свои нужды.

Как это работает

Пользовательская функция, называемая getSheetsData() , суммирует данные с каждого листа в электронной таблице на основе столбца «Статус» этого листа. Скрипт игнорирует листы, которые не должны быть включены в агрегацию, такие как листы ReadMe и Summary .

Сервисы Apps Script

Данное решение использует следующую службу:

• Сервис электронных таблиц — получает листы, которые необходимо обобщить, и подсчитывает количество элементов, соответствующих указанной строке. Затем скрипт добавляет вычисленную информацию в диапазон относительно места вызова пользовательской функции в электронной таблице.

Предварительные требования

Для использования этого примера необходимы следующие условия:

• Для работы потребуется учетная запись Google (для учетных записей Google Workspace может потребоваться подтверждение администратора).
• Веб-браузер с доступом в интернет.

Настройте скрипт

Чтобы создать копию таблицы с пользовательской функцией «Сводка данных» , нажмите следующую кнопку:

Сделать копию

Проект Apps Script для данного решения прилагается к электронной таблице.

Запустите скрипт

1. В скопированной электронной таблице перейдите на лист «Сводка» .
2. Щелкните ячейку A4 . В этой ячейке находится функция getSheetsData() .
3. Перейдите к одному из листов владельца и обновите или добавьте данные в этот лист. Вы можете попробовать следующие действия:
   • Добавьте новую строку с примером информации о билете.
   • В столбце «Статус» измените статус существующего тикета.
   • Измените положение столбца «Статус» . Например, на листе Owner1 переместите столбец «Статус» из столбца C в столбец D.
4. Перейдите на лист «Сводка» и просмотрите обновленную сводную таблицу, созданную функцией getSheetsData() из ячейки A4 . Возможно, вам потребуется установить флажок в строке 10, чтобы обновить кэшированные результаты пользовательской функции. Google кэширует пользовательские функции для оптимизации производительности.
   • Если вы добавили или обновили строки, скрипт обновит количество заявок и их статусов.
   • Если вы изменили положение столбца «Статус» , скрипт всё равно будет работать должным образом с новым индексом столбца.

Просмотрите код

Чтобы просмотреть код Apps Script для этого решения, нажмите «Просмотреть исходный код» :

Просмотреть исходный код

Code.gs

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

Посмотреть на 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() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  const outputArr = [];

  // For each sheet, summarizes the data and pushes to a temporary array.
  for (const s in sheets) {
    // Gets sheet name.
    const sheetNm = sheets[s].getName();
    // Skips ReadMe and Summary sheets.
    if (sheetNm === READ_ME_SHEET_NAME || sheetNm === PM_SHEET_NAME) {
      continue;
    }
    // Gets sheets data.
    const values = sheets[s].getDataRange().getValues();
    // Gets the first row of the sheet which is the header row.
    const 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.
    const columnOwner = headerRowValues.indexOf("Owner Name");
    const 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.
    const owner = values[0][columnOwner];
    // Counts the total number of tasks.
    const 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.
    const completeCnt = filterByPosition(
      values,
      "Complete",
      columnStatus,
    ).length;
    // Counts the number of tasks that have the 'In-Progress' status.
    const inProgressCnt = filterByPosition(
      values,
      "In-Progress",
      columnStatus,
    ).length;
    // Counts the number of tasks that have the 'Scheduled' status.
    const scheduledCnt = filterByPosition(
      values,
      "Scheduled",
      columnStatus,
    ).length;
    // Counts the number of tasks that have the 'Overdue' status.
    const 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);
}

Модификации

Вы можете редактировать пользовательскую функцию по своему усмотрению в соответствии со своими потребностями. Чтобы увидеть дополнительную опцию для ручного обновления результатов пользовательской функции, нажмите «Обновить кэшированные результаты ».

Обновить кэшированные результаты

В отличие от встроенных функций, Google кэширует пользовательские функции для оптимизации производительности. Это означает, что если вы измените что-либо внутри своей пользовательской функции, например, вычисляемое значение, обновление может произойти не сразу. Чтобы обновить результат функции вручную, выполните следующие действия:

1. Чтобы добавить флажок в пустую ячейку, нажмите «Вставка» > «Флажок» .
2. Добавьте ячейку, содержащую флажок, в качестве параметра пользовательской функции, например, getSheetsData(B11) .
3. Установите или снимите флажок, чтобы обновить результаты работы пользовательской функции.

Авторы

Данный пример поддерживается компанией Google при содействии экспертов-разработчиков Google.

Следующие шаги

• Пользовательские функции в таблицах
• Дополнительные листы