Update the Created By (AuthorID) Field of a SharePoint Online List Item with Power Automate

[FraserM]

Disclaimer: There are a few steps required to get this done, but the reward at the end is worth the effort.

Get Created By field GUID

To update the Created By column, we need to first find it’s Field GUID.  Because the Created By column is set to read only, we will query our list for all fields but filter them to only show ones set ReadOnlyField = true

1. Create a new Send HTTP Request to SharePoint action.  Fill in, adjust according to your tenant / match the rest.
   
   URI: _api/web/lists/getbytitle('Your%20List%20Name')/fields?$filter=ReadOnlyField eq true
   
   
2. Run your flow and open the run history, we want to get at the Outputs from the action we created in step 1.
   
   Copy the body content and paste it into a text editor of your choice.
   
   
3. Search for Created By in your text editor, and just a few lines up from where it appears is your Created By Field GUID.  Note this because we are going to need it later.
   
   You can remove the Send HTTP Request action we created in step 1 – no longer required.
   
   

Get UserID Number to write to Created By column

Created By is a person column that has an associated AuthorId field.  The AuthorId field contains a numeric value (ie: 11) which is used to populate the Created By person data.  Our end goal (soon)-- to simply update this field to the numeric value of the user we want listed in the Created By column.

First we need to get our replacement numeric value from somewhere. In the following example I look up a sharepoint users numeric value, simply by supplying their email address (in the example this was dynamic content supplied by a person column in my list called 'EmployeeName')

1. Create a new Send HTTP Request to SharePoint action.  Fill in, adjust according to your tenant / match the rest.  Let’s get some user data we can pull details from.
   
   Uri: _api/web/siteusers/getByEmail('[USER@EMAIL.ADDRESS]')
   Plug in any dynamic content that will provide you with an email address
   
2. Now that we have this collection of user data, instead of messing around with formulas to isolate the Users ID #, it’s JSON… let’s parse it.  Create a new Parse JSON action and plug in Body from the dynamic content provided by the Send HTTP Request of the step above.
   
   This guide is long enough, if you don’t know how to use a Parse JSON action / generate from sample – it’s super easy to find a how-to on the net.  Go check that out and come back.  Forgive me.
   
   The Parse JSON action will now create a schwack of dynamic content that can be selectable in future actions.  One dynamic content entry called 'Id', is the numeric value associated to the email address we provided above and exactly what we are going to plug into the AuthorId field!  But, first we must...
   
   

Unlock the Created By column

Now that we have taken care of all the prerequisites, let’s get on with unlocking the column for manual updating.  The column is locked by SharePoint automatically after a record gets updated - if you don't unlock it before attempting to update it's contents, the column doesn't get changed.

Create a new Send HTTP Request to SharePoint action.  Fill in, adjust according to your tenant/list/Created By fields GUID, match the rest.

Uri: _api/web/lists/getbytitle('Your%20List%20Name')/fields(guid'Your-GUID-goes-here-yasss')
Body: { '__metadata': { 'type': 'SP.Field' }, 'ReadOnlyField': false }

Update the Created By field

Now that the Created By column is unlocked, we can update its fields.

Create a new Send HTTP Request to SharePoint action.  Fill in, adjust accordingly to your tenant/list, match the rest.

Uri: _api/web/lists/getbytitle('Your%20List%20Name')/items('[ID]')
The ID you should be able to plug in from dynamic content from other actions (ie: Get Items, When a New Item is Created, etc)

Body: { '__metadata': { 'type': 'SP.Data.Your_x0020_List_x0020_NameListItem' }, 'AuthorId': [Id] }
For the [Id] shown, we plug in some dynamic content from our Parse JSON Action.  Note: !pay attention here to cAse.  Use the dynamic content Id (uppercase i lowercase d) -- and not id (all lowercase) when you choose.

Re-lock the Created By column

Now we can set the Created By column back to read-only.

Create a new Send HTTP Request to SharePoint action.  Fill in, adjust according to your tenant/list/Created By fields GUID, match the rest.

Uri: _api/web/lists/getbytitle('Your%20List%20Name')/fields(guid'Your-GUID-goes-here-yasss')
Body: { '__metadata': { 'type': 'SP.Field' }, 'ReadOnlyField': true }

I spent the better part of a day sifting through bits and pieces from various forums -- most of them producing more questions than solutions.  Hopefully, you find this helpful (and save yourself some time).

-F

[Illmas]

