CODEWITHSUNDEEP
excelmicrosoft-graph-api
Barry Gervin
Barry Gervin

Reputation: 63

Excel Table Columns without Data, or Schema

I'm trying to load the rows of a table into a typed collection. I'm familiar with the "BuildList" samples that rely on getting row data into a string array. However, this isn't very reliable if a user inserts a column or rearranges the column position in a table. So I want to get a list of columns and their ordinal positions of the table.

What is the best way to do this? The /Rows call doesn't bring back column names, and /Columns is good, but it also brings back all the data for all columns. Is there a way to bring back Columns but with no data or some other call to describe the table schema (although I don't expect to get data types).

Update I'm using the sample app from here: https://github.com/microsoftgraph/aspnet-donations-rest-sample/blob/master/Microsoft-Graph-ASPNET-Excel-Donations/ExcelAPIHelper.cs. Specifically, the BuildList method used hard coded stringarray references (lines 108-112). This is very fragile and will break if the user rearranges the columns in the excel file, or inserts an extra column.

I want to get the list of columns from a table without the data. The List Columns Method (i.e. table('MyTable')/columns) returns all of the data values for each column as well). See ref: https://developer.microsoft.com/en-us/graph/docs/api-reference/v1.0/api/table_list_columns. ) I hope that is clearer.

Upvotes: 1

Views: 410

Answers (1)

Barry Gervin
Barry Gervin

Reputation: 63

I found that you can use OData query parameters and just pull back the names and index of the columns with $select=name,index. 

https://graph.microsoft.com/beta/drives/omitted/items/omitted/workbook/worksheets('Sheet2')/tables('Table2')/Columns?$select=name,index

and the resulting json

{
"@odata.context": "https://graph.microsoft.com/beta/$metadata#drives('omitted')/items('omitted')/workbook/worksheets('Sheet2')/tables('Table2')/columns(name)",
"value": [
    {
        "@odata.id": "/drives('omitted')/items('omitted')/workbook/worksheets(%27%7B00000000-0001-0000-0100-000000000000%7D%27)/tables(%272%27)/columns(%279%27)",
        "id": "9",
        "name": "Date"
    },
    {
        "@odata.id": "/drives('omitted')/items('omitted')/workbook/worksheets(%27%7B00000000-0001-0000-0100-000000000000%7D%27)/tables(%272%27)/columns(%2710%27)",
        "id": "10",
        "name": "Time"
    },
    {
        "@odata.id": "/drives('omitted')/items('omitted')/workbook/worksheets(%27%7B00000000-0001-0000-0100-000000000000%7D%27)/tables(%272%27)/columns(%2711%27)",
        "id": "11",
        "name": "Rink"
    }
}

Upvotes: 2

Related Questions