Connect Google Workspace

// ==========================================
// Google Workspace 통합 API (Full Edition)
// Sheets, Calendar, Gmail, Drive, Docs, Forms, Tasks
// ==========================================

function doPost(e) {
  try {
    var data = JSON.parse(e.postData.contents);
    return handleRequest(data);
  } catch(err) {
    return jsonResponse({status: 'error', message: err.toString()});
  }
}

function doGet(e) {
  try {
    if (e.parameter.action) {
      var data = {};
      for (var key in e.parameter) { data[key] = e.parameter[key]; }
      if (e.parameter.values) data.values = JSON.parse(e.parameter.values);
      if (e.parameter.headers) data.headers = JSON.parse(e.parameter.headers);
      if (e.parameter.questions) data.questions = JSON.parse(e.parameter.questions);
      if (e.parameter.guests) data.guests = JSON.parse(e.parameter.guests);
      if (e.parameter.choices) data.choices = JSON.parse(e.parameter.choices);
      return handleRequest(data);
    }
    return jsonResponse({
      status: 'ok',
      message: 'Google Workspace API is running!',
      services: ['sheets', 'calendar', 'gmail', 'drive', 'docs', 'forms', 'tasks'],
      time: new Date().toISOString()
    });
  } catch(err) {
    return jsonResponse({status: 'error', message: err.toString()});
  }
}

function handleRequest(data) {
  var action = data.action;
  switch(action) {
    // GOOGLE SHEETS
    case 'sheet_append': return handleSheetAppend(data);
    case 'sheet_read': return handleSheetRead(data);
    case 'sheet_update': return handleSheetUpdate(data);
    case 'sheet_create': return handleSheetCreate(data);
    case 'sheet_delete_row': return handleSheetDeleteRow(data);
    case 'sheet_clear': return handleSheetClear(data);
    case 'sheet_get_names': return handleSheetGetNames(data);
    case 'sheet_add_tab': return handleSheetAddTab(data);
    case 'sheet_find': return handleSheetFind(data);

    // GOOGLE CALENDAR
    case 'calendar_add': return handleCalendarAdd(data);
    case 'calendar_list': return handleCalendarList(data);
    case 'calendar_delete': return handleCalendarDelete(data);
    case 'calendar_bulk_delete': return handleCalendarBulkDelete(data);
    case 'calendar_update': return handleCalendarUpdate(data);
    case 'calendar_allday': return handleCalendarAllDay(data);
    case 'calendar_search': return handleCalendarSearch(data);

    // GMAIL
    case 'gmail_send': return handleGmailSend(data);
    case 'gmail_read': return handleGmailRead(data);
    case 'gmail_search': return handleGmailSearch(data);
    case 'gmail_trash': return handleGmailTrash(data);
    case 'gmail_label': return handleGmailLabel(data);
    case 'gmail_mark_read': return handleGmailMarkRead(data);
    case 'gmail_draft': return handleGmailDraft(data);
    case 'gmail_reply': return handleGmailReply(data);

    // GOOGLE DRIVE
    case 'drive_upload': return handleDriveUpload(data);
    case 'drive_list': return handleDriveList(data);
    case 'drive_share': return handleDriveShare(data);
    case 'drive_delete': return handleDriveDelete(data);
    case 'drive_create_folder': return handleDriveCreateFolder(data);
    case 'drive_move': return handleDriveMove(data);
    case 'drive_rename': return handleDriveRename(data);
    case 'drive_get_info': return handleDriveGetInfo(data);
    case 'drive_copy': return handleDriveCopy(data);

    // GOOGLE DOCS
    case 'doc_create': return handleDocCreate(data);
    case 'doc_append': return handleDocAppend(data);
    case 'doc_read': return handleDocRead(data);
    case 'doc_replace': return handleDocReplace(data);

    // GOOGLE FORMS
    case 'form_create': return handleFormCreate(data);
    case 'form_responses': return handleFormResponses(data);

    // GOOGLE TASKS
    case 'tasks_list': return handleTasksList(data);
    case 'tasks_add': return handleTasksAdd(data);
    case 'tasks_complete': return handleTasksComplete(data);
    case 'tasks_delete': return handleTasksDelete(data);

    default:
      return jsonResponse({status: 'error', message: 'Unknown action: ' + action});
  }
}

// ==========================================
// GOOGLE SHEETS
// ==========================================

function handleSheetAppend(data) {
  var sheet = SpreadsheetApp.openById(data.sheetId).getSheetByName(data.sheetName || 'Sheet1');
  sheet.appendRow(data.values);
  return jsonResponse({status: 'success', message: 'Row appended'});
}

