Мини-гайд по отправке сообщений из Google Таблицы или базы данных с Python

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

Допустим, есть заранее приготовленная база данных в CMS, куда попадают заявки от покупателей — возможно, это заказ товаров или в ресторане. Наша задача: используя указанный номер телефона, уведомить пользователя о том, что его заказ готов к выдаче. Добавим, что нам потребуется имя покупателя и адрес пункта выдачи. 

Основная идея работы заключается в том, чтобы связать базу данных с формой SMS-рассылки. Она, в свою очередь, использует API Exolve для отправки ранее заготовленного сообщения. Для упрощения задачи мы расскажем про реализацию проекта с применением Google Таблиц, но благодаря одному из наших прошлых гайдов можно легко произвести привязку к CMS.

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

Именно поэтому алгоритм работы будет следующий:

1. Создать Excel-файл со всей необходимой информацией (имя, адрес, номер телефона, номер заказа).

2. Создать форму для автоматической отправки сообщений (с автоматической подстановкой данных, кодом авторизации).

3. Сделать привязку Excel-файла к форме для автоматической передачи данных каждые 20 секунд.

4. Profit.

Это решение может показаться немного «костыльным» из-за формы — без нее можно обойтись, автоматизировав чтение и отправку сообщений.

Для того, чтобы система работала в автоматическом режиме, потребуется сервер. Нагрузка не очень большая, можно использовать даже просто рабочий ноутбук — из требований к серверу можно выделить только go 1.20, наличие SSL (https), а также маршрутизацию.

Этап 1. Создание Excel-файла

Лучший вариант — воспользоваться онлайн-системой по типу Google Docs. Это потребует постоянного подключения к интернету или общей сети, но гарантирует практически моментальный отклик при необходимости.

Из обязательных полей можно выделить:

• номер получателя;

• имя покупателя;

• текст для рассылки («Ваш заказ номер НН готов»);

• адрес получения («подойдите туда-то»);

• номер отправителя (если внутри Exolve арендовали несколько номеров для разных филиалов, например).

Итоговый вариант таблицы может выглядеть следующим образом:

При необходимости можно добавить несколько полей и реализовать это в программном коде. Также MTC Exolve позволяет использовать имя отправителя для юрлиц, чтобы пользователи в поле отправителя видели не просто номер, а название организации — для этого нужно лишь подтвердить аккаунт и заключить договор.

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

import asyncio import json import requests as requests from google.oauth2 import service_account from googleapiclient.discovery import build async def send_message_client(data):     url = 'https://api.exolve.ru/messaging/v1/SendSMS'     # для сайта     response = requests.post(url=url, headers={         "Authorization": "”Код авторизации”",     }, json=data)     print(type(response.json))     print(response.json)     print(response)     return response class GoogleSheetData:     def __init__(self):         # Путь к файлу JSON с учетными данными         self.SERVICE_ACCOUNT_FILE = 'credentials.json'         # Создание учетных данных из файла JSON         self.creds = service_account.Credentials.from_service_account_file(             self.SERVICE_ACCOUNT_FILE,             scopes=[                 'https://www.googleapis.com/auth/spreadsheets',                 'https://www.googleapis.com/auth/drive',             ]         )         # Создание клиента API         self.service = build('sheets', 'v4', credentials=self.creds)         # ID вашей таблицы Google Sheets         self.spreadsheet_id = 'ID'     async def processing_google_sheet(self):         try:             # открываем файл JSON, читаем данные и записываем их в переменную 'data'             # если файл не найден, создаем новую пустую переменную 'data'             try:                 with open('data.json', "r") as file:                     data = json.load(file)             except FileNotFoundError:                 data = {1: ['Имя', 'Ваш заказ номер НН готов', 'подойдите туда-то', 'Номер']}             # получаем номер строки с которым будем работать             row = int(list(data.keys())[0]) + 1             # Запрос на получение данных из таблицы Google Sheets(получаем определенную строку в range)             response = self.service.spreadsheets().values().get(                 spreadsheetId=self.spreadsheet_id,                 range=f"Лист1!A{row}:E{row}",             ).execute()             # получаем строку с данными             values = response.get('values', [])             print(values)             # проверяем на наличие данных             if not values:                 print('No data found.')             elif len(values[0]) >= 5 and '' not in values[0]:                 data_excel = {"number": values[0][4], "destination": values[0][0], "text": f"{values[0][1]}, Ваш заказ номер {values[0][2]} готов, подойдите {values[0][3]}"}                 print(data_excel)                 result_send_message = await send_message_client(data_excel)                 data = {row: values[0]}                 print(data)                 # записываем новые данные в файл 'data.json'                 with open('data.json', "w") as file:                     json.dump(data, file, indent=4, ensure_ascii=False)         except Exception as e:             print(f"An error occurred: {e}") async def run_processing_google_sheet(google_sheet_req):     while True:         await google_sheet_req.processing_google_sheet()         await asyncio.sleep(20) if __name__ == "__main__":     google_sheet_req = GoogleSheetData()     asyncio.run(run_processing_google_sheet(google_sheet_req))

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

