Автоматизация. Разработка динамического прототипа курьерской службы на сервисах Google.
16.08.2024
Комментариев нет
Содержание
Это гостевой пост от участника нашего сообщества про Таблицы Гугл Владислава Апенкина.
Владислав, спасибо тебе за участие, активность и демонстрацию возможностей автоматизации!
Введение
В современном мире малый и средний бизнес постоянно ищет способы оптимизации процессов и снижения затрат. Одной из областей, где часто возникают проблемы с эффективностью, является логистика и курьерская служба. В этой статье мы рассмотрим, как компания смогла автоматизировать процессы своей курьерской службы с помощью бесплатных инструментов Google, значительно повысив эффективность работы и снизив количество ошибок.
Преимущества использования Google Forms и Google Sheets для автоматизации:
- Бесплатность и доступность
- Простота в использовании и настройке
- Возможность гибкой кастомизации под нужды бизнеса
- Интеграция с другими сервисами Google
- Автоматизация рутинных процессов
- Централизованное хранение данных
- Возможность работы с мобильных устройств
- Совместный доступ и редактирование в реальном времени
Пункты реализации процесса:
- Автоматизация подачи заявок
- Генерация уникальных номеров заявок
- Автоматизация отчетности водителя
- Интеграция с учетной системой
Давайте рассмотрим каждый пункт подробнее:
Автоматизация подачи заявок
Раньше заявки поступали водителю через WhatsApp в свободной форме, что приводило к путанице и ошибкам. Решением стало создание Google Forms для подачи заявок. Форма была связана с Google Sheets для хранения данных.
Для автоматизации процесса был написан скрипт, который отправляет уведомления о новых заявках в Telegram-чат. Вот фрагмент кода для отправки сообщений в Telegram:
const token = '' //токен бота
const idChat = '' //id чата
function sendTelegram(){ //метод отправки сообщений в телеграм
let wb=SpreadsheetApp.getActive() //берём активную таблицу
let ss=wb.getSheets() //берём список листов
if(ss[1].getRange("A1").getValue()!=ss[1].getRange("A2").getValue()){
while(ss[1].getRange("A1").getValue()!=ss[1].getRange("A2").getValue()){
let text="Заказ "+ss[0].getRange(ss[1].getRange("A1").getValue()+1,9,1,1).getValue()+"\nМесто/адрес точки получения груза (услуги): "+"\<b\>\<u\>\<i\>"+ss[0].getRange(ss[1].getRange("A1").getValue()+1,3,1,1).getValue()+"\</i\>\</u\>\</b\>"+"\nОписание груза: "+ss[0].getRange(ss[1].getRange("A1").getValue()+1,4,1,1).getValue()+"\nМесто/адрес доставки груза (услуги): " +"\<b\>\<u\>\<i\>"+ss[0].getRange(ss[1].getRange("A1").getValue()+1,5,1,1).getValue()+"\</i\>\</u\>\</b\>"+"\n№ заказа: "+ss[0].getRange(ss[1].getRange("A1").getValue()+1,6,1,1).getValue()+"\nПоездка в личных целях: "+ss[0].getRange(ss[1].getRange("A1").getValue()+1,7,1,1).getValue()+"\nИнициатор: "+ss[0].getRange(ss[1].getRange("A1").getValue()+1,8,1,1).getValue()+"\nПроект: "+ss[0].getRange(ss[1].getRange("A1").getValue()+1,10,1,1).getValue()
sendText(idChat,text)
ss[1].getRange("A1").setValue(ss[1].getRange("A1").getValue()+1)
}
}
}
function sendText(chatId, text) {
let data = {
method: 'sendMessage',
chat_id: String(chatId),
text: text,
parse_mode: 'HTML'
};
let options = {
method: 'post',
payload: data
};
UrlFetchApp.fetch('https://api.telegram.org/bot' + token + '/', options)
}
По началу всё работало корректно, но в результате тестирования мы столкнулись с проблемой: при подаче заявки скрипт не понимает (и не должен без доработки), какие строки (ответ на форму хранится в виде строки ответов, поэтому одна заявка – одна строка) уже отправлены, а какие нет. Данную проблему мы решили следующим образом.
- На соседнем листе выставляем два значения: «было строк» и «стало строк».
- Пишем скрипт, который при отправке формы будет менять значение «стало строк» и ставим триггер «При отправке формы».
- В методе отправки сообщений в конце работы скрипта прописываем строку, которая будет менять значение «было строк» и зацикливаем весь метод на отправку сообщений, пока два значения «было» и «стало» не сравняются.
*. В коде также видно, что значения для сообщения берём из диапазонов, начальная ячейка которых находится по адресу [«Было строк», «Номер нужного столбца», «кол-во строк», «кол-во столбцов»].
function control(){ //метод обновления значений "Было"
let wb=SpreadsheetApp.getActive().getSheetByName("Списки") //берём лист
wb.getRange("A2").setValue(wb.getRange("A2").getValue()+1) //увеличиваем значение "было" для заявок
wb.getRange("B2").setValue(wb.getRange("B2").getValue()+1) //увеличиваем значение "было" для заказов
order() //запускаем метод генерации заказов
}
Таким образом, мы «научили» скрипт понимать, какие строки-заявки уже отправлены в работу, а какие ещё нет, а также брать значения именно из новых строк-заявок.
Генерация уникальных номеров заявок
Для удобства отслеживания заявок был реализован генератор уникальных номеров. Номер формируется из даты и порядкового номера заявки. Вот фрагмент кода генератора:
function order(){
let wb=SpreadsheetApp.getActive()
let ss=wb.getSheets()
let year=Utilities.formatDate(new Date(), 'GMT+7', 'yy')
let mounth=Utilities.formatDate(new Date(), 'GMT+7', 'MM')
let day=Utilities.formatDate(new Date(), 'GMT+7', 'dd')
if(ss[1].getRange("B1").getValue()!=ss[1].getRange("B2").getValue()){
while(ss[1].getRange("B1").getValue()!=ss[1].getRange("B2").getValue()){
let count=ss[1].getRange("B1").getValue()+1
if(ss[1].getRange("A10").getValue()<10){
ss[0].getRange(ss[1].getRange("B1").getValue()+1,9,1,1).setValue("#"+year+mounth+day+"00"+ss[1].getRange("A10").getValue())
ss[1].getRange("A10").setValue(ss[1].getRange("A10").getValue()+1)
ss[0].getRange(ss[1].getRange("B1").getValue()+1,10,1,1).setFormula("=VLOOKUP(H"+count+";'Списки'!$F$2:$G$61;2;0)")
}
else{
if(10<ss[1].getRange("A10").getValue()<100){
ss[0].getRange(ss[1].getRange("B1").getValue()+1,9,1,1).setValue("#"+year+mounth+day+"0"+ss[1].getRange("A10").getValue())
ss[1].getRange("A10").setValue(ss[1].getRange("A10").getValue()+1)
ss[0].getRange(ss[1].getRange("B1").getValue()+1,10,1,1).setFormula("=VLOOKUP(H"+count+";'Списки'!$F$2:$G$61;2;0)")
}
else{
ss[0].getRange(ss[1].getRange("B1").getValue()+1,9,1,1).setValue("#"+year+mounth+day+ss[1].getRange("A10").getValue())
ss[1].getRange("A10").setValue(ss[1].getRange("A10").getValue()+1)
ss[0].getRange(ss[1].getRange("B1").getValue()+1,10,1,1).setFormula("=VLOOKUP(H"+count+";'Списки'!$F$2:$G$61;2;0)")
}
}
ss[1].getRange("B1").setValue(ss[1].getRange("B1").getValue()+1)
}
}
sendTelegram()
}
Генератор работает очень просто: собираю номер из текущей даты в формате yyMMdd + порядковый номер заказа. Строки-заявки без привязанных номеров определяли методом, описанным выше, только использовали соседнюю пару чисел, чтобы не вызвать путаницы с отправленными заказами. Порядковый номер заказа определяется значением на соседнем листе, которое обновляется при каждом присвоении номера.
Таким образом, номера никогда не повторяются, и каждая строка-заявка имеет уникальный номер.
В коде, условный оператор с тремя группами почти идентичных строк реализован всего лишь для того, чтобы номера заявок имели одинаковую длину. Это всего лишь вопрос эстетики.
Последняя проблема в задаче автоматизации подачи заявок – скрипты не успевали отрабатывать и заявки могли прийти без номера или вообще старые. Решение: просто в вызываемом методе обновления значения «стало строк» запускаем метод генерации заказов, а из последнего уже вызываем метод отправки заявки в канал Telegram.
Весь вышеуказанный код расположен в редакторе скриптов таблицы приёма заявок (ответов от формы).
В целях сокращения до минимума ручного труда мы также написали скрипт непосредственно в форме, который будет обновлять список инициаторов и выставили триггер по неделям.
function manager(){
let form=FormApp.getActiveForm() //берём активную форму
let order=SpreadsheetApp.openById('your_sheets_id').getSheetByName("Списки") //берём лист со списоком инициаторов
let values=order.getRange('F:F').getValues() //берём список инициаторов
let array=[] //создаём пустой массив
for(let i=1;i<values.length;i++){ //идём по списку иницаторов
if(values[i]!=0){ //есть значение массива не пустое
array.push(values[i][0]) //закидываем в конец массива
}
}
let item = form.getItems(FormApp.ItemType.LIST) //берём список всех полей для ответов с типом "раскрывающийся список"
for(let i=0;i<item.length;i++){ //идём по полученному списку
if(item[i].getTitle()=="Инициатор"){ //убеждаемся, что найденный список именно тот, который нам нужен
item[i].asListItem().setChoiceValues(array) //заполняем его значениями
}
}
}
Данный код расположен в редакторе скриптов самой формы отправки заявки.
Таким образом, мы получили работающую систему подачи заявок, которая имеет порядок и хранит историю всех поданных заявок, а также успевает генерировать и заполнять все необходимые поля. Система, на наш взгляд, получилась динамической и не требующей ручного вмешательства, разве что обновить список инициаторов непосредственно на листе в таблице.
Автоматизация отчетности водителя
Здесь стоит указать, что раньше водитель заполнял отчёт, прописывая туда данные, которые подаются в заявке.
Теперь, с помощью формы удалось сократить временные затраты на отчётность в целом и на заполнение одного отчёта.
Пример формы отчёта:
Мы также сделали Google Form с набором полей под наши требования, но тут возник вопрос: как определить, по какой заявке отчитался водитель и как научить скрипт определять, по каким заявкам водитель уже отчитался? Решение первого вопроса пришло само: у нас есть уникальные номера заявок. Для решения второго вопроса воспользуемся методом обновления значений раскрывающегося списка, описанный выше, и системой флагов.
Код ниже:
function orders(){ //метод обновления заказов в форме
let form=FormApp.getActiveForm() //открываем активную форму
let order=SpreadsheetApp.openById('your_sheets_id').getSheetByName("Список заказов") //открываем таблицу со списком заказов на листе
let values=order.getRange('A:A').getValues() //получем список заказов на листе
let flag=order.getRange('B:B').getValues() //получаем список флагов (отработанные заказы)
let array=[] //создание массива для удаления пустых значений
for(let i=0;i<values.length;i++){ //запускаем цикл по заказам
if(values[i]!=0 && flag[i]!=1){ //если заказ есть в списке и не отработан
array.push(values[i][0]) //закидываем в конец массива
}
}
let item = form.getItems(FormApp.ItemType.LIST) //ищем элемент формы - выпадающий список
for(let i=0;i<item.length;i++){ //идём по найденным элементам
if(item[i].getTitle()=="№ заказа из чата"){ //проверяем что найденный элемент тот, который нужен
if(array[0]!==undefined){ //делаем проверку, что массив не пуст
item[i].asListItem().setChoiceValues(array) //закидываем туда значения
}
else{ //если массив всё же пустой
item[i].asListItem().setChoiceValues(['Заказов нет!']) //закидываем одно значение
}
}
}
}
function onFormSubmit(e){ //метод получения ответов
// данный метод предназначен для обновления списка заказов при ответе на форму. Суть обновления - сокращение списка неотработанных заказов
var formResponse = e.response; //получаем все возможные поля с ответами
var itemResponses = formResponse.getItemResponses(); //получаем список конкретных ответов
let orderOne=itemResponses[0].getResponse() //получаем номер заказа
let order=SpreadsheetApp.openById('your_sheets_id').getSheetByName("Список заказов") //открываем таблицу со списком заказов на листе
let values=order.getRange('A:A').getValues() //получем список заказов на листе
for(let i=0;i<values.length;i++){ //идём по всем заказам
if(values[i]==orderOne){ //если заказ, на который был ответ найден
order.getRange(i+1,2).setValue(1) //ставим флаг
}
}
orders() //запускаем функцию обновления заказа
}
Этот код расположен в редакторе скриптов формы с отчётностью.
Начнём с того, где и как реализована система флагов. В таблице, куда отправляется отчёт водителя, мы создали ещё лист, куда поместили список заказов, взятый из таблицы с заявками. Код:
function update(){ //метод обновления заказов на листе с отчётом
let wb=SpreadsheetApp.getActive().getSheets() //берём все листы
let wb1=SpreadsheetApp.openById('your_sheets_id').getSheets() //берём все листы из таблицы с заказами
let orders=wb1[0].getRange('I:I').getValues() //на нужном листе берём диапазон заказов
wb[wb.length-1].getRange(1, 1, orders.length, 1).setValues(orders) //обновляем заказы в таблице с отчётом
}
Код выше находится в редакторе скриптов таблицы с отчётами по заявкам.
При отправке формы, скрипт из формы берёт ответ заполняющего, достаёт из определённого вопроса номер заявки и ищет его на листе. Когда номер заявки найден, рядом скрипт ставит флаг – единицу. Далее работает скрипт обновления номеров заявок, который берёт все номера заявок, где рядом не указан флаг, и ячейка имеет не пустое значение.
Таким образом, мы получили, на наш взгляд опять же, динамическую систему отчётности водителя, в которой минимизирован шанс ошибки. Единственная проблема, которая осталась нерешённой: скрипт не успевает отрабатывать до того, как заново открылась форма. Задержка составляет около 5 секунд. Долго это или нет, решайте сами.
Интеграция с учетной системой
Для интеграции всех данных в единую учетную систему был создан еще один Google Sheets документ. Скрипты автоматически собирают данные из форм заявок и отчетов водителей, объединяя их в единую таблицу. Вот фрагмент кода для получения данных о заявках:
function autoSaveApplication(){
let ss=SpreadsheetApp.openById('1your_sheets_id_app').getSheets()
let ssRep=SpreadsheetApp.openById('your_sheets_id').getSheets()
let val=ss[0].getRange("A:A").getValues()
let start=0
let stop=0
for(let i=0;i<val.length;i++){
if(Utilities.formatDate(new Date(val[i]), 'GMT+7', 'yyyy-MM-dd')==Utilities.formatDate(new Date(), 'GMT+7', 'yyyy-MM-dd') && start==0){
start=i+1
}
else{
if(val[i][0]=='' && stop==0){
stop=i
break;
}
}
}
let valRep=ssRep[0].getRange("A:A").getValues()
let startRep=0
for(let j=1;j<valRep.length;j++){
if(valRep[j][0]=='' && startRep==0){
startRep=j+1
break
}
}
if(start!=0){
ssRep[0].getRange(startRep,1, stop-start+1,1).setValues(ss[0].getRange(start,1,stop-start+1,1).getValues())
ssRep[0].getRange(startRep,2, stop-start+1,1).setValues(ss[0].getRange(start,3,stop-start+1,1).getValues())
ssRep[0].getRange(startRep,3, stop-start+1,1).setValues(ss[0].getRange(start,5,stop-start+1,1).getValues())
ssRep[0].getRange(startRep,4, stop-start+1,1).setValues(ss[0].getRange(start,4,stop-start+1,1).getValues())
ssRep[0].getRange(startRep,5, stop-start+1,1).setValues(ss[0].getRange(start,6,stop-start+1,1).getValues())
ssRep[0].getRange(startRep,6, stop-start+1,1).setValues(ss[0].getRange(start,8,stop-start+1,1).getValues())
ssRep[0].getRange(startRep,7, stop-start+1,1).setValues(ss[0].getRange(start,10,stop-start+1,1).getValues())
ssRep[0].getRange(startRep,8, stop-start+1,1).setValues(ss[0].getRange(start,9,stop-start+1,1).getValues())
ssRep[0].getRange(startRep,14, stop-start+1,1).setValues(ss[0].getRange(start,7,stop-start+1,1).getValues())
}
}
Фрагмент кода для получения данных об отчётах:
function autoSaveApplication(){
let ss=SpreadsheetApp.openById('1your_sheets_id_app').getSheets()
let ssRep=SpreadsheetApp.openById('your_sheets_id').getSheets()
let val=ss[0].getRange("A:A").getValues()
let start=0
let stop=0
for(let i=0;i<val.length;i++){
if(Utilities.formatDate(new Date(val[i]), 'GMT+7', 'yyyy-MM-dd')==Utilities.formatDate(new Date(), 'GMT+7', 'yyyy-MM-dd') && start==0){
start=i+1
}
else{
if(val[i][0]=='' && stop==0){
stop=i
break;
}
}
}
let valRep=ssRep[0].getRange("A:A").getValues()
let startRep=0
for(let j=1;j<valRep.length;j++){
if(valRep[j][0]=='' && startRep==0){
startRep=j+1
break
}
}
if(start!=0){
ssRep[0].getRange(startRep,1, stop-start+1,1).setValues(ss[0].getRange(start,1,stop-start+1,1).getValues())
ssRep[0].getRange(startRep,2, stop-start+1,1).setValues(ss[0].getRange(start,3,stop-start+1,1).getValues())
ssRep[0].getRange(startRep,3, stop-start+1,1).setValues(ss[0].getRange(start,5,stop-start+1,1).getValues())
ssRep[0].getRange(startRep,4, stop-start+1,1).setValues(ss[0].getRange(start,4,stop-start+1,1).getValues())
ssRep[0].getRange(startRep,5, stop-start+1,1).setValues(ss[0].getRange(start,6,stop-start+1,1).getValues())
ssRep[0].getRange(startRep,6, stop-start+1,1).setValues(ss[0].getRange(start,8,stop-start+1,1).getValues())
ssRep[0].getRange(startRep,7, stop-start+1,1).setValues(ss[0].getRange(start,10,stop-start+1,1).getValues())
ssRep[0].getRange(startRep,8, stop-start+1,1).setValues(ss[0].getRange(start,9,stop-start+1,1).getValues())
ssRep[0].getRange(startRep,14, stop-start+1,1).setValues(ss[0].getRange(start,7,stop-start+1,1).getValues())
}
}
Фрагмент кода для получения данных об отчётах:
function autoSaveReports(){
let ss=SpreadsheetApp.openById('your_sheets_id').getSheets() //берём текущую таблицу
let ssRep=SpreadsheetApp.openById('your_sheets_id_rep').getSheets() //берём таблицу с отчётами
while(ssRep[0].getRange('K1').getValue()<ssRep[0].getRange('J1').getValue()){ //проверяем, были ли отчёты вообще за текущий день
let valRep=ssRep[0].getRange(ssRep[0].getRange('K1').getValue(),2).getValue() //берём номер заявки первого отчёта в диапазоне
let flag=0 //устанавливаем начальное значение номера строки
let val=ss[0].getRange('H:H').getValues() //берём номера заявок уже полученных в таблицу
for(let i=0;i<val.length;i++){ //идём по списку номеров заявок
if(val[i][0]==valRep){ //если нашли совпадение наомера заявки из таблицы с отчётами и в текущей таблице
flag=i+1 //устанавливаем номер строки
break //прерываем цикл
}
}
/** следующие строки проставляют занчения из отчёта водителя напротив конкретных заявок */
if(flag!=0 && ss[0].getRange(flag, 9).getValue()==''){
ss[0].getRange(flag, 9).setValue(ssRep[0].getRange(ssRep[0].getRange('K1').getValue(), 2).getValue())
ss[0].getRange(flag, 10).setValue(ssRep[0].getRange(ssRep[0].getRange('K1').getValue(), 4).getValue())
ss[0].getRange(flag, 11).setValue(ssRep[0].getRange(ssRep[0].getRange('K1').getValue(), 5).getValue())
ss[0].getRange(flag, 13).setValue(ssRep[0].getRange(ssRep[0].getRange('K1').getValue(), 3).getValue())
ss[0].getRange(flag, 15).setValue(ssRep[0].getRange(ssRep[0].getRange('K1').getValue(), 6).getValue())
ss[0].getRange(flag, 16).setValue(ssRep[0].getRange(ssRep[0].getRange('K1').getValue(), 7).getValue())
ss[0].getRange(flag, 17).setValue(ssRep[0].getRange(ssRep[0].getRange('K1').getValue(), 8).getValue())
ssRep[0].getRange(ssRep[0].getRange('K1').getValue(), 9).setValue(1)
}
/** следующие строки обрабатывают ошибки, если были найдены ответы на одну и ту же заявку, или её вообще в списке не было */
else{
flag=ss[1].getRange("L1").getValue()
ss[1].getRange(flag, 9).setValue(ssRep[0].getRange(ssRep[0].getRange('K1').getValue(), 2).getValue())
ss[1].getRange(flag, 10).setValue(ssRep[0].getRange(ssRep[0].getRange('K1').getValue(), 4).getValue())
ss[1].getRange(flag, 11).setValue(ssRep[0].getRange(ssRep[0].getRange('K1').getValue(), 5).getValue())
ss[1].getRange(flag, 13).setValue(ssRep[0].getRange(ssRep[0].getRange('K1').getValue(), 3).getValue())
ss[1].getRange(flag, 15).setValue(ssRep[0].getRange(ssRep[0].getRange('K1').getValue(), 6).getValue())
ss[1].getRange(flag, 16).setValue(ssRep[0].getRange(ssRep[0].getRange('K1').getValue(), 7).getValue())
ss[1].getRange(flag, 17).setValue(ssRep[0].getRange(ssRep[0].getRange('K1').getValue(), 8).getValue())
ss[1].getRange(flag, 1).setValue(ssRep[0].getRange(ssRep[0].getRange('K1').getValue(), 1).getValue())
ssRep[0].getRange(ssRep[0].getRange('K1').getValue(), 9).setValue(1)
}
}
}
Пример таблицы с отчётами и заказами:
Пример таблицы с отчётами до перемещения заказа в общую таблицу:
И пример сообщения в ТГ канал:
Заключение
Использование Google Forms и Google Sheets позволило компании создать гибкую, масштабируемую и эффективную систему управления курьерской службой. Основные преимущества:
- Снижение количества ошибок за счет автоматизации процессов
- Экономия времени на обработку заявок и составление отчетов
- Централизованное хранение всех данных
- Возможность быстрого анализа эффективности работы
- Низкая стоимость внедрения (только время на разработку)
- Возможность дальнейшей доработки и масштабирования системы
Для малого и среднего бизнеса такой подход может стать отличным решением для оптимизации процессов без значительных финансовых вложений. Google предоставляет мощные инструменты, которые при правильном использовании могут значительно повысить эффективность работы компании.
Если нужно индивидуальное решение под ваши задачи или доработка существующего, то у нас его можно заказать.
С вами была команда GoogleSheets.ru, мы даём бизнесу возможность заниматься развитием, а не операционкой. Все вопросы, замечания и пожелания пишите в комментариях или в наш чат по Гугл таблицам, будем рады обратной связи.
