如何基于Google Sheet单元格值控制打卡按钮状态并同步更新？

我正在用Google App Script开发一款考勤打卡Web应用，目前已经实现了登录模块和一个能切换“Time In”/“Time Out”文本的按钮。现在需要完成以下功能：

• 在User工作表新增一列，存储每位用户当前的打卡状态（已签到/已签退）
• 点击按钮时同步更新该状态
• 按钮要根据用户当前状态自动显示“Time In”或“Time Out”
• 点击按钮时执行对应的签到/签退逻辑

现有代码如下：

HTML文件代码 #

<!DOCTYPE html>
<html>
  
  <head>
    <base target="_top">
  </head>
  <body>
    <form onsubmit="login(event)">
      <label for="username">Username:</label>
      <input type="text" id="username" required><br>
      <label for="password">Password:</label>
      <input type="password" id="password" required><br>
      <input type="submit" value="Login">
    </form>

<button id="ContButton" onclick="openForm()" hidden>Dashboard</button>

<div class="form-popup" id="Dashboard">

  <form class="form-container">
    <h1>Dashboard</h1>
    <p>Welcome, <span id="name"></span></p>
    <p>Number: <span id="number"></span></p>
    <button type="button" class="btn" id ="timebutton" onclick="switchbutton()">Time In</button>
    <button type="button" class="btn cancel" onclick="closeForm()">Close</button>

  </form>
</div>
    <script>
      //Login function event handler
      function login(event) {
        event.preventDefault();

        var username = document.getElementById('username').value;
        var password = document.getElementById('password').value;

        google.script.run.withSuccessHandler(function(user) {
          if (user) {
            alert("Logged in");
            let p = document.getElementById('ContButton'); //unhidding the dashboard button when the user logs in successfully 
            p.removeAttribute("hidden");
            localStorage.userSession = JSON.stringify(user);
            //for dashboard pulling from local storage.
            var user = JSON.parse(localStorage.userSession);
            console.log(user);
            document.getElementById('name').innerHTML = user[2];
            document.getElementById('number').innerHTML = user[3];
          } else {
            alert('Incorrect username or password');
          }
        }).checkLogin(username, password);
      }

      

function openForm() {
  document.getElementById("Dashboard").style.display = "block";
}


// function also clears fields for the next user when they close the dashboard.
function closeForm() {
  localStorage.clear();
    window.location.replace = "/";

}

//function to switch the text on the Time in/Time out button and trigger the function for each
function switchbutton(){
   var x = document.getElementById("timebutton");
  if (x.innerHTML === "Time In") {
    x.innerHTML = "Time Out";
    flipIn();
  } else {
    x.innerHTML = "Time In";
    flipOut();
  }
}

// function to Time In with alert
function flipIn(){
alert("Timed In");
return;
}
//function to Time Out with alert
function flipOut(){
alert("Timed Out");
return;
}

    </script>
  </body>
</html>

Code.gs文件代码 #

var sheet = SpreadsheetApp.openById('1yK6WEr3Pp0uzRLlO3mR1E6aKBgyBNoEEbqu6M944O-A').getSheetByName('User');


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

function checkLogin(username,password) {
  var data = sheet.getDataRange().getValues();
  for (var i = 1; i < data.length; i++) {
    if (data[i][0] === username && data[i][1] === password) {
      return data[i];
    }
  }
}

function getUserData(username) {
  var data = sheet.getDataRange().getValues();
  
  for (var i = 1; i < data.length; i++) {
    if (data[i][0] === username) {
      return {
        username: data[i][0],
        name: data[i][2],
        number: data[i][3]
      };
    }
  }
}

1. 工作表新增状态列 #

在User工作表的第5列（假设现有列是A:用户名、B:密码、C:姓名、D:编号）插入一列，表头设为Status，所有用户初始状态设为已签退。

2. 修改Code.gs，新增状态相关函数 #

var sheet = SpreadsheetApp.openById('1yK6WEr3Pp0uzRLlO3mR1E6aKBgyBNoEEbqu6M944O-A').getSheetByName('User');

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

function checkLogin(username,password) {
  var data = sheet.getDataRange().getValues();
  for (var i = 1; i < data.length; i++) {
    if (data[i][0] === username && data[i][1] === password) {
      return data[i];
    }
  }
}

