Apps Script Web表单提交报错：TypeError: Cannot read properties of null (reading 'appendRow')

开发者阿泽

2026-7-18

排查Apps Script表单提交报错：TypeError: Cannot read properties of null (reading 'appendRow')

提交Web应用表单时触发报错：TypeError: Cannot read properties of null (reading 'appendRow')，以下是用到的代码文件：

code.gs

var folderID = ""; //the "root" with folder ID to upload files to a specific folder
var sheetName = ""; //the "Data" with the data sheet name

function doGet() {
  return HtmlService.createTemplateFromFile('Index').evaluate();
}

/* @Include JavaScript and CSS Files */
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
      .getContent();
}

function uploadFiles(formObject) {
  try {
    var folder = DriveApp.getFolderById(folderID);
    var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
    var fileUrl = "";
    var fileName = "";

    //Upload file if exists and update the file url
    if (formObject.myFile.length > 0) {
      var blob = formObject.myFile;
      var file = folder.createFile(blob);
      file.setDescription("Uploaded by " + formObject.first_name);
      fileUrl = file.getUrl();
      fileName = file.getName();
    } else{
      fileUrl = "Record saved without a file";
    }

    //Saving records to Google Sheet
    sheet.appendRow([
      formObject.first_name,
      formObject.last_name,
      formObject.gender,
      formObject.dateOfBirth,
      formObject.email,
      formObject.phone,
      fileName,
      fileUrl,
      Utilities.formatDate(new Date(), "GMT+5:30", "yyyy-MM-dd'T'HH:mm:ss'Z'")]);
    
    // Return the URL of the saved file
    return fileUrl;
    
  } catch (error) {
    return error.toString();
  }
}

Index.html

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css"
    integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.js"></script>
  <?!= include('JavaScript'); ?>
</head>

<body>
  <div class="container">
    <div class="row">
      <div class="col-6">

        <form id="myForm" onsubmit="handleFormSubmit(this)">
          <p class="h4 mb-4 text-center">Contact Details</p>

          <div class="form-row">
            <div class="form-group col-md-6">
              <label for="first_name">First Name</label>
              <input type="text" class="form-control" name="first_name" placeholder="Your full name..."/>
            </div>

            <div class="form-group col-md-6">
              <label for="last_name">Last Name</label>
              <input type="text" class="form-control" id="last_name" name="last_name" placeholder="Last Name">
            </div>
          </div>

          <div class="form-row">
            <div class="form-group col-md-6">
              <p>Gender</p>
              <div class="form-check form-check-inline">
                <input class="form-check-input" type="radio" name="gender" id="male" value="male">
                <label class="form-check-label" for="male">Male</label>
              </div>
              <div class="form-check form-check-inline">
                <input class="form-check-input" type="radio" name="gender" id="female" value="female">
                <label class="form-check-label" for="female">Female</label>
              </div>
            </div>
            <div class="form-group col-md-6">
              <label for="dateOfBirth">Date of Birth</label>
              <input type="date" class="form-control" id="dateOfBirth" name="dateOfBirth">
            </div>
          </div>

          <div class="form-group">
            <label for="email">Email</label>
            <input type="email" class="form-control" id="email" name="email" placeholder="Email">
          </div>

          <div class="form-group">
            <label for="phone">Phone Number</label>
            <input type="tel" class="form-control" id="phone" name="phone" placeholder="Phone Number">
          </div>

           <div class="form-group">
            <label for="FormControlFile">Photo</label> 
            <input name="myFile" class="form-control-file" type="file" id="FormControlFile" />
           </div>
          <br>
          <button type="submit" class="btn btn-primary btn-block">Submit</button>
        </form>
        <br>
        <div id="output"></div>
      </div>
    </div>
  </div>
</body>
</html>

JavaScript.html