Helloe Fraser

Your steps are detailed however as a non programmer iam not able to use it across for my scenario.

Could this technique be used dynamically to update a Sharepoint list item that has been copied from another list?

I have flow that that copies across from list 1 to list 2 if the user ticks a yes.

I would like the flow to update the created by or modified by field for the copied item through the same flow while its being created or updated.

All help is appreciated.

[bradleyhorton]

Hi @FraserM Would this work the same for editing the Created By column in a document library, rather than a list?

I've tried to follow this through and am able to amend other columns, just not the AuthorID/EditorID so I'm guessing the field isn't unlocking for me to edit but I'm not sure why. It might just be I'm getting the wrong FieldID.

Any help greatly appreciated.

Here's my current HTTP request to unlock the field 

[Anonymous]

I follow all the steps and the result is ok in the flow, but don´t change the Author in the list, I test it changing the ID, but still continue with the same author ID

[Anonymous]

Wow, just wow, you man just saved my ass. Thanks a lot for sharing this, if you ever visit Czech Republic I owe you a beer 🙂 

[lukaszpudlo]

I'm trying to anonymize entries in a SharePoint list that are more than one year old, that is, I would like to change all Created By for those entries to "Anonymous".

I imagine that I can skip the Parse JSON part of the above manual. When I'm running the last Send an HTTP request to SharePoint, I'm getting an error message, saying that the format is incorrect. The details shows it's something to do with the items('[ID]').

This is what I have at the moment:

Do you have any suggestions on how to achieve a flow that would allow me to anonymize data that is older than one year?

[Anonymous]

Hello Fraser,

thanks for your instructions.

I'm trying your solution, but the AuthorId stubbornly keeps its original value.

Do you know if something has changed why this wouldn't be working anymore?

Kind regards,

Frans

[sliu17]

Hi, 

I am hitting an error on the step where it tries to update the created by field but i am getting :

Invalid JSON. A token was not recognized in the JSON content.

Any ideas?

[sliu17]

I was able to figure out the issue. Schema was not all there when i generated a sample so recreating that resolved my invalid JSON error. 

IF you follow the instructions, you should have no issues. if you do run into problems, i would suggest double checking every config against the posts and if you continue to have issues, rebuilding the components or checking your schema should resolve most issues. 

[rl17]

I've been through this several times and I'm missing something.

I get an error on the UpdateCreatedBy request:

A 'PrimitiveValue' node with non-null value was found when trying to read the value of a navigation property; however, a 'StartArray' node, a 'StartObject' node, or a 'PrimitiveValue' node with null value was expected.

