Консолидация листов таблиц при помощи Скрипта

Автор оригинального скрипта: Стив Вебстер.
Ссылки на сопутствующий материал:

• Ссылка на оригинальную статью: Consolidate Spreadsheet Sheets with Apps Script
• Ссылка на лист с примером: Консолидация таблиц
• и ссылка на вспомогательную таблицу: Консолидация - внешняя таблица 

Зачем?

К примеру, у тебя есть торговая команда, каждый участник которой ведет собственную таблицу с продажами. Главный менеджер по продажам должен видеть всю картину. Для этого ему приходится вручную копировать все данные на одну таблицу.

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

Решение

Ты можешь создать собственный Гугл Скрипт для того, чтобы обновлять лист [Master] при помощи созданного меню. Это делается через Инструменты → Редактор скриптов.

Функция ниже создает пользовательское меню:

function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [ {name: "Update Master", functionName: "updateMaster"}];
ss.addMenu("Scripts", menuEntries);
}

Замечу, что название пункта меню ты можешь изменить внутри кода.
Под этим кодом потом вставишь функцию "updateMaster". После сохранения скрипта и обновления листа появится новый пункт меню "Scripts".

При вызове функция "updateMaster" запустится. Теперь ты можешь разделить таблицы исполнителей и настроить права доступа.

Чтобы скрипт работал, тебе нужно главный лист назвать "Master", а каждый из листов-исходников должен иметь в названии суффикс "-Rep" (например, "Макс-Rep").

Скрипт засунет все имена листов, содержащих "-Rep" в один массив, а содержание таблиц на этих листах в другой массив. После чего он все данные вставит на лист "Master".

Еще один бонус от создателей скрипта заключается в том, что по завершении работы он удаляет все лишние строки на листе "Master".

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

function updateMaster() {
// This script was developed by Dito’s Steve Webster (www.ditoweb.com)
/*
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

http://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.
*/
var repArray = new Array();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var allSheets = ss.getSheets();
// build array of all sheets
for (i in allSheets) {
if ((allSheets[i].getName()).match(/.*?\-Rep$/))
{repArray.push(allSheets[i].getName());}
}

// store all sheets in array
var sheetArray = [];
// loop through all rep sheets
for (var j in repArray) {
// get each sheet
var tempSheet = ss.getSheetByName(repArray[j]);
// get sheet data
var dataRange = tempSheet.getDataRange().getValues();
// remove the first header row
dataRange.splice(parseInt(0), 1);
// append sheet data to array
var sheetArray = sheetArray.concat(dataRange);
}

// Time to update the master sheet
var mSheet = ss.getSheetByName("Master");
// save top header row
var headerRow = mSheet.getRange(1,1,1,12).getValues();
// clear the whole sheet
mSheet.clear({contentsOnly:true});
// put back the header row
mSheet.getRange(1, 1, 1, 12).setValues(headerRow);
// write to the Master sheet via the array
mSheet.getRange(2, 1, sheetArray.length, 12).setValues(sheetArray);
// force spreadsheet updates
SpreadsheetApp.flush();
// pause (1,000 milliseconds = 1 second)
Utilities.sleep("200");

// delete empty rows at bottom
var last = mSheet.getLastRow();
var max = mSheet.getMaxRows();
if (last !== max) {mSheet.deleteRows(last+1,max-last);}

//Макс Махров: добавил кусок кода от себя. 
//Он соритрует конечную таблицу по значению из первого столбца
var range = mSheet.getRange(2, 1, sheetArray.length, 12);
range.sort(1); //единица -- это номер сортируемого столбца
}

Автор скрипта еще говорит, что он не стал уменьшать количество строк на исходных листах: это слишком рискованно. Он предлагает это делать вручную каждым исполнителем.

Еще один момент, на который обращаю твое внимание. Скрипт написан для таблицы, в которой 12 столбцов. Для изменения их числа, поменяй число 12 в тексте скрипта на свое.

Копировать скрипт можно только при упоминании сайта автора (www.ditoweb.com).