How to get google sheet's cell values as is using google script

Question

I'm about to use google sheet as my database for my android app small project. I'm using Google Script to handle the request from my app.

In my google sheet, I store;

• A2:A = date as dd/mm/yyyy e.g 21/12/2019 but the display format is dd-MMM e.g 21-Dec

• C2:D = time as hh:mm:ss e.g 21:00:00 but the display format is hh:mm e.g 21:00

Yes, I need a different format for the display and input.

My google sheet:

When I use google script to get a value of the cell, it seems that it is reformatted

the date looks like this: Sat Jan 01 2019 00:00:00 GMT+0700 (ICT) the time the other hand, change in value a bit. 20:00:00 to 19:52:48

Is there any function to get cell real values as text without being reformatted?

The only thing that I can think of is instead of using getValues(), I can use getDisplayValues(). The values will not be reformatted, but it is not a solution for me, as it will take the display format.

Snippet of my code:

function updateData(e, sheet) {
  var tgl = e.parameter.tgl;
  var dtg = e.parameter.dtg;
  var plg = e.parameter.plg;
  var lbr = e.parameter.lbr;

  var rangeHead = sheet.getRange("A2:A");
  var valuesHead = rangeHead.getValues();

  var rangeFirst = sheet.getRange("C2:D")
  var valuesFirst = rangeFirst.getValues();

  var rangeSecond = sheet.getRange("G2:G")
  var valuesSecond = rangeSecond.getValues();

  for (var i = 0; i < valuesHead.length; i++) {
    if (valuesHead[i][0] === tgl) {
      if(dtg!="null") { valuesFirst[i][0] = dtg; }
      if(plg!="null") { valuesFirst[i][1] = plg; }
      if(lbr!="null") { valuesSecond[i][0] = lbr; }
      break;
    }
  }
  rangeFirst.setValues(valuesFirst);
  rangeSecond.setValues(valuesSecond);
}

The code won't work as I will comparing 21/12/2019 with Sat Jan 01 2019 00:00:00 GMT+0700 (GMT).

[UPDATE 1]: Thank you P-Burke for the enlightenment. Now, I have an idea to solve the date problem. I know that the script pulls the date as date object, but I am unaware that it also saves as a date object. (hehe my bad) I don't realize it as there is no autocomplete when I call values[0][0]. of course, as it recognizes the object type at the run time.

So, my workaround will be; I will call getDate, getMonth+1, and getYear. After that, I will compare with my parameter freely.

Though, the time cell still a bit confusing for me. the time offset is 18 minutes 12 seconds. I don't think it's because of timezone different and my computer clock. the timezone different is too big and I 've made sure that the script, spreadsheet, and local timezone all the same. My computer clock is also only a minute less behind.

[UPDATE 2]: Alright, enough with the confusion. It seemed that the script converts the time to Date object respect to my local timezone. I got this answer from another thread. So, actually, my local timezone changes many times and some of them have offset smaller than hours unit (one of the timezones used in my area is UTC +7:07:12h). The only source documenting those changes I could find is from https://www.timeanddate.com/time/zone/indonesia/jakarta. Finally, I gave up. For my goodness sake, I will just use getDisplayValue and ignore the seconds. Unless you guys have any other workaround, I will be so grateful.

Thank you once again to the community.

Answer 1

Firstly, and I don't know if this is related to your issue, but the spreadsheet and the script each have their own timezone setting: Spreadsheet: File >> Spreadsheet Settings >> Time Zone. Script: File >> Project Properties >> Time Zone. And if these are different that can lead to confusion. One answer, if all your users are in the same timezone, is to set them to the same. Alternatively these can be determined from within your script as described here, and logic included to handle any differences. I don't understand the few minutes time difference, perhaps your PC clock is inaccurate?

The other point, which I think is more relevant to your question is that you effectively have multiple date/time formats in play. The picture below shows that in the spreadsheet times are edited in one format (02/01/2019 09:00:00), but displayed in whatever format is defined for the cell using the format menu. Yet when the cell values are pulled into a script using getValues() and displayed they appear as follows: Values: [[Thu Jan 31 09:00:00 GMT+00:00 2019, Wed Jan 02 09:00:00 GMT+00:00 2019]].

Yet in the code below, values[0][0] and values[0][1] are actually JavaScript Date() objects and can be compared in the usual way, alternatively they can be reformatted into whatever string format you require as illustrated in the code below:

function myFunction() {
    var ss = SpreadsheetApp.getActive();
    var ws = ss.getActiveSheet();
    var input_range = ws.getRange("A1:B1");
    var values = [];
    values = input_range.getValues(); // Returns a multi-dimensional array, hence [0][0] to access.
    Logger.log("Values: %s", values);

    // As Date() objects the usual methods are available.
    Logger.log("Date().getMonth(): %s",values[0][0].getMonth());
    Logger.log("Date().getYear(): %s",values[0][1].getYear());

    // This formats the date as Greenwich Mean Time in the format
    // year-month-dateThour-minute-second.
    var formattedDate = Utilities.formatDate(values[0][0], "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");
    Logger.log(formattedDate);
    formattedDate = Utilities.formatDate(values[0][1], "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");
    Logger.log(formattedDate);
}

Logger.log output:    
[19-01-31 11:43:17:635 GMT] Values: [[Thu Jan 31 09:00:00 GMT+00:00 2019, Wed Jan 02 09:00:00 GMT+00:00 2019]]
[19-01-31 11:43:17:636 GMT] Date().getMonth(): 0.0
[19-01-31 11:43:17:637 GMT] Date().getYear(): 2019.0
[19-01-31 11:43:17:638 GMT] 2019-01-31T09:00:00Z
[19-01-31 11:43:17:638 GMT] 2019-01-02T09:00:00Z