Translation error between R1C1 and A1 notation for a spreadsheet formula

[Bharal]

Hello,

I have a problem with my Appsheet application. In a data sheet, I have a column that contains the following formula:

=QUERY(INDIRECT(CONCATENATE("TARIF-";J45;"!C2:C"));"SELECT C WHERE C <= DATE '"&TEXT(A45;"yyyy-MM-dd")&"' ORDER BY C DESC LIMIT 1";)

When this data sheet is imported as a table in my application, the formula is converted to R1C1 format, so far no problem.

QUERY(INDIRECT(CONCATENATE("TARIF-",RC[-1],"!C2:C")),"SELECT C WHERE C <= DATE '"&TEXT(RC[-10],"yyyy-MM-dd")&"' ORDER BY C DESC LIMIT 1",)

Note that the

;

of the formula are converted to 

,

When I add a new entry to this table, the saved formula is calculated for the column that contains it and returns an error. On closer inspection, we find the following formula in the cell (looking directly in the sheet) :

=QUERY(INDIRECT(CONCATENATE("TARIF-";J45;"!C2:C"));"SELECT C WHERE C <= DATE '"&TEXT(A45,"yyyy-MM-dd")&"' ORDER BY C DESC LIMIT 1";)

You can see that all the , in the formula in R1C1 notation have been converted to ; except for the following :

......'"&TEXT(A45,"yyyy-MM-dd")&"'......

One , remains and is not converted back to ;

The formula can therefore no longer be calculated, and the cell returns an error.

By directly modifying the formula in the column field on AppSheet, if we replace the , by a ; and add a new entry via the application, we also find an error, since the ; remains, but the cell notation just before is no longer interpreted as such and remains as it is in the cell.

=QUERY(INDIRECT(CONCATENATE("TARIF-";J45;"!C2:C"));"SELECT C WHERE C <= DATE '"&TEXT(RC[-10];"yyyy-MM-dd")&"' ORDER BY C DESC LIMIT 1";)

If you look at the formula, you will notice that the problem with the , happens while it is in a large string between " which is the SELECT... query of the QUERY() formula.

Is there a escape character to use so that the , converts well to ; when adding new data?

Is this an error on my part?

Thank you for your answer

[dbaum]

I don't follow all the details you explain. Nonetheless, here are some potentially relevant troubleshooting techniques in case you haven't already tried them:

• Be sure locale settings are as intended in the app and the data source.
• When I've encountered issues with A1 formulas in a spreadsheet data source that are converted to R1C1 expressions in an AppSheet column's Spreadsheet formula property, I've had some success by revising the formula in the data source to use R1C1 notation to begin with.

[Bharal · Accepted solution]

Error corrected by replacing this in the formula:

"SELECT C WHERE C <= DATE '"&TEXT(A2;"yyyy-MM-dd")&"' ORDER BY C DESC LIMIT 1"

With the following:

CONCATENATE("SELECT C WHERE C <= DATE ";CHAR(39);TEXT(A2;"yyyy-MM-dd");CHAR(39);" ORDER BY C DESC LIMIT 1")

(I made sure that the cell reference was no longer inside quotation marks)