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

статьи
интернет

Доброго времени суток, дорогие читатели, вредины, злодеи, доброжелатели и прочие личности. Сегодня мы про Google Scripts, точнее скрипты в таблицах как таковые.

 

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

Я думаю, что очень многие из Вас умеют пользоваться Excel'ем или его аналогом, а некоторые, может, даже и гугловскими таблицами, про которые писали здесь.

Те, кто пользуется диском Google (Google Drive), наверное уже использовали Таблицы (Spreadsheets) и заметили, что по функционалу они немного уступают Экселю, но тем не менее это всё ещё мощный инструмент.

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

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

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

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

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

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

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

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

список значений - Google Docs, Google Drive, Google Scripts: как писать скрипты, макросы и код - скриншот 1

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

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

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

создание макроса -  Google Docs, Google Drive, Google Scripts: как писать скрипты, макросы и код - скриншот 2

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

создание скрипта -  Google Docs, Google Drive, Google Scripts: как писать скрипты, макросы и код - скриншот 3

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

Редактор Скриптов -  Google Docs, Google Drive, Google Scripts: как писать скрипты, макросы и код - скриншот 4

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

Нам нужно:

  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 добавляет меню "Скрипты" к таблице при открытии оной. И выглядит это дело так:

добавление своего меню -  Google Docs, Google Drive, Google Scripts: как писать скрипты, макросы и код - скриншот 5

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

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, заходим в меню "Скрипты" и выбираем опцию "Покрасить". Смотрим, как наши фрукты обрели жизнь цвета :)

В общем-то на этом всё. Но не совсем.

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

Скрипты и макросы таблиц Google, дополнение

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

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

Они будут приходить в случае, если скрипт выполнялся с ошибками.

добавление триггеров -  Google Docs, Google Drive, Google Scripts: как писать скрипты, макросы и код - скриншот 6

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

результат -  Google Docs, Google Drive, Google Scripts: как писать скрипты, макросы и код - скриншот 7

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

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

Послесловие

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

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

Продолжения раз-два-готовим и три. Ну и комментарии конечно содержат много вкусного.

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

Хотите знать и уметь, больше и сами?

Мы предлагаем Вам обучение на нашем проекте. Компьютеры, сайты, администрирование, сервера, сети, SEO и другое. Не является рекламой. Предложение от sonikelf.ru

Записаться
Sonikelf's Project's логотип Космодамианская наб., 32-34 Россия, Москва (916) 174-8226

А что думаете Вы?

10000
  Подписаться  
Уведомить о
Sonikelf

Первый типа :)

HardDisk

Второй :)

Sonikelf

Почти первый :)

cthdth

Третий :)

Sonikelf

И то хорошо :)

ProQ.Lucky

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

Sonikelf

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

ProQ.Lucky

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

DmN

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

Sonikelf

Да уж :)

SerGe

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

Sonikelf

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

SerGe

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

Сергей

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

Sonikelf

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

том

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

Sonikelf

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

Stijit

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

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

Дмитрий

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

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

Серега

Круть! А скажите, может ли быть такая фича:
Нужен скрипт для 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];»

Tom

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

Кирилл

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

Михаил

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

Дмитрий

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

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

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

Sonikelf

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

LordBeheliar

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

Sonikelf

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

LordBeheliar

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

Раиль

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

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

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

Tim

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

Александр

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

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

Спасибо.

Юра

Здравствуйте!.
Как написать скрипт, что бы он срабатывал когда в ячейку А№(№-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()]]); //Заполняем текущую дату
}

}

Виталий

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

Виталий

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

Виталий

Подскажите пожалуйста, вписал ваш скрипт, все замечательно работает, но есть вопрос.
Скрипт работает по всей колонке номер 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) // если нету, то оно добавляется
}
}
};

Виталий

Спасибо! :)

Илья

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

Илья

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

Илья

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

Андрей

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

Планируемое время 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")). Как использовать функцию РАЗНДАТ в скрипте?

Milla

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

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

autor

Подскажите пожалуйста, в какую строку вставлять 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);
}
}

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

autor

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

Илья

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

Анна

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

Igor

Здравствуйте !
Если можно прошу помочь. Как при помощи скрипта можно быстро путешествовать по книге с большим количеством страниц. К примеру : Вы находитесь на Лист 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)
но как то не красиво а , как скриптом решить не знаю.
Подскажите .

Igor

