Does AppSheet uniformly respect spreadsheet column types?

[Elijah_Magrane]

@praveen
I have a key column and it contains values that are considered both numbers and text, for example 1234 and 1234-1. This column also is they key of a table and acts as the criteria in several SUMIF(S) spreadsheet formulas.

Now I had to adjust spreadsheet formulas today and as a result I had to reformat some of my column types. Now AppSheet wont return a value for nearly all of the columns that contain a spreadsheet formula in which 1234 is included as the criteria #VALUE! error). Rows in which have 1234 “-1” are fine and return a value in AppSheet.

I have ensured the column types are the same between both AppSheet and Excel and it makes no differences ( i have regenerated and played around with formatting). The only thing that resolves it turning a 1234 into 1234-1. All of my formulas in Excel return.

Is this expected behavior? Is there a way to reconcile this

[pravse]

I don’t completely understand the situation.

However, when you use Excel (is it from Dropbox?), AppSheet has to download and compute all the formulas in it (whereas with Google Sheets, it is Google that computes the formulas).

We have a code library that understands Excel and runs formulas, but it is not 100% compatbile with Excel. So some formulas can cause trouble. I don’t know if this is the cause of the issue you are seeing, because I don’t quite understand the problem symptoms accurately. I wonder if expressing 1234 as an Excel string (i.e. with a leading single quote '1234) would resolve the issue

[Elijah_Magrane]

I believe it is the way AppSheet defines column types in relation to spreadsheet formulas. For example, as I previously mentioned I had spreadsheet formula that used a criteria that could be a string (“1234-1”) or a number (1234). When AppSheet evaluated the column that contained spreadsheet formula in this case SUMIF('Production Details'!R2C[-8]:R9418C[-7],'Incoming RM'!RC[-10],'Production Details'!R2C[-7]:R9418C[-7]). (The 'Incoming RM'!RC[-10] being the criteria that could a string or number). When AppSheet first generates the table it selected this column as text, I then had to switch the column to decimal in order to return a value.

When I made change to spreadsheet formula and regenerated the table I could not get get a value to return regardless of the AppSheet column type. However when I removed the columns for the spreadsheet regenerated and then added them back in. Everything worked (I still need to switch the columns from text to decimal in AppSheet, like I previously mentioned).

I think this example illustrates some of the things we were discussing in this thread. It is hard to troubleshoot when I am unsure of the expected behavior from on scenario to another