Google Apps Script获取表格数据后HTML展示格式不符问题求助

阿华AIGC实验室

2026-5-15

Fixing Google Sheets Data Formatting in HTML with Google Apps Script

I see exactly what's going on here—when you pull date and time values from Google Sheets into Apps Script, they come through as native Date objects. When you try to display these directly in HTML, they default to the messy, timezone-included toString() output instead of the clean formatted version you see in your Sheet. Let's fix this by properly formatting the values before sending them to the frontend.

Step 1: Format Dates & Times on the Server Side

The most reliable approach is to handle formatting in your Apps Script function so the frontend gets ready-to-display strings right away. Update your gettssubmitted() function like this:

function gettssubmitted() { 
  var employee = getcurruser(); 
  var doc = SpreadsheetApp.openById(timesheet); 
  var sheet = doc.getSheetByName("Current"); 
  var data = sheet.getDataRange().getValues(); 
  var retdata = ArrayLib.filterByText(data, 1, employee); 
  retdata = ArrayLib.sort(retdata, 2, Desc); 

  // Match your Sheet's timezone (check script settings to confirm)
  var timeZone = Session.getScriptTimeZone();

  // Format each row to match your Sheet's display style
  retdata = retdata.map(row => {
    // Format Time Sheet Date (index 2) to "MM/dd/yy" (matches your example: 12/28/17)
    if (row[2] instanceof Date) {
      row[2] = Utilities.formatDate(row[2], timeZone, "MM/dd/yy");
    }
    // Format Slot Start (index 4) and Slot End (index 5) to "HH:mm" (24h format like 8:00, 17:00)
    if (row[4] instanceof Date) {
      row[4] = Utilities.formatDate(row[4], timeZone, "HH:mm");
    }
    if (row[5] instanceof Date) {
      row[5] = Utilities.formatDate(row[5], timeZone, "HH:mm");
    }
    return row;
  });

  Logger.log("timesheet"); 
  Logger.log(data); 
  return retdata; 
}

Step 2: Simplify the Frontend HTML

Now that the data is pre-formatted, you can remove the broken .toString("d MMMM yyyy") call—just output the values directly:

<body> 
  <? var logged_user = getcurruser(); ?> 
  <label><?=logged_user?></label> 
  <? var data = gettssubmitted(); ?> 
  <table> 
    <tr> 
      <th>Time Sheet Date</th> 
      <th>Job Ticket</th> 
      <th>Start Time</th> 
      <th>End Time</th> 
      <th>Comments</th> 
    </tr> 
    <? for (var i = 0; i < data.length; i++) { ?> 
    <tr> 
      <td class="dispdate"><?= data[i][2] ?></td> 
      <td><?= data[i][3] ?></td> 
      <td><?= data[i][4] ?></td> 
      <td><?= data[i][5] ?></td> 
      <td><?= data[i][6] ?></td> 
    </tr> 
    <? } ?> 
  </table> 
</body>

Quick Customization Tips

Timezone Check: Ensure your script's timezone matches your Google Sheet's timezone. You can verify/update this in the Apps Script editor under File > Project properties > Time zone.
Adjust Formats: If you want a different date style (like January 28, 2017 instead of 12/28/17), change the format string to "MMMM d, yyyy". For 12-hour time with AM/PM, use "h:mm a" instead of "HH:mm".
Server-Side Advantage: Handling formatting on the server ensures consistency for all users, no matter their browser's local timezone settings.

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