Comments 21
А кто-то создавал web-apps поверх гугл таблиц? Могли бы в кратце поделиться впечатлениями? Для себя буквально недавно открыли google apps script, и были удивленны, что раньше не обращали внимание на столь мощную фичу. Можно создавать полноценные http api, где таблицы играют роль DB. А учитывая, что поддерживаются cross-origin запросы, можно создавать разные single page приложения, где уже в свою очередь данные с таблиц можно хоть в каком виде анализировать, отображать, редактировать. И самое классное это то, что права доступа к таблицам и апи настраиваются раздельно.
Главное, внимательно посмотрите лимиты, которые гугл выставляет для всех скриптов на apps script. Потому что я однажды начал не с этого и потратил лишнее время на написание скрипта, который априори отрезался бы гуглом как слишком долгий — из-за большого набора данных, которые нужно обработать и записать в таблицу.
Нам частенько приходится с помощью IMPORTRANGE собирать данные из многих таблиц (скажем, из 50-ти) и если достаем из каждой таблицы еще и большие массивы данных, то такая формула может начать тормозить. Очень.
Другой пример, у нас таблица на 20к строк и мы на дашборде выводим различные показатели с помощью сложных формул, они обращаются к этой таблице и что-то там высчитывают. Такое тоже может очень быстро начать тормозить, особенно, если в рабочей книге много листов, много данных и много формул.
Как можно это побороть? Пишем скрипт, который по нажатию кнопки (либо по расписание несколько раз в день) вставляет в выбранную ячейку формулу IMPORTRANGE из всех нужных таблиц, данные загружаются и он сразу же заменяет их на значения, убирая формулу. Нашему документу не приходится обновлять постоянно ресурсоемкую формулу, он облегчено вздыхает и начинает работать быстрее.
Под спойлером пример такого скрипта.
function formulasbor()
{
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sh0 = ss.getSheetByName("sh0")
//f - наша глобальная и тормозящая формула
var f = "=sum(a1+a2)"
sh0.getRange("a2:r").clearContent()
sh0.getRange("a2").setFormula(f)
Utilities.sleep(15000)
var last0 = (sh0.getLastRow() + 1)
var range = sh0.getRange("A2:R"+last0)
range.setValues(range.getValues())
}
И я пришёл к подобному решению и кстати оно помогает не только с IMPORTRANGE, но и с IMPORTDATA.
Третий год работает система, которая собирает с 8 файлов (16 разных ответственных за первичку, работают посменно) по 16к строк, потом эти данные суммируются и строится красивый график с 8 сериями за год, и это работает. Я был удивлён, когда увидел как гугл моментально отрисовывает такой наглядный график с тремя тысячами точек. И главное это всё бесплатно и было сделано мной за несколько часов, с учётом создания и раздачи аккаунтов и согласовыванием форм с контрагентами. Построить такую систему на 1С было бы куда дороже.
В прошлом году нам удалось при помощи Google табличек и скриптов собрать систему отчетности для 60-ти человек, со всякими сложными сводными таблицами и графиками, триггерным выполнением скриптов в облаке (даже если файлы закрыты). Тоже бесплатно, оперативно и решениями из коробки. Да и большое удовольствие работать с интерфейсом не обремененным лишними кнопками :)
Кстати, по поводу скрипта — у меня есть еще одна его версия, где скрипт берет ряд формул из одной строки и циклом вставляет эти формулы построчно (каждая строка — посчитанные с помощью importrange показатели из разных док-тов) и заменяет на значения. Такое может помочь если у нас много формул и мы не хотим, чтобы они постоянно обновлялись.
Я еще с 20 века работал с Exсel.
Пользовался очень удобным графическим интерфейсом.
Зная как должен проходить график расчётной функции просто "хватал" график в определенной точке и тянул вверх-вниз, а программа пересчитывала таблицу.
В 21-м веке функцию отрезали в Exell.
Может в Google табличках она сохранена?
Я тоже вспомнил такую возможность, кажется в Office 2003 ещё была.
Google недавно обновил интерфейс редактирования диаграмм, но этой функции так и не появилось
Мне как-то тоже потребовалось от руки рисовать и править графики распределения звонков по времени суток, пришлось писать софтинку для этого. В Excel можно было по очереди значения менять, у меня было удобнее, можно было одним движением нарисовать график мышкой и сразу увидеть таблицу данных, а потом уже подправлять почти как в Excel.
Жаль исходники пропали в недрах ЭКЦ МВД.
Не могу понять, как скриптом вставить сразу несколько формул в двумерный массив (разом, по-формуле-то можно)
Вот здесь пример:
Уже несколько лет использую google для автоматизации всяких мелких задач, не всегда достаточно имеющихся в spreadsheet функций. Вот какие последние функции были созданы, вдруг кому-то пригодится:
- Преобразует адрес в свободной форме в ячейке в координаты и ссылку на карту. Функция разбирает JSON.parse() получаемый UrlFetchApp.fetch() от геокодера яндекса. Используется в нашем неком подобии CRM на гугло таблицах.
- Проверяет наличие на гуглодиске файла договора DriveApp.searchFiles(), если нет файла оповещает по почте ответственного за реестр договоров, который хранится в таблице.
- Анализируем радиосвязи на КВ, получая данные в текстовом виде из программы wsprx. Используются почти все функции из статьи, а так же не указанные здесь очень полезные IMPORTDATA() и SPLIT(). Тут почти всё сделано средствами таблиц, скрипт только обновляет таблицу по триггеру и сообщает, если что-то идёт не так и прохождения долго нет MailApp.sendEmail().
А в какие нибудь квоты упирались? Или может знаете, как много на самом деле гугл дает бесплатно cpu и io?
Упираюсь иногда, но критичных для себя пока не нашёл.
Приходят иногда письма, со сводками сбоев, но при этом всё продолжает работать.
Список квот есть тут. Процессорное время там фигурирует только в виде одного ограничения в 90 минут в день на все скрипты вызванные триггером.
Меня это устраивает, аккаунтов у меня много, разные скрипты на разных аккаунтах.
1. Искали название для небольшого проекта. В первой колонке накидали слова русские. Во-второй перевели на английский, в третей — на литиницу и т.д. Потом искали интересные формы.
2. Черновой перевод фраз приложения на другие языки. Где были сомнения позже привлекли переводчика.
надеюсь на продолжение
Еще Гугл таблицы позволяют разбивать текст в одной ячейке по столбцам, используя любой разделитель (по количеству символов), экселька же позволяет поставить всего один символ-разделитель.
Можно:
1. создаем вспомогательную Таблицу [название картинки], [ссылка на нее]
2. создаем выпадающий список со всеми названиями картинок
3. создаем около списка формулу вида =image(VLOOKUP(A2;C2:D4;2;0))
где А2 — вып. список, а C2:D4 — диапазон таблицы из пункта 1
4. Теперь при выборе картинки в выпадающем списке она будет отображаться в ячейке, в которую мы ввели формулу.
Таблица с примером: docs.google.com/spreadsheets/d/1yiSHolO9iUFZRnE2ND4Cp4lirGs2AfSKH1zXfOOmMpA/edit#gid=0
То есть я вписываю данные, диаграммка генерится, и в соседней ячейке появляется ссылка на ее изображение, которую я могу вставить в адресную строку и увижу эту картинку.
Полезные функции Google Таблиц, которых нет в Excel