How to transform sharepoint list items into an excel format

Question

I'm trying to fetch the columns inside a sharepoint list using below MS Graph API:-

sites/site-id/lists/list-id/items?expand=fields

Below is the output that I'm getting:-

{

  "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#sites('{site-id}')/lists('{list-id}')/items",

  "@odata.nextLink": "https://graph.microsoft.com/v1.0/sites/{site-id}/lists/{list-id}/items?skiptoken=Paged=TRUE%26p_ID=100",

  "value": [

    {

      "id": "1",

      "etag": ""{etag}"",

      "parentReference": {

        "id": "parent-id",

        "driveId": "parent-drive-id"

      },

      "fields": {

    "@odata.etag" :1244884dhhehjjiye, 

        "Title": "Item 1",

        "Description": "Description of Item 1",

        "Category": "Category A",

        "Status": "Active"

      }

    },

    {

  "@odata.etag":" Shdhdhdheg27473"

      "id": "2",

      "etag": ""{etag}"",

      "parentReference": {

        "id": "parent-id",

        "driveId": "parent-drive-id"

      },

      "fields": {

    "@odata.etag":" Jdnfjureggge1377

        "Title": "Item 2",

        "Description": "Description of Item 2",

        "Category": "Category B",

        "Status": "Inactive"

      }

    },

    {

      "id": "3",

      "etag": ""{etag}"",

      "parentReference": {

        "id": "parent-id",

        "driveId": "parent-drive-id"

      },

      "fields": {

    "@odata.etag": " dgehiognvvvt6783-68", 

        "Title": "Item 3",

        "Description": "Description of Item 3",

        "Category": "Category C",

        "Status": "Active"

      }

    }

  ]

}

Now I want to store all the columns inside the fields attribute of Value array in an excel sheet. I was not able to find any way for extracting the fields and storing it in an excel sheet.

Answer 1

I recommend that you use the Microsoft Graph API to fetch the data as you have done. You will need to parse the JSON response to extract the relevant fields.

Then use Python with the pandas library to convert the JSON data into a DataFrame.

Assuming you have already fetched the data using Microsoft Graph API and have the JSON response stored in a variable named data.

You can use the pandas library to parse the JSON data and convert it to a DataFrame.

import pandas as pd

# Sample JSON data (replace with your actual data)

data = {

  "value": [

    {

      "id": "1",

      "fields": {

        "Title": "Item 1",

        "Description": "Description of Item 1",

        "Category": "Category A",

        "Status": "Active"

      }

    },

    {

      "id": "2",

      "fields": {

        "Title": "Item 2",

        "Description": "Description of Item 2",

        "Category": "Category B",

        "Status": "Inactive"

      }

    },

    {

      "id": "3",

      "fields": {

        "Title": "Item 3",

        "Description": "Description of Item 3",

        "Category": "Category C",

        "Status": "Active"

      }

    }

  ]

}

# Extract fields data

fields_data = [item['fields'] for item in data['value']]

# Convert to DataFrame

df = pd.DataFrame(fields_data)

# Display DataFrame

print(df)

You can save the DataFrame to an Excel file using the to_excel method.

# Save DataFrame to Excel

df.to_excel('sharepoint_list_items.xlsx', index=False)

print("Data has been saved to 'sharepoint_list_items.xlsx'")