<script>
  function preventFormSubmit() {
        var forms = document.querySelectorAll('form');
        for (var i = 0; i < forms.length; i++) {
          forms[i].addEventListener('submit', function(event) {
            event.preventDefault();
          });
        }
      }
  window.addEventListener('load', preventFormSubmit);

  function handleFormSubmit(formObject){
    google.script.run.withSuccessHandler(updateUrl).withFailureHandler(onFailure).uploadFiles(formObject);
  }

  function updateUrl(url) {
    var div = document.getElementById('output');
    if(isValidURL(url)){
      div.innerHTML = '<div class="alert alert-success" role="alert"><a href="' + url + '">File uploaded successfully!</a></div>';
      document.getElementById("myForm").reset();
    }else{
      //Show warning message if file is not uploaded or provided
      div.innerHTML = '<div class="alert alert-danger" role="alert">'+ url +'!</div>';
    }
  }

  function onFailure(error) {
    var div = document.getElementById('output');
    div.innerHTML = '<div class="alert alert-danger" role="alert">'+ error.message +'!</div>';
  }

  function isValidURL(string) {
    var res = string.match(/(http(s)?:\/\/.)?(www\.)?[-a-zA-Z0-9@:%._\+~#=]{2,256}\.[a-z]{2,6}\b([-a-zA-Z0-9@:%_\+.~#?&\/\/=]*)/g);
    return (res !== null);
  }
</script>

报错原因

TypeError: Cannot read properties of null (reading 'appendRow') 表示调用appendRow时，sheet变量为null，核心原因包括：

sheetName为空字符串，未指定目标工作表名称
SpreadsheetApp.getActive()未获取到正确的目标表格（比如Web应用部署时执行账户错误，或当前激活表格不是存储数据的表格）
指定的工作表名称拼写错误，或该工作表不存在于目标表格中
额外问题：folderID为空会导致文件上传逻辑报错，且formObject.myFile.length的判断在无文件时会触发undefined报错

解决方案

补全配置变量
在code.gs开头替换为实际值：

var folderID = "你的Drive文件夹ID"; // 用于上传文件的文件夹ID
var sheetName = "Data"; // 存储数据的工作表名称
var spreadsheetID = "你的表格ID"; // 存储数据的表格ID

确保获取正确的表格
将原获取工作表的代码替换为通过表格ID直接获取，避免getActive()的不确定性：
```
var spreadsheet = SpreadsheetApp.openById(spreadsheetID);
var sheet = spreadsheet.getSheetByName(sheetName);
```

增加空值校验
在调用appendRow前添加校验，提前抛出明确错误：

if (!sheet) {
  throw new Error("未找到指定的工作表，请检查sheetName配置");
}

修复文件上传判断逻辑
修改无文件时的判断，避免undefined报错：

if (formObject.myFile && formObject.myFile.length > 0) {
  // 原上传逻辑
} else {
  fileUrl = "Record saved without a file";
}

修正后的完整code.gs

var folderID = "你的Drive文件夹ID"; // 替换为目标文件夹ID
var sheetName = "Data"; // 替换为目标工作表名称
var spreadsheetID = "你的表格ID"; // 存储数据的表格ID

function doGet() {
  return HtmlService.createTemplateFromFile('Index').evaluate();
}

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

function uploadFiles(formObject) {
  try {
    var folder = DriveApp.getFolderById(folderID);
    var spreadsheet = SpreadsheetApp.openById(spreadsheetID);
    var sheet = spreadsheet.getSheetByName(sheetName);
    var fileUrl = "";
    var fileName = "";

    // 校验工作表是否存在
    if (!sheet) {
      throw new Error("未找到指定的工作表，请检查sheetName配置");
    }

    // 处理文件上传逻辑
    if (formObject.myFile && formObject.myFile.length > 0) {
      var blob = formObject.myFile;
      var file = folder.createFile(blob);
      file.setDescription("Uploaded by " + formObject.first_name);
      fileUrl = file.getUrl();
      fileName = file.getName();
    } else {
      fileUrl = "Record saved without a file";
    }

    // 保存记录到表格
    sheet.appendRow([
      formObject.first_name,
      formObject.last_name,
      formObject.gender,
      formObject.dateOfBirth,
      formObject.email,
      formObject.phone,
      fileName,
      fileUrl,
      Utilities.formatDate(new Date(), "GMT+5:30", "yyyy-MM-dd'T'HH:mm:ss'Z'")
    ]);
    
    return fileUrl;
    
  } catch (error) {
    return error.toString();
  }
}

内容的提问来源于stack exchange，提问作者Jack

Apps Script Web表单提交报错：TypeError: Cannot read properties of null (reading 'appendRow')

code.gs #

Index.html #

JavaScript.html #

报错原因 #

解决方案 #