Этап 2. Создание формы отправки

Несмотря на то, что мы уже создали связь таблицы с API Exolve для отправки сообщений, для перестраховки и ручной отправки мы соберем форму. Значения в нее будут подставляться автоматически. Также это защитит нашу программу от сбоев при неправильном заполнении полей.

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

Для удобства дальнейшего использования мы написали код работы формы на Python. Остается вставить его в любой компилятор и сохранить в виде отдельного файла:

import asyncio import json import re import requests as requests from google.oauth2 import service_account from googleapiclient.discovery import build from quart import Quart, render_template, request  app = Quart(__name__) async def send_message_client(data):     url = 'https://api.exolve.ru/messaging/v1/SendSMS'     # для сайта     response = requests.post(url=url, headers={         "Authorization": "Код авторизации",     }, json=data)     return response @app.route('/', methods=['GET', 'POST']) def index():     if request.method == 'POST':         data = dict(request.form)         for key in request.form:             if request.form[key] == '':                 return render_template('index.html', error='Не все поля заполнены!')             if key == 'destination':                 if not re.match('7\d{9}', request.form[key]):                     return render_template('index.html', error='Неправильный формат номера телефона')         result_send_message = send_message_client(data)         if "error" in result_send_message.json:             return render_template('index.html', error=f"Ошибка, вид ошибки: {result_send_message.json()}")         return render_template('index.html', error=f"Запрос обработан!, id message: {result_send_message.json()}")     return render_template('index.html') if __name__ == "__main__":     app.run()

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

Формируем файл JavaScript, в котором распишем, что должна делать эта форма: в частности, получать значения в специальные поля и передавать их в API Exolve:

async function postData(formProps) {   const url = 'https://api.exolve.ru/messaging/v1/SendSMS';   const data = { number: number, destination: destination, text: text };    try {     console.log(JSON.stringify(data));     const response = await fetch(url, {       method: "POST",       body: JSON.stringify(formProps),       headers: {         "Authorization": "Код авторизации",       },     });     const json = await response.json();     console.log("Успех:", JSON.stringify(json));   } catch (error) {     console.error("Ошибка:", error);   } } function logSubmit(event) {   event.preventDefault();   const formData = new FormData(event.target);   const formProps = Object.fromEntries(formData);   postData(formProps); } const form = document.getElementById("form"); form.addEventListener("submit", logSubmit);

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

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

Рабочий, но не очень эстетичный момент, не так ли? Последний шаг — добавить визуальную составляющую — стиль в CSS.

