为了将 Google Calendar 与 MySQL 事件同步需要建立 Google Api 与本地 MySQL 资料库
完整Code - https://github.com/su0625/google_calendar-mysql
Google Api
1.建立完 Google API 之后可以获得 API 金钥以及 OAuth 用户端 ID
2.将 API 金钥以及 OAuth 用户端 ID,贴到 View/Calendar 底下的每份 Ejs 里
MYSQL
1.建立名称为 event 的资料表 栏位分别为 id,Date,Event,Event_id
网页
JavaScript
新增事件(Google Calendar)
先透过getElementById
获得新增事件所需的时间、事件名称,并将值加入 resource,指定时区为Asia/Taipei
,再利用 Google Api 的insert 就可以将事件加入到 Calendar
function insertEvents() { var start = document.getElementById("start").value; var end = document.getElementById("end").value; var summary=document.getElementById("summary").value; // 判断框框是否为空 if (start.length > 0 && end.length > 0 && summary.length>0){ var resource = { "summary": summary, "description": "", "location": "Tapei", "start": { "dateTime": start+":00", "timeZone":"Asia/Taipei" }, "end": { "dateTime": end+":00", "timeZone":"Asia/Taipei" } }; var request = gapi.client.calendar.events.insert({ 'calendarId': 'primary', 'resource': resource }); request.execute(function(resp) { console.log(resp); }); alert("Added successfully") }else{ alert("Please check your datetime and event.") } }
新增事件(MySQL)
当 Calendar 事件新增完后,透过选取事件範围时间并按下List event 就可以查看事件是否成功加入,另外编辑删除事件时都需要用到 Event Id,因此需要特别储存起来
var array1 = []; //建立空阵列 var event_content = []; var event_time=[]; var event_id =[] if (events.length > 0) { for (i = 0; i < events.length; i++) { var event = events[i]; var when = event.start.dateTime; var id = event.id if (!when) { when = event.start.date; } //将事件文字丢入阵列 array1.push('<br>'+event.summary + ' (' + when + ')') event_content.push(event.summary) event_time.push(when) event_id.push(id) } document.getElementById("event_name").innerHTML = "Upcoming events:"; // 列出最近10笔event document.getElementById("Sdate_name").innerHTML= array1 ; document.getElementById("event_content").value= event_content ; document.getElementById("event_content_time").value= event_time ; document.getElementById("event_id").value= event_id ; } else{ document.getElementById("event_name").innerHTML = "No upcoming events"; }
再按下 SQL 按钮触发 Post,显示出来的事件就会新增到本地 MySQL
app.post('/', function(req, res, next) { search_text = req.body.searchText; // 要新增的 Data event_content = req.body.event_content event_time = req.body.event_content_time event_id = req.body.event_id // sql 已有Data sql_date = req.body.sql_date sql_event = req.body.sql_event sql_event_id = req.body.sql_event_id console.log(event_content) event_content = event_content.split(","); event_time = event_time.split(","); event_id = event_id.split(","); // 检查sql 是不是空的 if (typeof sql_event === "undefined") { console.log("SQL Empty") sql_event_id=["Empty"] } for (i=0; i< sql_event_id.length; i++){ if (sql_event_id.includes(event_id[i]+"</td")){ console.log("重複事件") delete event_content[i] delete event_time[i] delete event_id[i] } else{ continue; } } // 删掉空值 var event_content = event_content.filter(el => el); var event_time = event_time.filter(el => el); var event_id = event_id.filter(el => el); console.log("删除完剩下",event_content,event_time,event_id) req.getConnection(function(error, conn) { for (i=0; i< event_content.length; i++) { event_time[i] = event_time[i].replace('+08:00','') var content = { Date: event_time[i], Event: event_content[i], Event_id: event_id[i], } conn.query('INSERT INTO event SET ? ', content, function(err, result) { if (err) { console.log("err") throw err } else { req.flash('success', 'Data added successfully!') } }) } res.redirect('/calendar') })})
显示事件页面
当按下 Edit 时,页面跳转,自动带入原先事件名称及开始时间
编辑事件(Google Calendar)
在 gapi 里带入需要编辑事件的event id,时间(start_date,end_date),事件名称(event_content)
function calendar_Edit(id,event_id) { var event_content = document.getElementById("event_name").value; var start_date = document.getElementById("start_date").value; var end_date = document.getElementById("end_date").value; console.log(id,event_id,event_content) console.log("start",start_date) var request = gapi.client.calendar.events.update({ 'calendarId': 'primary', 'eventId': event_id, "resource": { "end": { "dateTime": end_date+":00", "timeZone":"Asia/Taipei" }, "start": { "dateTime": start_date+":00", "timeZone":"Asia/Taipei" }, "summary": event_content } }); request.execute(function(resp) { console.log(resp); }); }
编辑事件(MySQL)
MySQL 的部分是透过 id 指定需要编辑的事件
app.get('/edit', function (req, res, next) { var id = req.query.id; console.log('Edit id',id) req.getConnection(function(error, conn) { conn.query('SELECT * FROM event WHERE id = ?', id, function (err, rows) { if (err) { console.log(err); } var data = rows; res.render('calendar/edit', { title: 'Edit event', data: data }); }); });})
删除事件(Google Calendar)
在 gapi 里带入需要删除事件的event id
function calendar_delete(id,event_id) { var request = gapi.client.calendar.events.delete({ 'calendarId': 'primary', 'eventId': event_id, }); request.execute(function(resp) { console.log(resp); }); // 删除sql event window.location.href = "/calendar/delete?id=" + id; }
删除事件(MySQL)
MySQL 的部分是透过 id 指定需要删除的事件
app.get('/delete', function (req, res, next) { var id = req.query.id; console.log(id) req.getConnection(function(error, conn) { conn.query('DELETE FROM event WHERE id = ?', id, function (err, rows) { if (err) { console.log(err); } res.redirect('/calendar/event'); }); });})
Reference:
https://developers.google.com/calendar/api/v3/reference/events/insert