barn4k , спасибо Вам за готовое решение . Скрипт работает реально очень хорошо. Складывается такое ощущение , что гугул таблица была укомплектована пункт меню "Переходы" разработчиком. Оригинальное и удобное решение добавить в меню- "Переходы". Как для меня, то второй скрипт более удобный , но первый хорош для беглого просмотра листов. Одним словом все супер. Хотел уточнить если использовать не порядковый номер листа , а его название то скрипт наверно совсем поменяется? Если взять Ваш пример с фруктами , то таблицы будут находится на двух разных листах , а название одного листа "Фрукт" , а другого "Цвет фрукта" . Еще раз Вам спасибо.

Денис

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

Elza

Добрый день, 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-м показателям
.....

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

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

Elza

barn4k, а можно как-то с вами связаться вне этого сайта?)

Евгений

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

Дмитрий

Добрый день!
Скрипт что Вы писали ранее работает , но он распространяется сразу на все листы и диапазоны. Не могли бы Вы подсказать как сделать так, чтобы он распространялся только на определенный лист и определенный диапазон ( к примеру: после изменения чего либо в столбце 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()]]); //Заполняем текущую дату
}

}
}

Андрей

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

Денис

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

Sonikelf

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

Сергей

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

Александр

Добрый день!
Подскажите, пожалуйста, как сделать скрипт, который переносит данные из ячейки в примечание?
Вариант№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());
};

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

barn4k

Добрый день!

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

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

Александр

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

barn4k

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

Александр

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

barn4k

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

Александр

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

Александр

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

barn4k

Скрипт ниже. Достаточно заменить текст в строчках:
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);
    }
  }
}
Александр

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

Дмитрий

Добрый день.

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

Руслан

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

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

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

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

Антон

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

Дмитрий

Здравствуйте, большое спасибо за урок. Нашёл небольшую ошибку в коде:
строка 12 "<" нужно заменить на "<". На правах новичка немного помучался.

Подскажите, как можно реализовать вот такую задачу:

"Есть 1 лист – со строками различных задач, на этом листе есть столбец с исполнителями.
Есть 2 лист – с соответствием исполнителей и аккаунтов гугловских.

Нужен скрипт, который позволяет раздать права на редактирование только ответственным за задачу (плюс указанным в отдельном места админам типа меня и прочих)"

Спасибо!

barn4k

Добрый день!

Спасибо за ошибку, видимо пропустил когда вычищал косяки автопарсера.

Я так полагаю, уже решили задачу в вк :)

Виталий

Добрый день! Спасибо за очень полезный материал. Если можно, подскажите как можно решить следующую задачу с помощью скрипта:
Есть исходная таблица Гугл, которая постоянно обновляется информацией.Нужно для клиента (например Тест_Клиент) импортировать в отдельную таблицу выборочную информацию, которая касается конкретного него - выбрать данные из определенных колонок - код/марка/ID/объект/клиент/дата монтажа/дата демонтажа/. Причем на первый лист новой таблицы (Установлено) импортировать данные, в которых стоит дата монтажа, во второй лист (Демонтировано) - импортировать данные, в которых стоит дата монтажа и демонтажа (в том числе перемещать с листа Установлено на лист Демонтировано) и на третий лист (Склад) ипортировать данные, напротив которых в дате монтажа и демонтажа пустые ячейки. На 99% эту задача была решена формулами и фильтрами, но когда начали добалятся новые данные в исходную таблицу - выяснилось что нужно обновлять фильтры(: и все усилия пошли даром.

Елена

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

елена

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

Шахром

Добрый день! Подскажите пожалуйста, как можно сохранить историю в Гугл таблице. Т.е. чтобы я зашёл в таблицу и продолжал с той строки которой в последний раз редактировал. Заранее благодарю Вас за помощь. Это для меня очень важно).

barn4k

Не припомню у гугла такой функции. А оно для чего вам?

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

Шахром

Добрый день!
Подскажите пожалуйста, возможно ли написать скрипт с которой вышла бы одна вкладка в контекстном меню и с которым, я бы нажал на него и мой курсор возвращался в последнюю редактированную мною строку?

barn4k

Добавьте следующий код:

function onOpen() { // Добавляем меню при открытии документа
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Мои скрипты') // Название меню
  .addItem('Перейти к ячейке', 'goTo') // Название пункта меню
  .addToUi();
}

function setTrigger() { // Создаем триггер, который выполняет функцию myFunction при редактировании любой ячейки
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger("myFunction").forSpreadsheet(ss).onEdit().create();
}

