Google Apps Script中Google表格日期格式化指南

在google apps script开发web应用并从google表格中拉取数据时，日期格式经常是开发者遇到的一个常见挑战。当使用range.getvalue()方法获取单元格内容时，日期通常会以默认的date对象形式返回，这在web应用中直接显示时可能不符合我们期望的“dd mmmm yyyy”等特定格式。本教程将详细介绍如何有效地处理这些日期格式问题，并提供代码示例。

问题背景：getValue()返回的日期格式

在您的示例代码中，b3和b8变量通过sheet.getRange(i, 3).getValue()和sheet.getRange(i, 8).getValue()获取日期。虽然Google表格内部可能已经设置了显示格式，但getValue()方法返回的是一个JavaScript Date对象，当将其直接拼接成字符串时，会使用其默认的字符串表示形式，这通常不是用户友好的“dd MMMM yyyy”格式。

例如，如果Google表格中显示的是“2023年10月26日”，getValue()可能返回一个类似Thu Oct 26 2023 00:00:00 GMT+0800 (China Standard Time)的Date对象。直接将其插入HTML字符串会导致显示不佳。

解决方案一：使用Utilities.formatDate进行自定义格式化

Google Apps Script提供了Utilities.formatDate()方法，允许您将Date对象格式化为任意字符串格式。这是处理日期格式化最灵活和推荐的方法。

Utilities.formatDate(date, timeZone, format)方法接受三个参数：

• date: 要格式化的Date对象。
• timeZone: 用于解释日期的时区。通常，您可以使用Session.getScriptTimeZone()来获取脚本运行的时区，以确保一致性。
• format: 一个字符串，定义所需的日期时间格式。例如，“dd MMMM yyyy”表示“日 月份全称 年份”。

示例代码：

LALAL.AI

AI人声去除器和声乐提取工具

下载

假设您想将b3和b8格式化为“dd MMMM yyyy”：

function submitDT(obj){
  var ss = SpreadsheetApp.openById("google sheet url");
  var sheet = ss.getSheetByName("mysheet");
  var flag = 1;
  var lr = sheet.getLastRow();

  for(var i = 1; i <= lr; i++){
    var vid = sheet.getRange(i, 2).getValue();
    if(vid == obj){
      flag = 0;
      var b1 = sheet.getRange(i, 1).getValue();      
      var b3_date = sheet.getRange(i, 3).getValue(); // 获取原始Date对象
      var b4 = sheet.getRange(i, 4).getValue();
      var b5 = sheet.getRange(i, 5).getValue();
      var b6 = sheet.getRange(i, 6).getValue();
      var b7 = sheet.getRange(i, 7).getValue();
      var b8_date = sheet.getRange(i, 8).getValue(); // 获取原始Date对象

      // 使用Utilities.formatDate进行格式化
      var b3 = Utilities.formatDate(b3_date, Session.getScriptTimeZone(), "dd MMMM yyyy");
      var b8 = Utilities.formatDate(b8_date, Session.getScriptTimeZone(), "dd MMMM yyyy");

      var data = "
Transaction Information.
Tracking No.:
"
      + obj + "
Transaction Title
" + b1 + "
Date Accepted by Focal:
" + b3 + "
Name of Establishment:
" + b4 + "
Address:
" + b5 + "
Status:
" + b6 + "
Claimed by:
" + b7 + "
Date Claimed:
" + b8 + "";
      return data;
    }
  }
  if(flag == 1){
    var data = "Transaction not found. For other concerns, please contact us at **********";
    return data;
  } 
}

常用日期格式化字符串：

• yyyy: 四位年份 (e.g., 2023)
• MM: 两位月份 (e.g., 01-12)
• MMM: 月份缩写 (e.g., Jan)
• MMMM: 月份全称 (e.g., January)
• dd: 两位日期 (e.g., 01-31)
• HH: 24小时制小时 (e.g., 00-23)
• hh: 12小时制小时 (e.g., 01-12)
• mm: 分钟 (e.g., 00-59)
• ss: 秒 (e.g., 00-59)
• a: 上午/下午标记 (e.g., AM/PM)

解决方案二：使用Range.getDisplayValue()获取显示值

如果您希望Web应用中显示的日期格式与Google表格中单元格的显示格式完全一致，那么Range.getDisplayValue()是一个更简洁的选择。此方法直接返回单元格中可见的字符串值，而不是Date对象。

示例代码：

