Microsoft graph table api not returning column values in its date format

Harshad Panmand 0
Viewed 30

I'm retrieving data from Microsoft workbook/excel file's table. Workbook's table has date-of-birth column which has date format. While retrieving values using Microsoft graph's table api it returns dates as some integer value.

API i'm using:

https://graph.microsoft.com/v1.0/me/drive/root:/book.xlsx:/workbook/tables/table4/columns

Microsoft graph API reference : https://docs.microsoft.com/en-us/graph/api/table-list-columns?view=graph-rest-1.0&tabs=http

{
"@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users('<user_id>')/drive/root/workbook/tables('table4')/columns",
"value": [
    {
        "@odata.id": "/users('<user_id>')/drive/root/workbook/tables(%27%7BF44C7573-9BBE-46F3-B0BE-C5607762D2AB%7D%27)/columns(%271%27)",
        "values": [
            [
                "Name"
            ],
            [
                "Student 1"
            ],
            [
                "Student 2"
            ]
        ],
        "id": "1",
        "index": 0,
        "name": "Name"
    },
    {
        "@odata.id": "/users('<user_id>')/drive/root/workbook/tables(%27%7BF44C7573-9BBE-46F3-B0BE-C5607762D2AB%7D%27)/columns(%273%27)",
        "values": [
            [
                "DOB"
            ],
            [
                35339
            ],
            [
                34249
            ]
        ],
        "id": "3",
        "index": 1,
        "name": "DOB"
    }
 ]
}

Original values are enter image description here

Is there any way to get date values in its original format

excel-2010 microsoft-graph-api
1 Answers

The endpoint you are using

GET /me/drive/root:/book.xlsx:/workbook/tables/table4/columns

returns workbookTableColumn resource type which has only information about raw values without information about data type and formatting.

You can use id or name of workbookTableColumn and call dataBodyRange endpoint

GET /me/drive/root:/{item-path}:/workbook/tables/{id|name}/columns/{columnId|columnName}/dataBodyRange
GET /me/drive/root:/book.xlsx:/workbook/tables/{id|name}/columns/{columnId|columnName}/dataBodyRange

which returns range resource type.

Range resource type has property text which represents text values of the specified range.

...
"text": [
    [
        "09.06.2022"
    ],
    [
        ""
    ]
],
...
user22501520
Related