我的軟體寶藏庫

// Google Apps Script 程式碼
const DATA_SHEET_NAME = "寶藏資料";
const CONFIG_SHEET_NAME = "設定";
const SPREADSHEET = SpreadsheetApp.getActiveSpreadsheet();

function setup() {
  // 設定資料工作表
  let dataSheet = SPREADSHEET.getSheetByName(DATA_SHEET_NAME);
  if (!dataSheet) {
    dataSheet = SPREADSHEET.insertSheet(DATA_SHEET_NAME);
    const headers = ["ID", "軟體名稱", "網址", "說明文字", "影片連結", "分類", "點擊數"];
    dataSheet.appendRow(headers);
  } else {
    // 檢查點擊數欄位是否存在
    const headers = dataSheet.getRange(1, 1, 1, dataSheet.getLastColumn()).getValues()[0];
    if (headers.indexOf("點擊數") === -1) {
      dataSheet.getRange(1, headers.length + 1).setValue("點擊數");
    }
  }

  // 設定組態工作表
  let configSheet = SPREADSHEET.getSheetByName(CONFIG_SHEET_NAME);
  if (!configSheet) {
    configSheet = SPREADSHEET.insertSheet(CONFIG_SHEET_NAME);
    configSheet.appendRow(["鍵", "值"]);
    configSheet.appendRow(["網頁標題", "我的軟體寶藏庫"]);
    configSheet.appendRow(["最後更新時間", ""]);
  } else {
      const keys = configSheet.getRange("A:A").getValues().flat();
      if (keys.indexOf("最後更新時間") === -1) {
          configSheet.appendRow(["最後更新時間", ""]);
      }
  }
  
  SpreadsheetApp.flush();
}

function doGet(e) {
  try {
    // 取得資料
    const dataSheet = SPREADSHEET.getSheetByName(DATA_SHEET_NAME);
    let treasures = [];
    if (dataSheet && dataSheet.getLastRow() > 1) {
      const dataRange = dataSheet.getRange(2, 1, dataSheet.getLastRow() - 1, dataSheet.getLastColumn());
      const values = dataRange.getValues();
      const headers = dataSheet.getRange(1, 1, 1, dataSheet.getLastColumn()).getValues()[0];
      treasures = values.map(row => {
        let obj = {};
        headers.forEach((header, index) => {
          obj[header] = (header === "ID") ? String(row[index]) : row[index];
        });
        return obj;
      });
    }

    // 取得設定
    const configSheet = SPREADSHEET.getSheetByName(CONFIG_SHEET_NAME);
    let settings = {};
    if (configSheet && configSheet.getLastRow() > 1) {
       const configRange = configSheet.getRange(2, 1, configSheet.getLastRow() - 1, 2);
       const configValues = configRange.getValues();
       configValues.forEach(row => {
         settings[row[0]] = row[1];
       });
    }

    return ContentService.createTextOutput(JSON.stringify({ status: "success", data: { treasures, settings } })).setMimeType(ContentService.MimeType.JSON);
  } catch (error) {
    return ContentService.createTextOutput(JSON.stringify({ status: "error", message: error.toString() })).setMimeType(ContentService.MimeType.JSON);
  }
}

function doPost(e) {
  try {
    const requestData = JSON.parse(e.postData.contents);
    const action = requestData.action;

    if (action === 'batchUpdate') {
      const { treasures, settings } = requestData.payload;
      
      const dataSheet = SPREADSHEET.getSheetByName(DATA_SHEET_NAME);
      if (!dataSheet) throw new Error("資料工作表不存在");
      const dataHeaders = dataSheet.getRange(1, 1, 1, dataSheet.getLastColumn()).getValues()[0];
      if (dataSheet.getLastRow() > 1) {
        dataSheet.getRange(2, 1, dataSheet.getLastRow() - 1, dataSheet.getLastColumn()).clearContent();
      }
      if (treasures && treasures.length > 0) {
        const newValues = treasures.map(entry => dataHeaders.map(header => entry[header] || ""));
        dataSheet.getRange(2, 1, newValues.length, newValues[0].length).setValues(newValues);
      }
      
      const configSheet = SPREADSHEET.getSheetByName(CONFIG_SHEET_NAME);
      if (!configSheet) throw new Error("設定工作表不存在");
      
      const lastUpdateTime = new Date().toLocaleString("zh-TW", { 
          timeZone: "Asia/Taipei",
          year: 'numeric',
          month: '2-digit',
          day: '2-digit',
          hour: '2-digit',
          minute: '2-digit',
          hour12: false
      }).replace(/, /g, ' ');
      
      const keys = configSheet.getRange("A:A").getValues().flat();
      const titleIndex = keys.indexOf("網頁標題");
      const timeIndex = keys.indexOf("最後更新時間");

      if (titleIndex > -1) {
          configSheet.getRange(titleIndex + 1, 2).setValue(settings["網頁標題"] || "我的軟體寶藏庫");
      }
       if (timeIndex > -1) {
          configSheet.getRange(timeIndex + 1, 2).setValue(lastUpdateTime);
      }

      return ContentService.createTextOutput(JSON.stringify({ status: "success", message: "批次更新成功" })).setMimeType(ContentService.MimeType.JSON);
    
    } else if (action === 'incrementClick') {
      const { ID } = requestData.payload;
      const dataSheet = SPREADSHEET.getSheetByName(DATA_SHEET_NAME);
      if (!dataSheet) throw new Error("資料工作表不存在");

      const idColumnValues = dataSheet.getRange(2, 1, dataSheet.getLastRow() - 1, 1).getValues().flat().map(String);
      const rowIndexInArray = idColumnValues.indexOf(String(ID));
      
      if (rowIndexInArray !== -1) {
        const sheetRowIndex = rowIndexInArray + 2;
        const headers = dataSheet.getRange(1, 1, 1, dataSheet.getLastColumn()).getValues()[0];
        const clickColIndex = headers.indexOf("點擊數");
        if(clickColIndex !== -1) {
          const sheetColIndex = clickColIndex + 1;
          const cell = dataSheet.getRange(sheetRowIndex, sheetColIndex);
          const currentClicks = Number(cell.getValue()) || 0;
          cell.setValue(currentClicks + 1);
          return ContentService.createTextOutput(JSON.stringify({ status: "success", message: "點擊數更新成功" })).setMimeType(ContentService.MimeType.JSON);
        } else {
          throw new Error("找不到 '點擊數' 欄位");
        }
      }
      throw new Error("在試算表中找不到對應的 ID: " + ID);
    }

    return ContentService.createTextOutput(JSON.stringify({ status: "error", message: "未知的操作" })).setMimeType(ContentService.MimeType.JSON);
  } catch (error) {
    return ContentService.createTextOutput(JSON.stringify({ status: "error", message: error.toString() })).setMimeType(ContentService.MimeType.JSON);
  }
}