function myFunction() { // Получаем текущий лист и ячейку и записываем это в свойства Пользователя
  var sheet = SpreadsheetApp.getActiveSheet();
  var sName = sheet.getName();
  var currentCell = sheet.getActiveCell().getA1Notation();
  var userProperties = PropertiesService.getUserProperties();
  userProperties.setProperty("mySheetName", sName);
  userProperties.setProperty("myCell", currentCell);
}

function goTo() { // Переходим в последнюю редактированную ячейку на основе информациии
  var userProperties = PropertiesService.getUserProperties(); // записанной в свойствах пользователя
  var lastModifiedSheet = userProperties.getProperty("mySheetName");
  var lastModifiedCell  = userProperties.getProperty("myCell");
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName(lastModifiedSheet).getRange(lastModifiedCell).activate();
}

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

Шахром

Большое спасибо :).

Stas

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

function WhereColor (){

var sheet = SpreadsheetApp.getActiveSpreadsheet(); // Наш активный лист
var NumRows = sheet.getNumRows(); // узнать кол-во рядов на листе
var NumColumns = sheet.getNumColumns(); // узнать кол-во столбцов на листе
var Data = sheet.getRange(1, 1, NumRows, NumColumns).getValues(); // получить значение каждой ячейки
var color = "#00FF00"; // Цвет ячейки
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]; // Заодно добавляем значение ячейки в сумму
}
}
}
}

Stas

Работает гуд. Спасибо. Но вопрос как теперь подсчет(количество) мне вынести в нужную мне ячейку?

barn4k

Перед последним знаком "}" допишите:
sheet.getRange("C1:D1").setValues([["Кол-во:", counter]]);

где C1:D1 - это ячейки с описанием и значением параметра counter.

Кирилл

Здравствуйте! Вопрос по скрипту в гугл диске. Подскажите, пожалуйста. Нужно сделать скрипт, который будет складывать ячейки из диапазона B1:B20 за определенную дату (которая прописывается в столбце А). Например в ячейку А1 внесена дата - 01.01.17. Нужно посчитать сумму ячеек в столбце B до того ряда, в котором в столбце А будет новая дата 02.01.17.
Эта сумма будет прописываться в ячейке С1 (напротив ячейки с 01.01.17). За следующий день сумма прописывается напротив ячейки с 02.01.17.
Сложность в том, что количество строк в каждой дате разное. Спасибо! Надеюсь, понятно объяснил.

barn4k

Добрый день!
На самом деле данную операцию легко сделать без скриптов.
Введите в ячейке D1 формулу "=UNIQUE(A1:A)"
Далее введите в ячейку C1 формулу "=SUMIF(A$1:A;D1;B$1:B)"

Растяните ячейку C1 до последнего значения из ячейки D. Вот и всё :)

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

Кирилл

Спасибо за быстрый ответ! Попробую!

Кирилл

Почти нужное решение. Нюанс только в том, что сумма должна быть в ячейке напротив ячейки "A..." с новой датой а не в первых ячейках, равных уникальной дате в ячейке "D...".

barn4k

Нужен пример таблицы, что требуется.

Кирилл

10.12.17 | 5 | 15 |
| 5 | |
| 5 | |
11.12.17 | 6 | 7 |
| 1 | |
12.12.17 | 3 | 5 |
| 2 | |

Вот примерно так. Спасибо.

barn4k

Странный формат таблицы. Ну да ладно. В таком варианте код ниже (последнюю строку он определяет сам на основе колонки А):

function myFunction() {
  var ss = SpreadsheetApp.getActiveSheet();
  var lastrow = ss.getLastRow();
  var range = ss.getRange("A1:B"+lastrow);
  var array = range.getValues();
  var sum=0;
  var c=-1;
  for (var i=0; i<array.length; i++){
    if (array[i][0].constructor.name == 'Date' ) {
      if (c != -1) {
        ss.getRange(c+1, 3).setValue(sum);
      }
      sum = array[i][1];
      c=i;
    }
    else if (array[i][0].constructor.name == 'Number') {
      sum += array[i][0];
    }
  }
  ss.getRange(c+1, 3).setValue(sum);
}
Михаил

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

barn4k

Скрипт ниже. Для изменения значений смотрите комментарии "Обратить внимание". Скрипт будет отрабатывать каждый раз при открытии файла.

function onOpen() { // Запускаем скрипт при открытии документа
  myFunction()
}
 
