Cli-microsoft365: Enhancement: Enable batching for SharePoint List Item creation

[garrytrinder]

The spo listitem add command should allow users to create multiple SharePoint List Items without having to execute the same command multiple times.

If you need to create a couple of items this most likely is not an issue but we should consider scenarios where hundreds or even thousands of items need to be created. At the moment the automation script required to perform this action would run into hundreds and thousands of lines.

To address this issue, it is proposed that a new command be created under 'spo listitem' called 'batch' with a sub commands for add, i.e. spo listitem batch add, that takes a definition file (initially txt or csv) as an input which describes each item to be created when the command is executed.

Example: spo listitem batch add --path listitems.csv

This approach would then allow us to further expand the batch command with more sub commands to cover remove, update and get should the need arise.

Once the approach is validated, we could implement it on other types like file, list, field, file, etc.

Based on research by @waldekmastykarz using the Microsoft Graph batched requests would provide the best performance, therefore we should look to use this in our implementation.

Source: https://blog.mastykarz.nl/fastest-way-create-sharepoint-list-items/

[russgove]

anyone working on this.?

[russgove]

also The spo listitem update command should also allow users to update multiple SharePoint List Items without having to execute the same command multiple times.

[russgove]

And perhaps rather than just a csv as input, we could let it take it’s inputs from another list?

[garrytrinder]

Hey @russgove 👋🏻

No one is working on this at the moment, would you like to take it?

also The spo listitem update command should also allow users to update multiple SharePoint List Items without having to execute the same command multiple times.

This is something that we can consider, however this issue is to provide a way of bulk creating items in the first instance. Once we have validated an approach for creation we can certainly look to enhance the functionality to provide a similar update command.

And perhaps rather than just a csv as input, we could let it take it’s inputs from another list?

We can certainly do this, however let's keep it simple with using flat files (csv) for now, we can always enhance the command once the base functionality is validated.

[russgove]

Yes , I can take it. I have most of the required code from that other command i proposed.

[garrytrinder]

Great 😀

All yours @russgove 👍🏻

[russgove]

@garrytrinder So what you call spo listitem batch add is remarkably similar to what I call[ed] spo field copy!

We're both talking about bulk updates to sharepoint list items.

You want to read from a CSV, I want to read from sharepoint.
You want to add new items , I want to update existing items.

The hard parts of both our needs are the same -- the data type conversion, the batching, the mapping, maybe some caching.

So for spo listitem batch add :
Lets say column headers must match the internal name of the field to be updated.
We need a --batchsize parameter to say how many updates to send to the backend at a time.
We need to have some switches to determine how to map the text we recieve from a csv column to a sharepoint field. (i.e. for a user column , is it the display name , or the email we should map on)

[russgove]

does anyone have any preferences as to which csv parser should be used?

[garrytrinder]

The hard parts of both our needs are the same -- the data type conversion, the batching, the mapping, maybe some caching.

I'm not sure I agree with the data conversion part, the issue does not propose to implement any conversion of data types but to simplify the creation of list items in bulk.

Just to clarify what is being proposed, currently to create multiple items using the CLI the user would need to create a line for each item they wish to create. This could become a burden to maintain if creating 100s of items this way.

spo listitem add --contentType Item --listTitle "Demo List" --webUrl https://contoso.sharepoint.com/sites/project-x --Title "Demo Item 1"
spo listitem add --contentType Item --listTitle "Demo List" --webUrl https://contoso.sharepoint.com/sites/project-x --Title "Demo Item 2"
spo listitem add --contentType Item --listTitle "Demo List" --webUrl https://contoso.sharepoint.com/sites/project-x --Title "Demo Item 3"

The proposed spo listitem batch add command would simplify this process by allowing the user to supply a flat file that contains the values to create the list items. I see this new command in its most basic form as acting as a wrapper for the spo listitem add command functionality that already supports different data types.

spo listitem batch add --path listitems.csv

The contents of the listitems.csv file in the above example would contain the following contents, the headings mapping to the options on the spo listitem add command, Title being an example of a SharePoint field.

contentType,listTitle,webUrl,Title
Item,Demo List,https://contoso.sharepoint.com/sites/project-x,Demo Item 1
Item,Demo List,https://contoso.sharepoint.com/sites/project-x,Demo Item 2
Item,Demo List,https://contoso.sharepoint.com/sites/project-x,Demo Item 3

@waldekmastykarz @VelinGeorgiev do you have any suggestions regarding the above design?

We need a --batchsize parameter to say how many updates to send to the backend at a time.

I don't think we need to expose this to the user and is something that can be handled internally by the command.

does anyone have any preferences as to which csv parser should be used?

fast-csv looks like a good option which has decent support and examples https://www.npmjs.com/package/fast-csv

[waldekmastykarz]

@garrytrinder agreed with the design you proposed. Let's keep it simple to start with and we can always extend it later if there is any interest.

[russgove]

OK, I was thinking that we would 'convert' that data on the csv to a form that was acceptable to sharepoint. We would put john.[email protected] in the csv and the command would convert it to the format needed for SP. Same idea for other column types.

I'll skip all that stuff an make it just duplicate what listitem add does.

[waldekmastykarz]

Hey @russgove, are you still working on this?

[russgove]

hey,
haven’t had a chance to work on it in a long,long time.
i hope to get some time over the holidays.
if i’m blocking someone else from working on it, you can remove me from the task.
russell

Sent from my iPad

On Nov 21, 2020, at 2:18 PM, Waldek Mastykarz [e-mail] wrote:


Hey @russgove, are you still working on this?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub, or unsubscribe.

[waldekmastykarz]

No problem. I don't think it's blocking anyone.

[garrytrinder]

I have created a new issue (#2029) with a defined spec in the new command template to provide some clarity around the initial ask for this proposal, therefore I will close this issue as the work will be carried out against the new issue.