Google表格日期格式化设置教程

本篇文章给大家分享《Google表格日期格式化教程》，覆盖了文章的常见基础知识，其实一个语言的全部知识点一篇文章是不可能说完的，但希望通过这些问题，让读者对自己的掌握程度有一定的认识(B 数)，从而弥补自己的不足，更好的掌握它。

本文旨在解决Google Apps Script从Google表格获取日期数据并显示在Web应用中时，日期格式不符合预期的问题。我们将介绍两种核心解决方案：使用Utilities.formatDate进行自定义格式化，以及利用Range.getDisplayValue直接获取表格中显示的格式。此外，还将提供优化数据读取效率的最佳实践，通过getValues批量获取数据，从而提升Web应用的性能。

在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”表示“日 月份全称 年份”。

示例代码：

假设您想将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 = "<center><table><tr><th colspan=2>Transaction Information.</th></tr><tr><td>Tracking No.:</td><td>"
      + obj + "</td></tr><tr><td>Transaction Title</td><td>" + b1 + "</td></tr><tr><td>Date Accepted by Focal:</td><td>" + b3 + "</td></tr><tr><td>Name of Establishment:</td><td>" + b4 + "</td></tr><tr><td>Address:</td><td>" + b5 + "</td></tr><tr><td>Status:</td><td>" + b6 + "</td></tr><tr><td>Claimed by:</td><td>" + b7 + "</td></tr><tr><td>Date Claimed:</td><td>" + b8 + "</td></tr></table></center>";
      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 = "<center><table><tr><th colspan=2>Transaction Information.</th></tr><tr><td>Tracking No.:</td><td>"
      + obj + "</td></tr><tr><td>Transaction Title</td><td>" + b1 + "</td></tr><tr><td>Date Accepted by Focal:</td><td>" + b3 + "</td></tr><tr><td>Name of Establishment:</td><td>" + b4 + "</td></tr><tr><td>Address:</td><td>" + b5 + "</td></tr><tr><td>Status:</td><td>" + b6 + "</td></tr><tr><td>Claimed by:</td><td>" + b7 + "</td></tr><tr><td>Date Claimed:</td><td>" + b8 + "</td></tr></table></center>";
      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 = "<center><table><tr><th colspan=2>Transaction Information.</th></tr><tr><td>Tracking No.:</td><td>"
      + obj + "</td></tr><tr><td>Transaction Title</td><td>" + b1 + "</td></tr><tr><td>Date Accepted by Focal:</td><td>" + b3_formatted + "</td></tr><tr><td>Name of Establishment:</td><td>" + b4 + "</td></tr><tr><td>Address:</td><td>" + b5 + "</td></tr><tr><td>Status:</td><td>" + b6 + "</td></tr><tr><td>Claimed by:</td><td>" + b7 + "</td></tr><tr><td>Date Claimed:</td><td>" + b8_formatted + "</td></tr></table></center>";
      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应用。

到这里，我们也就讲完了《Google表格日期格式化设置教程》的内容了。个人认为，基础知识的学习和巩固，是为了更好的将其运用到项目中，欢迎关注golang学习网公众号，带你了解更多关于的知识点！