如何用Google Apps Script处理谷歌表格空单元格及#NUM!计算错误

我正在开发员工休假卡自动化功能，需求是获取谷歌表格M、N列的非空单元格值，分别与J、K列的值相加。目前用Google Apps Script实现（数据来自HTML表单），但计算最新VL余额时出现#NUM!错误，预期输出应为24.208，求解决。

现有Google Apps Script代码 #

var row = [form.totalUT, form.totalVLavailed, form.totalSLavailed, form.periodOfLeave];

var undertime = row[0]; //undertime
var availedVL = row[1]; //availedVL
var availedSL = row[2]; //availedSL
var leaveRange = row[3]; //period of Leave

//Conversion of Working Hours/Minutes into Fraction of a Day
const workDay = 1;
const hoursEquivalent = 8;
const hrConverted = workDay / hoursEquivalent;
const minsConverted = hrConverted / 60; 

//Converted Vacation Leave from undertime value
var convertedUT = minsConverted * undertime;

var sheettest = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lastRow = sheettest.getLastRow();

//Get Sick Leave Balance
var prevSLBalance = sheettest.getRange(lastRow, 15).getValues();
if (sheettest.getRange(lastRow, 15).isBlank()){
  prevSLBalance = sheettest.getRange(lastRow - 1, 15).getValues();
}

//Get Vacation Leave Balance
var prevVLBalance = sheettest.getRange(lastRow, 14).getValues();
if (sheettest.getRange(lastRow, 14).isBlank()){
  prevVLBalance = sheettest.getRange(lastRow - 1, 15).getValues();
}
var lastestVLBalance = (parseFloat(prevVLBalance) + leaveEarned) - ((parseFloat(availedVL) + convertedUT);

现有HTML表单代码 #

<form id="leaveForm">
<label for="totalUT">Total Undertime in minute/s:</label> 
<input type="text" id="totalUT" name="totalUT"><br><br>

<label for="totalVLavailed">Total VL availed in day/s:</label> 
<input type="text" id="totalVLavailed" name="totalVLavailed"><br><br>

<label for="totalSLavailed">Total SL availed in day/s:</label> 
<input type="text" id="totalSLavailed" name="totalSLavailed"><br><br>

<label for="periodOfLeave">Period of Leave:</label>
<input type="text" id="periodOfLeave" name="periodOfLeave"><br><br>

<div>
  
<input type="button" value="Done" onclick="submitForm();">

问题分析与修正方案 #

导致#NUM!错误的核心问题：

• getValues()使用错误：getValues()返回二维数组（比如[[24]]），直接用parseFloat转换会失败，应该用getValue()获取单个单元格的数值。
• VL余额备用列引用错误：获取备用VL余额时，错误引用了第15列（SL列），应改为第14列（VL列）。
• 括号不匹配：计算lastestVLBalance时右侧多了一个左括号，语法错误导致计算异常。
• leaveEarned变量未定义：代码直接使用该变量但未赋值，会导致NaN，进而引发#NUM!。
• 表单输入无限制：text类型输入框可能传入非数字内容，建议改为number类型并设置默认值。

修正后的Google Apps Script代码

var row = [form.totalUT, form.totalVLavailed, form.totalSLavailed, form.periodOfLeave];

// 处理表单输入，空值默认设为0
var undertime = parseFloat(row[0]) || 0; 
var availedVL = parseFloat(row[1]) || 0;
var availedSL = parseFloat(row[2]) || 0;
var leaveRange = row[3];

// 简化工时转天数的计算逻辑
const workDay = 1;
const hoursEquivalent = 8;
const minsConverted = workDay / (hoursEquivalent * 60); 

var convertedUT = minsConverted * undertime;

var sheettest = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lastRow = sheettest.getLastRow();

// 获取病假余额：改用getValue()，处理空值
var prevSLBalance = sheettest.getRange(lastRow, 15).getValue();
if (prevSLBalance === "") {
  prevSLBalance = sheettest.getRange(lastRow - 1, 15).getValue();
}
prevSLBalance = parseFloat(prevSLBalance) || 0;

// 获取年假余额：修正备用列引用，改用getValue()
var prevVLBalance = sheettest.getRange(lastRow, 14).getValue();
if (prevVLBalance === "") {
  prevVLBalance = sheettest.getRange(lastRow - 1, 14).getValue();
}
prevVLBalance = parseFloat(prevVLBalance) || 0;

// 补充定义leaveEarned（需根据业务逻辑赋值，示例假设月度应休2天）
var leaveEarned = 0;
if (leaveRange.includes("month")) {
  leaveEarned = 2;
}

// 修正括号，正确计算最新VL余额，保留三位小数匹配预期输出
var latestVLBalance = (prevVLBalance + leaveEarned) - (availedVL + convertedUT);
latestVLBalance = parseFloat(latestVLBalance.toFixed(3));

修正后的HTML表单代码

<form id="leaveForm">
<label for="totalUT">Total Undertime in minute/s:</label> 
<input type="number" id="totalUT" name="totalUT" min="0" step="1" value="0"><br><br>

<label for="totalVLavailed">Total VL availed in day/s:</label> 
<input type="number" id="totalVLavailed" name="totalVLavailed" min="0" step="0.001" value="0"><br><br>

<label for="totalSLavailed">Total SL availed in day/s:</label> 
<input type="number" id="totalSLavailed" name="totalSLavailed" min="0" step="0.001" value="0"><br><br>

<label for="periodOfLeave">Period of Leave:</label>
<input type="text" id="periodOfLeave" name="periodOfLeave" placeholder="如：January 2024"><br><br>

<div>
<input type="button" value="Done" onclick="submitForm();">
</div>
</form>

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