利用JavaScript 连接Google Calendar与MySQL,同步编辑删除事件

为了将 Google Calendar 与 MySQL 事件同步需要建立 Google Api 与本地 MySQL 资料库

完整Code - https://github.com/su0625/google_calendar-mysql

Google Api
1.建立完 Google API 之后可以获得 API 金钥以及 OAuth 用户端 ID
http://img2.58codes.com/2024/20142082GhpJmFwRYB.png

2.将 API 金钥以及 OAuth 用户端 ID,贴到 View/Calendar 底下的每份 Ejs 里
http://img2.58codes.com/2024/20142082wxadOJTeVj.png

MYSQL
1.建立名称为 event 的资料表 栏位分别为 id,Date,Event,Event_id
http://img2.58codes.com/2024/201420829ytAWeNxNI.png

网页
http://img2.58codes.com/2024/20142082z0nY9SaGL7.png

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')  })})

显示事件页面
http://img2.58codes.com/2024/201420828rfVw6Bj1V.png
当按下 Edit 时,页面跳转,自动带入原先事件名称及开始时间
http://img2.58codes.com/2024/20142082p95tg38RDJ.png

编辑事件(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


关于作者: 网站小编

码农网专注IT技术教程资源分享平台,学习资源下载网站,58码农网包含计算机技术、网站程序源码下载、编程技术论坛、互联网资源下载等产品服务,提供原创、优质、完整内容的专业码农交流分享平台。

热门文章