Set a formated date cell for Google sheet api with nodeJS

Question

I'm trying to update my google sheet by inserting a date. I want to have different format for different cells. Using nodeJS api I have a class Sheet with is used in another class SpreadSheet witch contains the spreadsheet info to make batchupdates

import {sheets_v4} from "googleapis";

type DatePattern = "dd-mm-yyyy" | "hh:mm:ss" | "dd-mm-yyyy hh:mm:ss" | string
type DateType = "DATE" | "TIME" | "DATE_TIME"

export class Sheet {

    private request: sheets_v4.Schema$Request[] = []

    /**
     *
     * @param columnIndex {number} Index of the column (Starting from 0)
     * @param rowIndex {number} Index of the row (Starting from 0)
     * @param value {string}
     */
    setPercentFormula(columnIndex: number, rowIndex: number, value: string) {
        this.request.push({
            updateCells: {
                fields: 'userEnteredValue,userEnteredFormat',
                range: {
                    sheetId: this.sheetId,
                    startColumnIndex: columnIndex,
                    endColumnIndex: columnIndex + 1,
                    startRowIndex: rowIndex,
                    endRowIndex: rowIndex + 1,
                },
                rows: [
                    {
                        values: [
                            {
                                userEnteredValue: {
                                    formulaValue: value
                                },
                                userEnteredFormat: {
                                    numberFormat: {
                                        type: "PERCENT",
                                        pattern: "#0.00%;-#0.00%;0%"
                                    }
                                }
                            },
                        ]
                    },
                ]
            }
        })
        return this
    }

    /**
     *
     * @param columnIndex {number} Index of the column (Starting from 0)
     * @param rowIndex {number} Index of the row (Starting from 0)
     * @param value {string}
     * @param type {DateType}
     * @param pattern {DatePattern} Pattern format
     */
    setDateWithPatternCell(columnIndex: number, rowIndex: number, value: string, type: DateType, pattern: DatePattern) {
        this.request.push({
            updateCells: {
                fields: 'userEnteredValue,userEnteredFormat',
                range: {
                    sheetId: this.sheetId,
                    startColumnIndex: columnIndex,
                    endColumnIndex: columnIndex + 1,
                    startRowIndex: rowIndex,
                    endRowIndex: rowIndex + 1,
                },
                rows: [
                    {
                        values: [
                            {
                                userEnteredValue: {
                                    stringValue: value,
                                },
                                userEnteredFormat: {
                                    numberFormat: {
                                        type: type,
                                        pattern: pattern
                                    }
                                }
                            },
                        ]
                    },
                ]
            }
        })
        return this
    }
}

I have other utility function, for boolean value or formula for example. All of they put the actions in the request array, then this array is use in batchUpdate call. For example, the use of setPercentFormula work perfectly well.

The result in my sheet is a cell with a starting coma and then the date as string given in the value parameter of my function.

It looks like my formatting isn't accounted.

As it is a number format, I also tried to use userEnteredValue: { numberValue: value } and give it a timestamp as input but resulting in setting the number in the cell and no date.

I also tried to make to separate call by putting two value in my request array. One setting the string and another one setting the formatting. with the same result.

I would like as a result to have my sheet with my date value as if I manually got in the file and clicked Format > Number > Date for example. Without the starting coma and a value recognized as a date.

Here are some input I tried :

Sheet.setDateWithPatternCell(0, 0, '2022-08-10', 'DATE', 'yyyy-mm-dd')
Sheet.setDateWithPatternCell(0, 0, Date.now(), 'DATE', 'yyyy-mm-dd')
Sheet.setDateWithPatternCell(0, 0, Date.now(), 'DATE_TIME', 'yyyy-mm-dd hh:mm:ss')

I expect the output in my google sheet to be the date with the format is specified. Not the string (as I see in my sheet it's starting with a ' in the value)