*, *::before, *::after {   margin: 0;   padding: 0;   box-sizing: border-box; } body {   min-height: 100vh;   padding: 50px;   display: flex;   align-items: center;   justify-content: center;   background-color: #18191c;   color: #FFFFFF;   font-family: 'Kanit', sans-serif;   font-family: 'Roboto', sans-serif;   font-size: 18px; } h4 {     color: red;     margin-bottom: 2vh; } input, textarea{   width: 100%;   padding: 12px;   margin-bottom: 25px;   border: 1px solid #ccc;   border-radius: 4px;   box-sizing: border-box;   resize: vertical; } label {   display: flex;   align-items: center;   justify-content: space-between;   margin-bottom: 25px; } .input, .button {   width: 350px; } .input {   border: 1px solid #ffffff;   border-radius: 6px;   padding: 10px 15px;   background-color: transparent;   color: #ffffff;   font-family: inherit;   font-size: inherit;   font-weight: 300;   -webkit-appearance: none;   appearance: none; } .input:focus {   border-color: #FFD829;   outline: none; } .button {   display: block;   min-width: 210px;   border: 2px solid transparent;   border-radius: 6px;   margin-left: auto;   padding: 9px 15px;   color: #000000;   font-size: 18px;   font-weight: 300;   font-family: inherit;   transition: background-color 0.2s linear; } .button:hover {   background-color: #FF0032;   cursor: pointer;   transition: background-color 0.2s linear; } .button:focus-visible {   border: 2px solid #ffffff;   outline: none; } .button:focus {   border: 2px solid #ffffff;   outline: none; } .button-yellow {   background-color: #FFD829; } .button:disabled {   opacity: 0.5;   cursor: not-allowed; } .hidden {   display:none; } @media (max-width: 768px) {   body {     padding: 30px;   }   label {     display: block;   }   .input, .button {     display: block;     width: 100%;   } }

Для того, чтобы получить доступ к форме, нужно вручную запустить сайт — потребуется подключение к Интернету, а также запущенный ранее созданный нами файл. При желании можно «вшить» код в пользовательский интерфейс, а также настроить.

Этап 3. Настройка

Чтобы все работало корректно и не требовало доработок, необходимо дополнительно создать файл настройки с актуализацией версий используемых библиотек:

aiofiles==23.2.1 anyio==4.1.0 beautifulsoup4==4.12.2 blinker==1.7.0 cachetools==5.3.2 certifi==2023.11.17 charset-normalizer==3.3.2 click==8.1.7 Flask==3.0.0 google==3.0.0 google-api-core==2.15.0 google-api-python-client==2.111.0 google-auth==2.25.2 google-auth-httplib2==0.2.0 google-auth-oauthlib==1.2.0 googleapis-common-protos==1.62.0 greenlet==3.0.2 h11==0.14.0 h2==4.1.0 hpack==4.0.0 httpcore==1.0.2 httplib2==0.22.0 httpx==0.25.2 Hypercorn==0.15.0 hyperframe==6.0.1 idna==3.6 itsdangerous==2.1.2 Jinja2==3.1.2 MarkupSafe==2.1.3 oauth2client==4.1.3 oauthlib==3.2.2 priority==2.0.0 protobuf==4.25.1 pyasn1==0.5.1 pyasn1-modules==0.3.0 pyparsing==3.1.1 python-telegram-bot==20.7 Quart==0.19.4 requests==2.31.0 requests-oauthlib==1.3.1 rsa==4.9 six==1.16.0 sniffio==1.3.0 soupsieve==2.5 SQLAlchemy==2.0.17 typing_extensions==4.9.0 uritemplate==4.1.1 urllib3==2.1.0 Werkzeug==3.0.1 wsproto==1.2.0

Для всех желающих оставляем ссылку на готовый код в GitHub с полной сборкой и всеми составляющими.

Работу алгоритма можно представить следующим образом: сотрудник заполняет соответствующую форму (создается автоматически для рабочего интерфейса или с помощью Google Форм), которая передает данные в общую таблицу. Возможно также ручное заполнение — например, если нужно вносить правки в уже созданные заявки.

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

Последний шаг — непосредственная работа API Exolve для отправки сообщения на указанный номер телефона. В коде уже прописаны автоматические комментарии к полям («подойдите туда-то»), их можно изменить самостоятельно. Как итог — покупатели получат следующие сообщения:

Мы повторимся, что этот код можно упростить, автоматизировав его и убрав форму — то есть, передавать данные напрямую в API. Но в таком случае исключается возможность ручного воздействия — изменения данных и отправки сообщений «вне очереди».