Google Sheets脚本追加行时文本格式值转时间格式问题

我已将Logged_Data工作表的E2:E列设置为「纯文本」格式，但运行以下脚本时，Data_Input!D8中设为纯文本格式的数字，在Logged_Data工作表中显示为时间格式。

问题截图 #

• Data_Input!D8单元格格式为「纯文本」：
  
  
• Logged_Data工作表中显示为时间格式：
  

使用的脚本 #

function transposeAndAppend() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let shtIn = ss.getSheetByName("Data_Input");
  let shtOut = ss.getSheetByName("Logged_Data");

  let responses = shtIn.getRange("D4:D11").getValues();
  //responses is a 2d array that looks like this [[a],[b],[c]]
  
  //first transpose it
  let transResp = responses[0].map((resp,i)=>responses.map(r => r[i]))
  //we now have [[a,b,c]] 

  shtOut.appendRow(transResp.flat())

    //Clear the 'form'
  shtIn.getRange("D4:D11").clearContent();
}

原因分析 #

核心原因是getValues()与appendRow()的类型解析机制：

1. getValues()方法读取的是单元格的实际值，而非显示格式或文本内容。即便Data_Input!D8设为纯文本，若输入内容符合时间格式（如12:30），Google Sheets会在后台将其解析为时间类型的数值（本质是代表日期的小数），getValues()会直接读取这个数值而非纯文本字符串。
2. appendRow()写入数据时，会根据值的类型自动匹配格式：如果写入的是时间类型数值，Sheets会优先应用时间格式显示，哪怕目标列已设置为纯文本，也会被临时覆盖。

谷歌支持人员Hyde曾表示这种情况本不应发生，但实际是Sheets的自动类型解析机制导致的例外情况。

修复方法 #

方法一：读取单元格显示文本（推荐）

将getValues()替换为getDisplayValues()，该方法直接获取单元格的显示文本，完全保留纯文本格式：

function transposeAndAppend() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let shtIn = ss.getSheetByName("Data_Input");
  let shtOut = ss.getSheetByName("Logged_Data");

  // 改用getDisplayValues()获取显示文本
  let responses = shtIn.getRange("D4:D11").getDisplayValues();
  
  let transResp = responses[0].map((resp,i)=>responses.map(r => r[i]))

  shtOut.appendRow(transResp.flat())

  shtIn.getRange("D4:D11").clearContent();
}

方法二：写入后强制设置纯文本格式

若需保留getValues()的逻辑，可在写入后精准设置目标单元格为纯文本格式：

function transposeAndAppend() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let shtIn = ss.getSheetByName("Data_Input");
  let shtOut = ss.getSheetByName("Logged_Data");

  let responses = shtIn.getRange("D4:D11").getValues();
  
  let transResp = responses[0].map((resp,i)=>responses.map(r => r[i]))
  let flatResp = transResp.flat();

  // 获取待写入的行号
  let targetRow = shtOut.getLastRow() + 1;
  // 写入数据
  shtOut.getRange(targetRow, 1, 1, flatResp.length).setValues([flatResp]);
  // 强制将E列（第5列）的目标单元格设为纯文本格式
  shtOut.getRange(targetRow, 5).setNumberFormat('@');

  shtIn.getRange("D4:D11").clearContent();
}

注：@是Google Sheets中代表纯文本格式的代码，setValues()替代appendRow()是为了精准定位目标单元格。

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