Google Apps Script日期格式修正及起始日期对齐问题求助

Hey there, let's work through your two date-related problems step by step—removing the time component from your date columns and fixing that annoying off-by-one start date glitch.

1. Root Causes of the Issues #

• Time showing in dates: Google Sheets returns dates as full Date objects, which include time data when serialized and parsed in the frontend.
• Off-by-one start date: This is a timezone mismatch—your Sheet’s dates are stored in UTC, but your frontend renders them in your local timezone. For example, 12/18/2018 UTC translates to 12/17/2018 16:00 if you’re in the UTC-8 timezone.

Here’s how to fix both problems:

Update Code.gs to Format Dates Correctly #

We’ll format dates on the backend to strip time and ensure they use your desired timezone, eliminating the shift issue:

function doGet(e) {
 return HtmlService
 .createTemplateFromFile("Line Chart multiple Table")
 .evaluate()
 .setTitle("Google Spreadsheet Chart")
 .setSandboxMode(HtmlService.SandboxMode.IFRAME);
}

function getSpreadsheetData() {
 var ssID = "1jxWPxxmLHP-eUcVyKAdf5pSMW6_KtBtxZO7s15eAUag";
 var sheet = SpreadsheetApp.openById(ssID).getSheets()[1];
 var data1 = sheet.getRange('A2:F9').getValues();
 var data2 = sheet.getRange('A2:F9').getValues();
 
 // Configure timezone and date format (adjust these to your needs)
 var timeZone = Session.getScriptTimeZone(); // Uses your script's timezone, or hardcode like "Asia/Shanghai"
 var dateFormat = "yyyy-MM-dd"; // Or "MM/dd/yyyy" for your preferred display

 // Format date column (assuming column A is the date column, index 0)
 const formatRowDates = (row) => {
   if (row[0] instanceof Date) {
     row[0] = Utilities.formatDate(row[0], timeZone, dateFormat);
   }
   return row;
 };

 data1 = data1.map(formatRowDates);
 data2 = data2.map(formatRowDates);

 var rows = {data1: data1, data2: data2};
 return JSON.stringify(rows);
}

Update the HTML Frontend to Render Dates Properly #

Now we’ll explicitly tell Google Charts that the first column is a date, and format it to hide time in both the chart and table:

<!DOCTYPE html>
<html>
<head>
 <script src="https://www.gstatic.com/charts/loader.js"></script>
</head>
<body>
 <div id="linechartweekly"></div>
 <div id="table2"></div>
 <div class="block" id="message" style="color:red;"></div>
 <script>
 google.charts.load('current', {'packages':['table', 'corechart', 'line']});
 google.charts.setOnLoadCallback(getSpreadsheetData);

 function display_msg(msg) {
   console.log("display_msg():"+msg);
   document.getElementById("message").style.display = "block";
   var div = document.getElementById('message');
   div.innerHTML = msg;
 }

 function getSpreadsheetData() {
   google.script.run.withSuccessHandler(drawChart).withFailureHandler(failure_callback).getSpreadsheetData();
 }

 function drawChart(r) {
   var rows = JSON.parse(r);

   // Build DataTable for the line chart with explicit date type
   var data1 = new google.visualization.DataTable();
   data1.addColumn('date', 'Date');
   data1.addColumn('number', 'USL');
   data1.addColumn('number', 'UCL');
   data1.addColumn('number', 'Data');
   data1.addColumn('number', 'LCL');
   data1.addColumn('number', 'LSL');

   // Convert formatted date strings back to Date objects
   rows.data1.forEach(row => {
     const [year, month, day] = row[0].split('-');
     const date = new Date(year, month - 1, day); // Months are 0-indexed in JavaScript
     data1.addRow([date, row[1], row[2], row[3], row[4], row[5]]);
   });

   // Build DataTable for the table visualization
   var data2 = new google.visualization.DataTable();
   data2.addColumn('date', 'Date');
   data2.addColumn('number', 'USL');
   data2.addColumn('number', 'UCL');
   data2.addColumn('number', 'Data');
   data2.addColumn('number', 'LCL');
   data2.addColumn('number', 'LSL');

   rows.data2.forEach(row => {
     const [year, month, day] = row[0].split('-');
     const date = new Date(year, month - 1, day);
     data2.addRow([date, row[1], row[2], row[3], row[4], row[5]]);
   });

   var options1 = {
     title: 'SPC Chart weekly',
     legend: ['USL', 'UCL', 'Data', 'LCL', 'LSL'],
     colors: ['Red', 'Orange', 'blue', 'Orange', 'Red'],
     pointSize: 4,
     hAxis: {
       format: 'MM/dd/yyyy' // Hide time, show only date
     }
   };

   var chart1 = new google.visualization.LineChart(document.getElementById("linechartweekly"));
   chart1.draw(data1, options1);

   var table2 = new google.visualization.Table(document.getElementById("table2"));
   table2.draw(data2, {
     showRowNumber: false, 
     width: '50%', 
     height: '100%',
     columns: [
       {type: 'date', label: 'Date', format: 'MM/dd/yyyy'} // Format table date column
     ]
   });
 }

 function failure_callback(error) {
   display_msg("ERROR: " + error.message);
   console.log('failure_callback() entered. ' + error.message);
 }
 </script>
</body>
</html>

Quick Adjustments for Your Setup #

• Date column index: If your date isn’t in column A (index 0), update row[0] in Code.gs to match your actual date column.
• Timezone: If Session.getScriptTimeZone() doesn’t match your desired timezone, replace it with a hardcoded value like "America/New_York" or "Asia/Shanghai".
• Date format: Adjust dateFormat in Code.gs and the format properties in the frontend options to match your preferred display style (e.g., "dd/MM/yyyy" for day-month-year).

This approach ensures dates are formatted consistently on the backend (eliminating timezone shifts) and rendered without time components in both the chart and table.

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