статьи

Google Docs, Google Drive, Google Scripts: как писать скрипты, макросы и код — часть 0

Доброго времени суток, дорогие читатели, вредины, злодеи, доброжелатели и прочие личности. Минул период страшных страданий, начиная от сервера проекта, мосэнерго, clip2net-а и заканчивая прочими проблемами, что напали на нас за последнюю неделю. Кстати, это всё показало, что многие не совсем знакомы с нашим , , , группой в , и т.п., из-за чего мы монотонно отвечали всё это время на Ваши письма, смс и даже звонки.

Google Spreadsheet

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

script

Я думаю, что очень многие из Вас умеют пользоваться Excel'ем или , а некоторые, может, даже и . Те, кто пользуется диском Google (Google Drive), наверное уже использовали Таблицы (Spreadsheets) и заметили, что по функционалу они немного уступают Экселю, но тем не менее это всё ещё мощный инструмент. Так вот, в Экселе были макросы (этакие команды, упрощающие и автоматизирующие вычисления), написанные на небезызвестном языке VBA (Visual Basic for Applications). В Таблицах Google также есть макросы, которые именуются скриптами и пишутся уже на языке Javascript. С ними мы сегодня и познакомимся.

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

Соберитесь в комочек мозга.. И приступим :)

Создание таблицы Google Drive и наполнение её контентом

Рассмотрим такую простенькую задачку:
У нас есть две колонки, в первой мы пишем названия фруктов, а во второй цвет, который соответствует этому фрукту. И мы хотим, чтобы при вводе цвета в колонке цветов автоматически менялся бы цвет названия фрукта.

Если Вы забыли как вообще пользоваться документами Google, то милости просим почитать соответствующую и уже упомянутую выше . Если Вам это не нужно совсем, то читать наверное и дальше даже нет смысла. Хотя, конечно, кому что :)

Так вот, создаем новую таблицу Google, именуем её, например, "Фрукты". Ну, как, например.. Учитывая, что пример про фрукты, то.. Ну Вы поняли :)

Теперь добавляем на первый лист наши фрукты и цвета:

список значений

Примечание! Для того, чтобы считались фрукты, введите в ячейку А1 формулу:

="фрукт ("&COUNTA(A2:A)&")"

Теперь создадим макрос. Для этого идем в меню "Инструменты" и выбираем "Управление скриптами". Появится всплывающее меню, где мы жмем на кнопку "Создать".

создание макроса

В появившемся окошке выбираем "Пустой проект".

создание скрипта

Откроется редактор, который на первый взгляд (да и на второй) может вызвать ступор.

Редактор Скриптов

Собственно, что дальше? А дальше мы начинаем писать наш собственный макрос ручками (да, всё самостоятельно). Как будет выглядеть наш макрос? Нужно составить схемку сего процесса (иначе этот процесс займет у Вас очень много времени).

Нам нужно:

  1. Достать значения цветов из второй колонки;
  2. В соответствии с этими значениями задавать цвета для первой колонки.

Итак.. Вроде бы всё просто.. Если знать, как это делать, конечно :)

к содержанию ↑

шКоддинг

Перейдем к самому коду:

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{name : "Покрасить",functionName : "MakeMeHappy"}];
  sheet.addMenu("Скрипты", entries);
};

function MakeMeHappy(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = sheet.getActiveRange();
  var data = range.getValues();
  if(range.getColumn() == 2){
    for (var i=0;i < data.length;i++){
      range.offset(i,-1,1,1).clearFormat();
      range.offset(i,-1,1,1).setHorizontalAlignment("center");
      range.offset(i,-1,1,1).setVerticalAlignment("center");
      switch (data[i][0]){
        case "зеленый":
        case "Зеленый":
          range.offset(i,-1,1,1).setFontColor("#00dd00");
          break;
        case "салатовый":
        case "Салатовый":
          range.offset(i,-1,1,1).setBackgroundColor("#87dd47");
          break;
        case "желтый":
        case "Желтый":
          range.offset(i,-1,1,1).setBackgroundColor("#ffff00");
          break;
        case "оранжевый":
        case "Оранжевый":
          range.offset(i,-1,1,1).setFontColor("#dd7711");
          break;
        case "красный":
        case "Красный":
          range.offset(i,-1,1,1).setFontColor("#dd0000");
          break;
        case "фиолетовый":
        case "Фиолетовый":
          range.offset(i,-1,1,1).setFontColor("#800080");
          break;
        default: break;
      }
    }
  }
};

Теперь я постараюсь Вам его объяснить. Функция onOpen добавляет меню "Скрипты" к таблице при открытии оной. И выглядит это дело так:

добавление своего меню

Теперь по коду:

var sheet = SpreadsheetApp.getActiveSpreadsheet();

Эта строчка добавляет в переменную sheet идентификатор открытого нами документа, чтобы потом по нему обращаться к документу.

var entries = [{name : "Покрасить",functionName : "MakeMeHappy"}];

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

sheet.addMenu("Скрипты", entries);

Этот метод добавляет к нашему документу меню "Скрипты".

Функция MakeMeHappy, собственно, и будет нашей главной функцией, которая красит фрукты.
Сначала я объявляю переменные:

var sheet = SpreadsheetApp.getActiveSpreadsheet();
var range = sheet.getActiveRange();
var data = range.getValues();

Соответственно, в переменной sheet находится идентификатор нашего документа. В переменной range находится выделенная нами область (например, ячейки B2:B6), в переменной data находятся значения этих ячеек в виде массива.

if(range.getColumn() == 2){...}

В этом условии мы проверяем, что выбранный диапазон ячеек соответствует второй колонке (в которой цвета фруктов).

for (var i=0;i < data.length;i++){...}

В этом цикле мы проходимся по каждой ячейке из диапазона B2:B

range.offset(i,-1,1,1).clearFormat();
range.offset(i,-1,1,1).setHorizontalAlignment("center");
range.offset(i,-1,1,1).setVerticalAlignment("center");

Эти три свойства убирают форматирование ячеек A[i] (например, A1, A2, A3 и т.п., т.к. мы внутри цикла), а также центрируют значения в ячейке по вертикали и горизонтали.

Тут следует иметь в виду, что т.к. наш диапазон соответствует второй колонке (В2:В), а нам надо убрать форматирование и отцентровать первую колонку, то для этого используется метод offset (номер ряда диапазона, номер колонки, кол-во рядов, кол-во колонок). Например, метод range.offset(0,1,4,3) для ячейки B2 (т.е. range соответствует B2:B2 ) будет означать, что мы будем воздействовать не на ячейку B2:B2, а на диапазон [B+1][2+0]:[В+3][2+(4-1)] = C2:E5. Более подробно сморите в .

switch (data[i][0]){
        case "зеленый":
        case "Зеленый":
          range.offset(i,-1,1,1).setFontColor("#00dd00");
          break;
        case "салатовый":
        case "Салатовый":
          range.offset(i,-1,1,1).setBackgroundColor("#87dd47");
          break;
          ...
}

Функция switch является так называемым переключателем. Она смотрит значение переменной и в соответствии с тем, что в ней хранится, выполняет определенное условие "case". Можно её переписать в стандартном виде if else. Но получится очень неудобно. Например:

switch (c){
case 1: условие_1; break;
case 2: условие_2; break;
case 3: условие_3; break;
default: условие_4; break;
}

..Будет эквивалентно функции:

if (c == 1) условие_1;
else if (c == 2) условие_2;
else if (c == 3) условие_3;
else условие_4;

Т.к. можно ввести цвет как с большой, так и с маленькой буквы, то нам надо по два условия, что соответствует записи case "зеленый": case "Зеленый": действие; break; (у меня это записано блочной структурой). Нужно иметь в виду, что после каждого действия надо писать функцию break; т.к. иначе мы будем выполнять все условия по порядку, а не то, которое нам надо. Условие default используется в том случае, если для нашей переменной нет подходящего условия.

range.offset(i,-1,1,1).setFontColor("#00dd00");

