~楓花雪岳~: [GAS] 上傳檔案至 Google Drive

星期五, 12月 26, 2025

[GAS] 上傳檔案至 Google Drive - Excel to Sheet

在 Google Apps Script 內操作檔案有兩種方式，分別為 DriveApp 和 Drive API，要把 Excel 轉為 Sheet 必須使用 Drive API 才能進行轉換

Drvie API 服務

要使用 Drive API 必須先在服務內加入，該筆記是加入 Drive API V3，該筆記記錄當下 V2 還是存在的

index.html

<!DOCTYPE html>
<html>

<head>
  <base target="_top">
  <?!= include('css') ?>
</head>

<body>

  <div class="app-container">
      <!-- return false：避免 form onsubmit 預設 redirect 行為 -->
      <form onsubmit="handleFormSubmit(this); return false;">

      <label class="field-label">電子郵件：</label>
      <input type="email" name="userEmail" class="input-field" placeholder="example@mail.com" required>

      <label class="field-label">選擇檔案：</label>
      <!-- 有限定檔案為 xls 和 xlsx -->
      <input type="file" name="myFile" class="input-field"
                  accept=".xls,.xlsx, application/vnd.ms-excel, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                  required>

      <button type="submit" class="btn-submit">上傳</button>
    </form>

    <div class="result-message">等待操作</div>
  </div>

  <script>
    function onSuccessHandler(response) {
        const display = document.querySelector('.result-message');
        display.innerText = "【成功】\n" + response;
        display.className = "result-message text-success";
    }

    function onFailureHandler(error) {
        const display = document.querySelector('.result-message');
        display.innerText = "【失敗】\n" + error.message;
        display.className = "result-message text-failure";
    }

    function handleFormSubmit(formObject) {

        let display = document.querySelector('.result-message');
        
        let fileInput = formObject.myFile; 
                
        // 透過 .files 屬性取得檔案
        let file = fileInput.files[0];
        let fileName = file.name.toLowerCase();

        // 檢查副檔名
        if (!fileName.endsWith('.xls') && !fileName.endsWith('.xlsx')) {
            display.innerText = "【格式錯誤】\n檔案格式不正確。\n請只上傳 Excel 檔案 (.xls, .xlsx)";
            display.className = "result-message text-failure";
            return;
        }

        display.innerText = "正在處理檔案，請勿重整頁面";
        display.className = "result-message";

        // 直接傳遞 formObject 
        google.script.run 
          .withSuccessHandler(onSuccessHandler)
          .withFailureHandler(onFailureHandler)
          .processForm(formObject);
      }

  </script>
</body>

</html>

code.gs

function doGet() {
  return HtmlService.createTemplateFromFile('index')
    .evaluate()
    .setTitle('[GAS] 上傳檔案至 Google Drive - Excel to Sheet');
}

function include(fileName) {
  return HtmlService.createHtmlOutputFromFile(fileName).getContent();
}

function processForm(formObject) {

  try {
    let parentFoldID = getFoldID(); 
    let parentFolder = DriveApp.getFolderById(parentFoldID);

    let timestamp = Utilities.formatDate(new Date(), "GMT+8", "yyyyMMdd_HHmmss");
    let subFolder = parentFolder.createFolder(timestamp);
    let subFolderId = subFolder.getId(); // 取得 ID 供後續 Drive API 使用

    // 儲存 Excel 檔案
    let excelFileBlob = formObject.myFile; 
    subFolder.createFile(excelFileBlob);

    let fileMetaData = {
      name: excelFileBlob.getName(),
      mimeType: MimeType.GOOGLE_SHEETS, // 指定檔案類型
      parents: [subFolderId] // 指定父資料夾，必須是資料夾ID (FoldID) 的陣列格式
    };

    // 透過 Drvie API 把 Excel 轉換為 Sheet
    let sheetFileByAPI = Drive.Files.create(fileMetaData, excelFileBlob);
    
    // 檔案 ID 是相同，單純方便操作檔案，才透過 DriveApp.getFileById() 再轉回 File Class 來使用
    let sheetFileByApp = DriveApp.getFileById(sheetFileByAPI.id);
    let url = sheetFileByApp.getUrl();
    sheetFileByApp.setDescription("透過 DriveApp 設定：上傳者為 " + formObject.userEmail)

    let message = `
      上傳時間：${timestamp}
      Email：${formObject.userEmail}
      上傳檔案 URL：${url}`;

    return message;    

  } catch (error) {
    throw new Error("系統錯誤：" + error.toString());
  }
}

CSS

<style>
  .app-container {
    font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
    padding: 30px;
    line-height: 1.6;
  }

  .field-label {
    display: block;
    margin-bottom: 5px;
    font-weight: bold; 
  }

  .input-field {
    width: 280px;
    padding: 8px;
    margin-bottom: 15px;
    border: 1px solid #ccc; 
    border-radius: 4px;
    display: block;
  }

  .btn-submit {
    display: block;
    padding: 8px 20px;
    background-color: #007bff;
    color: white;
    border: none;
    border-radius: 4px;
    cursor: pointer;
  }

  .btn-submit:hover {
    background-color: #0056b3;
  }

  .result-message {
    margin-top: 20px;
    padding: 15px; 
    border-left: 5px solid #eee;
    background-color: #f9f9f9;
    white-space: pre-wrap;
  }

  .text-success {
    border-left-color: #28a745;
    color: #155724;
  }

  .text-failure {
    border-left-color: #dc3545;
    color: #721c24;
  }
</style>

執行結果