Date is storing has a String

[vinodhreddygs]

Hi @theoephraim , When ever I try to store EST date (like 06/19/2020) in the sheet cell, it stores as String format using cell based API but if try to store same 06/19/2020 using row based API it stores as a Date object. Please let me know how to store date as a Date format instead string in sheet using cell based API. Thanks.

[RobertSmith8]

same problem, this code stores date as a string rawData.getCell(min+j, 4).value = moment().format('YYYY-MM-DD HH:mm')

How can I store is as a Date object?

it was working in the older version (v2) of this module

[theoephraim]

I think you would need to update the formatting rules for those cells. https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#NumberFormat

You could either do it beforehand in the UI on the whole column, or you can do it programatically by updating cell.numberFormat

[RobertSmith8]

I think you would need to update the formatting rules for those cells. https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#NumberFormat

You could either do it beforehand in the UI on the whole column, or you can do it programatically by updating cell.numberFormat

Only works if I do manually not programatically

My ugly workaround is to use a helper column like = A1*1

           rawData.getCell(min+j, 4).formula = '=F' + String(min+j+1)+'*1'
           rawData.getCell(min+j, 5).value = moment().format('YYYY-MM-DD HH:mm')

[callumgare]

I'm getting this issue too. Unfortunately cell.numberFormat = { type: 'DATE' } doesn't seem to help. Also unfortunately I can not use helper columns as a workaround in my situation.

[franciscojun]

I can confirm this is an issue. cell.numberFormat = {type: 'DATE', pattern: 'dd/mm/yyyy'} doesn't work at all.

[franciscojun]

Dear @theoephraim ,

I think I found the bug:

On (Line 115, GoogleSpreadsheetCell.js): if (_.isEqual(newVal, _.get(this._rawData, 'userEnteredFormat.${param}')))

Scenario: My current (original) cell format is 'Date', as defined using Google Sheet interface. My new cell value is, let's say, '25/01/1980' (dd/mm/yyyy). This date is always stored as string even if I call cell.numberFormat = { type: 'DATE', pattern: 'dd/mm/yyyy'}; because the line 115 above prevents this setting to be applied to the actual online cell. The final result is: I always end up with a date stored as string, even if I try to change. I can confirm this is the issue because changing the original cell format (Google Sheet interface) to, let's say, plain text, the date format is properly applied! (Because original format is different than the intended one).

Unfortunately I can't make a PR soon, but I hope this helps you in fixing the issue.

For those facing this issue, as a workaround, the example above shows that we can make the intended format to be applied to the cells by ensuring the original cell format is different than the intended one.

[theoephraim]

@franciscojun - thanks so much for tracking things down. I'll see if I can take a look this weekend :)

[Stepashka20]

@franciscojun I have the same problem :( As a result, how to insert a date into a cell? What should I change on line 115?

[antony]

I'm not sure line 115 is the problem. Logging out the request shows that the update gets through without issue:

'[\n' +
  '  {\n' +
  '    "updateCells": {\n' +
  '      "rows": [\n' +
  '        {\n' +
  '          "values": [\n' +
  '            {\n' +
  '              "userEnteredValue": {\n' +
  '                "numberValue": 1675771166965\n' +
  '              },\n' +
  '              "userEnteredFormat": {\n' +
  '                "numberFormat": {\n' +
  '                  "type": "DATE",\n' +
  '                  "pattern": "mmm yyy"\n' +
  '                }\n' +
  '              }\n' +
  '            }\n' +
  '          ]\n' +
  '        }\n' +
  '      ],\n' +
  '      "fields": "userEnteredValue,userEnteredFormat",\n' +
  '      "start": {\n' +
  '        "sheetId": 1002285593,\n' +
  '        "rowIndex": 1,\n' +
  '        "columnIndex": 0\n' +
  '      }\n' +
  '    }\n' +
  '  }\n' +

I'm still trying to diagnose why google sheets doesn't pick this up

[antony]

I don't think there's a bug here. After much experimentation, the way to ensure a date formats correctly is to ensure you're passing it as a serial number.

See this example:

function toSerialDate (inDate) {
  const returnDateTime = 25569.0 + ((inDate.getTime() - (inDate.getTimezoneOffset() * 60 * 1000)) / (1000 * 60 * 60 * 24))
  return Number(returnDateTime.toString().substring(0, 5))
}

cell.value = toSerialDate(new Date())
cell.numberFormat = {
  type: 'DATE',
  format: 'MM yyy'
}

That will render a formatted date.

[theoephraim]

combining into #630