Методы setFontColor и setBackgroundColor задают цвета текста и фона в виде #rrggbb (r-red, g-green, b-blue, диапазоны цветов) соответственно.

Теперь проверим функцию. Выделяем диапазон B2:B9, заходим в меню "Скрипты" и выбираем опцию "Покрасить". Смотрим, как наши фрукты обрели жизнь цвета :)

В общем-то на этом всё. Но можно сделать небольшое дополнение, чтобы не приходилось каждый раз запускать функции вручную. Для этого зайдите в редакторе скриптов в меню "Ресурсы" и выберите там "Триггеры текущего проекта". Откроется менюшка, в которой уже будет наша функция onLoad. Добавляем новую функцию (1) и задаем название функции (2) и тип активации оной (3). Также можно нажать на "Уведомления" и добавить/убрать свой почтовый адрес из списка уведомлений. Они будут приходить в случае, если скрипт выполнялся с ошибками.

добавление триггеров

Конечный результат действа:

результат

Ну, в общем.. Да.

к содержанию ↑

Послесловие

Поздравляю с первым скриптом. Это лишь малая доля того, что можно сделать при помощи такого мощного устройства, как Google Scripts. Понятно, что наверное большинство читателей такая штука пугает, тем более, что другие статьи не так суровы и "ругаются" на Вас кодом, да и прочими ужасами жизни.. Но что уж делать.

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

P.S. За существование оной статьи отдельное спасибо другу проекта и члену нашей команды под ником “barn4k“.

Оценить

  1. 5
  2. 4
  3. 3
  4. 2
  5. 1