function handleSheetRead(data) {
  var sheet = SpreadsheetApp.openById(data.sheetId).getSheetByName(data.sheetName || 'Sheet1');
  var range = sheet.getRange(data.range || 'A1:D10');
  return jsonResponse({status: 'success', data: range.getValues()});
}

function handleSheetUpdate(data) {
  var sheet = SpreadsheetApp.openById(data.sheetId).getSheetByName(data.sheetName || 'Sheet1');
  if (data.values) {
    sheet.getRange(data.range).setValues(data.values);
  } else {
    sheet.getRange(data.range).setValue(data.value);
  }
  return jsonResponse({status: 'success', message: 'Cell updated'});
}

function handleSheetCreate(data) {
  var ss = SpreadsheetApp.create(data.title || 'New Spreadsheet');
  if (data.headers) {
    ss.getActiveSheet().appendRow(data.headers);
  }
  return jsonResponse({status: 'success', sheetId: ss.getId(), url: ss.getUrl()});
}

function handleSheetDeleteRow(data) {
  var sheet = SpreadsheetApp.openById(data.sheetId).getSheetByName(data.sheetName || 'Sheet1');
  sheet.deleteRow(data.row);
  return jsonResponse({status: 'success', message: 'Row ' + data.row + ' deleted'});
}

function handleSheetClear(data) {
  var sheet = SpreadsheetApp.openById(data.sheetId).getSheetByName(data.sheetName || 'Sheet1');
  if (data.range) {
    sheet.getRange(data.range).clear();
  } else {
    sheet.clear();
  }
  return jsonResponse({status: 'success', message: 'Cleared'});
}

function handleSheetGetNames(data) {
  var ss = SpreadsheetApp.openById(data.sheetId);
  var names = ss.getSheets().map(function(s) { return s.getName(); });
  return jsonResponse({status: 'success', sheets: names});
}

function handleSheetAddTab(data) {
  var ss = SpreadsheetApp.openById(data.sheetId);
  var newSheet = ss.insertSheet(data.tabName);
  if (data.headers) newSheet.appendRow(data.headers);
  return jsonResponse({status: 'success', message: 'Tab "' + data.tabName + '" created'});
}

function handleSheetFind(data) {
  var sheet = SpreadsheetApp.openById(data.sheetId).getSheetByName(data.sheetName || 'Sheet1');
  var allData = sheet.getDataRange().getValues();
  var results = [];
  var query = (data.query || '').toLowerCase();
  for (var i = 0; i < allData.length; i++) {
    for (var j = 0; j < allData[i].length; j++) {
      if (String(allData[i][j]).toLowerCase().indexOf(query) !== -1) {
        results.push({row: i + 1, col: j + 1, value: allData[i][j], rowData: allData[i]});
      }
    }
  }
  return jsonResponse({status: 'success', results: results});
}

// ==========================================
// GOOGLE CALENDAR
// ==========================================

function handleCalendarAdd(data) {
  var calendar = CalendarApp.getDefaultCalendar();
  var event = calendar.createEvent(
    data.title,
    new Date(data.startTime),
    new Date(data.endTime),
    {description: data.description || '', location: data.location || ''}
  );
  if (data.guests) {
    data.guests.forEach(function(g) { event.addGuest(g); });
  }
  return jsonResponse({status: 'success', eventId: event.getId()});
}

function handleCalendarList(data) {
  var calendar = CalendarApp.getDefaultCalendar();
  var start = new Date(data.startDate || new Date());
  var end = new Date(data.endDate || new Date(start.getTime() + 7*24*60*60*1000));
  var events = calendar.getEvents(start, end);
  var result = events.map(function(ev) {
    return {
      title: ev.getTitle(), start: ev.getStartTime().toISOString(),
      end: ev.getEndTime().toISOString(), description: ev.getDescription(),
      location: ev.getLocation(), id: ev.getId(), isAllDay: ev.isAllDayEvent()
    };
  });
  return jsonResponse({status: 'success', events: result});
}

function handleCalendarDelete(data) {
  var event = CalendarApp.getDefaultCalendar().getEventById(data.eventId);
  if (!event) return jsonResponse({status: 'error', message: 'Event not found'});
  event.deleteEvent();
  return jsonResponse({status: 'success', message: 'Event deleted'});
}

