Currently, I’m having a mismatch schema errors where the App detects 31 columns in my table while the schema has only 18 columns. I’m using Google Sheets as my data source. I’m not allowed to modify the sheet by any means, told by my team lead and now I’m in a position where I have to figure out how to bypass the schema errors without making use of 2 sheets (which I’m using as a temporary solution in order for the App to work). My team lead told me he did his research and found a solution to bypass the error and he said that it’s in the Appsheet documentation. I did ask him how he did it or where in the documentation, even which section did he find said solution. He proceeded to tell me to go read the documentation again and asked if I even read it properly. I did and found nothing so I’m up here asking for help if anybody knows what is the solution to bypass the mismatch schema errors
Hello @Bill,
Welcome to the Community!
I believe the problem can be resolved by simply tapping the Regenerate button.
Anytime you add or remove columns from the data source - sheet or database - you MUST tap the Regenerate button. You cannot manually add the “normal” columns to your tables.
“Normal” columns are the ones indicated with the “dark” pencil icon - except [_RowNumber] which is automatically added to all AppSheet tables. The “normal” columns MUST match between the data source and the AppSheet table.
The “blue” pencil labeled columns are Virtual Columns and do not count towards the columns that contribute to the schema. They are app side columns only.
Here is the link to the support documentation for your reference-
I hope it helps! Thanks!
I actually have been spamming the regenerate button since I first encounter the error. The problem here is that there are indeed 31 columns, but the other 13 columns are empty, supposedly anyway. I don’t know what’s in them but Appsheet is picking it up.
In general it sounds that there is some data such as probably some spreadsheet formulas or some hidden characters in those 13 columns. Alternatively the data maybe in white font so not visible.
But essentially the AppSheet editor is detecting some data in those 13 columns( even if 1 row has some data, the editor will give that error.).
Is there a way to make to make Appsheet not detect those columns or something to do with the table settings to hide those columns?
The following suggestion is not fully tested.
One option you could try is name those 13 columns something like [Unused_1], …., [Unused_13] in the sheet and regenerate the schema.
Then hide all those columns with show_if turned off.
Caution: However this needs to be very carefully done because there could be some unforeseen impact based on what hidden data is contained in those 13 columns and if that data is dependent on the non hidden 18 columns. Without knowing what data exists in those 13 columns it is very difficult to know how it could impact.
You could possibly try it in a test app before applying it in a production version app.
Wish I could do that, but I can’t modify anything in the sheet, not because I don’t have edit permission but was told not to do anything to it. I think the team lead found a way to manipulate the columns in the documentation or something and he ain’t telling me anything about it. Just kept telling to read the documentation
Okay, thank you. Got it.
Maybe some other community colleague has some input for you.
thanks for giving me some of your time and offered to help, much appreciated
Hello @Bill,
If you want to hide columns and not delete them in the source data, you can do so by:
- Navigate to “Views” in the left navigation
- Go to the “Column Order” section
- Select Manual from Automatic || Manual options
- Hover over the columns you do not want to show and click on the X, This will remove the column from the view but the column will remain in the source table
To add back the column simply click on “Add”.
Alternatively you can create a slice, which allows you to selectively show any information you need.
I hope this is useful!
I did try creating a slice but since the actual source table that the slice was created from has the schema error, the preview stayed showing the error screen about the schema error.
And what i want is Appsheet to just grab the 18 columns in the sheet that have valid data, and not grabbing the 13 empty columns as well. Is it possible to create such outcome within Appsheet?
He lied.
You did.
Sounds like your team lead is either trying to make your job difficult (doesn’t like you?), trying to teach you a lesson (thinks you’re lazy?), or themself doesn’t know (wants you to take the fall?). Gotta wonder if they actually created this problem intentionally, or maybe they’re using the extra columns as scratch space to do temporary calculations with the sheet’s data (prioritizing their own convenience over the stability of the app).
The problem:
- mismatch schema errors
- 31 columns in my table while the schema has only 18
- Google Sheets as my data source
- not allowed to modify the sheet
- bypass the schema errors without making use of 2 sheets (clever idea, BTW)
@Suvrutt_Gurjar’s suggestion is the right one: regenerate the schema and allow AppSheet to add the unneeded columns.
Assuming the extra columns have no header row content, they should be given generic names like Column_24, so you don’t need to rename them in the app table unless you care to. I’d recommend keeping the name from the data source (if any) or the auto-generated name. Changing names will create confusion when you regenerate again in the future.
As @Suvrutt_Gurjar suggested, hide the unneeded app columns by disabling Show?. You should also disable Edit?, as the app should never try to write content to those columns. Leaving Edit? enabled will cause problems. Every time you regenerate, you’ll want to ensure these are all disabled.
Were I in your shoes, I would lean toward creating and using a slice that omits the unneeded columns where possible, rather than using the table itself.
Are all of the extra columns after the intended columns (versus intermixed)? If so, examine the app configuration of the very last one for some hint why AppSheet wants to include it.
- Any worksheet column that has any content (literal or formula-generated) in its header (the first row) will always be included in the AppSheet table.
- If any cell in the worksheet column has a formula, AppSheet will see it and include the column in the app. Check the Spreadsheet formula option in the app column’s configuration.
- If any cell in the worksheet column has any literal value, even if it’s just a space, AppSheet will see it and include the column in the app. If the app column’s type is anything other than Text, this is a hint to what the worksheet column might (be formatted to) contain.
So I did a data check on those 13 supposedly empty columns again, and three of them have some data in them. Still doesn’t explain why when Appsheet generate the table after the source was inserted, only 18 appeared in the schema. No generic naming of the 13 columns or anything, they just don’t exist
But when i imported the source into the Appsheet integrated database, those 13 columns appeared there, so I’m wondering if I have to make use of the integrated database, does the database sync with the spreadsheet? If so then I might just use it.
I do not understand this. Now the columns don’t exist?
They exist but when I add the it into Appsheet
Appsheet only takes 18 columns. However, if I import the sheet into the Appsheet integrated database
Appsheet registered them, giving them generic names and all that
So I figured out why the error is happening. The sheet that is being used in Appsheet is a protected sheet and there is a cell in column AE that has text in it, and since the sheet is being protected, Appsheet can’t reach column AE which result in the mismatch schema errors.
Now I have to figure out how to only grab only the columns that have headers.
You cannot.
Your screenshot indicates you have not regenerated the structure, but you said you have.
I did, and have been doing so, in hope that somehow it would fix the schema but to no avail, unfortunately lol. I might have to ask to be able to clean up the sheet a bit to fix this. Thanks for lending me your time to help me with this predicament.
Do you have EDIT access on that spreadsheet? That is, to be able to open it as a Google Sheet and hand edit the sheet? If you can do this, then the simplest way to fix your problem without removing any protected formulas from your team workers is to add column headers for every column all the way up to column AE in your screenshot. This was suggested by @Suvrutt_Gurjar earlier on.
Once you have added the column names then refresh the schema in your app so that all the new column names are showing.
Then you can use the tick boxes in AppSheet Editor to stop the columns from being edited and shown.
I can see that you are NOT allowed to modify the sheet. Does that mean that your app will only use the data in that sheet as a READ ONLY datasource whereby the actual updates to rows in the sheet are performed by your team members using manual data entry? If this is the case then your only option is to use a new google sheet file and have an IMPORTRANGE() formula that grabs the data and columns you require from the locked spreadsheet.
Once the two sheets are linked, you can use your second sheet as the datasource in your app. Set it as READ only as ANY writes to this datasource will break the formula in the sheet and your app will break.
If you require the ability to WRITE new rows to the sheet then you should discuss getting write access to the original sheet with your team members…
I’ve solved it. Just needed a little discussion about cleaning the sheet up a bit. Everything works now