I saw some senior creators on the Internet sharing how to use Google spreadsheets to build a database and combine it with the chatbot of the official LINE account to build some data queries. This whole process made me feel quite interested, so I started to use it. Started my own path of learning and testing.

　　In this test, a Google spreadsheet is used as the database, and the command "importdata()" for retrieving web page data is used to retrieve the "Meteorological Data Open Data Platform" of the Meteorological Bureau and the "Environmental Data Open Platform" of the Environmental Protection Department, and Google Some functions of the spreadsheet are used, and APP Script is used to extract, arrange, calculate, and integrate data every hour. After it is produced, it will look like the one shown in Figure 1. 

　　Since everyone has different habits of sorting and arranging data, the arrangement model does not necessarily have to be as shown in Figure 1. This database will start recording in November 2020, and historical environmental weather data can also be reviewed later.

　　After completing the creation of the data, I created a database that can be captured by the LINE chat robot, as shown in Figure 2 below, to facilitate the use of Google Apps Script and the LINE chat robot to establish a data acquisition bridge; then create an official LINE account Automatically respond to messages, as shown in Figure 3. The main goal of this test is to easily check the environmental weather in various counties and cities in Taiwan. The purpose is also to allow you to easily check whether the current environmental weather is suitable for going out or what you need to pay attention.

　　The next step is to write the code for Google Apps Script, as shown in Figure 4 below, to turn the Google Sheet data into a corresponding database that responds to the LINE chat robot. The code part also refers to the sharing and teachings of many seniors on the Internet, and then It has been extended and expanded, and completed after repeated tests, so I really want to thank the seniors for their sharing and teaching.

　　If the link is successful, LINE's chatbot will respond to the message we set. Judging from the picture below, Figure 4 is the response that will appear when you click on the "Operation Instructions" of the menu; Figure 5 is when you click on the "Environmental Weather Overview of Various Regions" in the menu, the responses for Beibeiji, Taozhumiao, and Zhongshan will appear. There are several multi-page messages such as Changtou, Yunjia Nan, Gaoping District, Yihua East, etc. Figure 6 is an example of clicking "Changchang Investment", and the reply shows three more than the Taichung area, Changhua area, and Nantou area Page message; Figure 7 takes "Taichung Area" as an example, and LINE will retrieve the Google Sheet data to respond.

　　Below is the QR-Code I tested using LINE. The function is quite simple. It is just for testing if you are interested. On the other hand, you can also use it to check the environment and weather conditions. If you are interested, you can also use LINE to scan and play with it. Quite interesting.

　　During testing, sometimes "#N/A" appears in some data. There are two possibilities for this. One is that the open data platform is temporarily unable to display data due to some factors. It may be that the equipment is under maintenance or other factors; Another situation is that Google Apps Script exceeds the execution limit; but for personal use, Google Spreadsheet is complete and sufficient in terms of functionality and data calculation and analysis.

◎References:

1. Google Docs Editor Instructions - Google Spreadsheets

2. List of Google spreadsheet functions

3. LINE Official Account Product Operation Manual

4. Google Apps Script quotas and limits

5. 大榔頭的電腦隨筆-GAS17： Linebot’s Basic Interactive Postback Function

6. Implement LINE chatbot (Google Apps Script)