function myFunction() { // Получаем текущий лист и ячейку и записываем это в свойства Пользователя
  var ss = SpreadsheetApp.getActiveSheet();
  var me = Session.getEffectiveUser();
  var datarange = "E";       // Обратить внимание. Колонка с датами
  var start = 1;             // Обратить внимание. С какой строки начинать сравнение (если есть заголовки например)
  var end = ss.getLastRow();
  var backColor = "#dddddd"; // Обратить внимание. Цвет закрашиваемой строки
  var range;
  for (var i=start; i<=end; i++){
    var flag = false;
    range = ss.getRange(datarange+i);
    if (dateDiffInDays(range.getValue())>3){ // если разница между сегодняшним днем и датой из ячейки больше трех дней
      var protectedRange = ss.getRange(i, 1, 1, ss.getLastColumn()) // записываем весь ряд в переменную
      var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
      if (protections.length == 0) { // Если защищенных диапазонов нет
        var protection = protectedRange.protect().setDescription('Time ended'); // защитить весь ряд
        protection.addEditor(me);
        protection.removeEditors(protection.getEditors());
        if (protection.canDomainEdit()) {
          protection.setDomainEdit(false);
        }
      }else{ // Если есть
        for (var j = 0; j < protections.length; j++) { // Проверяем если этот ряд не защищен ранее
          var protectionid = protections[j];
          if (protectionid.getRange().getRow() == range.getRow()) { // сравниваем каждый ряд из списка защищенных рядов с текущим защищаемым рядом
            flag = true; 
          }
        }
          if (flag == false){ // Если данный ряд не был ранее защищен
            protectedRange.setBackground(backColor); // закрасить строку целиком
            var protection = protectedRange.protect().setDescription('Time ended'); // защитить весь ряд
            protection.addEditor(me);
            protection.removeEditors(protection.getEditors());
            if (protection.canDomainEdit()) {
              protection.setDomainEdit(false);
            }
            break; // и выйти из цикла, дальше нет смысла сравнивать защищенные ряды
        }
      }
    }
  }
}
// функция сравнения дат
function dateDiffInDays(a) {
  var _MS_PER_DAY = 1000 * 60 * 60 * 24; // сколько миллисекунд в одном дне = 1000мс. * 60сек. * 60мин. * 24ч.
  var utc2 = Date.UTC(a.getFullYear(), a.getMonth(), a.getDate());
  return Math.floor((Date.now() - utc2) / _MS_PER_DAY);
}
Михаил

Спасибо большое, зарабоооооотало!

barn4k

Не за что :)

Алена

Добрый день.
Помогите, пожалуйста, со скриптом. Есть исходная таблица для редактирования, в которой постоянно меняются значения (цена). А есть отдельная таблица-прайс со ссылками на определенные колонки из исходной таблицы, в том числе и на колонку с ценами. Как можно во второй таблице-прайсе при изменении цены в исходной таблице (соответственно изменится цена и во второй, колонка С) в колонке, например, F делать пометки о произошедших изменениях, желательно вычислять значения на сколько изменилась цена?

barn4k

Добрый день!

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

Алена

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

barn4k

Можно поступить следующим образом:
1) на первоначальной таблице с ценами добавить такой скрипт:

function onEdit(e) {
  var range = e.range;
  var oldV = e.oldValue;
  var newV = e.value;
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var col = range.getColumn();
  var row = range.getRow();
  if (col == 3 && row > 1) { // Если колонка с ценами и ряд не с заголовком
    ss.getRange(row, col+1).setValue(oldV); // то в соседнюю ячейку поместить старую цену
    ss.getRange(row, col+2).setValue(newV-oldV); // а через одну разницу в ценах
  }
}

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

укажите нужную колонку (col) с ценами в строчке if (col == 3 && row > 1), у меня это колонка 3 - "C"

2) Во второй таблице с ценами в первой ячейке напишите формулу:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1bxaPbJ65DgHwL_Zlg28JnmixjXYZX21GtBAb2jhB_h0/edit"; "A1:E")

где первый аргумент - это ссылка на исходную таблицу, а второй аргумент - это диапазон, который нужно импортировать (в данном случае у меня это диапазон A1:E, куда входят колонки в таком порядке: артикул/название/цена/старая цена/динамика)

Алена