function getUserData(username) {
  var data = sheet.getDataRange().getValues();
  
  for (var i = 1; i < data.length; i++) {
    if (data[i][0] === username) {
      return {
        username: data[i][0],
        name: data[i][2],
        number: data[i][3],
        status: data[i][4] // 新增状态字段
      };
    }
  }
}

// 获取用户当前打卡状态
function getUserStatus(username) {
  var data = sheet.getDataRange().getValues();
  for (var i = 1; i < data.length; i++) {
    if (data[i][0] === username) {
      return data[i][4];
    }
  }
  return "已签退"; // 默认返回已签退
}

// 更新用户打卡状态
function updateUserStatus(username, newStatus) {
  var data = sheet.getDataRange().getValues();
  for (var i = 1; i < data.length; i++) {
    if (data[i][0] === username) {
      sheet.getRange(i+1, 5).setValue(newStatus); // 第5列是状态列，行号i+1因为表头是第1行
      return true;
    }
  }
  return false;
}

3. 修改前端HTML代码，同步状态与按钮文本 #

<!DOCTYPE html>
<html>
  
  <head>
    <base target="_top">
  </head>
  <body>
    <form onsubmit="login(event)">
      <label for="username">Username:</label>
      <input type="text" id="username" required><br>
      <label for="password">Password:</label>
      <input type="password" id="password" required><br>
      <input type="submit" value="Login">
    </form>

<button id="ContButton" onclick="openForm()" hidden>Dashboard</button>

<div class="form-popup" id="Dashboard">

  <form class="form-container">
    <h1>Dashboard</h1>
    <p>Welcome, <span id="name"></span></p>
    <p>Number: <span id="number"></span></p>
    <button type="button" class="btn" id ="timebutton" onclick="switchbutton()">Time In</button>
    <button type="button" class="btn cancel" onclick="closeForm()">Close</button>

  </form>
</div>
    <script>
      //Login function event handler
      function login(event) {
        event.preventDefault();

        var username = document.getElementById('username').value;
        var password = document.getElementById('password').value;

        google.script.run.withSuccessHandler(function(user) {
          if (user) {
            alert("Logged in");
            let p = document.getElementById('ContButton');
            p.removeAttribute("hidden");
            localStorage.userSession = JSON.stringify(user);
            var user = JSON.parse(localStorage.userSession);
            console.log(user);
            document.getElementById('name').innerHTML = user[2];
            document.getElementById('number').innerHTML = user[3];
            // 登录后获取用户状态，更新按钮文本
            google.script.run.withSuccessHandler(function(status) {
              var btn = document.getElementById('timebutton');
              btn.innerHTML = status === "已签到" ? "Time Out" : "Time In";
            }).getUserStatus(user[0]);
          } else {
            alert('Incorrect username or password');
          }
        }).checkLogin(username, password);
      }

function openForm() {
  document.getElementById("Dashboard").style.display = "block";
  // 打开面板时重新获取状态，避免缓存问题
  var user = JSON.parse(localStorage.userSession);
  google.script.run.withSuccessHandler(function(status) {
    var btn = document.getElementById('timebutton');
    btn.innerHTML = status === "已签到" ? "Time Out" : "Time In";
  }).getUserStatus(user[0]);
}

// function also clears fields for the next user when they close the dashboard.
function closeForm() {
  localStorage.clear();
  window.location.href = "/"; // 修正跳转写法
}

//function to switch the text on the Time in/Time out button and trigger the function for each
function switchbutton(){
   var x = document.getElementById("timebutton");
   var user = JSON.parse(localStorage.userSession);
  if (x.innerHTML === "Time In") {
    google.script.run.withSuccessHandler(function(success) {
      if (success) {
        x.innerHTML = "Time Out";
        alert("已签到");
      } else {
        alert("签到失败，请重试");
      }
    }).updateUserStatus(user[0], "已签到");
  } else {
    google.script.run.withSuccessHandler(function(success) {
      if (success) {
        x.innerHTML = "Time In";
        alert("已签退");
      } else {
        alert("签退失败，请重试");
      }
    }).updateUserStatus(user[0], "已签退");
  }
}
    </script>
  </body>
</html>

关键说明 #

• 状态列对应工作表第5列（数组索引4），如果你的列位置不同，需要调整data[i][4]和sheet.getRange(i+1,5)中的数字
• 登录和打开面板时都会重新获取用户状态，确保按钮文本与工作表状态一致
• 打卡操作通过google.script.run调用后端函数更新工作表，避免前端状态与后端不一致

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