cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kbsv
Level: Powered On

Cannot enter value into a blank Excel cell

Hi. I am brand new to Flow, and am trying to create an Excel-flow which updates a table with new rows found, then sets the status to "updated" in the original table. The cells in the original table are empty (as in, no data whatsoever). It seems I cannot use the Update Row-function and simply type in the desired value of the empty cells - that gives me a 400-not found-error. Is there a nice and simple way to accomplish my goal? Or do I have to start using functions and variables? Given that I'm a complete newbie to the product, and this seems a basic functionality, I would really really love to avoid all variables and functions, if possible. 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Cannot enter value into a blank Excel cell

Excellent!  We're on a good path, then. 

If we switch your "Get Row" to "List Rows present in a Table", then it will grab every row in the table.  To just get the ones you want, you can add a filter inside this step, or use a condition.  Depending on the size of your table, the filter will probably give you better performance.

From there, use "Add a Row into a Table" to add the data from the original table into your new table.  This will start an "Apply to Each" loop, because Flow will add each row as it comes up.

 

If your condition (that you already have) is still necessary, leave that in.  If you don't need the condition (maybe because your filter in "List Rows" covered it, then just go straight to your Update Row (to update the original sheet).

 

As long as your update is inside the Apply loop, you should have access to the ID field you mentioned from the List Rows step.  You'll use that ID column in the "Key Column" and then the dynamic "ID" value as the "Key Value" from the original sheet, and now Flow will know exactly which row should be updated.

 

Remember, the steps will look like this:  Recurrence trigger > List Rows from the original sheet > Add Rows to the new sheet > Update rows on the original sheet.  (just so you don't try to update the new sheet or something Smiley Happy)

 

Keep us posted.

-Ed-

 

 

If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

View solution in original post

6 REPLIES 6
Super User
Super User

Re: Cannot enter value into a blank Excel cell

@kbsv 

Hi there!

So...if I understand correctly, the Update part isn't working maybe because it can't figure out which rows to update.  Sharing a screenshot of your current Flow will be helpful, but here's how I might sketch this out:

  • Trigger would be a Recurrence (on a schedule...maybe once a day) - What are you using now?
  • Next, List the rows in the table (from original sheet)
  • Either add a condition to filter for the "Updated" field being blank, or use an ODATA filter in the step above.
  • Add a row to a table (the new sheet) using the information from the step above.
  • Update the original rows (one at a time).  I think the issue you're facing now is that the original rows might not have a unique identifier??  Something like an ID or key field for Flow to know which row to update.  How do you have it set currently?

Let us know what you've got, and maybe if there's anything we can use to uniquely identify each row from the original spreadsheet.

 

Thanks!

-Ed-

 

If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

kbsv
Level: Powered On

Re: Cannot enter value into a blank Excel cell

Hi. So here are a couple of screenshots. One is of the cut-back flow, just focussing on finding and updating the blank cells in a specific column. The Get-Row command actually finds the empty cells now, but strangely the Update-Row cannot update the row using the exact same syntax.

 

The second screenshot is of the error message I get on the Update-Row command. 

 

basic flow.pngerror msg.png

Super User
Super User

Re: Cannot enter value into a blank Excel cell

Is it possible that the concat expression generates a 'key' which is produced in multiple rows?  The Get Row might just go to the first match, but the Update Row will want to be more specific...at least in my head.

 

Is there a part in the process that adds the rows, that could include a truly 'native' and unique identifier?  Still not sure if this is the issue, just poking at the box a bit.

 

Thanks!

-Ed-

kbsv
Level: Powered On

Re: Cannot enter value into a blank Excel cell

There is an ID-column in the table that the first Get-Row command uses, and those are unique. How would I use the unique ID in order to get the Update-Row command to complete successfully?

Super User
Super User

Re: Cannot enter value into a blank Excel cell

Excellent!  We're on a good path, then. 

If we switch your "Get Row" to "List Rows present in a Table", then it will grab every row in the table.  To just get the ones you want, you can add a filter inside this step, or use a condition.  Depending on the size of your table, the filter will probably give you better performance.

From there, use "Add a Row into a Table" to add the data from the original table into your new table.  This will start an "Apply to Each" loop, because Flow will add each row as it comes up.

 

If your condition (that you already have) is still necessary, leave that in.  If you don't need the condition (maybe because your filter in "List Rows" covered it, then just go straight to your Update Row (to update the original sheet).

 

As long as your update is inside the Apply loop, you should have access to the ID field you mentioned from the List Rows step.  You'll use that ID column in the "Key Column" and then the dynamic "ID" value as the "Key Value" from the original sheet, and now Flow will know exactly which row should be updated.

 

Remember, the steps will look like this:  Recurrence trigger > List Rows from the original sheet > Add Rows to the new sheet > Update rows on the original sheet.  (just so you don't try to update the new sheet or something Smiley Happy)

 

Keep us posted.

-Ed-

 

 

If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

View solution in original post

kbsv
Level: Powered On

Re: Cannot enter value into a blank Excel cell

Brilliant stuff, thank you so much for your help Ed - my flow now does what it's supposed to. Smiley Happy

Helpful resources

Announcements
firstImage

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

firstImage

Incoming: New and improved badges!

Look out for new contribution recognition badges coming SOON!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Solution Authors
Top Kudoed Authors
Users online (4,585)