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

Google Sheets - Updating a specific row

Hello All --
I am needing to update some rows in Google Sheets.  Updating a record requires that I provide a value for the "__PowerAppsId__" column - the column that PowerApps creates automatically in Google Sheets to use as a unique identifer for each row. I don't know what the value for the column is without actually opening the sheet and looking. I can't figure out how to programatically retreive it for a row.  I tried creating a card that would display the Id but that doesn't work.  Any suggestions?

What is the formula for updating a Google Sheet with regard to the Id field?

Help would be most appreciated!

4 REPLIES 4
Super User
Super User

Re: Google Sheets - Updating a specific row

Hi @jhn1 ,

 

From what I recall, the __powerappsId__ field was strictly for PowerApps 'system' use. 

Logically it sounds like a primary key column you can use - but it doesn't appear as a column through the connector so you can't reference it.

If you want to update a specific row in something like Sheets or Excel, you need to find it by using a different column with unique values.  If you don't have any columns where the values are all unique, you'd have to create one and manage it if you're doing updates to data.

For example, create an 'ID' column on the table and fill it with consecutive numbers.  Whenever you add data to the table, you'd have to collect the highest number from the column and +1 it for the new record.

 

Hope this helps,


RT

jhn1
Level: Powered On

Re: Google Sheets - Updating a specific row

Hello @RusselThomas ,

Thank you for your suggestion. I already found this suggestion as the answer in another post and tried it.  Failed.  I get the error message "__PowerAppsId__: field required".  So, it seems (unless I'm missing something) PowerApps knows exactly what it wants in this situation, but won't expose the data for me to provide it. 

I would love to find a solution for this!

 

Thanks again for your help.

Super User
Super User

Re: Google Sheets - Updating a specific row

Hi @jhn1 ,

hmmmm, ok perhaps I didn't understand properly...

PowerApps needs (and auto-generates) the __powerappsId__ column, but you can't use it.  So forget about using it for LookUps or anything Smiley Happy

 

If you decided to add your own  primary key lookup column - eg: "myIDcolumn", you must make sure it's part of (whatever the Excel Table equivalent is in sheets), otherwise it will be ignored - plus you have to populate it yourself.

In Excel, depending on the version, you might even have to destroy the Table construct and recreate it to include the ID column with the rest of the data - but essentially as long as it's recognised as part of the Table, PA should pic it up no problem.  The error message you're receiving sounds the __powerappsId__ column was removed or something.

Kind regards,

RT 

jhn1
Level: Powered On

Re: Google Sheets - Updating a specific row

Thanks for your thoughts, @RusselThomas !
I can't find anything in Google Sheets help about formatting a spreadsheet as a table the way you can in Excel.  All I can find is how to create a Pivot Table, which is not what we want here, I think.  Anyway, I had no trouble making my "ID" field from Google Sheets visible in PA, putting it on an invisible card and then referring to it in my formula.  It just didn't seem to fly as a unique key field.  When attempting to use it in my Update formula, I got the error message I described in my last reply.  And I did not erase or otherwise change the column in my Google Sheets titled "__PowerAppsId__".  (I have always felt the best policy was to leave it strickly alone figuring that PA put it there for a reason.) 

I am puzzled!