function handleCalendarBulkDelete(data) {
  var calendar = CalendarApp.getDefaultCalendar();
  var start = new Date(data.startDate || '2020-01-01');
  var end = new Date(data.endDate || '2030-01-01');
  var query = (data.query || '').toLowerCase();
  var events = calendar.getEvents(start, end, query ? {search: query} : {});
  var deleted = 0;
  events.forEach(function(event) {
    if (!query || event.getTitle().toLowerCase().indexOf(query) !== -1) {
      event.deleteEvent();
      deleted++;
    }
  });
  return jsonResponse({status: 'success', deleted: deleted});
}

function handleCalendarUpdate(data) {
  var event = CalendarApp.getDefaultCalendar().getEventById(data.eventId);
  if (!event) return jsonResponse({status: 'error', message: 'Event not found'});
  if (data.title) event.setTitle(data.title);
  if (data.description) event.setDescription(data.description);
  if (data.location) event.setLocation(data.location);
  if (data.startTime && data.endTime) event.setTime(new Date(data.startTime), new Date(data.endTime));
  return jsonResponse({status: 'success', message: 'Event updated'});
}

function handleCalendarAllDay(data) {
  var event = CalendarApp.getDefaultCalendar().createAllDayEvent(
    data.title, new Date(data.date),
    {description: data.description || '', location: data.location || ''}
  );
  return jsonResponse({status: 'success', eventId: event.getId()});
}

function handleCalendarSearch(data) {
  var calendar = CalendarApp.getDefaultCalendar();
  var start = new Date(data.startDate || new Date());
  var end = new Date(data.endDate || new Date(start.getTime() + 30*24*60*60*1000));
  var events = calendar.getEvents(start, end, {search: data.query});
  var result = events.map(function(ev) {
    return {title: ev.getTitle(), start: ev.getStartTime().toISOString(), id: ev.getId()};
  });
  return jsonResponse({status: 'success', events: result});
}

// ==========================================
// GMAIL
// ==========================================

function handleGmailSend(data) {
  GmailApp.sendEmail(data.to, data.subject, data.body, {
    htmlBody: data.htmlBody || null, cc: data.cc || '', bcc: data.bcc || '',
    name: data.senderName || '', replyTo: data.replyTo || ''
  });
  return jsonResponse({status: 'success', message: 'Email sent'});
}

function handleGmailRead(data) {
  var threads = GmailApp.getInboxThreads(0, data.count || 10);
  var result = threads.map(function(thread) {
    var msgs = thread.getMessages();
    var last = msgs[msgs.length - 1];
    return {
      threadId: thread.getId(), subject: last.getSubject(),
      from: last.getFrom(), to: last.getTo(),
      date: last.getDate().toISOString(), body: (last.getPlainBody() || last.getBody() || '').substring(0, 500),
      isUnread: thread.isUnread(), messageCount: msgs.length
    };
  });
  return jsonResponse({status: 'success', emails: result});
}

function handleGmailSearch(data) {
  var threads = GmailApp.search(data.query, 0, data.count || 10);
  var result = threads.map(function(thread) {
    var msg = thread.getMessages()[0];
    return {
      threadId: thread.getId(), subject: msg.getSubject(),
      from: msg.getFrom(), date: msg.getDate().toISOString(),
      snippet: (msg.getPlainBody() || msg.getBody() || '').substring(0, 300), isUnread: thread.isUnread()
    };
  });
  return jsonResponse({status: 'success', emails: result});
}

function handleGmailTrash(data) {
  var thread = GmailApp.getThreadById(data.threadId);
  thread.moveToTrash();
  return jsonResponse({status: 'success', message: 'Moved to trash'});
}

function handleGmailLabel(data) {
  var thread = GmailApp.getThreadById(data.threadId);
  var label = GmailApp.getUserLabelByName(data.label) || GmailApp.createLabel(data.label);
  if (data.remove) {
    thread.removeLabel(label);
  } else {
    thread.addLabel(label);
  }
  return jsonResponse({status: 'success', message: 'Label updated'});
}

function handleGmailMarkRead(data) {
  var thread = GmailApp.getThreadById(data.threadId);
  if (data.unread) { thread.markUnread(); } else { thread.markRead(); }
  return jsonResponse({status: 'success', message: 'Read status updated'});
}

function handleGmailDraft(data) {
  GmailApp.createDraft(data.to, data.subject, data.body, {
    htmlBody: data.htmlBody || null, cc: data.cc || ''
  });
  return jsonResponse({status: 'success', message: 'Draft created'});
}

function handleGmailReply(data) {
  var thread = GmailApp.getThreadById(data.threadId);
  var msgs = thread.getMessages();
  var lastMsg = msgs[msgs.length - 1];
  lastMsg.reply(data.body, {htmlBody: data.htmlBody || null});
  return jsonResponse({status: 'success', message: 'Reply sent'});
}

