Can I add rows to an empty Excel worksheet using MS Graph?

Question

Hi,

I am testing using the Microsoft Graph Explorer, and I can successfully add a new worksheet to a workbook using the REST API, but is it possible to also add data to the new empty worksheet? There doesn't seem to be a POST option for new rows in a worksheet.

Alternatively, it seems I can add rows to a Table within the spreadsheet (https://learn.microsoft.com/en-us/graph/excel-write-to-workbook and https://learn.microsoft.com/en-us/graph/api/table-post-rows?view=graph-rest-1.0&tabs=http).

However, when I add a table to a spreadsheet, and then use the GET command https://graph.microsoft.com/v1.0/me/drive/items/{drive-item-id}/workbook/worksheets/{sheet-id}/tables

I get the following error, even though the worksheet does contain a table:

{  
    "error": {  
        "code": "ItemNotFound",  
        "message": "The requested resource doesn't exist.",  
        "innerError": {  
            "code": "itemNotFound",  
            "message": "The requested resource doesn't exist.",  
            "date": "2021-02-15T01:47:03",  
            "request-id": "4d499bbd-82ca-4fb6-8e18-312bffa24386",  
            "client-request-id": "a3073642-7c42-de6a-463e-d9e25c42b340"  
        }  
    }  
}  

I also get this error when I try and POST data to the table, using both /rows/add and /columns

Appreciate any help I can get with this.

Thanks

Ian

Answer 1

@OM Developer AU I am happy to help. If this answer was helpful please consider accepting and upvoting to help other uses find answers.

For the error you are getting, it should work fine if you use indices in order and the payload is ok. So here are two things you could be doing wrong.

First case:
If you have a table that has "address": "Sheet1!A1:D5", and try to create a column whose index is 99 , that index does not exist in this table because cells A TO D has 0-3 indices and 99 is out. So ensure the index falls within your table.

Second case:

If you are passing wrong values and not matching the row values for the column.
If you table has "address": "Sheet1!A1:D5", values has to be a list of values including the column name. For example, the below will create the last column on table with "address": "Sheet1!A1:D5" with 5 row fields.

   POST /me/drive/items/{item-id}/workbook/tables/Table1/columns  
   {  
       "id": "4",  
       "name": "Column4",  
       "index": 3,  
       "values": [  
           [  
               "Column3"  
           ],  
           [  
               "value 1"  
           ],  
           [  
               "value 2"  
           ],  
           [  
               "value 3"  
           ],  
           [  
               "value 4"  
           ],  
           [  
               "value 5"  
           ]  
       ]  
   }  

That said, I think the documentation creating columns https://learn.microsoft.com/en-us/graph/api/table-post-columns?view=graph-rest-1.0&tabs=http is insufficient on explaining usage of the value field.

Answer 2

As far as I understand, You must have a table with known Name or Id in the sheet before you can insert rows into it. I am able to use POST /me/drive/{drive-item-id}/Book1.xlsx:/workbook/tables/Table1/rows/add to add rows to an existing table.

As for the error you get when try to get tables:
I have been able to go through this process and get the tables successfully with both Personal and Business type drives. So I created an empty Excel worksheet and was able to create a table and rows using the alternative endpoints you mentioned. I am not sure why you get 404. Here is are steps that can help you figure out assuming you have created the sheet with a table in it

Try the following.
Get your drives and use the desired drive ID
GET /me/drive

Next Get the drive items and take the ID of the Excel WorkBook
GET /me/drive/root/children or with GET /drives/{drive-id}/root/children

Next Get the worksheets in the workbook and take the sheet id. Note you mat get a sheet id that looks like {0001000-000000000000} and you have to use it as is ( Note the braces).
GET /me/drive/items/{excel-workbook-ID/drive-item-id}/workbook/worksheets

Finally Get the tables in this sheet
GET /me/drive/items/{excel-workbook-ID/drive-item-id}/workbook/worksheets/{0001000-000000000000}/tables

You should be able to get the tables. I think you did not use the correct of either sheet-id or drive-item-id. I also suggest you try this on Graph Explorer

Answer 3

Hi @DanstanOnyango-6080 ,

Thanks for your help.

I can now get my tables, but I'm having trouble creating columns.

When I try
POST /me/drive/items/{drive_item_id}/workbook/worksheets/Sheet1/tables/Table2/columns

with the request:

{  
"id": "99",  
"name": "name-value",  
"index": 99,  
"values": "values-value"  
}  

I get this error:

{  
"error": {  
"code": "InvalidArgument",  
"message": "The argument is invalid or missing or has an incorrect format.",  
"innerError": {  
"code": "invalidArgument",  
"message": "The argument is invalid or missing or has an incorrect format.",  
"date": "2021-02-17T06:58:56",  
"request-id": "85f6afb7-7083-4403-bd7d-fd157b1ae41e",  
"client-request-id": "faef0d3c-2ab8-c92f-7864-bd1111427fe3"  
}  
}  
}  

Any ideas what I'm doing wrong here? I'm following this doc: https://learn.microsoft.com/en-us/graph/api/table-post-columns?view=graph-rest-1.0&tabs=http

Thanks again.

Ian

Answer 4

@zemuldo Thanks very much. I have been able to successfully add a new column to the table. Is it possible to POST multiple columns using the same request? Or do I have to run a different POST for each column I want to add?