Благодарю!!
Отличное решение. Все работает. Единственное но.... так как колонка с ценами - это формула, разница цен не высчитывается в итоге(( Ошибка: #NUM!

barn4k

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

function setTrigger(){
  var sheet = SpreadsheetApp.getActive();
  ScriptApp.newTrigger("compare")
  .forSpreadsheet(sheet)
  .onChange()
  .create(); 
  copyPrice();
}

function compare() {
  var mainsheet = "Лист1"; // Название листа с ценами
  var copiedsheet = "copied"; // Название технического листа
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainsheet);
  var copyss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(copiedsheet);
  var st = 2; // С какой строки начинать. Для пропуска заголовка
  var en = ss.getLastRow();
  var ColPrice = "E"; // Колонка с ценой
  var edited = false;
  for (var i=st; i<=en; i++){
    var copyCellval = copyss.getRange(i, 1).getValue();
    var cell = ss.getRange(ColPrice+i);
    var cellval = cell.getValue();
    if (copyCellval != cellval) {
      cell.offset(0, 1).setValue(copyCellval);
      cell.offset(0, 2).setValue(copyCellval-cellval);
      edited = true;
    }
  }
  if (edited) copyPrice();
}

function copyPrice(){
  var mainsheet = "Лист1"; // Название листа с ценами
  var copiedsheet = "copied"; // Название технического листа
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainsheet);
  var copyss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(copiedsheet);
  var ColPrice = "E"; // Колонка с ценой
  var st = 2; // С какой строки начинать. Для пропуска заголовка
  var en = ss.getLastRow();
  for (var i=st; i<=en; i++){
    var copyCell = copyss.getRange(i, 1);
    var cellval = ss.getRange(ColPrice+i).getValue();
    copyCell.setValue(cellval);
  }
}
Алена

Супер! Все работает. Очень удобно. Мою задачу решает.
Благодарю, barn4k!!! Всего Вам самого доброго!

Sonikelf

Немного обновили статью и вытащили на главную, ибо вроде нужно :)

Alina

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

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

Позже писали:
"...Но написав ее в таков виде var pole3 = ss.getRange(rows,7).setFormula("=IF(D10>0;"Да";"Нет")"); я получил такой ответ "После списка аргументов отсутствует ")"....."
После рассуждения, человек пришел к выводу, что ему нет необходимости привязываться к конкретной ячейке, поэтому нашел другой и более подходящий ему способ решения задачи.
Но, к сожалению, у меня возникла необходимость, привязать логическую формулу ЕСЛИ к конкретной ячейке. Пытаюсь найти ответ в интернете, но всё в пустую, никто об этом не пишет.

На данный момент, из всей информации, которую мне удалось найти в интернете, строка приняла такой вид:
cell.setFormula("=IF(L2="ПД";HYPERLINK("http://testlink.ua/?id_number="&J2;"ПД");""));
Но в результате выдаёт ту же ошибку: "После списка аргументов отсутствует ")". "
Буду благодарна за любую помощь.

barn4k

Добрый день!
У вас ошибка в самой формуле. Неверно трактуются кавычки. Поменяйте её на эту:
cell.setFormula('=IF(L2="ПД";HYPERLINK("http://testlink.ua/?id_number="&J2;"ПД");)');

Alina

В каких только вариациях я не меняла эти кавычки.. но похоже этот вариант пропустила. Всё работает, огромное Вам спасибо)

barn4k

Обращайтесь :)
С кавычками частая проблема, когда есть некоторая формула или строка, в которой есть эти самые кавычки. В этом случае делают так называемое экранирование кавычек (escaping quotes). Мы всю строку обрамляем в одинарные кавычки, а внутри них уже используем двойные так, как хотим :)

Антон

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

barn4k

Добрый день!

Можно вставить изображения кнопок (которые можно нарисовать прямо в гугле), либо импортировать в таблицу, потом расставляете кнопки, выбираете каждую кнопку (у нее справа появятся три точки), кликаете на точки и выбираете "Назначить скрипт". Для кнопок "Наверх" пишете toStart, для кнопок оглавления пишете названия вида toCp#, где # номер главы.

В редакторе скриптов создаете такие функции:

function toStart(){ // Для всех кнопок "Наверх"
  var ss = SpreadsheetApp.getActiveSheet();
  ss.setActiveSelection("A1");
}

function toCp1(){ // Для кнопки первой главы
  var ss = SpreadsheetApp.getActiveSheet();
  ss.setActiveSelection("A100");
}

function toCp2(){ // Для кнопки второй главы
  var ss = SpreadsheetApp.getActiveSheet();
  ss.setActiveSelection("A200");
}

function toCp10(){ // Ну и так далее
  var ss = SpreadsheetApp.getActiveSheet();
  ss.setActiveSelection("A1000");
}
Антон

