|
// 建立或獲取指定資料夾 |
|
function getOrCreateFolder(parentFolder, folderName) { |
|
const folders = parentFolder.getFoldersByName(folderName); |
|
if (folders.hasNext()) { |
|
return folders.next(); |
|
} else { |
|
return parentFolder.createFolder(folderName); |
|
} |
|
} |
|
|
|
// 取得或創建當天的檔案 |
|
function getOrCreateDailyFile(format) { |
|
const today = new Date(); |
|
const year = today.getFullYear().toString(); |
|
const month = Utilities.formatString("%02d", today.getMonth() + 1); |
|
|
|
// 獲取或創建 GoogleAppsScript 目錄 |
|
const rootFolders = DriveApp.getFoldersByName("GoogleAppsScript"); |
|
let rootFolder; |
|
if (rootFolders.hasNext()) { |
|
rootFolder = rootFolders.next(); |
|
} else { |
|
rootFolder = DriveApp.createFolder("GoogleAppsScript"); |
|
} |
|
|
|
// 建立年份資料夾 |
|
const yearFolder = getOrCreateFolder(rootFolder, year); |
|
// 建立月份資料夾 |
|
const monthFolder = getOrCreateFolder(yearFolder, `${year}-${month}`); |
|
|
|
// 設定檔案名稱 (年-月-日) |
|
const fileName = Utilities.formatDate(today, "GMT+8", "yyyy-MM-dd") + `.${format}`; |
|
|
|
// 在月份資料夾中尋找今天的檔案 |
|
let file; |
|
const files = monthFolder.getFilesByName(fileName); |
|
|
|
if (files.hasNext()) { |
|
file = files.next(); |
|
} else { |
|
// 創建新的檔案 |
|
if (format === 'xlsx') { |
|
const spreadsheet = SpreadsheetApp.create(fileName); |
|
DriveApp.getFileById(spreadsheet.getId()).moveTo(monthFolder); |
|
const sheet = spreadsheet.getSheets()[0]; |
|
sheet.getRange("A1:D1").setValues([["時間戳記", "訊息內容", "發送者", "其他資料"]]); |
|
file = DriveApp.getFileById(spreadsheet.getId()); |
|
} else { |
|
file = monthFolder.createFile(fileName, "時間戳記,訊息內容,發送者,其他資料\n", MimeType.PLAIN_TEXT); |
|
} |
|
} |
|
|
|
return file; |
|
} |
|
|
|
// 寫入數據的 doGet 函數 |
|
function doGet(e) { |
|
const action = e.parameter.action || 'write'; |
|
const format = e.parameter.format || 'xlsx'; // 預設為 xlsx 格式 |
|
|
|
if (action === 'read') { |
|
return getSheetData(e); |
|
} |
|
|
|
try { |
|
const params = e.parameter; |
|
const file = getOrCreateDailyFile(format); |
|
|
|
// 準備要寫入的數據 |
|
const timestamp = new Date().toLocaleString("zh-TW", {timeZone: "Asia/Taipei"}); |
|
const rowData = `${timestamp},${params.message || ""},${params.sender || ""},${params.extraData || ""}\n`; |
|
|
|
if (format === 'xlsx') { |
|
const spreadsheet = SpreadsheetApp.openById(file.getId()); |
|
const sheet = spreadsheet.getSheets()[0]; |
|
sheet.appendRow([timestamp, params.message || "", params.sender || "", params.extraData || ""]); |
|
} else { |
|
const content = file.getBlob().getDataAsString() + rowData; |
|
file.setContent(content); |
|
} |
|
|
|
return ContentService.createTextOutput(JSON.stringify({ |
|
status: "success", |
|
message: "數據已成功儲存", |
|
data: params |
|
})).setMimeType(ContentService.MimeType.JSON); |
|
|
|
} catch (error) { |
|
return ContentService.createTextOutput(JSON.stringify({ |
|
status: "error", |
|
message: error.toString() |
|
})).setMimeType(ContentService.MimeType.JSON); |
|
} |
|
} |
|
|
|
// 讀取數據的函數 |
|
function getSheetData(e) { |
|
try { |
|
const params = e.parameter; |
|
const targetDate = params.date || Utilities.formatDate(new Date(), "GMT+8", "yyyy-MM-dd"); |
|
const format = params.format || 'xlsx'; |
|
|
|
// 解析目標日期 |
|
const dateParts = targetDate.split('-'); |
|
const year = dateParts[0]; |
|
const month = dateParts[1]; |
|
|
|
// 獲取 GoogleAppsScript 目錄 |
|
const rootFolder = DriveApp.getFoldersByName("GoogleAppsScript").next(); |
|
const yearFolder = rootFolder.getFoldersByName(year).next(); |
|
const monthFolder = yearFolder.getFoldersByName(`${year}-${month}`).next(); |
|
const files = monthFolder.getFilesByName(`${targetDate}.${format}`); |
|
|
|
if (!files.hasNext()) { |
|
return ContentService.createTextOutput(JSON.stringify({ |
|
status: "error", |
|
message: "找不到指定日期的檔案" |
|
})).setMimeType(ContentService.MimeType.JSON); |
|
} |
|
|
|
const file = files.next(); |
|
let data; |
|
|
|
if (format === 'xlsx') { |
|
const spreadsheet = SpreadsheetApp.openById(file.getId()); |
|
const sheet = spreadsheet.getSheets()[0]; |
|
data = sheet.getDataRange().getValues(); |
|
} else { |
|
const content = file.getBlob().getDataAsString(); |
|
data = content.split('\n').map(line => line.split(',')); |
|
} |
|
|
|
// 將數據轉換為 JSON 格式 |
|
const headers = data[0]; |
|
const jsonData = data.slice(1).filter(row => row.length > 1).map(row => { |
|
let obj = {}; |
|
headers.forEach((header, index) => { |
|
obj[header] = row[index]; |
|
}); |
|
return obj; |
|
}); |
|
|
|
return ContentService.createTextOutput(JSON.stringify({ |
|
status: "success", |
|
date: targetDate, |
|
data: jsonData |
|
})).setMimeType(ContentService.MimeType.JSON); |
|
|
|
} catch (error) { |
|
return ContentService.createTextOutput(JSON.stringify({ |
|
status: "error", |
|
message: error.toString() |
|
})).setMimeType(ContentService.MimeType.JSON); |
|
} |
|
} |