3

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

Vyron B.
  • 35
  • 3
  • 7

1 Answers1

3

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()
Lee
  • 5,305
  • 1
  • 6
  • 12
  • Thanks for the answer. At the ctx.execute_query() part, I get this error: '-1, Microsoft.SharePoint.Client.InvalidClientQueryException', 'Invalid JSON. A token was not recognized in the JSON content.', "400 Client Error: Bad Request for url: What could this mean? Is this an authentication issue? Because I can draw data just fine – Vyron B. Jul 02 '20 at 10:49
  • So, to answer my own comment, I need to rerun the entire snippet every time I need to upload a new item (from context-auth and forward). Thank you for the heads up! – Vyron B. Jul 03 '20 at 12:02
  • still getting the json error if i put many entries – Carlost Sep 08 '22 at 07:01
  • @Carlost check if you have any nulls (pandas `nan` values) in your dataframe. You can remove them by adding `df = df.fillna('')` like they mention in [this answer](https://stackoverflow.com/a/41213102/8121682). That fixed my json error. – matt123788 Jun 02 '23 at 00:02