function submitDT(obj){
  var ss = SpreadsheetApp.openById("google sheet url");
  var sheet = ss.getSheetByName("mysheet");
  var flag = 1;
  var lr = sheet.getLastRow();

  for(var i = 1; i <= lr; i++){
    var vid = sheet.getRange(i, 2).getValue();
    if(vid == obj){
      flag = 0;
      var b1 = sheet.getRange(i, 1).getValue();      
      var b3 = sheet.getRange(i, 3).getDisplayValue(); // 直接获取显示格式的字符串
      var b4 = sheet.getRange(i, 4).getValue();
      var b5 = sheet.getRange(i, 5).getValue();
      var b6 = sheet.getRange(i, 6).getValue();
      var b7 = sheet.getRange(i, 7).getValue();
      var b8 = sheet.getRange(i, 8).getDisplayValue(); // 直接获取显示格式的字符串

      var data = "
Transaction Information.
Tracking No.:
"
      + obj + "
Transaction Title
" + b1 + "
Date Accepted by Focal:
" + b3 + "
Name of Establishment:
" + b4 + "
Address:
" + b5 + "
Status:
" + b6 + "
Claimed by:
" + b7 + "
Date Claimed:
" + b8 + "";
      return data;
    }
  }
  if(flag == 1){
    var data = "Transaction not found. For other concerns, please contact us at **********";
    return data;
  } 
}

选择建议：

• 如果您的Web应用需要自定义的、与表格显示不完全一致的日期格式，或者需要对日期进行进一步的计算，请使用Utilities.formatDate()。
• 如果Web应用中显示的日期格式与Google表格中设置的显示格式一致，并且您不需要对日期对象进行操作，Range.getDisplayValue()是更简单的选择。

最佳实践：批量读取数据以提高效率

在您的原始代码中，循环内多次调用sheet.getRange().getValue()，这会导致每次迭代都与Google表格服务进行一次通信。这种逐个单元格读取的方式效率低下，尤其是在处理大量数据时会显著降低脚本性能。

Google Apps Script的最佳实践是使用批量操作，例如Range.getValues()方法，一次性获取一个区域内的所有数据。getValues()会返回一个二维数组，其中每个内部数组代表一行数据。

优化后的代码示例：

function submitDT(obj){
  var ss = SpreadsheetApp.openById("google sheet url"); // 请替换为您的Google Sheet ID
  var sheet = ss.getSheetByName("mysheet");
  var flag = 1;
  var lr = sheet.getLastRow();

  // 最佳实践：一次性获取所有相关数据
  // 获取从第一行第一列到最后一行的第八列的所有数据
  var allData = sheet.getRange(1, 1, lr, 8).getValues(); 

  // 遍历allData数组，注意数组是0-indexed
  for(var i = 0; i < allData.length; i++){
    var row = allData[i]; // 当前行的数据数组
    var vid = row[1];     // 第二列 (索引为1) 的值，对应原始b2

    if(vid == obj){
      flag = 0;
      var b1 = row[0];     // 第一列 (索引为0)
      var b3_date = row[2]; // 第三列 (索引为2)，原始Date对象
      var b4 = row[3];     // 第四列 (索引为3)
      var b5 = row[4];     // 第五列 (索引为4)
      var b6 = row[5];     // 第六列 (索引为5)
      var b7 = row[6];     // 第七列 (索引为6)
      var b8_date = row[7]; // 第八列 (索引为7)，原始Date对象

      // 检查b3_date和b8_date是否为有效的Date对象，
      // 因为getValues()可能返回空单元格或非日期类型的值
      var b3_formatted = (b3_date instanceof Date) ? Utilities.formatDate(b3_date, Session.getScriptTimeZone(), "dd MMMM yyyy") : b3_date;
      var b8_formatted = (b8_date instanceof Date) ? Utilities.formatDate(b8_date, Session.getScriptTimeZone(), "dd MMMM yyyy") : b8_date;

      var data = "
Transaction Information.
Tracking No.:
"
      + obj + "
Transaction Title
" + b1 + "
Date Accepted by Focal:
" + b3_formatted + "
Name of Establishment:
" + b4 + "
Address:
" + b5 + "
Status:
" + b6 + "
Claimed by:
" + b7 + "
Date Claimed:
" + b8_formatted + "";
      return data;
    }
  }
  if(flag == 1){
    var data = "Transaction not found. For other concerns, please contact us at **********";
    return data;
  } 
}

注意事项：

• getValues()返回的二维数组是0-indexed的，这意味着第一行是allData[0]，第一列是row[0]。
• 在应用Utilities.formatDate()之前，最好检查获取到的值是否确实是一个Date对象，以避免对非日期类型的数据进行格式化时出现错误。
• 如果您使用getDisplayValues()，则无需进行日期类型检查，因为它们总是返回字符串。

总结

通过本教程，您应该已经掌握了在Google Apps Script Web应用中处理Google表格日期格式的两种主要方法：

1. Utilities.formatDate(date, timeZone, format): 提供高度灵活的自定义日期格式化功能。
2. Range.getDisplayValue(): 直接获取Google表格中单元格的显示格式字符串。

同时，为了提高脚本的运行效率和性能，强烈建议采用Range.getValues()进行批量数据读取，而不是在循环中逐个单元格地调用getValue()或getDisplayValue()。结合这些技术，您可以构建出既功能强大又高效的Google Apps Script Web应用。