I have a Google Sheet where column 'A' is a date Time column. LINK
i converted this Google sheet file to JSON Data. Link
from this JSON data is fetching my website page in a table. Link
The Problem with date is it's showing 17/01/2023 19:47:35 To 2023-01-17T14:17:35.270Z How can I make this Readable
My Apps Script Code is below
function doGet(e) {
var sheet = SpreadsheetApp.getActive();
var nse = sheet.getSheetByName("Sheet1");
var data = [];
var rlen = nse.getLastRow();
var clen = nse.getLastColumn();
var rows = nse.getRange (1,1, rlen, clen).getValues();
for(var i = 1; i < rows.length; i ) {
var datarow = rows[i];
var record = {};
for(var j=0; j < clen ; j ){
record[rows[0][j]] = datarow[j];
}
data.push(record);
}
var result = JSON.stringify(data);
return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);
}
and My Javascript code is here.
<section>
<h1>My Google Sheet Table</h1>
<!-- TABLE CONSTRUCTION-->
<table id='mytable'>
<!-- HEADING FORMATION -->
<tr>
<th>Date</th>
<th>Name</th>
<th>Phone Number</th>
<th>Months</th>
</tr>
<script>
$(document).ready(function () {
// FETCHING DATA FROM JSON FILE
$.getJSON("JsonDataLink",
function (data) {
var content = '';
// ITERATING THROUGH OBJECTS
$.each(data, function (key, value) {
//CONSTRUCTION OF ROWS HAVING
// DATA FROM JSON OBJECT
content = '<tr>';
content = '<td>'
value.DATE '</td>';
content = '<td>'
value.Name '</td>';
content = '<td>'
value.Phone '</td>';
content = '<td>'
value.Months '</td>';
content = '</tr>';
});
//INSERTING ROWS INTO TABLE
$('#mytable').append(content);
});
});
</script>
</section>
</body>
</html>
CodePudding user response：
When I saw your provided sample Spreadsheet, it seems that the values of the column "A" of "DATE" are the date object. In this case, how about modifying it as follows?
Pattern 1:
In this pattern, only Google Apps Script is modified.
IMPORTANT:
As an important point, in your provided sample Spreadsheet, there are 2 functions of doGet. In this case, the expected result might not be able to be obtained. So, please remove one of the 2 doGet functions and please modify it as follows.
Modified script:
function doGet(e) {
var sheet = SpreadsheetApp.getActive();
var nse = sheet.getSheetByName("Sheet1");
var data = [];
var rlen = nse.getLastRow();
var clen = nse.getLastColumn();
var rows = nse.getRange(1, 1, rlen, clen).getDisplayValues(); // Modified
for (var i = 1; i < rows.length; i ) {
var datarow = rows[i];
var record = {};
for (var j = 0; j < clen; j ) {
record[rows[0][j]] = datarow[j];
}
data.push(record);
}
var result = JSON.stringify(data);
return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);
}
Pattern 2:
In this pattern, only Google Apps Script is modified. In this modification, the date object retrieved by getValues is formatted.
From:
record[rows[0][j]] = datarow[j];
To:
record[rows[0][j]] = j == 0 ? Utilities.formatDate(datarow[j], Session.getScriptTimeZone(), "yyyy-MM-dd HH:mm") : datarow[j];
- Please modify the format for your actual situation.
Pattern 3:
In this pattern, only Javascript is modified. In this modification, moment.js is used. You can see it with as https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.29.4/moment.min.js of CDN.
Please modify your Javascript as follows.
From:
content = '<td>'
value.DATE '</td>';
To:
content = '<td>'
moment(new Date(value.DATE)).format('YYYY-MM-DD HH:mm') '</td>';
- Please modify the format for your actual situation.
Note:
When you modified the Google Apps Script of Web Apps, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful about this.
You can see the detail of this in my report "Redeploying Web Apps without Changing URL of Web Apps for new IDE (Author: me)".