(11 голосов, в среднем: 5 из 5)
  1. Первый типа :)

  2. Второй :)

  3. Третий :)

  4. Ответить
    ProQ.Lucky

    Четвертый :)
    За написание статьи Андрею и 6aPHaK спасибо)
    Извините что не в тему. Хотел спросить когда собираетесь и собираетесь ли вообще выпускать свой сборничек с программами, который карманный софт. Про него говорилось в аудиокасте.
    И объясните пожалуйста куда такие вопросы задавать в следующий раз, чтобы темы не засорять.

    • Как только - так сразу. Мы пытаемся сделать флешку готовым образом.
      В общем -в процессе.

      • Ответить
        ProQ.Lucky

        понятно, спасибо)
        а когда следующий аудиокаст? мне, думаю и не только мне, прошлый очень понравился :)

  5. "Всё такое прочее в комментариях!" Радуйтесь! =)

  6. Уважаемый Андрей, здравствуйте! А по Excel у вас не будет чего-нибудь похожего на эту статью?:)

    • Можем что-то соорудить, если хочется :)

    • По Экселю можно попробовать соорудить, просто там другой язык программирования, что мне не очень нравится :)

  7. Очень хочется! Кажется это будет интересно многим, не только мне. Excel для меня вообще лес дремучий:)

  8. Ответить
    Сергей

    Где справочник по по этим скриптам можно найти? И примеры все приводят по таблицам, а хотелось бы возможности и ограничения по формам.
    В любом случае, спасибо! полезно.

    • Наверное как-то так - https://sites.google.com/site/scriptsexamples/

    • Добрый день!
      по таблицам: https://developers.google.com/apps-script/reference/spreadsheet/
      по формам: https://developers.google.com/apps-script/reference/forms/

  9. Большое спасибо за подробное расписывание скрипта. Как раз то, что нужно тому, кому охота разобраться. Только одного примера мало. Очень хотелось бы увидеть разные примеры с такими же подробными описаниями! В екселе в этом плане немного проще - можно записать последовательность действий в скрипт, а потом только подправить, чего не хватает.

    • Всегда пожалуйста, стараемся.

    • Надо подумать что еще можно соорудить :)

  10. Отличная статья, спасибо. А как сделать такой скрипт: если в ячейка покрашена в желтый цвет, то копировать всю строку на другой лист

    Заранее спасибо, Stijit

    • примерно так:
      var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("List1"); // Лист с желтой ячейкой
      var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("List2"); // Лист, куда копировать
      var Data = sheet1.getRange("A1:B1").getValues(); // A1:B1 - ряд с желтой ячейкой
      var Cell = sheet1.getRange("C1"); // номер желтой ячейки
      var yellow = "#ffff00"; // код для желтого цвета
      if (Cell.getBackground() == yellow) sheet2.getRange("A1:B1").setValues(Data);
      // Если цвет ячейки желтый, то добавить всю строчку на Лист 2

  11. Ответить
    Дмитрий

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

    если будет возможность подскажите, как считать сумму ячеек, которые окрашены в один цвет?

    • примерно так:

      var sheet = SpreadsheetApp.getActiveSpreadsheet(); // Наш активный лист
      var NumRows = sheet.getNumRow(); // узнать кол-во рядов на листе
      var NumColumns = sheet.getNumColumns(); // узнать кол-во столбцов на листе
      var Data = sheet.getRange(1, 1, NumRows, NumColumns).getValues(); // получить значение каждой ячейки
      var color = "#ffffff"; // Цвет ячейки
      var counter = 0; // Счетчик
      var Sum = 0; // Сумма значений ячейки, окрашенной в цвет color
      for (var i=0; i<Data[0].length; i++){ // цикл по строкам
      for (var j=0; j<Data.length; j++){ // цикл по столбцам
      if (sheet.getRange(i+1, j+1, 1, 1).getBackground() == color) { // Сравниваем цвет каэдой ячейки с цветом color
      counter++; // Если цвет совпадает, то увеличиваем кол-во найденных ячеек на 1
      Sum += Data[i][j][0]; // Заодно добавляем значение ячейки в сумму
      }
      }
      }

      • Ответить
        Дмитрий

        Спасибо, но что-то ему не нравится, может я что-то не так делаю :)
        И ещё вопрос: скрипты эти работают только в обновлённых таблицах spreadsheets? Или старые тоже обновляются автоматически?)

        Вообще, где потихоньку лучше начать изучать скрипты?) У Вас на сайте можно? HTML и CSS изучал, на PHP погиб, потому что не знал, с чего начать :)

      • Ответить
        Дмитрий

        TypeError: Не удается обнаружить функцию getNumRow в объекте Spreadsheet. (строка 3, файл Код)

        Такая же проблема с подсчётом колонок на листе

      • Ответить
        Серега

        barn4k, скажите, а может ли быть такая фича:
        Нужен скрипт для Google docs, который при изменении каждой ячейки сохраняет предыдущую информацию, новую добавляет перед старой, в квадратных скобках пишет : дата, время, пользователь. В конце «;». И так каждый раз. Исключение 1я строка – название столбцов
        Пример:
        - добавили фразу «заявка принята» получилось:
        «заявка принята [26.03.14 14:58 GoodAvto@gmail.com];»
        -добавили «одобрено», получилось:
        «одобрено [26.03.14 14:59 GoodAvto@gmail.com]; заявка принята [26.03.14 14:58 GoodAvto@gmail.com];»

  12. Ответить
    Серега

    Круть! А скажите, может ли быть такая фича:
    Нужен скрипт для Google docs, который при изменении каждой ячейки сохраняет предыдущую информацию, новую добавляет перед старой, в квадратных скобках пишет : дата, время, пользователь. В конце «;». И так каждый раз. Исключение 1я строка – название столбцов
    Пример:
    - добавили фразу «заявка принята» получилось:
    «заявка принята [26.03.14 14:58 GoodAvto@gmail.com];»
    -добавили «одобрено», получилось:
    «одобрено [26.03.14 14:59 GoodAvto@gmail.com]; заявка принята [26.03.14 14:58 GoodAvto@gmail.com];»

  13. Замечательная идея. получится, что в ячейке будет видно только начало строки, актуальное на сейчас, но предыдущая информация не будет потеряна.

  14. Ответить
    Кирилл

    Доброго времени суток. Очень нужна Ваша помощь.
    Нужен скрипт который блокирует изменение данных в ячейках по такому принципу:
    Если (например) на листе 10 в ячейке А1 проставлено "STOP" (или там цифра 1), то определенные диапазоны ячеек на листах 1,2,3,4... защищены от изменений и удаления в т.ч. и у автора документа (во избежание случайного удаления или изменения), а если проставлено "RUN" (или например 0), то данные в этих же диапазонах можно редактировать и удалять. Обычными средствами этого достигнуть не удалось. И было бы шикарно, если бы добавить еще и введение пароля.

  15. Ответить
    Михаил

    Доброго времени! Спасибо за хорошую статью, просто и доступно.
    Столкнулся с такой проблемой - Google формы при сохранении данных в таблицу изменяют в числах разделитель десятичной части с "запятой" на "точку". В итоге, вместо цифр в таблице имеем текст. Сейчас меняю в ячейках "точку" на "запятую" вручную через Меню-Правка-Найти и заменить. Но хотелось бы это автоматизировать. Как правильно написать команду замены? Готов на любую помощь. Заранее спасибо.

  16. Ответить
    Дмитрий

    Доброго время суток, бодаюсь с такой задачей :

    Необходимо что бы в ручном режиме либо автоматическом (ежедневно в 23-00 данные из столбца G переносились на второй лист в определенные ячейки), т.е. не замещались друг другом а на регулярной основе дополнялись.

    Есть у кого идеи как реализовать ? :) Приветствуется любая помощь ибо в дополнениях гугла не нашел готового решения

    • Приветствуем :)
      Пока у меня идей нет, но может сам автор статьи появится ;)

  17. Ответить
    LordBeheliar

    Можно ли сделать уведомление через смс сервис: что бы приходило сообщение об изменении данных в определенной ячейке?

    • В принципе это реально

      • Ответить
        LordBeheliar

        Можете помочь в этом вопросе?

        • Гугл может слать уведомления только на почту .

  18. Ответить
    Раиль

    Доброго времени суток всем. Пишу с просьбой. Знаний в google скриптах = 0. Но хочу сделать импорт данных из google таблиц (Имя, Телефон, Примечание)в google контакты в автоматическом режиме.

    На данные момент на сайте есть форма обратной связи на ней посетитель оставляет свои данные, имя, телефона и т.д. Данные сразу же попадают в таблицу Google. И соответсвенно автоматически заполняется. Хотелось бы чтобы они также импортировались в кнтакты Google. чтобы призвонке уже знать что клиент с нами контактировал.

    Заранее благодарен за ответ.

  19. А есть ли у кого-нибудь идеи, как сделать так, чтобы при создании нового листа, ему приваивалось имя в форме даты и времени создания листа?

    • Добавьте такой скрипт:
      function onOpen() {
      var sheet = SpreadsheetApp.getActiveSpreadsheet();
      var entries = [{name : "Добавить лист",functionName : "addSheet"}];
      sheet.addMenu("Скрипты", entries);
      };

      function addSheet(){
      var sheet = SpreadsheetApp.getActiveSpreadsheet();
      var date = new Date();
      var time = date.getDate()+"."+(date.getMonth()+1)+"."+date.getFullYear()+" "+date.getHours()+":"+date.getMinutes()+":"+date.getSeconds();
      sheet.insertSheet(time);
      };
      и запустите скрипт onOpen.
      Когда понадобится создать новый лист, выбираете пункт меню "Скрипты" - "Добавить лист"

  20. Ответить
    Александр

    Здравствуйте!

    А как считать скриптом формулу (не значение - getvalue) ячейки?
    И потом перенести ее (вставить в другое место).

    Спасибо.

    • С помощью функции getFormula можно записать формулу ячейки. С помощью функции setFormula можно ее применить.

      var sheet = SpreadsheetApp.getActiveSpreadsheet().getActive();
      var range = sheet.getRange("B5");
      var formula = range.getFormula();
      var rangeApply = "C5";
      sheet.getRange(rangeApply).setFormula(formula);

  21. Здравствуйте!.
    Как написать скрипт, что бы он срабатывал когда в ячейку А№(№-1,2,3....) что то записывали, и тогда в ячейку В№(№-1,2,3....) записывалась дата, когда ввели эти данные.

    • Юра, у меня получился вот такой код:

      function myFunction() {
      ScriptApp.newTrigger('onEdit').forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet()).onEdit().create()
      }

      function onEdit(e){
      var range = SpreadsheetApp.getActiveRange(); //Получаем активный диапазон
      var a = range.getRow(); // Адрес строки активной ячейки

      if (SpreadsheetApp.getActiveSheet().getRange(a,1).getValues()==''){ //Если дата не заполнена
      SpreadsheetApp.getActiveSheet().getRange(a,1).setValues([[new Date()]]); //Заполняем текущую дату
      }

      }

  22. Ответить
    Виталий

    Помогите пожалуйста, задача такая: в диапазоне ячеек F2:F21 есть проверка данных с определенными значениями, как сделать так что бы можно было выбирать не одно а несколько значений?

    • function onEdit(e) { // функция добавления значений в строку. Срабатывает автоматически, когда какое-то действие c ячейками происходит
      var sheet = SpreadsheetApp.getActive();
      var sheetName = sheet.getSheetName();
      if (sheetName == "Сводная" && typeof e.value != "object"){ // "Сводная" - название вашего листа, где требуется проверка данных
      var range = e.range;
      var flag = false;
      var newValue = e.value;
      var oldValue = e.oldValue;
      var column = range.getColumn();
      if ((column == 3) && oldValue && newValue){ // 3 - номер колонки с проверкой данных
      var strAr = oldValue.split(", "); // разделитель между значениями
      for (var i=0; i<strAr.length; i++){ // Если прошлое значение и нынешнее существует, то
      if(strAr[i] == newValue) { // если в ячейке такое значение есть,
      range.setValue(oldValue); // то новое не добавляется
      flag = true;
      break;
      }
      }
      if (!flag) range.setValue(oldValue+", "+newValue); // если нету, то оно добавляется
      }
      }
      };

      • Ответить
        Виталий

        Спасибо большое, очень помогли. Не подскажите если у меня новый лист создается каждый день, возможно ли сделать так что бы этот скрипт работал на всех листах, что бы не вписывать имя листа каждый раз?

        • Ответить
          Виталий

          var strAr = oldValue.split(", "); // разделитель между значениями
          И как сделать так что бы разделитель был просто с новой строчки, как будто нажал CTRL+Enter

        • Для того, чтобы скрипт срабатывал на всех листах, замените 4 строчку:
          if (typeof e.value != "object")

          Что по поводу разделителя, замените 11 строчку:
          var strAr = oldValue.split("\n");

          и 19 строчку:
          if (!flag) range.setValue(oldValue+"\n"+newValue)

  23. Ответить
    Виталий

    еще раз большое спасибо!

  24. Ответить
    Виталий

    Подскажите пожалуйста, вписал ваш скрипт, все замечательно работает, но есть вопрос.
    Скрипт работает по всей колонке номер 6, это колонка F в моей таблице, можно ли сделать так что бы он работал с F2 по F20?
    function onEdit(e) { // функция добавления значений в строку. Срабатывает автоматически, когда какое-то действие c ячейками происходит
    var sheet = SpreadsheetApp.getActive();
    var sheetName = sheet.getSheetName();
    if (typeof e.value != "object"){ // "Сводная" - название вашего листа, где требуется проверка данных
    var range = e.range;
    var flag = false;
    var newValue = e.value;
    var oldValue = e.oldValue;
    var column = range.getColumn();
    if ((column == 6) && oldValue && newValue){ // 3 - номер колонки с проверкой данных
    var strAr = oldValue.split("\n"); // разделитель между значениями
    for (var i=0; i<strAr.length; i++){ // Если прошлое значение и нынешнее существует, то
    if(strAr[i] == newValue) { // если в ячейке такое значение есть,
    range.setValue(oldValue); // то новое не добавляется
    flag = true;
    break;
    }
    }
    if (!flag) range.setValue(oldValue+"\n"+newValue) // если нету, то оно добавляется
    }
    }
    };

    • Да, конечно.
      Добавьте после строки var column = range.getColumn(); следующую:

      var row = range.getRow();

      Поправьте строчку if ((column == 6) && oldValue && newValue){ // 3
      на:

      if ((column == 6 && row >= 2 && row <= 20) && oldValue && newValue){
  25. Ответить
    Виталий

    Спасибо! :)

  26. Доброго времени,
    как написать написать скрипт который в гугл таблице заполняет ячейки, в которых собирается информация о дате изменения файла, который лежит на гуглдрайве (любой тип файла)

    • Приветствую!

      Не очень понятно зачем такой скрипт нужен, учитывая что гугл драйв показывает когда какой файл был изменен + по каждому файлу есть статистика по изменениям

  27. Файлов много и это могут быть не Гугл таблицы, а эксель.

    • Так это неважно. Он показывает все измененные файлы в порядке убывания/возрастания. Т.е. всё тоже самое, что и делал бы скрипт.

  28. Файлы могут быть в разных папках. Т.е. нужно создать отдельную папку куда добавить все нужные файлы?

    • Да, можно так, т.к. обнаружил, что изменение файлов в подпапках не меняет дату в самой подпапке.

      Ниже код для таблицы google, который проверяет даты файлов в указанной папке и всех её подпапках. Если файл был изменен позже, чем дата в таблице, то дата поменяется и подсветится красным. Нужно повесить триггер на функцию "checkFolder", которая будет проверять даты через выбранный вами промежуток времени. Для формирования списка файлов используйте функцию "listFolderContents". Она добавит все файлы в указанной папке и всех ее подпапках в формате "имя файла", "имя папки, в которой этот файл", "ссылка на файл", "дата изменения".

      function listFolderContents() {
        var foldername = 'test1';  //Укажите название папки, в которой проверять.
        var mainfolderclass = DriveApp.getFoldersByName(foldername);
        var mainfolder = mainfolderclass.next();
        var contents = mainfolder.getFiles();
        var foldername = mainfolder.getName();
        var sheet = SpreadsheetApp.getActive().getActiveSheet();
        var file;
        var name;
        var link;
        var datefile; 
        while(contents.hasNext()) {
          file = contents.next();
          name = file.getName();
          link = file.getUrl();
          datefile = file.getLastUpdated();
          sheet.appendRow( [name, foldername, link, datefile] );     
        }  
        var subfolders = mainfolder.getFolders();
        checksubs(subfolders);
      };
      function checksubs(foldervar){
        while (foldervar.hasNext()){
          var folder = foldervar.next();
          var contents = folder.getFiles();
          var foldername = folder.getName();
          var sheet = SpreadsheetApp.getActive().getActiveSheet();
          var file;
          var name;
          var link;
          var datefile;
          while(contents.hasNext()) {
            file = contents.next();
            name = file.getName();
            link = file.getUrl();
            datefile = file.getLastUpdated();
            sheet.appendRow( [name, foldername, link, datefile] );     
          }
        }
      };
      
      function checkFolder(){
        var foldername = 'test1'; //Укажите название папки, в которой проверять.
        var mainfolderclass = DriveApp.getFoldersByName(foldername);
        var mainfolder = mainfolderclass.next();
        var contents = mainfolder.getFiles();
        var foldername = mainfolder.getName();
        var sheet = SpreadsheetApp.getActive().getActiveSheet();
        var file;
        var name;
        var link;
        var datefile; 
        var count = 1;
        while(contents.hasNext()) {
          count++;
          file = contents.next();
          name = file.getName();
          link = file.getUrl();
          datefile = file.getLastUpdated();
          checkDate (sheet, name, foldername, datefile);
        }
        var subfolders = mainfolder.getFolders();
        checkSubs(subfolders, count);
      }
      function checkSubs(foldervar, count){
        while (foldervar.hasNext()){
          var folder = foldervar.next();
          var contents = folder.getFiles();
          var foldername = folder.getName();
          var sheet = SpreadsheetApp.getActive().getActiveSheet();
          var file;
          var name;
          var link;
          var datefile;
          while(contents.hasNext()) {
            count++;
            file = contents.next();
            name = file.getName();
            link = file.getUrl();
            datefile = file.getLastUpdated(); 
            checkDate (sheet, name, foldername, datefile);
          }
        }
      };
      
      function checkDate (sheet, filename, foldername, datefile){
        var lastRow = sheet.getLastRow();
        for (i=2; i<=lastRow; i++){
          if (sheet.getRange(i, 1).getValue() == filename && sheet.getRange(i, 2).getValue() == foldername){
            if (sheet.getRange(i, 4).getValue() < datefile) {
              sheet.getRange(i, 4).setValue(datefile);
              sheet.getRange(i, 4).setBackground('#ff4444');
            } else sheet.getRange(i, 4).setBackground('#ffffff');
          }
        }
      };
      
  29. Ответить
    Андрей

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

    Планируемое время 0 12 20 27
    Фамилия москва новосибирск хабаровск владивосток
    Петров 01.01.2016 12.01.2016 25.01.2016
    Иванов 06.01.2016 25.01.2016
    Сидоров 12.01.2016 25.01.2016 27.01.2016

    Я захожу в таблицу, у меня установлено планируемое время в пути. Скрипт проверяет, просрочил ли кто-то время. Ищет пустую строку, вычисляет прошедшее время с момента старта и если прошло дней больше, чем планировалось, то выделяет пустую ячейку красным цветом и ставит, сколько дней прошло (=РАЗНДАТ(A7;СЕГОДНЯ();"D")). Как использовать функцию РАЗНДАТ в скрипте?

    • Приветствую!

      Для вставки формулы используйте функцию setFormula.
      Например:
      cell.setFormula("=DATEDIF(A7;TODAY();"D")");
      где cell - в этой переменной находится ячейка, в которую вы хотите записать формулу.

      • Ответить
        Андрей

        Спасибо большое! Буду пробовать.

  30. Здравствуйте

    Подскажите пожалуйста как мне создать кнопку, при нажатии которой будут сворачиваться/разворачиваться строки, к примеру, с 9 по 24 и расположить её в ячейке, к примеру, А8?

    • Добрый день!

      Для этого вставьте изображение в ячейку А8 (на панели навигации пункт меню "Вставка" - "Изображение"). Задайте интересующий вас размер кнопки и в правом верхнем углу изображения и нажмите на стрелку. Откроется выпадающее меню, где выберите пункт "Назначить скрипт...". Введите там "clickBtn". В ячейке А7 введите текст "Click button to hide 9-24 rows" (или любой другой)
      В редакторе добавьте код:

      function clickBtn (){
        var sheet = SpreadsheetApp.getActive();
        var Cell_txt = sheet.getRange("A7"); // Ячейка с текстом, она будет менять текст в зависимости от нажатия.
        var rowstart = 9; // С какого ряда начать
        var rowend = 24; // Каким рядом закончить
        var str_hide = "Click button to hide "+rowstart+"-"+rowend+" rows"; // Текст ячейки при нажатии на кнопку, чтобы спрятать ячейки. Должна совпадать с текстом, который вы ввели в ячейке.
        var str_unhide = "Click button to unhide "+rowstart+"-"+rowend+" rows"; // Текст ячейки при нажатии на кнопку, чтобы открыть ячейки
        var range = sheet.getRange("A"+rowstart+":A"+rowend);
        if (Cell_txt.getValue() == str_hide) {
          sheet.hideRow(range);
          Cell_txt.setValue(str_unhide);
        } else {
          sheet.unhideRow(range);
          Cell_txt.setValue(str_hide);
        }
      };
      • Большое спасибо!

      • Ответить
        Дмитрий

        Здравствуйте! Подскажите, пожалуйста, можно ли реализовать эту функцию сворачивания/разворачивания ячеек, без вставки изображения, а например, чтобы сворачивание/разворачивание происходило при клике на ячейку с текстом "Click button to hide 9-24 rows"?
        Заранее спасибо!

        • Так не получится, но можно сделать выпадающее меню с опциями "свернуть, развернуть, --- (ничего не делать)". Тогда при выборе одной из опций, ячейки будут соотв. сворачиваться/разворачиваться.

          Скрипт ниже:

          function collapse(){
            var sheet = SpreadsheetApp.getActive().getActiveSheet();
            var range = sheet.getActiveCell();
            var rowstart = 9; // С какого ряда начать
            var rowend = 24; // Каким рядом закончить
            var action = range.getValue();
            var rangeToApply = sheet.getRange(rowstart, 1, rowend-rowstart+1, 1);
            switch (action){
              case "Свернуть":
                sheet.hideRow(rangeToApply);
                range.setValue("---");
                break;
              case "Развернуть":
                sheet.unhideRow(rangeToApply);
                range.setValue("---");
                break;
              default: break;
            }
          };
          

          Добавьте в редакторе скриптов новый триггер: Функция collapse - из таблицы - изменение.
          Создайте в какой-нибудь ячейке проверку данных (пкм на ячейке - проверка данных - правила: значение из списка "Свернуть,Развернуть,---")

          • Дмитрий

            Отлично, спасибо большое!!

  31. Подскажите пожалуйста, в какую строку вставлять cell.setFormula( если у меня такой скрипт:
    function goEmail(){
    var ss = SpreadsheetApp.getActiveSheet();
    //узнаем редактируемую ячейку
    var r = SpreadsheetApp.getActiveRange();
    //узнаем номер колонки
    var cols = r.getColumn();
    var rows = r.getRow();
    //если колонка №7 то записываем коммент
    if(cols==7){
    var sheet = SpreadsheetApp.getActiveSheet();
    //№
    var pole1 = ss.getRange(rows,2).getValue();
    var pole2 = ss.getRange(rows,5).getValue();
    var pole3 = ss.getRange(rows,7).getValue();
    var email = pole1;
    var subject = "Повідомлення про відкриття доступу";
    var message = "Вам відкрито доступ до ресурсу: " + pole2;
    MailApp.sendEmail(email,subject,message);
    }
    }

    Заранее благодарю

    • А в какую ячейку вы хотите вставить формулу?
      Исходя из кода, это не очень понятно. Если в pole3, то var pole3 = ss.getRange(rows,7).setFormula("=ФОРМУЛА");

      • Благодарю за быстрый ответ.

        Вы правильно уловили, в колонку 7 "pole3". Но написав ее в таков виде var pole3 = ss.getRange(rows,7).setFormula("=IF(D10>0;"Да";"Нет")"); я получил такой ответ "После списка аргументов отсутствует ")". (ряд 14, файл "goEmail")".

        Этот скрипт для автомат. оправки писем об изменениях в таблице, Он активируется после изменений внесенных (в моем случае) в колонке 7. С его помощью я хотел сделать автоматическую отправку ссылки на ресурсу если идентификация пользователя прошла успешно. Все это я сделал с помощью формул, но как заставить его делать отправку если изменения в колонке 7 были сделаны формулой, а не редакцией в ручную, не понимаю. Во решил воспользоваться Вашей рекомендацией setFormula. Но тоже не получилось. Надеюсь Вы найдете время помочь разобраться. Сразу оговорюсь для программирования я не создан. Спасибо

  32. Полагаю, что более разумно в моем случае использовать формулу =INDEX(D:D) вместо =IF(D10>0;"Да";"Нет")"), так как я не привязываюсь к конкретной клетке. Как только заполняется новый ряд данными, а это адрес почты и веб-ссылка, в колонке 7 в текущем ряду функция INDEX вписывает какое то значение, на что скрипт реагирует и отправляет письмо. Вот такой задум

    • Я, на самом деле, не знаю функции, которая бы мониторила изменение, сделанное формулой. Однако, формула ведь не меняет значение просто так, т.е. где-то в другой ячейке меняется значение, которое влечет изменение ячейки под формулой. Т.е. как мне видится алгоритм:
      мы проверяем были ли сделаны какие-то изменения функцией onEdit (e).
      Если были, то проверяем где эти изменения были.
      Если они были в колонке D, то проверяем значение этой ячейки больше ли нуля (аналог формулы =IF(D10>0;"Да";"Нет")")), если да, то отправляем письмо, если нет, то ничего не делаем

  33. Может в курсе и подскажите. Столкнулись с проблемой обхода объектов (файл и папки) скриптом.
    Доступ, через скрипт, есть только до тех объектов в домене где учетка-запускатель, указана редактором (без ограничений). В свою очередь, есть приложение BetterCloud, которое обходит это ограничение, какое API используется для этих целей?

    • Я с таким не сталкивался, но на оф сайте написано, что оно использует Google Apps. Может, само приложение откуда-то берет учетные данные и подставляет их где надо. Может там где-то их надо вручную ввести.

  34. Подскажите пожалуйста как написать скрипт.
    Есть Статус задачи - Выполнена, Новая, Отложена. И вот если его поменять на "выполнена" - необходимо отсылать емейл на определенный адрес. Тоесть грубо говоря Если A1=B2 - send mail to....
    Такое возможно????

    • В целом, тут все довольно просто:

      function sendEmail() {
        var sheet = SpreadsheetApp.getActive();
        var range = sheet.getActiveRange();
        var row = range.getRow();
        var column = range.getColumn();
        if (row >= 20 && row <= 25 && column == 1) { // Указываем границы, в которых искать ячейки со статусом. В данном примере мы будем искать статус в ячейках А20:А25
          if (range.getValue() == "выполнена") {  // с каким статусом сравнивать
            var email = "test@example.com"; // почта получателя
            var subject = "Заголовок письма"; // заголовок письма
            var message = "Изменение статуса в ячейке "+range.getA1Notation();  // текст письма
            MailApp.sendEmail(email,subject,message); // Функция отправки письма
          }
        }
      };

      Добавляете после этого новый триггер в редакторе скриптов на вкладке "Ресурсы" - Триггеры текущего проекта -- добавить - функция sendEmail - из таблицы - изменение. Чтобы при любом изменении таблицы проверялась функция.

  35. Здравствуйте !
    Если можно прошу помочь. Как при помощи скрипта можно быстро путешествовать по книге с большим количеством страниц. К примеру : Вы находитесь на Лист 1 ячейка А1 нажали на кнопку и перешли на Лист 100 ячейка А1 . Утомительно перелистывать листы руками, может можно этот процесс автоматизировать. В VBA решается просто
    Sheets("Лист1").Select
    Range("A1").Select
    Sheets("Лист100").Select
    Range("А1").Select
    End Sub
    Можно через
    =ГИПЕРССЫЛКА("https://docs.google.com/spreadsheets/d/......";'Лист100'!A1)
    но как то не красиво а , как скриптом решить не знаю.
    Подскажите .

    • Добрый день!
      Довольно интересный вопрос и я нашел довольно удобный способ решения.
      У вас в таблице появляется пункт меню "Переходы", где есть два подпункта - перейти вперед и перейти назад. После этого появляется всплывающее окно, где вы вводите на сколько листов вперед/назад вы хотите перейти. Если вы хотите перейти дальше, чем последний лист (например вы ввели 10, хотя у вас всего 9 листов), то вы попадете на последний лист. И наоборот.
      Сам код ниже:

      function onOpen() {
        var sheet = SpreadsheetApp.getActiveSpreadsheet();
        var entries = [{name : "Перейти вперед",functionName : "goNext"},
                       {name : "Перейти назад",functionName : "goPrev"}];
        sheet.addMenu("Переходы", entries);
      };
      function goNext(){
        letsGo(1);
      };
      function goPrev(){
        letsGo(-1);
      };
      
      function showPrompt(){
        var ui = SpreadsheetApp.getUi();
        var result = ui.prompt(
          'Введите количество листов:',
          ui.ButtonSet.OK_CANCEL);
        var button = result.getSelectedButton();
        var text = result.getResponseText();
        if (button == ui.Button.OK) {
          return Number(text);
        } else return -1;
      };
      
      function letsGo(direction){
        var step = showPrompt();
        if (typeof(step) != NaN && step > 0){ // Если мы ввели число больше 0
          var pos;
          var sheets = SpreadsheetApp.getActive().getSheets();
          var current = SpreadsheetApp.getActive().getActiveSheet().getIndex();
          var length = sheets.length;
          if (direction == 1 && current+step > length) pos = length-1; // Если мы вылазим за правую границу листов при переходе
          else if (direction == -1 && current-step < 0) pos = 0; // Если мы вылазим за левую границу листов при переходе
          else pos = current+step*direction-1;
          sheets[pos].setActiveSelection("A1");
        }
      };
      
      • Соотв. если вы знаете на какой именно номер листа вы хотите перейти, то скрипт немного изменится:

        function onOpen() {
          var sheet = SpreadsheetApp.getActiveSpreadsheet();
          var entries = [{name : "Перейти на указанный лист",functionName : "letsGo"}];
          sheet.addMenu("Переходы", entries);
        };
         
        function showPrompt(){
          var ui = SpreadsheetApp.getUi();
          var result = ui.prompt(
            'Введите на какой лист перейти:',
            ui.ButtonSet.OK_CANCEL);
          var button = result.getSelectedButton();
          var text = result.getResponseText();
          if (button == ui.Button.OK) {
            return Number(text);
          } else return -1;
        };
         
        function letsGo(){
          var step = showPrompt();
          if (typeof(step) != NaN && step > 0){ // Если мы ввели число больше 0
            var pos;
            var sheets = SpreadsheetApp.getActive().getSheets();
            var length = sheets.length;
            if (step > length) pos = length-1; // Если мы вылазим за правую границу листов при переходе
            else pos = step-1;
            sheets[pos].setActiveSelection("A1");
          }
        };
        
  36. barn4k , спасибо Вам за готовое решение . Скрипт работает реально очень хорошо. Складывается такое ощущение , что гугул таблица была укомплектована пункт меню "Переходы" разработчиком. Оригинальное и удобное решение добавить в меню- "Переходы". Как для меня, то второй скрипт более удобный , но первый хорош для беглого просмотра листов. Одним словом все супер. Хотел уточнить если использовать не порядковый номер листа , а его название то скрипт наверно совсем поменяется? Если взять Ваш пример с фруктами , то таблицы будут находится на двух разных листах , а название одного листа "Фрукт" , а другого "Цвет фрукта" . Еще раз Вам спасибо.

    • Не сказать, что скрипт сильно меняется, но я решил просто объединить эти варианты. Теперь есть четыре варианта перехода: вперед или назад на указанное кол-во листов, на указанный номер листа и на указанное имя листа.

      function onOpen() {
        var ui = SpreadsheetApp.getUi();
        ui.createMenu('Переходы')
        .addItem('Перейти вперед', 'goNext')
        .addItem('Перейти назад', 'goPrev')
        .addItem('Перейти по номеру листа', 'goListNumber')
        .addItem('Перейти по имени листа', 'goListName')
        .addToUi();
      };
      
      function goNext(){
        letsGo("next");
      };
      function goPrev(){
        letsGo("prev");
      };
      function goListNumber(){
        letsGo("number");
      };
      function goListName(){
        letsGo("name");
      };
      
      function showPrompt(type){
        var ui = SpreadsheetApp.getUi();
        var title = "";
        switch (type){
          case "next": title = "Укажите на сколько листов перейти вперед"; break; 
          case "prev": title = "Укажите на сколько листов перейти назад"; break; 
          case "number": title = "Укажите номер листа, на который перейти"; break; 
          case "name": title = "Укажите имя листа, на который перейти"; break; 
          default: break;
        }
        var result = ui.prompt(
          title,
          ui.ButtonSet.OK_CANCEL);
        var button = result.getSelectedButton();
        var text = result.getResponseText();
        if (button == ui.Button.OK) {
          return text;
        } else return -1;
      };
      
      function letsGo(type){
        var value = showPrompt(type);
        var flag = false;
        var direction;
        switch (type){
          case "next": direction = 1; break;
          case "prev": direction = -1; break;
          case "number": case "name": flag = true; break;
          default: break;
        }
        var sheets = SpreadsheetApp.getActive().getSheets();
        var length = sheets.length;
        var pos;
        if (!flag && typeof(Number(value)) != NaN && Number(value) > 0){ // Если мы ввели число
          value = Number(value); // Конвертируем строку в число
          var current = SpreadsheetApp.getActive().getActiveSheet().getIndex();
          if (direction == 1 && current+value > length) pos = length-1; // Если мы вылазим за правую границу листов при переходе
          else if (direction == -1 && current-value <= 0) pos = 0; // Если мы вылазим за левую границу листов при переходе
          else pos = current+value*direction-1;
          sheets[pos].setActiveSelection("A1");
        }
        if (flag && type == "number"){
          if (typeof(Number(value)) != NaN && Number(value) > 0){ // Если мы ввели число больше 0
            value = Number(value); // Конвертируем строку в число
            if (value > length) pos = length-1; // Если мы вылазим за правую границу листов при переходе
            else pos = value-1;
            sheets[pos].setActiveSelection("A1");
          }
        }
        if (flag && type == "name"){
          var i;
          for (i=0; i<length; i++){
            if (sheets[i].getName() == value) {
              sheets[i].setActiveSelection("A1");
              break;
            }
          }
        }
      };
      
  37. Ответить
    Денис

    Сдраствуйте. Помогите пожалуйста, нужно чтоб таблица запомнила предыдущие числа в столбце и как это сделать не пойму. Попробую обьяснить что мне нужно в виде формулы.
    У нас есть (x)-дни недели, (y)-недельный результат и (z)-месячный результат. Выглядит это примерно так. x+x+x+x+x+x+x=y-нужо чтоб таблица запомнила это число.
    Z=y (то число что запомнилось)-x-x-x (другие ежедневные результаты второй недели) находим разницу в сумме и добовляемся его к Z (чтоб к z добавлялось новое значение результата y). Попробую и пр другому ) есть 7 столбцов (дни недели) есть 1 столбец (сумма результатов тех 7 дней, 1 неделя) и есть 1 столбец (месячный результат) нужно чтоб когда меняешь чисто в строке с определённым днем оно добавлялось к месяцу, так как столбец неделя будет прыгать в зависимости от результатов дня. А мне надо чтоб столбец месяц добовляла каждое новое число не вычитывая Старое.

    • Добрый день!

      Если я правильно вас понял, то скрипт будет выглядеть таким образом:

      function evaluateMonth(){
        var sheet = SpreadsheetApp.getActive();
        var monthCell = sheet.getRange("I2"); // Ячейка с месяцем
        var weekCell = sheet.getRange("H2"); // Ячейка с неделей
        var monthData = monthCell.getValue();
        var Days = sheet.getRange("A2:G2").getValues(); // Ячейки дней
        var weekData = Days[0][0]+Days[0][1]+Days[0][2]+Days[0][3]+Days[0][4]+Days[0][5]+Days[0][6];
        weekCell.setValue(weekData);
        monthCell.setValue(monthData+weekData);
      };
      
      function onEdit(e) { // функция добавления значений в строку. Срабатывает автоматически, когда какое-то действие c ячейками происходит
        var sheet = SpreadsheetApp.getActive();
        var range = e.range;
        var newValue = e.value;
        var oldValue = e.oldValue;
        var column = range.getColumn();
        var row = range.getRow();
        if (column > 0 && column <8 && row ==2 && typeof(newValue) != NaN){ // Если изменения в ячейках A2:G2
          evaluateMonth();
        }
      };
      

      При этом достаточно обновлять информацию в любых из 7 ячеек дней, ячейка с неделей и месяцем автоматически рассчитаются и обновятся.

      • Ответить
        Денис

        Спасибо, попробую и и отпишу.

  38. Добрый день, barn4k!
    Не могу понять, как написать скрипт. Задача такая: у меня есть таблица, которая разделена на блоки. Сущность блока:
    Название отдела1 - сумма по отделу1 по показателю1 - среднее по отделу1 по показателю2 - сумма по отделу1 по показателю3
    сотрудник1 - данные по сотруднику1 по всем 3-м показателям
    сотрудник2 - данные по сотруднику2 по всем 3-м показателям
    .......
    Название отдела2 - сумма по отделу2 по показателю1 - среднее по отделу2 по показателю2 - сумма по отделу2 по показателю3
    сотрудник1 - данные по сотруднику1 по всем 3-м показателям
    сотрудник2 - данные по сотруднику2 по всем 3-м показателям
    .....

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

    Надеюсь, понятное объяснение) Сломала голову, как это сделать. я чайник, как вы поняли. Заранее спасибо за помощь

    • Добрый день!

      На самом деле, довольно неудобная таблица для работы. Если можно поменять структуру таблицы, то можно было бы обойтись без каких-либо скриптов, а только формулами.

      Например в таком виде:
      Название отдела1 - сумма по отделу1 по показателю1 - среднее по отделу1 по показателю2 - сумма по отделу1 по показателю3
      Название отдела2 - сумма по отделу2 по показателю1 - среднее по отделу2 по показателю2 - сумма по отделу2 по показателю3
      Название отделаN - сумма по отделуN по показателю1 - среднее по отделуN по показателю2 - сумма по отделуN по показателю3
      ----
      Сотрудники | Отдел | данные по сотруднику
      сотрудник1 | отдел сотрудника | данные по сотруднику1 по всем 3-м показателям
      сотрудник2 | отдел сотрудника | данные по сотруднику2 по всем 3-м показателям
      сотрудникM | отдел сотрудника | данные по сотрудникуM по всем 3-м показателям
      т.е. будет два функциональных блока - статистика по отделам и статистика по сотрудникам. При этом можно легко добавлять/изменять/удалять сотрудников и обновлять данные отделов без дополнительных действий (https://sonikelf.ru/attach/img/3CdOKJy.png).

      Если же формат должен остаться как был, то скрипт ниже:

      function onOpen() {
        var ui = SpreadsheetApp.getUi();
        ui.createMenu('Функции')
        .addItem('Обновить расчеты', 'doCount')
        .addToUi();
      };
      
      function doCount(){
        var sheet = SpreadsheetApp.getActive().getActiveSheet();
        var rows = sheet.getLastRow();
        var cols = sheet.getLastColumn();
        var range = sheet.getRange(2, 1, rows, cols);
        var valuesAR = range.getValues();
        var sheetname = sheet.getName();
        var orgunit = ["Бухгалтерия", "Администрирование", "Менеджмент", "Дирекция"];
        var i;
        var sum1=0, sum2=0, sum3=0, c=0, values, unitID=-1;
        for (i=0; i<rows-1;i++){
          if (valuesAR[i][0]){ // Если ячейка не пустая
            if (orgunit.indexOf(valuesAR[i][0]) == -1) { // Если не отдел
              c++;
              sum1+=valuesAR[i][1];
              sum2+=valuesAR[i][2];
              sum3+=valuesAR[i][3];
              if (i == rows-2){ // Если конец списка
                values = [[sum1, sum2/c, sum3]];
                sheet.getRange(2+unitID, 2, 1, cols-1).setValues(values);
              }
            } else { // Если отдел
              if (unitID != -1){
                values = [[sum1, sum2/c, sum3]];
                sheet.getRange(2+unitID, 2, 1, cols-1).setValues(values);
                unitID = i;
                c=0;
                sum1=0;
                sum2=0;
                sum3=0;
              } else unitID = i;
            }
          }
        }
      };
      

      После добавления скрипта вы запускаете скрипт "onOpen" (либо обновляете страницу с вашей таблицей) и в пункте меню "Функции" выбираете функцию "Обновить расчеты" (https://sonikelf.ru/attach/img/3CdQBPw.png).

      • спасибо! таблица неудобная, так как это только её часть)
        а если у меня больше отделов будет, то мне их все надо перечислить в строке 15?

      • чтобы проверить свое предположение, я добавила в 15 строку еще пару отделов, и теперь мне выдается ошибка в строке 27 (Неправильная ширина диапазона: указано 5, должно быть 6 ). Добавила сюда еще цифру *sh.getRange(2+unitID, 3, 2, 1, cols-1)* ==> ошибка *Не удается обнаружить метод getRange(number,number,number,number,number)*

        что не так) моя логика не подтвердилась)

        • Достаточно только добавить отделы в 15 строке:
          например добавить отдел "Склад":

          var orgunit = ["Бухгалтерия", "Администрирование", "Менеджмент", "Дирекция", "Склад"];

          Проверьте, что правильно стоят запятые и кавычки.

          У вас диапазоны такие же, как на картинке? (интересует с какой строки и колонки начинают перечисляться отделы. В моем случае отделы идут со второй строки и первой колонки)

          • Elza

            с отделами все верно, проверила.
            а если у меня больше показателей - я добавляю sum4, sum5?
            может, в этом моя ошибка?

          • Elza

            диапазон такой же, как у вас выбран - со второй строки и первого столбца.
            отличие в том, что в таблице 7 столбцов: отдел (под ним id сотрудников) - сотрудники - данные в 5-ти столбцах (5 показателей)

          • barn4k

            Да, дело в этом. Подправил скрипт под новые вводные. Плюс добавил несколько переменных и комментариев

            function onOpen() {
              var ui = SpreadsheetApp.getUi();
              ui.createMenu('Функции')
              .addItem('Обновить расчеты', 'doCount')
              .addToUi();
            };
            
            function doCount(){
              var sheet = SpreadsheetApp.getActive().getActiveSheet();
              var rows = sheet.getLastRow();
              var cols = sheet.getLastColumn();
              var startrow = 2; //  С какого ряда начинают идти отделы
              var startcol = 3; //  С какой колонки начинают идти показатели
              var checkcols = 5; // Сколько колонок с показателями
              var orgunit = ["Бухгалтерия", "Администрирование", "Менеджмент", "Дирекция", "Склад"];
              var range = sheet.getRange(startrow, 1, rows, cols);
              var valuesAR = range.getValues();
              var i;
              var c=0, values, unitID=-1;
              var sum1=0, sum2=0, sum3=0, sum4=0, sum5=0; // Переменные для каждого показателя
            
              for (i=0; i<rows-1;i++){
                if (valuesAR[i][0]){ // Если ячейка не пустая
                  if (orgunit.indexOf(valuesAR[i][0]) == -1) { // Если не отдел
                    c++;
                    sum1+=valuesAR[i][startcol-1]; // переменная для каждого показателя
                    sum2+=valuesAR[i][startcol+0];
                    sum3+=valuesAR[i][startcol+1];
                    sum4+=valuesAR[i][startcol+2];
                    sum5+=valuesAR[i][startcol+3];
                    if (i == rows-2){ // Если конец списка
                      values = [[sum1, sum2/c, sum3, sum4, sum5]];  // массив со значениями всех переменных показателей
                      sheet.getRange(startrow+unitID, startcol, 1, checkcols).setValues(values);
                    }
                  } else { // Если отдел
                    if (unitID != -1){
                      values = [[sum1, sum2/c, sum3, sum4, sum5]]; // массив со значениями всех переменных показателей
                      sheet.getRange(startrow+unitID, startcol, 1, checkcols).setValues(values);
                      unitID = i;
                      c=0;
                      sum1=0; //Обнуление всех сумм показателей
                      sum2=0;
                      sum3=0;
                      sum4=0;
                      sum5=0;
                    } else unitID = i;
                  }
                }
              }
            };
            
  39. barn4k, а можно как-то с вами связаться вне этого сайта?)

  40. Ответить
    Евгений

    Добрый день! У меня такая проблема: есть таблица в одной графе которой указанна номенклатура а в другой количество данной номенклатуры. Есть номенклатура которая повторяется и не один раз. Мне нужно сделать так что бы в отдельной вкладке автоматически формировался перечень номенклатуры и общее количество данной номенклатуры из мой таблице но уже что бы она фигурировала один раз . Например: есть перечень из 300 позиций и номенклатура "сахар" повторяется 6 раз с разным количеством. Мне надо посчитать общее количество сахара и вывести эту информацию на отдельную вкладку.
    Возможно ли это?

    • В отдельной вкладке в первой ячейке (название) вводите формулу "=UNIQUE('Лист1'!A2:A)", где 'Лист1'!A2:A - лист и диапазон с номенклатурами.
      В следующей колонке (кол-во) вводите формулу "=SUMIF('Лист1'!$A2:$A;A2;'Лист1'!$B2:$B)", где
      'Лист1'!$A2:$A - также, как в первой формуле;
      А2 - ячейка из первой колонки на том же листе;
      'Лист1'!$B2:$B - лист и диапазон с кол-вом номенклатуры.
      Потом эту формулу тянете вниз для всех номенклатур

      • Ответить
        Евгений

        Большое спасибо за оперативный ответ!!!

  41. Ответить
    Дмитрий

    Добрый день!
    Скрипт что Вы писали ранее работает , но он распространяется сразу на все листы и диапазоны. Не могли бы Вы подсказать как сделать так, чтобы он распространялся только на определенный лист и определенный диапазон ( к примеру: после изменения чего либо в столбце F в столбце G пишется дата изменения) ?
    Скрип был такой

    function myFunction() {
    ScriptApp.newTrigger('onEdit').forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet()).onEdit().create()
    }

    function onEdit(e){
    var range = SpreadsheetApp.getActiveRange();
    var a = range.getRow(); // Адрес строки активной ячейки

    if (SpreadsheetApp.getActiveSheet().getRange(a,1).getValues()==''){ //Если дата не заполнена
    SpreadsheetApp.getActiveSheet().getRange(a,1).setValues([[new Date()]]); //Заполняем текущую дату
    }

    }
    }

    • Добавьте после строки "var a" следующие строчки:

      var col = range.getColumn();
      var name = SpreadsheetApp.getActiveSheet().getName();
      if (name == "Лист1" && col == 6){ // Если изменения на листе "Лист1" и в колонке F

      дальше остальной код

      }

    • Ответить
      Андрей

      Добрый день, Дмитрий!
      Задался той же задачей, нужно при изменении значения в одном столбце устанавливать дату изменения в другом, если Вас не затруднит, отпишите пожалуйста, весь скрипт, почему-то не срабатывает этот. Заранее спасибо!

      • Приветствую!
        Поидее, должно работать сразу без включения триггеров. Но если не заработает, то добавьте триггер на эту функцию, "из таблицы", "изменение",

        function onEdit(e){
          var sheet = SpreadsheetApp.getActiveSheet();
          var range = sheet.getActiveRange();
          var row = range.getRow(); // Адрес строки активной ячейки
          var col = range.getColumn();
          var name = sheet.getName();
          if (name == "Лист1" && col == 6){ // Если изменения на листе "Лист1" и в колонке F
            if (sheet.getRange(row , 1).getValues()==''){ //Если дата в колонке 1 не заполнена
              sheet.getRange(row, 1).setValues([[new Date()]]); //Заполняем текущую дату
            }
          }
        };
  42. Ответить
    Денис

    Добрый день! подскажите, для удобства работы дилеров с прайсом, хочу, чтобы при выборе любой ячейки с розничной ценой из диапазона D4:J36 значение этой выбранной ячейки попадало в ячейку АА3 и умножалось на 0,85. Т.е. чтобы в ячейке АА3 сразу показывалась дилерская цена выбранной позиции. С помощью стандартных функций такого не сделать, видимо, а js еще для меня как тайга сибирская для фашиста... Очень буду признателен за вашу помощь!

    • Добрый день!

      Не очень понятно для чего делать именно так. Чем не подходит добавление колонки с дилерскими ценами после колонки с розничными? Колонка АА3 находится далековато и прокручивать горизонтально прайс не очень удобно в любом случае :)

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

  43. Первая часть статьи, обучающая непосредственно процессу, специально для страждущих - https://sonikelf.ru/kak-pisat-skripty-makrosy-i-kod-v-google-scripts-chast-1/

  44. Ответить
    Сергей

    Добрый день! Статься очень полезная, спасибо большое! Но возник вопрос, ну или проблема :)
    Но Google Excel выбивает ошибку в строке 3.
    А именно:
    "var entries = [{name : "Покрасить",functionName : "MakeMeHappy"}];"
    Какая здесь ошибка - не могу понять. Подскажите пожалуйста!

    • Добрый день!
      Покажите скриншот ошибки, может станет понятнее. Должно работать.

  45. Ответить
    Александр

    Добрый день!
    Подскажите, пожалуйста, как сделать скрипт, который переносит данные из ячейки в примечание?
    Вариант№2: берет данные из ячейки А1, выполняет вычисление(=А1*0,5) и выводит информацию в примечание к А1. Заранее спасибо!

    • Ответить
      Александр

      Пока удалось только это:
      function noteSetter() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheets()[0];

      var cell = sheet.getRange("B1");
      cell.setNote(sheet.getRange("A1").getValue());
      };

      Вопрос: как сделать для нескольких столбцов? Спасибо!

      • Добрый день!

        В целом, для нескольких ячеек это делается аналогично:
        function noteSetter() {
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var sheet = ss.getSheets()[0];

        var range = sheet.getRange("B1:B7");
        range.setNotes(sheet.getRange("A1:A7").getValues());
        };

  46. Ответить
    Александр

    Добрый день!
    Можно ли сделать скрытие строки по определенному значению. Например есть столбец, если в нем ставится "Да" или ничего не ставится - строка видна, если ставится "Нет" - строка автоматически скрывается? Спасибо

    • Да, можно. Смотрите мой комментарий на второй странице от 28 сентября 2016 12:31

      • Ответить
        Александр

        Спасибо, близко, но немного не то.
        У меня есть таблица, в которой, с другой стороны, клиент, добавляет в строки товар, если товар есть - ничего не делаем, если нет, ставлю - нет. Но потом, после того как клиент увидел, что я поставил нет, надо эту строку скрыть, чтоб не мешала.
        В идеале так: выпадающий список "Нет" - окрашивается строка красным. "Откл" - строка скрывается

        • А как вы поймете, что клиент увидел? Он после просмотра должен выбрать из выпадающего меню опцию "откл"?

          • Александр

            нет, общение в скайпе

          • Александр

            кстати, это хорошая мысль, я поставил "Нет" строка окрасилась в красный, он ставит "Откл" - строка скрывается - я понимаю что все в порядке

        • Скрипт ниже. Достаточно заменить текст в строчках:
          3 - Название листа
          4 - В какой колонке искать выпадающий список
          5 - С какой строки начать. Обычно актуально если есть заголовки в таблице

          function alarm(){
            var ss = SpreadsheetApp.getActiveSpreadsheet();
            var sheet = ss.getSheetByName("Лист3");
            var checkcolumn = "C";
            var begin = 2;
            var str = "";
            var end = sheet.getLastRow();
            for (var i=begin; i<=end; i++){
              str = sheet.getRange(checkcolumn+i).getValue();
              if (str == "Нет"){
                sheet.getRange("A"+i+":"+checkcolumn+i).setBackground("#cc0000");
              }else if (str == "Откл"){
                sheet.hideRows(i);
              }
            }
          }
          
          • Александр

            Спасибо большое! Это именно то что нужно

  47. Ответить
    Руслан

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

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];

    var cell = sheet.getRange("A3:H1000");

    cell.setBorder(true, true, true, true, true, true)

  48. Ответить
    Антон

    Добрый день! Подскажите пожалуйста как сделать следующее:
    Есть форма ( какой-либо диапазон строк и столбцов), необходимо создавать полный дубликат этой формы нажатием на кнопку "Добавить" или знак "+" .

 

* - комментарии могут появляться не сразу, попасть в спам или быть удалены за несоответствие правилам