What is Google Script?
Google Script – read through the name, we can also imagine its function: is a programming language based on Javascript with the editor, the translators are located on Google’s servers. With this tool, you can program to manipulate and intervene directly with Google services. In addition, there are many other interesting functions, among them, there is a function implementation with a certain time in a day, I took advantage of this point to push the schedule notice.
Start writing code only
The interface of Google App Script will look like this Click the New Project button to proceed with the Code writing
Initially, of course, I had to build a database for the Bot. Here I use google sheet to make database.
The content of this sheet is as follows: Since this is my first semester’s schedule, only from August to December.
There is a database already, how can I read the data from this sheet? This is when promoting the strength of Google Script. We can read the file sheet with the following function
1 2 | SpreadsheetApp <span class="token punctuation">.</span> <span class="token function">openById</span> <span class="token punctuation">(</span> file_tkb <span class="token punctuation">)</span> |
You can see the details here https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app
Where file_tkb
is the id
of the database sheet it is taken as in the image below
Next we will build the sheet reading logic so we can get the desired data:
1 2 3 4 | <span class="token keyword">var</span> today <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">Date</span> <span class="token punctuation">(</span> <span class="token punctuation">)</span> <span class="token punctuation">;</span> <span class="token keyword">var</span> first <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">Date</span> <span class="token punctuation">(</span> today <span class="token punctuation">.</span> <span class="token function">getFullYear</span> <span class="token punctuation">(</span> <span class="token punctuation">)</span> <span class="token punctuation">,</span> <span class="token number">6</span> <span class="token punctuation">,</span> <span class="token number">29</span> <span class="token punctuation">)</span> <span class="token punctuation">;</span> <span class="token keyword">var</span> theDay <span class="token operator">=</span> Math <span class="token punctuation">.</span> <span class="token function">round</span> <span class="token punctuation">(</span> <span class="token punctuation">(</span> <span class="token punctuation">(</span> today <span class="token operator">-</span> first <span class="token punctuation">)</span> <span class="token operator">/</span> <span class="token punctuation">(</span> <span class="token number">1000</span> <span class="token operator">*</span> <span class="token number">60</span> <span class="token operator">*</span> <span class="token number">60</span> <span class="token operator">*</span> <span class="token number">24</span> <span class="token punctuation">)</span> <span class="token punctuation">)</span> <span class="token operator">+</span> <span class="token number">.5</span> <span class="token punctuation">,</span> <span class="token number">0</span> <span class="token punctuation">)</span> <span class="token punctuation">;</span> |
In the above code, I take out the current day ( today
), the first day of the first week of the new semester ( first
here is July 29), then calculate how much today is the first day of school. day ( theDay
).
Next, create the following variables
1 2 3 4 5 6 | <span class="token keyword">var</span> week_1 <span class="token operator">=</span> theDay <span class="token operator">/</span> <span class="token number">7</span> <span class="token punctuation">;</span> <span class="token keyword">var</span> week_mod <span class="token operator">=</span> theDay <span class="token operator">%</span> <span class="token number">7</span> <span class="token punctuation">;</span> <span class="token keyword">var</span> week_col <span class="token punctuation">;</span> <span class="token keyword">var</span> week_row <span class="token punctuation">;</span> <span class="token keyword">var</span> message <span class="token punctuation">;</span> |
Inside:
- If
week_mod == 0 || week_mod == 6
, today is the weekend and assignmessage = "Hôm nay là cuối tuần chịu khó tập thể dục đi";
- In contrast, we have:
1 2 3 4 5 | week_col <span class="token operator">=</span> <span class="token function">parseInt</span> <span class="token punctuation">(</span> week_1 <span class="token punctuation">)</span> <span class="token operator">+</span> <span class="token number">1</span> <span class="token punctuation">;</span> week_row <span class="token operator">=</span> week_mod <span class="token punctuation">;</span> <span class="token keyword">var</span> real_col <span class="token operator">=</span> week_col <span class="token operator">+</span> <span class="token number">2</span> <span class="token punctuation">;</span> <span class="token keyword">var</span> real_row <span class="token operator">=</span> <span class="token punctuation">(</span> <span class="token punctuation">(</span> week_row <span class="token operator">-</span> <span class="token number">1</span> <span class="token punctuation">)</span> <span class="token operator">*</span> <span class="token number">3</span> <span class="token operator">+</span> <span class="token number">2</span> <span class="token punctuation">)</span> <span class="token punctuation">;</span> |
The example is a little easier to understand:
Assuming today is December 20/12/2019
, theDay = 145
, we have theDay = 145
, week_mod = 5
so that means Friday. real_col = 23 ; real_row = 14
we will start grabbing the land in the sheet in column 23, line 14.
To retrieve data I use the following code:
1 2 3 4 5 6 | <span class="token keyword">var</span> file_tkb <span class="token operator">=</span> <span class="token constant">YOUR_SHEET_ID</span> <span class="token punctuation">;</span> <span class="token keyword">var</span> ss <span class="token operator">=</span> SpreadsheetApp <span class="token punctuation">.</span> <span class="token function">openById</span> <span class="token punctuation">(</span> file_tkb <span class="token punctuation">)</span> <span class="token punctuation">;</span> <span class="token keyword">var</span> url <span class="token operator">=</span> ss <span class="token punctuation">.</span> <span class="token function">getUrl</span> <span class="token punctuation">(</span> <span class="token punctuation">)</span> <span class="token punctuation">;</span> <span class="token keyword">var</span> a <span class="token operator">=</span> ss <span class="token punctuation">.</span> <span class="token function">getSheets</span> <span class="token punctuation">(</span> <span class="token punctuation">)</span> <span class="token punctuation">[</span> <span class="token number">0</span> <span class="token punctuation">]</span> <span class="token punctuation">.</span> <span class="token function">getSheetValues</span> <span class="token punctuation">(</span> real_row <span class="token operator">+</span> <span class="token number">1</span> <span class="token punctuation">,</span> real_col <span class="token punctuation">,</span> <span class="token number">1</span> <span class="token punctuation">,</span> <span class="token number">1</span> <span class="token punctuation">)</span> <span class="token punctuation">;</span> <span class="token keyword">var</span> study_code <span class="token operator">=</span> a <span class="token punctuation">[</span> <span class="token number">0</span> <span class="token punctuation">]</span> <span class="token punctuation">[</span> <span class="token number">0</span> <span class="token punctuation">]</span> <span class="token punctuation">;</span> |
Once you have the data position in the sheet, take the data out with the getSheetValues
function, you can see the details here https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet Here data The return data is a 2-dimensional array, we obtain study_code = a[0][0]
, this is the subject code we need.
Create the corresponding dictionary for the subject code you just received
1 2 3 4 5 6 7 8 | <span class="token keyword">var</span> <span class="token constant">DICTIONARY</span> <span class="token operator">=</span> <span class="token punctuation">{</span> <span class="token string">"AT6"</span> <span class="token punctuation">:</span> <span class="token string">"An toàn điện toán đám mây"</span> <span class="token punctuation">,</span> <span class="token string">"TM5"</span> <span class="token punctuation">:</span> <span class="token string">"Chứng thực điện tử"</span> <span class="token punctuation">,</span> <span class="token string">"TM6"</span> <span class="token punctuation">:</span> <span class="token string">"Phòng chống và điều tra tội phạm máy tính"</span> <span class="token punctuation">,</span> <span class="token string">"TM7"</span> <span class="token punctuation">:</span> <span class="token string">"An toàn Internet & TM điện tử"</span> <span class="token punctuation">,</span> <span class="token string">"AT5"</span> <span class="token punctuation">:</span> <span class="token string">"Quản trị an toàn hệ thống"</span> <span class="token punctuation">}</span> |
So there is enough information to send the message and then reformat the message to send a bit for a reasonable
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | <span class="token keyword">if</span> <span class="token punctuation">(</span> study_code <span class="token operator">!=</span> <span class="token string">""</span> <span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token keyword">var</span> study_name <span class="token operator">=</span> <span class="token constant">DICTIONARY</span> <span class="token punctuation">[</span> study_code <span class="token punctuation">]</span> <span class="token punctuation">;</span> <span class="token keyword">if</span> <span class="token punctuation">(</span> study_name <span class="token operator">==</span> undefined <span class="token punctuation">)</span> <span class="token punctuation">{</span> message <span class="token operator">=</span> <span class="token string">"Opps!!!nLát nữa cậu phải học môn có mã là "</span> <span class="token operator">+</span> study_code <span class="token operator">+</span> <span class="token string">" từ 18h đến 21h đấy nhé!!"</span> <span class="token punctuation">;</span> message <span class="token operator">=</span> message <span class="token operator">+</span> <span class="token string">"nVui lòng update lại từ điển để mình có thể biết được tên môn"</span> <span class="token punctuation">;</span> message <span class="token operator">=</span> message <span class="token operator">+</span> <span class="token string">"nNếu mã môn trên là sai, hãy xem lại thời khóa biểu tại: "</span> <span class="token operator">+</span> url <span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token keyword">else</span> <span class="token punctuation">{</span> message <span class="token operator">=</span> <span class="token string">"Hê lô mai phen :))nLát nữa cậu phải học môn "</span> <span class="token operator">+</span> study_name <span class="token operator">+</span> <span class="token string">" từ 18h đến 21h đấy nhé!!"</span> <span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token punctuation">}</span> <span class="token keyword">else</span> <span class="token punctuation">{</span> message <span class="token operator">=</span> <span class="token string">"Hôm nay cậu được nghỉ đấy (tunghoa)"</span> <span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token keyword">if</span> <span class="token punctuation">(</span> week_mod <span class="token operator">==</span> <span class="token number">3</span> <span class="token punctuation">)</span> <span class="token punctuation">{</span> message <span class="token operator">=</span> message <span class="token operator">+</span> <span class="token string">"nHôm nay cũng là thứ 4, 21h đi đá bóng nhé"</span> <span class="token punctuation">;</span> <span class="token punctuation">}</span> |
The next step is to send the message with the above message
. Here I use Chatwork, Facebook Messenger, Telegram to receive notification messages.
Send a message about Chatwork
To be able to send messages about chatwork, we must first have the API_KEY of Chatwork . I have the document of the chatwork, so to be able to mention a person in the message, I have to use webhook, but my function is not working by this mechanism, so I used a small trick as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | <span class="token keyword">function</span> <span class="token function">send_to_chatwork</span> <span class="token punctuation">(</span> textMessage <span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token keyword">var</span> room_id <span class="token operator">=</span> <span class="token string">"170443428"</span> <span class="token keyword">var</span> <span class="token constant">API_TOKEN</span> <span class="token operator">=</span> <span class="token constant">YOUR_API_CHATWORK</span> <span class="token keyword">var</span> baseText <span class="token operator">=</span> <span class="token string">"[toall] Boss n"</span> <span class="token operator">+</span> textMessage <span class="token comment">//private user [To:3797875]</span> <span class="token keyword">var</span> headers <span class="token operator">=</span> <span class="token punctuation">{</span> <span class="token string">"X-ChatWorkToken"</span> <span class="token punctuation">:</span> <span class="token constant">API_TOKEN</span> <span class="token punctuation">}</span> <span class="token keyword">var</span> payload <span class="token operator">=</span> <span class="token punctuation">{</span> <span class="token string">"body"</span> <span class="token punctuation">:</span> baseText <span class="token punctuation">}</span> <span class="token keyword">var</span> options <span class="token operator">=</span> <span class="token punctuation">{</span> <span class="token string">"method"</span> <span class="token punctuation">:</span> <span class="token string">"post"</span> <span class="token punctuation">,</span> <span class="token string">"headers"</span> <span class="token punctuation">:</span> headers <span class="token punctuation">,</span> <span class="token string">"payload"</span> <span class="token punctuation">:</span> payload <span class="token punctuation">,</span> <span class="token string">"muteHttpExceptions"</span> <span class="token punctuation">:</span> <span class="token boolean">true</span> <span class="token punctuation">}</span> UrlFetchApp <span class="token punctuation">.</span> <span class="token function">fetch</span> <span class="token punctuation">(</span> <span class="token string">"https://api.chatwork.com/v2/rooms/"</span> <span class="token operator">+</span> room_id <span class="token operator">+</span> <span class="token string">"/messages?force=0"</span> <span class="token punctuation">,</span> options <span class="token punctuation">)</span> <span class="token punctuation">;</span> Logger <span class="token punctuation">.</span> <span class="token function">log</span> <span class="token punctuation">(</span> <span class="token string">"Send Chatwork ok"</span> <span class="token punctuation">)</span> <span class="token punctuation">;</span> <span class="token punctuation">}</span> |
Create your own and Bot a room chat, then add two people in the message sent to the text [toall]
so all the members in the room are mentioned in the message
Send messages via Messenger and Telegram
Telegram:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | <span class="token keyword">function</span> <span class="token function">send_to_telegram</span> <span class="token punctuation">(</span> textMessage <span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token keyword">var</span> payload <span class="token operator">=</span> <span class="token punctuation">{</span> <span class="token string">"method"</span> <span class="token punctuation">:</span> <span class="token string">"sendMessage"</span> <span class="token punctuation">,</span> <span class="token string">"chat_id"</span> <span class="token punctuation">:</span> <span class="token constant">YOUR_CHAT_ID</span> <span class="token punctuation">,</span> <span class="token string">"text"</span> <span class="token punctuation">:</span> textMessage <span class="token punctuation">,</span> <span class="token string">"parse_mode"</span> <span class="token punctuation">:</span> <span class="token string">"HTML"</span> <span class="token punctuation">}</span> <span class="token keyword">var</span> data <span class="token operator">=</span> <span class="token punctuation">{</span> <span class="token string">"method"</span> <span class="token punctuation">:</span> <span class="token string">"post"</span> <span class="token punctuation">,</span> <span class="token string">"payload"</span> <span class="token punctuation">:</span> payload <span class="token punctuation">}</span> <span class="token keyword">var</span> <span class="token constant">API_TOKEN</span> <span class="token operator">=</span> <span class="token constant">YOUR_API_BOT_TELEGRAM</span> <span class="token punctuation">;</span> UrlFetchApp <span class="token punctuation">.</span> <span class="token function">fetch</span> <span class="token punctuation">(</span> <span class="token string">'https://api.telegram.org/bot'</span> <span class="token operator">+</span> <span class="token constant">API_TOKEN</span> <span class="token operator">+</span> <span class="token string">'/'</span> <span class="token punctuation">,</span> data <span class="token punctuation">)</span> <span class="token punctuation">;</span> Logger <span class="token punctuation">.</span> <span class="token function">log</span> <span class="token punctuation">(</span> <span class="token string">"Send Telegram ok"</span> <span class="token punctuation">)</span> <span class="token punctuation">;</span> <span class="token punctuation">}</span> |
Messenger
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | <span class="token keyword">function</span> <span class="token function">send_to_facebook</span> <span class="token punctuation">(</span> textMessage <span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token keyword">var</span> recipient_ids <span class="token operator">=</span> <span class="token punctuation">[</span> <span class="token constant">YOUR_CHAT_ID</span> <span class="token punctuation">]</span> <span class="token punctuation">;</span> <span class="token keyword">var</span> <span class="token constant">API_TOKEN</span> <span class="token operator">=</span> <span class="token constant">YOUR_GRAPH_API_FACEBOOK</span> <span class="token keyword">for</span> <span class="token punctuation">(</span> <span class="token keyword">var</span> j <span class="token operator">=</span> <span class="token number">0</span> <span class="token punctuation">;</span> j <span class="token operator"><</span> recipient_ids <span class="token punctuation">.</span> length <span class="token punctuation">;</span> j <span class="token operator">++</span> <span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token keyword">var</span> messageData <span class="token operator">=</span> <span class="token punctuation">{</span> <span class="token string">"recipient"</span> <span class="token punctuation">:</span> <span class="token punctuation">{</span> <span class="token string">"id"</span> <span class="token punctuation">:</span> recipient_ids <span class="token punctuation">[</span> j <span class="token punctuation">]</span> <span class="token punctuation">}</span> <span class="token punctuation">,</span> <span class="token string">"message"</span> <span class="token punctuation">:</span> <span class="token punctuation">{</span> <span class="token string">"text"</span> <span class="token punctuation">:</span> textMessage <span class="token punctuation">}</span> <span class="token punctuation">}</span> <span class="token keyword">var</span> JSONdMessageData <span class="token operator">=</span> <span class="token punctuation">{</span> <span class="token punctuation">}</span> <span class="token keyword">for</span> <span class="token punctuation">(</span> <span class="token keyword">var</span> i <span class="token keyword">in</span> messageData <span class="token punctuation">)</span> <span class="token punctuation">{</span> JSONdMessageData <span class="token punctuation">[</span> i <span class="token punctuation">]</span> <span class="token operator">=</span> <span class="token constant">JSON</span> <span class="token punctuation">.</span> <span class="token function">stringify</span> <span class="token punctuation">(</span> messageData <span class="token punctuation">[</span> i <span class="token punctuation">]</span> <span class="token punctuation">)</span> <span class="token punctuation">}</span> <span class="token keyword">var</span> payload <span class="token operator">=</span> JSONdMessageData <span class="token comment">// payload.access_token = API_TOKEN</span> <span class="token keyword">var</span> options <span class="token operator">=</span> <span class="token punctuation">{</span> <span class="token string">"method"</span> <span class="token punctuation">:</span> <span class="token string">"post"</span> <span class="token punctuation">,</span> <span class="token string">"payload"</span> <span class="token punctuation">:</span> payload <span class="token punctuation">}</span> UrlFetchApp <span class="token punctuation">.</span> <span class="token function">fetch</span> <span class="token punctuation">(</span> <span class="token string">"https://graph.facebook.com/v5.0/me/messages?access_token="</span> <span class="token operator">+</span> <span class="token constant">API_TOKEN</span> <span class="token punctuation">,</span> options <span class="token punctuation">)</span> <span class="token punctuation">;</span> <span class="token punctuation">}</span> Logger <span class="token punctuation">.</span> <span class="token function">log</span> <span class="token punctuation">(</span> <span class="token string">"Send Facebook ok"</span> <span class="token punctuation">)</span> <span class="token punctuation">;</span> <span class="token punctuation">}</span> |
For Telegram we have to create a Telegram Bot and get YOUR_CHAT_ID
through YOUR_API_BOT_TELEGRAM
of that bot you can see the instructions here
For Messenger, we need to create a Page for Bot, then send Page a message to be able to get YOUR_CHAT_ID
through YOUR_GRAPH_API_FACEBOOK
, and about the details, I do not guide anymore to avoid lengthy, if really If you are interested, you can refer to other articles related to Graph API of Facebook
Finally, the setting for my function runs daily at a certain time frame
Here select the function name to execute to run daily
So you all know how the Bot works ).
Depending on the creativity, you can write a Bot with different functions, here I have to write more weather alerts.
And here is the result
My article here is the end of hope to help and bring joy to everyone. However, when I finished writing this article, my concept of ChatBot seemed to be confused , because ChatBot can reply automatically when receiving a message, currently my Bot has not done that, in fact it is just a function that pushes the message for a while.
So you understand what the ChatBot concept is like, if I’m misunderstanding then please let me know