Warm tip: This article is reproduced from serverfault.com, please click

How to Get Date and Time String from Google Spreadsheet as String?

发布于 2020-11-28 16:33:30

I'm trying to get Date and Time data from my Google SpreadSheet by Flutter HTTP Get method. However the problem is when I read it, Date and Time is read as different format. For example, In Google Sheet, only '2020-11-27' is written. But when I read it from Flutter, it reads '2020-11-27T00:00:00Z'. I tried data casting to string but now I got 'Mon Jan 13 2020 00:00:00 GMT+0900'. Is there anyone can help me to read it just as it is? I mean String format.

And time is also the same. I wrote only 11:10 in Google Sheet, but when I read it from Flutter, it says 1899-12-30 11:42:08. I have now idea where the time come from...

Please find my code below and help me. Thank you.

Google Spread Sheet Data.

test    2020-01-13  11:10   2022-11-01  3:03

Google Script Code

function doGet(request){
  var data = [];
  
  // Open Google Sheet using ID
  var SpreadSheets = SpreadsheetApp.openById("ID");

   // Get all values in active sheet
  var Sheet = SpreadSheets.getSheetByName("Data");
  var values = Sheet.getDataRange().getValues();  
  
  // Iterate values in descending order 
  for (var i = values.length - 1; i > 0; i--) 
  {
    // Get each row
    var row = values[i];

    // Create object
    var feedback = {};
    
    // ↓ String Casting and Org assignment. Both are not okay.
    feedback['DepartureDate'] = row[1].toString();    
    feedback['DepartureTime'] = row[2].toString();    
    feedback['ArrivalDate'] = row[3].toString();
    feedback['ArrivalTime'] = row[4].toString();
    //feedback['DepartureDate'] = row[1];
    //feedback['DepartureTime'] = row[2];    
    //feedback['ArrivalDate'] = row[3];
    //feedback['ArrivalTime'] = row[4];

    // Push each row object in data
    data.push(feedback);
  }     
 
  // Return result
  return ContentService
  .createTextOutput(JSON.stringify(data))
  .setMimeType(ContentService.MimeType.JSON);
}

Flutter HTTP Get Code

Future<List<DriveRecordForm>> getData_Easy() async {
    final response = await http.get(URL);
    if (response.statusCode == 200) {
      var temp = convert.jsonDecode(response.body);   // I checked response.body and the date and time is diffent from my expectation. (Expecation: 2020-01-13 / Result: Mon Jan 13 2020 00:00:00 GMT+0900...

Thank you.

Questioner
chan hong Park
Viewed
0
Diego 2020-11-29 00:37:18

When using getValues(), Google will interpret the cell content and give you the types it thinks are correct.

The values may be of type Number, Boolean, Date, or String, depending on the value of the cell.

Use getDisplayValues() if you want to get the values exactly as they appear in the spreadsheet (as strings).