// ==========================================
// GOOGLE DRIVE
// ==========================================

function handleDriveUpload(data) {
  var blob = Utilities.newBlob(
    Utilities.base64Decode(data.content), data.mimeType || 'text/plain', data.fileName
  );
  var file = DriveApp.createFile(blob);
  if (data.folderId) {
    DriveApp.getFolderById(data.folderId).addFile(file);
    DriveApp.getRootFolder().removeFile(file);
  }
  return jsonResponse({status: 'success', fileId: file.getId(), url: file.getUrl()});
}

function handleDriveList(data) {
  var items = [];
  if (data.folderId) {
    var folder = DriveApp.getFolderById(data.folderId);
    var files = folder.getFiles();
    while (files.hasNext() && items.length < (data.count || 20)) items.push(files.next());
    var folders = folder.getFolders();
    while (folders.hasNext() && items.length < (data.count || 20)) items.push(folders.next());
  } else if (data.query) {
    var it = DriveApp.searchFiles(data.query);
    while (it.hasNext() && items.length < (data.count || 20)) items.push(it.next());
  } else {
    var all = DriveApp.getFiles();
    while (all.hasNext() && items.length < (data.count || 20)) items.push(all.next());
  }
  var output = items.map(function(f) {
    return {name: f.getName(), id: f.getId(), url: f.getUrl(), mimeType: f.getMimeType(), size: f.getSize()};
  });
  return jsonResponse({status: 'success', files: output});
}

function handleDriveShare(data) {
  var file = DriveApp.getFileById(data.fileId);
  if (data.role === 'viewer') { file.addViewer(data.email); }
  else { file.addEditor(data.email); }
  return jsonResponse({status: 'success', message: 'Shared with ' + data.email});
}

function handleDriveDelete(data) {
  DriveApp.getFileById(data.fileId).setTrashed(true);
  return jsonResponse({status: 'success', message: 'File moved to trash'});
}

function handleDriveCreateFolder(data) {
  var parent = data.parentId ? DriveApp.getFolderById(data.parentId) : DriveApp.getRootFolder();
  var folder = parent.createFolder(data.name);
  return jsonResponse({status: 'success', folderId: folder.getId(), url: folder.getUrl()});
}

function handleDriveMove(data) {
  var file = DriveApp.getFileById(data.fileId);
  var dest = DriveApp.getFolderById(data.folderId);
  var parents = file.getParents();
  if (parents.hasNext()) parents.next().removeFile(file);
  dest.addFile(file);
  return jsonResponse({status: 'success', message: 'File moved'});
}

function handleDriveRename(data) {
  DriveApp.getFileById(data.fileId).setName(data.newName);
  return jsonResponse({status: 'success', message: 'Renamed to ' + data.newName});
}

function handleDriveGetInfo(data) {
  var file = DriveApp.getFileById(data.fileId);
  return jsonResponse({
    status: 'success',
    name: file.getName(), mimeType: file.getMimeType(), size: file.getSize(),
    url: file.getUrl(), created: file.getDateCreated().toISOString(),
    updated: file.getLastUpdated().toISOString(),
    owner: file.getOwner() ? file.getOwner().getEmail() : 'unknown',
    sharingAccess: file.getSharingAccess().toString()
  });
}

function handleDriveCopy(data) {
  var file = DriveApp.getFileById(data.fileId);
  var copy = file.makeCopy(data.newName || file.getName());
  if (data.folderId) {
    DriveApp.getFolderById(data.folderId).addFile(copy);
    DriveApp.getRootFolder().removeFile(copy);
  }
  return jsonResponse({status: 'success', fileId: copy.getId(), url: copy.getUrl()});
}

// ==========================================
// GOOGLE DOCS
// ==========================================

function handleDocCreate(data) {
  var doc = DocumentApp.create(data.title || 'New Document');
  if (data.content) doc.getBody().appendParagraph(data.content);
  return jsonResponse({status: 'success', docId: doc.getId(), url: doc.getUrl()});
}

function handleDocAppend(data) {
  var doc = DocumentApp.openById(data.docId);
  doc.getBody().appendParagraph(data.content);
  doc.saveAndClose();
  return jsonResponse({status: 'success', message: 'Content appended'});
}

function handleDocRead(data) {
  var doc = DocumentApp.openById(data.docId);
  var text = doc.getBody().getText();
  return jsonResponse({status: 'success', title: doc.getName(), content: text});
}

