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!
Is there any duplication of data presented therein the key columns? It may prevent them to be used as (primary) key.
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 !
Is that because the blank rows are counted?
@Julien @Romain_Marechal , did you guys manage to find a solution? If so, would you mind sharing it?
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 ๐
@ThomasClaes Hi, No solution unfortunately.
Do you have the same issue ? I think I doing bad something or nobody uses Bigquery and Appsheet...๐
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. ๐
I also find this error look really basic. I don't understand why no one from appsheet answers.....๐ค
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?
@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.
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
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.
By default AppSheet adds a field called _RowNumber as a key, you simply have to mark another field key and that's it.
Changing in the AppSheet works. Thank you
User | Count |
---|---|
17 | |
14 | |
8 | |
7 | |
4 |