Mini-guide to sending messages from Google Sheets or database with Python
In this article we will tell you how to automate sending notification messages based on data from a table.
Let's say there is a pre-prepared database in the CMS, where requests from customers are received – perhaps this is an order for goods or in a restaurant. Our task: using the specified phone number, notify the user that his order is ready for issue. Let's add that we will need the name of the customer and the address of the issue point.
The main idea of the work is to link the database with the SMS mailing form. It, in turn, uses API Exolve to send a previously prepared message. To simplify the task, we will tell you about the implementation of the project using Google Sheets, but thanks to one of our previous guides you can easily link to the CMS.
Submission form from the browser we have already implemented it before — is a method for simplifying data transfer that allows you to avoid the intricacies of programming if you urgently need to send a message.
That is why the algorithm of work will be as follows:
Create an Excel file with all the necessary information (name, address, phone number, order number).
Create a form for automatic sending of messages (with automatic data substitution, authorization code).
Link an Excel file to a form to automatically transfer data every 20 seconds.
Profit.
This solution may seem a bit of a hack because of the form – you can do without it by automating the reading and sending of messages.
In order for the system to work in automatic mode, a server is required. The load is not very large, you can even use just a working laptop – from the server requirements, we can highlight only go 1.20, the presence of SSL (https), and routing.
Step 1: Create an Excel file
The best option is to use an online system like Google Docs. This will require a constant connection to the Internet or a public network, but will guarantee a near-instant response when needed.
The following fields are mandatory:
recipient number;
buyer's name;
text for mailing (“Your order number NN is ready”);
receiving address (“come to there and then»);
sender's number (if several numbers were rented within Exolve for different branches, for example).
The final table may look like this:
If necessary, you can add several fields and implement this in the program code. MTC Exolve also allows you to use the sender name for legal entities so that users see not just a number in the sender field, but the name of the organization – all you need to do is confirm your account and sign an agreement.
You also need to create a program that will process the data in this table – read it and translate it further. To do this, download any Python compiler, paste the following code into it and save the file:
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))We tried to describe all the steps as clearly as possible, so that even an ordinary user could copy the code and try it on their table. As a result, we get a table with full access for the program that reads the data and transfers it to the sending form. In addition, we made processing for the presence of empty cells (then the code will simply skip such lines), as well as already processed applications. The check is performed once every 20 seconds, but you can set your own value.
Step 2: Create a submission form
Despite the fact that we have already created a table connection with the Exolve API for sending messages, for reinsurance and manual sending we will create a form. The values will be substituted into it automatically. This will also protect our program from crashes when the fields are filled in incorrectly.
First of all, we will write the processing code, and then we will supplement it with an external component – by the way, you can customize it at your discretion and even include it in the user interface.
For ease of further use, we have written the form code in Python. All that remains is to insert it into any compiler and save it as a separate file:
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()This code, in addition to sending messages, can also check the fields for correctness of filling – whether the phone number is specified correctly, for example. Otherwise, it will require parallel launch with the main table file.
We create a JavaScript file in which we describe what this form should do: in particular, receive values in special fields and pass them to the Exolve API:
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);You can read more about what is responsible for what and how exactly we put this form together in one of our previous articles.
During the setup process, our form will look like this:
A working, but not very aesthetic moment, isn't it? The last step is to add the visual component – the style in 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%;
}
}In order to access the form, you need to manually launch the site – you will need an Internet connection, as well as the previously created file launched. If desired, you can “sew” the code into the user interface, as well as customize it.
Step 3. Setup
To ensure that everything works correctly and does not require any modifications, you must additionally create a configuration file with updated versions of the libraries used:
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.0We leave it for everyone who wants it link to the finished code on GitHub with full assembly and all components.
The algorithm can be represented as follows: an employee fills out a corresponding form (created automatically for the work interface or using Google Forms), which transfers data to a common table. Manual filling is also possible – for example, if you need to make changes to already created applications.
After that, the automatic algorithm checks every 20 seconds (it is possible to increase the period to 20 minutes, an hour, etc.) whether new lines have appeared. And if they do, it reads them, sending the values to the sending form. It, in turn, checks the correctness of the input and serves to ensure that the employee can also independently enter and send data.
The last step is the actual work API Exolve to send a message to the specified phone number. The code already contains automatic comments to the fields (“go there”), you can change them yourself. As a result, buyers will receive the following messages:
We will repeat that this code can be simplified by automating it and removing the form — that is, transferring data directly to the API. But in this case, the possibility of manual intervention — changing data and sending messages “out of turn” — is excluded.