function handleDocReplace(data) {
  var doc = DocumentApp.openById(data.docId);
  doc.getBody().replaceText(data.searchPattern, data.replacement);
  doc.saveAndClose();
  return jsonResponse({status: 'success', message: 'Text replaced'});
}

// ==========================================
// GOOGLE FORMS
// ==========================================

function handleFormCreate(data) {
  var form = FormApp.create(data.title || 'New Form');
  if (data.description) form.setDescription(data.description);

  if (data.questions) {
    data.questions.forEach(function(q) {
      switch(q.type) {
        case 'text':
          form.addTextItem().setTitle(q.title).setRequired(q.required || false); break;
        case 'paragraph':
          form.addParagraphTextItem().setTitle(q.title).setRequired(q.required || false); break;
        case 'multiple':
          var mc = form.addMultipleChoiceItem().setTitle(q.title);
          mc.setChoiceValues(q.choices); mc.setRequired(q.required || false); break;
        case 'checkbox':
          var cb = form.addCheckboxItem().setTitle(q.title);
          cb.setChoiceValues(q.choices); cb.setRequired(q.required || false); break;
        case 'dropdown':
          var dd = form.addListItem().setTitle(q.title);
          dd.setChoiceValues(q.choices); dd.setRequired(q.required || false); break;
        case 'scale':
          form.addScaleItem().setTitle(q.title)
            .setBounds(q.low || 1, q.high || 5)
            .setRequired(q.required || false); break;
        case 'date':
          form.addDateItem().setTitle(q.title).setRequired(q.required || false); break;
        case 'time':
          form.addTimeItem().setTitle(q.title).setRequired(q.required || false); break;
      }
    });
  }
  return jsonResponse({
    status: 'success', formId: form.getId(),
    editUrl: form.getEditUrl(), publishedUrl: form.getPublishedUrl()
  });
}

function handleFormResponses(data) {
  var form = FormApp.openById(data.formId);
  var responses = form.getResponses();
  var result = responses.slice(-(data.count || 50)).map(function(r) {
    var answers = r.getItemResponses().map(function(ir) {
      return {question: ir.getItem().getTitle(), answer: ir.getResponse()};
    });
    return {timestamp: r.getTimestamp().toISOString(), answers: answers};
  });
  return jsonResponse({status: 'success', count: responses.length, responses: result});
}

// ==========================================
// GOOGLE TASKS
// ==========================================

function checkTasksEnabled() {
  if (typeof Tasks === 'undefined') {
    throw new Error('Tasks API not enabled. Add it via Services (+) in the sidebar.');
  }
}

function handleTasksList(data) {
  checkTasksEnabled();
  var taskLists = Tasks.Tasklists.list();
  if (data.listId) {
    var tasks = Tasks.Tasks.list(data.listId, {showCompleted: data.showCompleted || false});
    return jsonResponse({status: 'success', tasks: tasks.items || []});
  }
  var lists = (taskLists.items || []).map(function(l) {
    return {id: l.id, title: l.title};
  });
  return jsonResponse({status: 'success', taskLists: lists});
}

function handleTasksAdd(data) {
  checkTasksEnabled();
  var listId = data.listId || Tasks.Tasklists.list().items[0].id;
  var task = Tasks.Tasks.insert({
    title: data.title,
    notes: data.notes || '',
    due: data.due || null
  }, listId);
  return jsonResponse({status: 'success', taskId: task.id, title: task.title});
}

function handleTasksComplete(data) {
  checkTasksEnabled();
  var listId = data.listId || Tasks.Tasklists.list().items[0].id;
  Tasks.Tasks.patch({status: 'completed'}, listId, data.taskId);
  return jsonResponse({status: 'success', message: 'Task completed'});
}

function handleTasksDelete(data) {
  checkTasksEnabled();
  var listId = data.listId || Tasks.Tasklists.list().items[0].id;
  Tasks.Tasks.remove(listId, data.taskId);
  return jsonResponse({status: 'success', message: 'Task deleted'});
}

// ==========================================
// Utility
// ==========================================

function jsonResponse(data) {
  return ContentService
    .createTextOutput(JSON.stringify(data))
    .setMimeType(ContentService.MimeType.JSON);
}

// ==========================================
// Run this once from the editor to grant all permissions
// ==========================================

function authorize() {
  CalendarApp.getDefaultCalendar();
  GmailApp.getInboxThreads(0, 1);
  DriveApp.getFiles();
  var doc = DocumentApp.create('_auth_test');
  DriveApp.getFileById(doc.getId()).setTrashed(true);
  var form = FormApp.create('_auth_test');
  DriveApp.getFileById(form.getId()).setTrashed(true);
  Logger.log('All permissions granted!');
}