Recently, I happened to look into how google sheets connector works, and, to be honest, I was a little bit disappointed.
First, I wanted to blame google. But, it turned out, there is no hiding the fact that’s it’s been provided by Microsoft itself:
In general, this connector works. But there is one notable issue, which is we can’t specify “id” value when adding new row:
You may think that Id above is what it is, but no. I would have to be “Row Id”, which is what the connector would be using in other actions, such as Get Row:
You will find more details on “row id” in my earlier post:
Long story short, if I wanted to add a row to the spreadsheet in such a way that I’d still be able to read that specific row later using “Get Row” action, I would not be able to. Since I would not be able to specify “Row Id” – instead, it would be generated for me (for example, imagine that “Ford”/”Dodge”/etc are all id values. Assuming you’d want to be able to load car make data from the spreadsheet, you could do it if you kept adding rows to the spreadsheet manually, but you won’t be able to keep adding new rows to the spreadsheet directly from the Flow, since you’d be getting random row-id values instead of car makes).
For this one, I just added an idea, though I’m guessing it won’t be too popular since it might not be such a common scenario to use Google sheets with Power Automate:
Which brings me to the Excel Online connector. And that one shines in comparison.
None of the google sheets connector issues seem to exist there. There is a similar “get row” action, but I can specify a key column – there is no need for some special id columns/values:
Which is why “Add a row”, even though it looks very similar to the same action in Google sheets connector, works perfectly fine:
It’s my own “id” column this time, I don’t need a special “Row Id”, so it’s all good.
This seems to be consistent throughout Excel connector actions, which brings me to a very simple conclusion:
As of Nov 2020, if you need to use spreadsheets with Power Automate, and if you have a choice between Google Spreadsheets and Excel Online, go with Excel.
This has nothing to do with Google vs Microsoft. This has everything to do with the connector capabilities.