SOLVED

Populating document library column value from on of the uploaded Excel file's cells (Possible?)

Copper Contributor

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.

sheet1.png

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 :p ).

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!

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.

@Chris Webb 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:

flow.png

 

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...

So I'm finally getting around to this, I need to know a bit more about the excel sheet, basically in order to work with flow it has to be inside a table, and we have to somehow have a value on the row to retrieve the data in the row. Trying to find out if you can just put in a generic excel row number somehow but not finding anything.

The table thing can be tricky so that might kill it first off. Do you know if a table is used for the data in the excel file?

@Chris Webb 

 

Might still work :)

 

Looks like the cell I'm referencing is actually "Dashboard!X2"

It DOES exist within a table (yay) named "KPIData"

The table's column header is "BaselineTotal" (if that's something I/you would need to make this work?)

that's good. Trying to figure out how to reference. what is Dashboard!X2? is there anything static on the Row you are trying to pull? Like the row next to it, does it have a value always in that spot or anything like that?

@Chris Webb 

 

what is Dashboard!X2?

The cell reference:

Workbook Sheet Name = "Dashboard"

Cell = X2

 

is there anything static on the Row you are trying to pull? Like the row next to it, does it have a value always in that spot or anything like that?

I think I see what you mean here... If there I were to add a column before/after (left or right of - i.e. Y2 or W2) the cell containing the value that could just be a static label, this would be useful...?  I can perhaps drop something in...

Yeah I'm trying to find a way to reference the arrow and just say row 27 or whatever, but worse case if you could add something, so we can say column 'Temp', row value 'Key Value', then we could match that row and return the row with the actual value, which we can then set in SharePoint.

@Jamie Milliken basically it would work like this. you would point to library, with the create or modified trigger. Then point to static location / library and use the Filename with ext. from the first step. (might require the /) in front of it. Then you can take Key Column / Value from the column before the one you need, or really it can be anywhere on the sheet as long as it gets you the row the value is on. Put in the static match values since those should be the same on every sheet for mapping. Then you can return the value you need in a Update File properties step at the end in place of my Email example. 

 

If we can figure out how to just pull the row out via an array reference or something you can do away with the extra column and value static map and just refer direct to the row / value, assuming every single file is the same. 

 

flow.JPG

@Chris Webb 

 

Here's a quick snip of the table...

 

flow2.png

(highlighted number is the one I'm trying to pull for the property)

 

So column would be 'BaselineTotal'

Is the JobID in the filename or something? Could always do a substring or something for that to get the row value key we need.
Ok.... this is good. I understand. I think the only thing I'm stuck on is that you're referencing a single workbook (filename) in the Flow. So, is there a way to have the value that exists in each workbook uploaded populate its property value from the corresponding file that gets uploaded/modified?
Yes, the JobID is at the beginning of the filename in each one.

Ok, was thinking this wouldn't allow dynamic file names, but I figured it out, you actually have to use the Identifier ID from the trigger action as the file name, then you can static the table name and the key values and this works. 

 

That said, if your Filename begins with the ID, then all you need to do is for the Column is use the static ProjectID or whatever that first column is can't see it right now :p, and then for the row value, use an expression substring(filename, 0, length) basically, start typing in that, then for filename with cursor there, find the "Filename with extension" from the SharePoint trigger, then 0 to start at the left then length will be the projectID length, hopefully static. This should always then give you the value you need. Then at the last action you put in the "Update file properties" action, and you can use the Column from the Get Rows where you value you need to update a column in SharePoint. 

Hmm, seems your project ID's can differ length wise so that might not work. You can always add a ROWID column and a 1 if every file just has a single row in it and you can add that to the output on those files then you can just reference that for the row match and return the one column you need to update the SharePoint field.
The filename part - awesome, you're teaching me a ton already, lol.

And yes, that would probably be easy enough to add the rowID (I'm presuming essentially that this is the key?). That makes sense, as you're right, it's always a single row in the table.
best response confirmed by Jamie Milliken (Copper Contributor)
Solution
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.
Thanks Chris! I think this all give me plenty to make this work. I really appreciate you taking the time to guide me in the right direction - you've been more than helpful!

Cheers!
1 best response

Accepted Solutions
best response confirmed by Jamie Milliken (Copper Contributor)
Solution
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.

View solution in original post