If you ever wanted to use Google sheets in Power Automate, there is a connector for that:
However, it turned out to be a little tricky, and this is what this post is about.
Imagine there is a very simple spreadsheet:
What if I wanted to read a specific row from that spreadsheet in the Power Automate flow?
I can pick the file, I can also pick the worksheet… but what is supposed to go into the Row Id parameters?
Well, what if I put “1” into that field:
As soon as that happens, a new column gets added to my spreadsheet automatically:
Apparently, this column is where google sheets connector will try to find a match on the row id.
The column title is “__PowerAppsId__”. If I had that column in the spreadsheet, it would not be added one again – google sheets connector would just start using that column for the Get Row action.
So, now, I just need to fill that row with the correct ids (and I can use a formula for that):
And voila:
I”m back. I’ve never used this idea but have used “Get Rows” and “Get Row” google sheets connectors in the attempt to fetch data. I never fully succeeded. I have succeeded in using the “Insert Row” step.
In each of those steps the connected adds the “__PowerAppsId__”. However it also fills the row with data. Here’s a few rows of it. I’m wondering what it is for then? Do you think I can replace it with my own key per your suggestion and not cause any problems?
__PowerAppsId__
7d25fe7a58d247628cb7d497ede3f41a
66a4d6b13c5c4b70aa4de424980cd0ef
8b8b3e3f366b45dfb2206641bcf6aab3
3eff106a63e34cde8ecc0e691e74f37f
29215faf2f9749e4a73d8db49bdbc5c5
3013c8eb996f42a49acffa00ae0c7008
ce504b7c80924d7d8ffd8a662337939c
1e99c4ff842d4296892747f408721851
1631f5c9b7d645e39ca9b82cb801fb0c
2a90420cd46a4d8faa4a4c814544083d
24978b5eacf046fc80d10408792ff5b5
477cd531087b4f94ac5faa9762784cf8
054fb16944154dd380c5acf6a88a6614
1857f44a6f7541518ff3002e5477b2d5
f8b3b1a4182849e18ddaf5fabb70227d
Hm… right, that’s what’s happening. Not sure how to work around it – you may have to go back to the “Get Rows” and “Filter”. Just wrote up another post which might help:
https://www.itaintboring.com/power-platform/output-fields-now-you-see-them-and-not-you-dont-whats-up-with-flow-mechanics/
I’ve successfully changed __PowerAppsId__ columns in google sheet that power automate has already filled. No problems occur. In fact I replaced their default values (those long key-like strings) with formulas as you suggested.
Question regarding your comment “So, now, I just need to fill that row with the correct ids (and I can use a formula for that):”
If we use a formula then clearly that formula needs to be added to any new rows. If I used power automate’s “Insert Row” step then how do assure that the new row added includes the formula? Would I immediately follow with an “Update Row”? If so I’d need a “Row id”. Hmm. Feels like a catch-22.
Thoughts?