Issue in reading date from excel table

Question

Hi,
I have a DOB column in my excel which has a value "11/11/2020" but microsoft graph api is returning the value as "44146" . I m using nodejs to call the microsoft's API.

Please help me to resolve this .

Answer 1

Hi @Anshika Mishra

Thanks for reaching out!

The endpoint 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 which returns range resource type. Range resource type has property text which represents text values of the specified range.

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

Please find below screenshots for better understanding.

Without dataBodyRange in API

With dataBodyRange in API

Hope this helps!

If the answer is helpful, please click Accept Answer and kindly upvote. If you have any further questions about this answer, please click Comment.

Answer 2

Hi @HarmeetSingh7172 ,

Thanks for your response.
But I m bit concerned if there is any way possible to get date in proper format in "value []" of response instead of "text []" , because the columns are dynamic in nature, One excel can have date column whereas another excel could have number in column.

using "text [ ]" from response of graph API instead of "value[ ]" will convert all my return types to string .

If you know any way to convert this 5-digit date format to "dd-mm-yyyy" through any mathematical calculations or js function , it will also be helpful .

Thanks