My Unlock step (below) works fine. I can even do a GET request after this, read the fields as described (omitting the 'read only' filter, and confirm that the read only flag changes to false.

Here is my UpdateCreatedBy.. step:

Body field contains: { '__metadata': {'type': 'SP.Data.FlowtestListItem'},'AuthorID': 59} 

I manually entered the item id (confirmed it exists!) and the Author Id (found per another GET request). I will change them to be dynamic content when the Flow test run is working.

Note that I changed AuthorID to Author. Author is what shows up as the "InternalName" and "StaticName" slightly above the "Title": "Created By" as found via the GET step to find the GUID.

If I have 'AuthorID' in my body field I get this error:

The property 'AuthorID' does not exist on type 'SP.Data.FlowtestListItem'. Make sure to only use property names that are defined by the type.

In case it helps, here's the raw input that I can pull after the Flow test fails:

{

    "host": {

        "connectionReferenceName": "shared_sharepointonline",

        "operationId": "HttpRequest"

    },

    "parameters": {

        "dataset": "https://________.sharepoint.com/sites/QualityManagement",

        "parameters/method": "PATCH",

        "parameters/uri": "_api/web/lists/getbytitle('flowtest')/items(3)",

        "parameters/headers": {

            "content-type": "application/json;odata=verbose",

            "IF-MATCH": "*",

            "X-HTTP-Method": "MERGE"

        },

        "parameters/body": "{ '__metadata': {'type': 'SP.Data.FlowtestListItem'},'Author': 59}"

    }

}

As one other oddity... my list name is 'flowtest' (all lower case) but if I use SP.Data.flowtestListItem (i.e. lowercase f) I get the following error:

A type named 'SP.Data.flowtestListItem' could not be resolved by the model. When a model is available, each type name must resolve to a valid type.

The error goes away by changing to SP.Data.FlowtestListItem

If anyone has any ideas or further suggestions, I look forward to them. What started out as a very simple task has taken much more effort than anticipated.

If there's an easier way... I don't mind jumping ship and taking the easy way out! 🙂

My goal is to send a form to users, have them fill it out, have the Flow transfer the data into a Sharepoint List, and then have the Sharepoint List Item-level permissions set to allow users to come back and edit their submissions but not edit others (i.e. a user can edit items they created, which obviously fails if the Flow overwrites the creation and shows me as the creator instead of the user).

[FraserM]

@lukaszpudlo The [ID] at the end of the Uri is dynamic content .  It will be a numeric value that corresponds with the row number of the item being processed.  ie: /items(1) i think would be the first row in the list.
(I can see from the screenshot you are currently just typing in [ID] )

Uri: _api/web/lists/getbytitle('Your%20List%20Name')/items('[ID]')

Next, where you have typed 'Anonymous' that won't work.  That field is a numeric value assigned to all sharepoint users (and not a string of text as you have typed in). I have updated my original post above to better explain this - try passing a value in there just for fun.. say 1 or 2 just for fun 😉

Body: { '__metadata': { 'type': 'SP.Data.Your_x0020_List_x0020_NameListItem' }, 'AuthorId': [Id] }

As for an idea on how to anonymize after a year.. possibly.  From what I can see you want to set it to 'Anonymous' so why not create a user in O365/SharePoint called Anonymous, use the LookupSPUserId method i outlined to grab the associated Id numeric value and then run a scheduled flow to check for entries aged to fit your criteria, and set the AuthorId field on them.  You should be able to use an user@tenant.onmicrosoft.com address also so you wont need to assign any licenses. Follow up info here should you need to poke sharepoint to create the user object (post by Tiago Paes)

[FraserM]

@rl17  you are super close on yours.

I re-read your post and I think I know what the problem is.

Take a look here.  I'm willing to bet you renamed it from something else TO flowtest at some point along it's life. As a result, what you are passing in the body for 'Flowtest' isn't matching.

There is a way to get a list of all your columns (or "Field") info. Note: it's per view (ie: All Items, etc)

Method: POST
Uri: _api/web/lists/getbytitle('ListName')/views/getbytitle('View%20Name')/viewfields

check the outputs... here's all your column names

 Easiest way to fix it:
1. delete the flowtest column
2. recreate it using Flowtest as the name.

3. Change back to AuthorId in the Body section (Author is def not going to work) (here's a lot of discussion about it and it all shows updating the AuthorId field.. was low hanging fruit while looking for supporting evidence)

4. Try your flow again. Did that work? 

[rl17]

Feeling like a wild goose chase... I appreciate your suggestions @FraserM 

I followed your first link, which, if I read correctly, was intended to confirm the name of the list.

I sent the command (via web browser): 
https://________.sharepoint.com/sites/QualityManagement/_api/web/lists/getbytitle('flowtest')?$selec...
and got as a return: a whole bunch of stuff plus:

<updated>2021-03-19T22:55:51Z</updated><author><name /></author><content type="application/xml"><m:properties><d:ListItemEntityTypeFullName>SP.Data.FlowtestListItem</d:ListItemEntityTypeFullName></m:properties></content></entry>

So it looks pretty clear that it expects FlowtestListItem - which is fine, and matches what I had already found by my guess-and-check method. 🙂

After that, I tried the next part of your post, so I sent (again, just in a browser for the sake of speed):
https://_______.sharepoint.com/sites/QualityManagement/_api/web/lists/getbytitle('flowtest')/views/g...
(I have a view called ByPerson which shows the 'Created By' field that I want to change)
Which returned a whole bunch more, including:

"results": [

        "Person",

        "Date",

        "CEActivity",

        "Hours",

        "CEArea",

        "Notes",

        "Author"

      ]

So again, "Author" shows up, and not "AuthorID".

Not sure what you mean about deleting the 'flowtest' column - 'flowtest' is the name of my list, but there are no columns with that name.

In the body section of my UpdateCreatedBy step, changing to 'AuthorID' gives me the same error I had before:

The property 'AuthorID' does not exist on type 'SP.Data.FlowtestListItem'. Make sure to only use property names that are defined by the type.
clientRequestId: bc0292ac-5ba0-4647-92fc-30102f15534c
serviceRequestId: eceeb59f-505b-b000-e270-842485749fcc

Looked at your link in Step 3 - I agree that it all points to AuthorID - which is why I'm confused about the above error. The discussion in that link includes another call to find out if the field is read-only or not:

https://_____.sharepoint.com/sites/QualityManagement/_api/web/lists/getbytitle('flowtest')/fields/ge...

I can sift through the output from that and I do see ReadOnly="FALSE" (and plenty of references to 'Author' and no references to 'AuthorID').

Circles back to where I was before... if I put 'AuthorID' in the body call I get the 'property does not exist' error, and if I use 'Author' instead I get the 'PrimitiveValue node' message from my first post.

Again, I do appreciate the thoughts and effort into sorting this out. I hope it speaks to some deeper issue that Power Automate could really be simpler for the end-user!

[P_Paul]

Hello!

@FraserM  I did everything like you wrote but I am getting an error in Parse JSON step: Unable to process template language expressions in action 'Parse_JSON' inputs at line '1' and column '10316': 'Required property 'content' expects a value but got null. Path ''.'.

One step above I am getting data in Output so its not null or empty:

This is my shema in Parse JSON step:

{
     "type": [
    "object",
    "null"
    ],
    "properties": {
        "d": {
            "type": "object",
            "properties": {
                "__metadata": {
                    "type": "object",
                    "properties": {
                        "id": {
                            "type": "string"
                        },
                        "uri": {
                            "type": "string"
                        },
                        "type": {
                            "type": "string"
                        }
                    }
                },
                "Alerts": {
                    "type": "object",
                    "properties": {
                        "__deferred": {
                            "type": "object",
                            "properties": {
                                "uri": {
                                    "type": "string"
                                }
                            }
                        }
                    }
                },
                "Groups": {
                    "type": "object",
                    "properties": {
                        "__deferred": {
                            "type": "object",
                            "properties": {
                                "uri": {
                                    "type": "string"
                                }
                            }
                        }
                    }
                },
                "Id": {
                    "type": "integer"
                },
                "IsHiddenInUI": {
                    "type": "boolean"
                },
                "LoginName": {
                    "type": "string"
                },
                "Title": {
                    "type": "string"
                },
                "PrincipalType": {
                    "type": "integer"
                },
                "Email": {
                    "type": "string"
                },
                "Expiration": {
                    "type": "string"
                },
                "IsEmailAuthenticationGuestUser": {
                    "type": "boolean"
                },
                "IsShareByEmailGuestUser": {
                    "type": "boolean"
                },
                "IsSiteAdmin": {
                    "type": "boolean"
                },
                "UserId": {
                    "type": "object",
                    "properties": {
                        "__metadata": {
                            "type": "object",
                            "properties": {
                                "type": {
                                    "type": "string"
                                }
                            }
                        },
                        "NameId": {
                            "type": "string"
                        },
                        "NameIdIssuer": {
                            "type": "string"
                        }
                    }
                },
                "UserPrincipalName": {
                    "type": "string"
                }
            }
        }
    }
}

Do you know what is wrong?

[FraserM]

If you watch this video ... you will level up about twice in twenty minutes 😉
https://www.youtube.com/watch?v=kk2dfIcwOn8
trust me...

the error looks like its related to schema. its expecting a value somewhere and not receiving one.

in that video he talks about trimming down the data that goes into the schema generator/example. pay close attention to that 😉 and i think you've got it

[P_Paul]

Thanks a lot 🙂 everything working fine now.

I got another question/idea now. Can we set sharepoint/aad group as a CreatedBy same like User?

[Anonymous]

Wow impressive.

Need to use thos tipe to find my Sp.data A type named ’SP.Data.ListItem’ could not be resolved by the model - Fabozzi's Compiled Knowledge. 

Works like a charm by default.

Is it possible to update the created date also ? 

I add a action send http - I highlighted in yellow the GUID of the created column

but i don't know how to update the created column

Do you know how @FraserM ?

Thanks for the reply

[Anonymous]

Forget it, I change CreatedID by Created and it works. 

Thank you for the incredible Works. Thank you, you save me

[AndreasLu]

Hi, this is very useful (and it got me to learn something about Json). However I get a 401 (unauthorised) error on the unlock field command. 

I checked the parameters (site/list and field GUID) and they seem to be correct.

I'm the owner of the list, and have edit access to the site, but I'm not a Sharepoint admin.

What level of access is needed to the Sharepoint site and list?

Thanks

Andreas

[Anonymous]

I'm not sharepoint admin and it works for me.