Добавление и модификация данных в Google Sheets | Google Apps Script
Вставка
- Опубліковано 3 лют 2022
- Обучение программированию: курс-программирование.рф/?utm...
Пишем скрипты (макросы) для Гугл Таблиц. В этом ролике разбираемся, как работают функции вставки и модификации данных. Разберем единичную и множественную вставку данных. Коснемся методов, которые позволяют вставлять формулы. В итоге, подготовим отчет в котором реализуем практически все рассматриваемые методы.
Дополнительно, разберем функции Google Sheets: QUERY - выборка данных из диапазона значений по условию; UNIQUE - функция для получения уникальных значений; TRANSPOSE - функция транспонирующая значения (переворачивает строки в ячейки, ячейки в строки).
Методы рассматриваемые в видео:
setValue() - установка нового значения в выбранную ячейку. Выбор осуществляется через диапазон, получаемы getRange()
setValues([][]) - функция принимает массив из массивов. Каждый массив отвечает за строку.
appendRow([]) - функция принимает значение
getLastColumn() - получаем максимальный индекс ячейки
getLastRow() - максимальный индекс строки
setFormula()/setFormulas([][]) - вставить формулу/формулы
Если хотите освоить автоматизацию и написание скриптов для Google Spreadsheet, это отличный урок на русском языке. Основное назначение гугл таблиц - это хранение, манипуляцие данными и представление их в виде отчета.
Исходный код: github.com/keitmn/google_apps
Мой телеграм-канал: t.me/xe_coding
Огромное спасибо! Очень понятно и просто объясняешь!
Спасибо тебе огромное !! Ждём продолжения!!
Молодец! Очень понятно объясняешь. Жду продолжения.
Спасибо большое за такую оценку! В ближайшее времы планирую продолжить выкладывать новый контент. Решу пару важных вопросов и вперед.
Cool!😊 Давай ещё!
Мучался с Апишкой, очень помог видос. Спасибо
Рад, что был полезен!
Спасибо за уроки
Доброго времени суток.
Во первых: спасибо большое за ваши видео.
Вопрос: снимите пожалуйста видео про for, do и while.
Никак не могу найти: как можно определить не пустую ячейку в столбце? То есть, есть таблица куда попадают данные с приложения. Нужно определить на какую строку попала инфа, скопировать данные из этой строки и очистить поле для приложения.
Спасибо за труд, а можно больше по фильтрам + фильтр с множеством условий. Спасибо ещё раз
Пока не могу писать видео((((
@@KhoroshilovEI какая жаль ((
Классное видео, большое спасибо! Формула только в самом конце неправильно сработала. Там в итоге не диапазон суммируется, а только по две ячейки ([A2 + A7] , [B2 + B7]).
Так он вместо двоеточия в SUM() пишет точку с запятой. Поэтому вместо диапазона получается как перечисление ячеек.
Надо SUM( R[...]C :R[-1]C)
И не только в конце, а в начале с массивом в три строки так же))) А, в целом, видео - класс!
Жесть, что он творит!!!
А почему у вас формула не суммировала все значения?
Добрый день! Подскажите, какой метод нужен, чтобы скрипт вставлял не формулу, а значение этой формулы?
Какой язык используется в google apps script ? На базе java ?
Очень полезное видео. Спасибо. Доступно и понятно. У меня только один вопрос. Doget и в целом передача вызов запись данных с Гугл таблицы через функции которые вы показали без ограничения по количеству запросов? Гугл всегда ставит какие то лимиты всегда. Типа 1000 раз выполняет и все, нужно другую таблицу открывать. На выполнение функций Гугл даёт 6 мин. Больше выдает ошибку. Прошу вас ответить на мой вопрос. За раннее спасибо.
Под ругим видео ответил, тут продублирую. Вдруг у кого будет подобный вопрос, а то видео не посмотрят:
Ограничения действительно есть, их можно посмотреть по этой ссылке: developers.google.com/apps-script/guides/services/quotas#current_limitations
Хотя, была информация, что на запросы гугл вроде как отменял ограничения.
Сейчас лимит 20 000 запросов в день, т.е. новую таблицу не нужно создавать. Если этого мало, здесь уже надо смотреть по решаемой задаче. Google принимает POST запросы объемом до 50Мб. Можно накапливать данные и отправлять большими пакетами.
Что касается времени выполнения, оно так же есть. Как вы и сказали, 6 минут. Тут тоже нужно смотреть задачу. 6 минут, это реально много. Возможно стоит как-то пересмотреть подход к обработке данных.
@@KhoroshilovEI Евгений. Спасибо за столь оперативный развернутый ответ. Вам желаю больше подписчиков и крепкого здоровья. 20000 в день это за глаза)
Евгений, а можно будет сделать отдельно урок как вообще происходит наполнение гугл таблицы из формы сайта к примеру и последующего выводы нанных там на другую страницу или вкладку.
Так же все работает через fatch: POST и GET ?
Заранее спасибо
Добрый день! Мы дойдем до этого. Можно ускорить будет, только придумаю куда запихать это)))) В общем на днях сделаем.
Евгений привет, а можно ли принудительно пересчитать формулу в ячейке?.
Я вставил туда формулу =IMPORTHTML() и мне надо чтобы она пересчитывалась например 1 раз в минуту.
Я уже пробовал очищать формулу и вставлять заново, чтобы она пересчиталась, но скрипт так быстро отрабатывает, что походу формула не успевает отрагировать.
Если в одной функции сделать очищение формулы, а в другой вставку, то тоже работает, т.к. проходит какое-то время и Google Shets обновляет формулу и пересчитывает.
Если сделать последовательно запуск двух функций, то опять скрипт быстро отрабатывает и Google Shets не успевает отреагировать на изменения.
Пробовал между ними вставлять функцию sleep(), скрипт останавливается, но первая функция при этом не отрабатывает, т.к. ячейки не очищаются, а при вставке формулы второй функцией, Google Shets не успевает отреагировать на изменения.
Вообщем не могу понять как связать две функции, чтобы они отрабатывались последовательно и Google Shets успел бы отреагировать на изменения формул.
Добрый день! Почему-то выходит ошибка в report.getRange(2,i).setFormula('=QUERY(data!B:C,"select C where B='${value}'",0)') пишет, что неправильный синтаксис. Подскажите, пожалуйста, где? Все сверила, все ровно как у вас. Формула в гугл-таблице работает.
Добрый день! Разобрались с проблемой? Если нет, напишите, ошибка синтаксиса в скрипте или в самой таблице? Какая формула добавляется в таблицу? К нужным листам обращаетесь?
@@KhoroshilovEI Ошибка возникает в тот момент, когда в формуле заменяешь "&A1&" на ${value}. Сразу же подчеркивает знак доллара и дальше после value. Запрос не выполняется, так как пишет, что синтаксис не правильный. А что означает знак доллара? Такое ощущение, что программа не понимает, что от нее хотят именно в этом месте.
@@user-uc4gq3mh1v какие кавычки вы используете? Есть разница при использовании ` (кнопка с тильдой ~) и ' (буква Э). Когда используется ` можно использовать подстановки ${}. Если используете другой вид кавычек, нужно писать 'какая-то форумал до подстановки' + value + ' что-то после подстановки'. Больше информации можно найти в справочных материалах по Javascript.
@@KhoroshilovEI Спасибо, что отвечаете! Попробовала изменить кавычки. Если ${value} забирать в кавычки, которые на Э, то запрос не выполняется, неправильный синтаксис. Если забирать в кавычки на кнопке с тильдой, то запрос выполняется, но появляется ошибка в гугл-таблице в ячейках "Не удалось интерпретировать query string. Подробности: Параметр 2 в функции QUERY:NO_COLUMN: ${value}". Прикладываю код, посмотрите, пожалуйста: function setOtchet1() {
const ss = SpreadsheetApp.openById("1XoOrJ27LYZ1yH29LRy25fbttxKXbBpkKsFYHX58xzW4")
const report = ss.getSheetByName("Report")
const range = report.getRange("A1")
range.setFormula("=TRANSPOSE(UNIQUE(data!B:B))")
lastColumn = report.getLastColumn() +1
for( let i=1; i < lastColumn; i++) {
const value = report.getRange(1,i).getValue()
report.getRange(2,i).setFormula('=QUERY(data!B:C,"select C where B=`${value}`",0)')
}}
@@user-uc4gq3mh1v уже завтра гляну, но судя по всему вот тут ошибка:
'=QUERY(data!B:C,"select C where B=`${value}`",0)'
обратные кавычки должны быть снаружи, т.е. вся строка должна быть в них:
`=QUERY(data!B:C,"select C where B='${value}'",0)`
в ином случае, у вас формула в ячейке так и будет выглядеть B=`${value}`, т.е. подстановки не происходит
Ошибка выходит -- Report.getRange(2, i).setFormula('=query(data!b:c,"select C where B='${value}' ", 0)').