Forum Discussion
Populating document library column value from on of the uploaded Excel file's cells (Possible?)
I've been trying to 'automate' a property column in a document library by having its value populated by each document added.
To try to explain a little clearer:
User adds document to the library (always the same Excel workbook format - just different info):
Cell SheetX!E11 will always be a value that we want to reflect the value of the column for that document. i.e.
So I am trying to get cell SheetX!E11 in the workbook to populate the specific column in the library.
What I've tried:
- Linking the cell in the workbook to a property. This works offline, but Excel Online does not support linked properties - and it works, but only when the document is initially uploaded. The drawback was a showstopper.
Options I've thought 'might' work, but still looking to find out if they are possible solutions and if anyone can help:
- PowerShell script on a scheduled task to go through each file in the SPO doc library, look at their cell E11, and write that as their corresponding property.
- Flow/Power Automate... ? I don't have a lot of experience with this, but seems like it 'might' have the power to accomplish this (just not sure how to reference a since cell in a document and write it to the column property for that doc... Essentially the whole process 😛 ).
Does this somewhat make sense what I'm trying to accomplish? Logically it seems like a simple task - but some limitations of the SharePoint Doc Library might have added some complexity (or impossibility...).
Does anyone have any experience with something like this? Or is there somewhere else I should be looking for this answer? Regardless, I appreciate ANY direction anyone might be able to provide!
Thank you!
- Yeah just add a column: ROWID and for the row value in that column a 1 on your file exports. Then, you can just plug in ROWID for key column, and 1 for the key row. Then you will always get the value in the first row. Then for your value in the SharePoint action, you should have the BaselineTotal in the pick list from the "Get Row" section, you should see all your excel columns in there.
This basically would set your SharePoint column you put it in, to the first row of the document with the BaselineTotal value.
19 Replies
- Power automate all the way. If I can remember to come back to this tomorrow when I get in and to a PC I can share a rough idea how to do it. I know you must use tables usually to reference data in the flow but that’s for a row. Not sure if there was a trigger for an exact cell I’ll have to look it over but this should be possible in Power Automate.
ChrisWebbTech Thanks a million! I appreciate your response. I've looked at Power Automate, as I mentioned, but I only noticed being able to reference to 'rows' - although I'm sure there's a way. I intend to educate myself further with PA given its usefulness, but with the pressure/timeline on this one, I'm left to 'reach out'.
** Sidenote: As I was doing a little more digging this morn, I came across an extensive BI blog that seems to belong to you - ironic, lol. You can add another 'follower' 🙂
Again, thank you!
I've been messing around with Flow/PA a little, but my roadblocks tend to be:
a) How to get the value of a single cell from the document.
b) Flow seems to want a specific workbook/file in the steps, where I am wanting to reference the 'current file' (the one that is modified/added.)
I figured I'd draw a simple picture of what I'm trying to get out of it:
Of course, the trigger can even be scheduled to 'ripple through' each doc and set the property - Ideally it would be on add/modify though...