What is your input? Whenever possible, you need to include a minimal example that reproduces the issue. You can also include the expected behavior, the actual behavior, and how they differ, that would be helpful as well. Please visit [How to Ask](https://stackoverflow.com/help/how-to-ask) have some tips on how to write a question, so the community will be able to help you out in a better way. — Lorena Gomez, Aug 29 '22 at 21:17

score 1 · Accepted Answer · answered Aug 30 '22 at 00:14

I believe your goal is as follows.

You want to put a date object to a cell with your expected format using Sheets API.

Unfortunately, from your question, I couldn't find your tested values of columnIndex, rowIndex, value, type, pattern. So, in this answer, I would like to propose a sample request body for achieving your goal.

When "updateCells" request with the method of spreadsheets.batchUpdate is used, and a sample request body is as follows.

Sample request body:

{
  "requests": [
    {
      "updateCells": {
        "fields": "userEnteredValue,userEnteredFormat",
        "range": {
          "sheetId": 0,
          "startColumnIndex": 0,
          "endColumnIndex": 1,
          "startRowIndex": 0,
          "endRowIndex": 1
        },
        "rows": [
          {
            "values": [
              {
                "userEnteredValue": {
                  "numberValue": 44803
                },
                "userEnteredFormat": {
                  "numberFormat": {
                    "type": "DATE_TIME",
                    "pattern": "dd-mm-yyyy hh:mm:ss"
                  }
                }
              }
            ]
          }
        ]
      }
    }
  ]
}

44803 is the serial number. Please be careful about this.
When this request body is used with the method of spreadsheets.batchUpdate, 30-08-2022 00:00:00 is put into the cell "A1" of sheet ID of 0 as the date object.
For example, when the pattern, type and value are dd-mm-yyyy, DATE, and 44803, respectively, 30-08-2022 is put to the cell.

As another approach, when the method of spreadsheets.values.update is used, you can also the following script. In this case, 30-08-2022 00:00:00 is put to the cell "A1" of "Sheet1" as the date object.

  sheets.spreadsheets.values.update({
    spreadsheetId: "###", // Please set your Spreadsheet ID.
    range: "Sheet1!A1",
    valueInputOption: "USER_ENTERED",
    resource: { values: [["30-08-2022 00:00:00"]] },
  });

References:

Hi, thank you for your answer. I think you almost answer it but I have just one question : how do I convert a date (for example : new Date()) into the number you provided, as it doesn't look like a timestamp. I edited the question to show what I used as input. I would like to keep using only batchUpdate if it's possible and not just update as I want to push several update once each job. — MaximeL, Sep 01 '22 at 09:04
@MaximeL Thank you for replying. About your new question of `but I have just one question : how do I convert a date (for example : new Date()) into the number you provided, as it doesn't look like a timestamp. I edited the question to show what I used as input. I would like to keep using only batchUpdate if it's possible and not just update as I want to push several update once each job.`, I would like to support you. But, I have to apologize for my poor English skill. Unfortunately, I cannot understand what you want to do with your new question. Can I ask you the detail of your new question? — Tanaike, Sep 01 '22 at 11:09
@MaximeL I deeply apologize that I try to correctly understand your new question and I try to think of the solution to your new question by correctly understanding your new question. I think that This is due to my poor English skill. I deeply apologize for my poor English skill, again. — Tanaike, Sep 01 '22 at 11:11
no problem, I'll try my best to explain it. In you response in "Sample request body", you give as input `44803` for the field "numberValue". I would like to know where this value come from. How can you know this is `30-08-2022 00:00:00`. How do you convert `30-08-2022 00:00:00` into `44803` Schematically : `44803` <= ?how? => `30-08-2022 00:00:00` — MaximeL, Sep 05 '22 at 12:41
@MaximeL Thank you for replying. From your reply, I understood your 2nd question. In that case, `44803` is the serial number of `30-08-2022 00:00:00`. So, how about the sample script of `const res = ((new Date("2022/08/30")).getTime() / 1000 / 86400) + 25569;`? This is from https://stackoverflow.com/a/6154953 And, when you want to retrieve the integer number, please use `Math.ceil` and `Math.round`. By this, `44803` can be retrieved. If I misunderstood your 2nd question, I apologize. — Tanaike, Sep 05 '22 at 12:52

Set a formated date cell for Google sheet api with nodeJS

1 Answers1

Sample request body:

References: