0
250
ViewsGoogle sheets API sorting by date
I'm using nodejs with google sheets API and I'm trying to sort data by column with date string that looks like this: 'dd.mm', for example '01.03'. Currently I'm trying to do it by using batchUpdate, but it doesnt work. It sorts by day, 01 is first, 02 is second etc... My request looks like this:
await googleSheets.spreadsheets.batchUpdate({
spreadsheetId,
requestBody: {
requests: [
{
sortRange: {
range: {
sheetId,
startRowIndex,
endRowIndex,
startColumnIndex,
endColumnIndex,
},
sortSpecs: [
{
dimensionIndex,
sortOrder,
},
],
},
},
],
},
});
How can I make google sheets API see the columns as dates, not as strings? Because I assume thats where the problem is, the date string is sorted like a normal string.
·
Juan Pablo Isaza
1 answers
Answer question0
Format the range to date format of day.month in your Google Sheets.
- Highlight column or range you want to format.
- Click Format Tab -> Number -> Custom number format.
- In the text box, insert this value:
d"."m. - Click Apply.
Step by step example:
Google Sheets can now recognize the data as date, you can sort it now using the API.
Example:
Request:
{
"requests": [
{
"sortRange": {
"range": {
"sheetId": 0,
"startRowIndex": 0,
"startColumnIndex": 0,
"endRowIndex": 13,
"endColumnIndex": 1
},
"sortSpecs": [
{
"sortOrder": "ASCENDING",
"dimensionIndex": 0
}
]
}
}
]
}
Output:
Reference:
·
Juan Pablo Isaza
Report
Answer question