_RowNumber is forced to be the key of a Big Query database

[Julien]

Hi Community,

I'm trying to connect a Big Query database to a test App.

The platform forces the _RowNumber as key even if I set another columns. After hitting Save, the _RowNumber is set back as the key with this message :

The spreadsheet row number is being used as a key in the table '10316071_CTMP'. It's best to include an 'ID' column in the table.

In the App, when I try to edit a row, I get this error message :

Unable to update row in table '10316071_CTMP'. → The service bigquery has thrown an exception. No HttpStatusCode was specified. Job bigquery-305114/EU/job_871fc367_645c_4f1d_a84f_32792f47b6ab contained 2 error(s). First error message: Unrecognized name: _RowNumber at [1:817]: Message[Unrecognized name: _RowNumber at [1:817]] Location[query - ] Reason[invalidQuery] Domain[], Message[Unrecognized name: _RowNumber at [1:817]] Location[query - ] Reason[invalidQuery] Domain[]

Thanks for your help!

[Swoopy]

Is there any duplication of data presented therein the key columns? It may prevent them to be used as (primary) key.

[Romain_Marechal]

Hi Swoopy,

I work with Julien.

I have the same issues with empty table.

And I just tested with a new table. The new table is exactly the same table of the first one but with a "distinct" applied on the ID colunm to test it, same story.

Other test we follow again this procedure (see below) to create permission to appsheet but same story again.

https://support.google.com/appsheet/answer/11905680?hl=en&ref_topic=10102124

Thanks for your help !

[Swoopy]

Is that because the blank rows are counted?

[ThomasClaes]

@Julien @Romain_Marechal , did you guys manage to find a solution? If so, would you mind sharing it?

[volczi]

Hi,

I have a workaround:
1,  create a table in BQ
2,  add the table in AppSheet
3, go back to BQ and modify the schema: add a "_RowNumber " field

Try to write it. This way the BQ job will succeed.

Disatvantage:  The "regenerate schema" feature will fail if you trigger it after this. And AFAIK there is no way to fix it once you triggered. I had to recreate the table from scratch 😕 

[Romain_Marechal]

@ThomasClaes Hi, No solution unfortunately. 

Do you have the same issue ? I think I doing bad something or nobody uses Bigquery and Appsheet...😆

[ThomasClaes]

Yes, same issue. I'm thinking it's the latter of those two because this seems like a really basic issue and yet I can't find anything about it except this post. 😞

[Romain_Marechal]

I also find this error look really basic. I don't understand why no one from appsheet answers.....🤔

[Romain_Marechal]

@ThomasClaes 

Do you have try to add a Row_number table?

I haven't tried but maybe it's a prerequisite to work with a biquery table in Appsheet?

[ThomasClaes]

@Romain_Marechal 
Adding a _RowNumber column in BigQuery does work for creating a new entry, this column seems to remain null on creation. (Second row was added using the appSheet app)

Problem that occurs, since primary key is set on _RowNumber, this throws an error when trying to remove the above mentioned entry.

[VarunChandran]

Try to use a different column as Key and If that column has the data type Long Text then change it to Text because Key will not be enabled for Long Text Column. I already faced this issue and it has been resolved by this method

[Wimv]

This is likely the problem.  I have experienced the exact same thing.  All string columns in bigquery tend to default to long text.   When you put a key on the longtext field, it riles back to the rownumber.  When you change to a text field then make that column a key, it saves and stays. 

Show screen captures if this is still not working. 

[Monitoreo]

By default AppSheet adds a field called _RowNumber as a key, you simply have to mark another field key and that's it.