利用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

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