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)
I believe your goal is as follows.
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.
{
"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"]] },
});