pythonsharepointlistitemoffice365-rest-client

How to upload multiple items in a list in Sharepoint Online using python office 365 rest


I have a list in sharepoint online: ListA

This list has 13 items. I want to add more ,let's say 3 more.

I also have a dataframe with 3 columns: Title, Scientist, ID

I would like to create multiple new items, one for each row, and upload them into the list

   df = pd.DataFrame({'Title': ['A', 'B', 'C'], 'Scientist': ['d', 'e', 'f'], 'ID': [1,2,3]}) 

I am fully authenticated.

I use the add_item() method from: https://github.com/vgrem/Office365-REST-Python-Client/blob/master/office365/sharepoint/lists/list.py


    context_auth2 = AuthenticationContext(url=app_settings2['url'])
    context_auth2.acquire_token_for_app(client_id=app_settings2['client_id'], 
    client_secret=app_settings2['client_secret'])
    ctx2 = ClientContext(app_settings2['url'], context_auth2)

    new_list_ar = ctx2.web.lists.get_by_title("ListA")
    new_list_ar_items = new_list_ar.get_items().top(13)
    ctx2.load(new_list_ar_items)
    ctx2.execute_query() # I get the list items and check their names

    new_list_ar.add_item({'Title': df['Title'], 'Scientist': df['Scientist'], 'ID': df['ID']})
    ctx2.execute_query()


This doesn't work. I get the error: TypeError: Object of type Series is not JSON serializable

So, I was wondering whether there was a way to create a new item to a sharepoint list for each row in a dataframe


Solution

  • Sample test demo, id is generated by SharePoint automatically, don't set it.

    import json
    import pandas as pd
    from office365.runtime.auth.authentication_context import AuthenticationContext
    from office365.runtime.client_request import ClientRequest
    from office365.sharepoint.client_context import ClientContext
    
    app_settings = {
         'url': 'https://xxx.sharepoint.com/sites/lee',
         'client_id': 'clientid',
         'client_secret': 'secret'
    }
    
    context_auth = AuthenticationContext(url=app_settings['url'])
    context_auth.acquire_token_for_app(client_id=app_settings['client_id'],
    client_secret=app_settings['client_secret'])
    ctx = ClientContext(app_settings['url'], context_auth)
    
    list = ctx.web.lists.get_by_title("ListA")
    
    df = pd.DataFrame({'Title': ['A', 'B', 'C'], 'Scientist': ['d', 'e', 'f'], 'ID': [1,2,3]}) 
    
    for index, row in df.iterrows():
        print(row['Title'], row['Scientist'])
        list.add_item({'Title': row['Title'], 'Scientist': row['Scientist']})
    ctx.execute_query()