Спасибо! Круто! А подскажите, есть команда, чтоб не на ячейку был переход, а скажем, другой лист или сайт?

Дмитрий

Добрый день.

Прошу помощи, есть IMPORTRANGE, но он переносит данные в другую таблицу без переноса цвета. К примеру есть таблица (Лист1) в этом листе есть разные диапазоны ячеек которые выделены цветом, при переносе всего Листа1 в другую таблицу с помощью IMPORTRANGE, не переноситься цвет. Может есть возможность сделать это с помощью скрипта ?

Спасибо большое.

Людмила

Добрый день!
Огромное спасибо за статьи, некоторые вещи прояснились:)
Собственно, вопрос. Имеется таблица с данными по гербарным образцам (название вида, дата сбора, местообитание и прочее). Можно ли создать скрипт, который будет вытаскивать только нужные данные в нужном порядке в google docs?

Людмила

Спасибо, посмотрю!

Александр

Добрый день. Огромное спасибо за множество примеров! Но читая пользователей к концу комментариев, складывается впечатление "Мне должны помочь, я тут главный", даже не понимая, что это огромная и сложная работа, необходимая серьезных знаний. Уважаемый barn4k, есть несколько вопросов по google java script, само собой не за бесплатно. Если найдется немного времени - напишите на почту ap@sds-lab.net

Дмитрий

Добрый день.

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

Посмотрите примеры:
Вот это Google Sheets (https://drive.google.com/open?id=1NCHrLbtmcCygzWVCEIDfCV56gWRNrZL_7cICZkyPcUU) в которой заполняется и храниться информация. В правой части таблицы, на каждой строчке, есть выпадающий список в котором есть пункт "Заполнить договор", при нажатии на эту надпись, данные переносятся в "Google Docs", при нажатии "Заполнить договор" во второй строчке, данные будут браться со второй строчки и т.д. Желтым - информация которая не переноситься в договор.

А вот Google Docs (https://drive.google.com/open?id=11YTQFf80GUauav54ISf4CQDewBq1eBKKgStoDPaEuXg) в который будут вставляться информация. Зеленым это вставляемая информация.

Я читал Выше Ваш пример переноса информации с таблицы в документ, но так и не смог под свою более массовую задачу это сделать.

Благодарю за помощь.

Андрей

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

function getBackgroundColor(rangeSpecification) {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
return sheet.getRange(rangeSpecification).getBackgroundColor();
}

function sumWhereBackgroundColorIs(color, rangeSpecification) {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var range = sheet.getRange(rangeSpecification);
var x = 0;
for (var i = 1; i <= range.getNumRows(); i++) {
for (var j = 1; j <= range.getNumColumns(); j++) {
var cell = range.getCell(i, j);
if(cell.getBackgroundColor() == color)
x += parseFloat(cell.getValue());
}
}
return x;
}

function countCellsWithBackgroundColor(color, rangeSpecification) {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var range = sheet.getRange(rangeSpecification);
var x = 0;
for (var i = 1; i <= range.getNumRows(); i++) {
for (var j = 1; j <= range.getNumColumns(); j++) {
var cell = range.getCell(i, j);
if(cell.getBackgroundColor() == color)
x++;
}
}
return x;
};

barn4k

Добрый день!
Скрипт по подсчету суммы ячеек по цвету - это вы что имеете в виду?

В том скрипте, который у вас, все ок.
Добавьте еще одну функцию и запустите её. Только измените переменные range и applyrange. Результат функции увидите на самом листе.

function start(){
  var ss = SpreadsheetApp.getActive();
  var range = "A1"; // ячейка, у которой взять цвет
  var applyrange = "A1:A8"; // диапазон ячеек, где суммировать или считать кол-во
  var color = getBackgroundColor(range);
  SpreadsheetApp.getUi().alert(
    "Сумма ячеек с цветом " + color + " = " + sumWhereBackgroundColorIs(color, applyrange) + "\n" + 
    "Кол-во ячеек с цветом " + color + " = " + countCellsWithBackgroundColor(color, applyrange)
    );
}
Morgan

Добрый день!

К сожалению, я совсем не знаю js, а поиск на просторах интернета с решением не помог.
Возникла такая задача, необходим скрипт позволяющий сделать изображение появляющееся по наведению на ячейку, на подобие имеющихся примечаний, но не текст, а изображение. Возможно ли реализовать подобное?

Заранее благодарю за ответ.
Всего доброго!