尝试为google sheet创建进出小时数的自动计算脚本

function calculateHours() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  
  // 获取进出时间的列索引
  var inTimeColumn = getColumnIndexByName(sheet, "进时间");
  var outTimeColumn = getColumnIndexByName(sheet, "出时间");
  
  // 获取小时数的列索引
  var hoursColumn = getColumnIndexByName(sheet, "小时数");
  
  // 遍历每一行数据，计算小时数
  for (var i = 2; i <= lastRow; i++) {
    var inTime = sheet.getRange(i, inTimeColumn).getValue();
    var outTime = sheet.getRange(i, outTimeColumn).getValue();
    
    if (inTime && outTime) {
      var hours = (outTime - inTime) / (1000 * 60 * 60); // 计算小时数
      sheet.getRange(i, hoursColumn).setValue(hours);
    }
  }
}

// 根据列名获取列索引
function getColumnIndexByName(sheet, columnName) {
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  for (var i = 0; i < headers.length; i++) {
    if (headers[i] == columnName) {
      return i + 1;
    }
  }
  return -1; // 列名未找到
}