Retrieving the date record from google spreadsheet

Question

I use a datepicker, it has day,month and year.But it doesn’t have time(second ,minutes and hour). I already inserted the record in google spreadsheet. But I got a problem when I search and retrieve the records specifically at the date record.

For example if the date is 3/25/2017 in the google spreadsheet. When I search the records, the date value returned as 2017-03-24T15:00:00.000Z where the day is decreased by one day.

How can I fix this problem?

Please help.

The code

Html

<div id="Searchrecord">
<h2>Search </h2>
<form id="fsrecord">
<input type="text" name="sinvoice" id="sinvoice" placeholder="by invoice number"/> <br/>
<input type="text" name="surname" id="surname" placeholder="by your name"/> <br/>
<input type="text" name="scustomername" id="scustomername"  placeholder="by customer name"/> <br/>
<input type="text" name="spayementdate" id="spayementdate"  placeholder="by payment date"> <br>
<input type="submit" value="search" />
</form>
</div>

<div id="searchresult">

</div>

<script>
$( document ).ready(function() {
$("#spayementdate").datepicker();
 $("#fsrecord").submit(function() {
    google.script.run.withSuccessHandler(function(retsearch){
    var response = JSON.parse(retsearch);
    var newHTML=[];
        newHTML.push('<table>' + '<tr>' +'<td>'+"Invoice"+'</td>'+
                                        '<td>'+"Your Name"+'</td>'+
                                        '<td>'+"Customer Name" +'</td>'+
                                        '<td>'+"Email" + '</td>'+
                                        '<td>'+"Project Name" + '</td>'+
                                        '<td>'+"Amount of Money" + '</td>'+
                                        '<td>'+"Payment Date" +'</td>'+
                                        '<td>'+"Date Create" +'</td>'+
                                        '<td>'+"Branch" +'</td>'+
                                        '<td>'+ "File url" +'</td>'
        + '<tr>');
    for(var i =0 ; i< response.length ; i++){
      newHTML.push('<tr>' + '<td>'+ response[i].invoice + '</td>'
                          + '<td>'+ response[i].yourname + '</td>'
                          + '<td>'+ response[i].customername + '</td>'
                          + '<td>'+ response[i].email + '</td>'
                          + '<td>'+ response[i].projectname + '</td>'
                          + '<td>'+ response[i].amountofmoney + '</td>'
                          + '<td>'+ response[i].paymentday + '</td>'
                          + '<td>'+ response[i].datecreate + '</td>'
                          + '<td>'+ response[i].branch + '</td>'
                          + '<td>'+ '<a href="' + response[i].file + '" target="_blank" >file!</a>' + '</td>'


      +'</tr>');
    }
    newHTML.push('</table>');
    $("#results").hide();
    $("#searchresult").html(newHTML.join(""));

  }).processSearch(this);

 });

});

</script>

Code.cs

function getData() {
  var ss=SpreadsheetApp.openById('1PWJyASHmjJ_W8-72u8bbrGbN-Nv6kdkCvjdmYuNNlEY');
  var sheet=ss.getSheetByName('invoice1');
  return sheet;
}

function processSearch(searchform){
 var sheet = getData();
 var data = ObjApp.rangeToObjects(sheet.getDataRange().getValues());
 var searchinvoice=searchform.sinvoice;
 var searchfname=searchform.surname;
 var searchcname=searchform.scustomername;
 var searchpayementdate=searchform.spayementdate;
 var results = [];
 var events;
for(var i=0 ; i < data.length ; i++) {
if(searchinvoice == data[i].invoice) {
        events ={invoice:data[i].invoice,yourname:data[i].yourname, customername:data[i].customername,email:data[i].email,projectname:data[i].projectname,amountofmoney:data[i].amountofmoney,paymentday:data[i].paymentday,datecreate:data[i].datecreate,branch:data[i].branch,file:data[i].file  };

        results.push(events);
        return JSON.stringify(results);
      }
 }

   //Logger.log(results);
   return JSON.stringify(results);
}

[This](http://stackoverflow.com/questions/948532/how-do-you-convert-a-javascript-date-to-utc) or similar Q/As will help you. — Sangbok Lee, Mar 06 '17 at 10:43
Your script and spreadsheet might be in different time zones. — Anees Hameed, Mar 06 '17 at 17:11

score 0 · Accepted Answer · answered Mar 07 '17 at 08:38

I think you may have two problems which don't really depend on the code itself.

The date and time fields are actually stored as microseconds since the epoc in the Google Sheets.
When you set date/time format you need to make sure that your Google Sheet uses the same Time Zone and Locale information as your physical location - if these are different then you will get confusing results.

When you save only the date or only the time, some default values will be assumed for the missing part. So if you save only date, time part will have assumed value and it you save only time then the date part will have assumed value. These assumed values depend heavily on Locale and Time Zone settings.

Please check if these are affecting what you are seeing.

Retrieving the date record from google spreadsheet

1 Answers1