利用JavaScript 連接Google Calendar與MySQL，同步編輯刪除事件

javascript google calendar

Yingge170 2021-11-06 16:03:48 ‧ 1701 瀏覽

分享至

為了將 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