When you create an AppSheet application from Google Sheets, we do the following.
We ask Google Sheets to export your Google workbook as an Excel.xlsx file.
We use an Excel library to read the contents of the exported .xlsx file. This allows us to extract the cell data values, cell formats, cell formulas, and other values from each worksheet in the exported .xlsx file.
In the case of formulas, we convert the formulas from A1 format to R1C1 format. We do this because it is easier for us to interpret formulas in R1C1 format. We can more easily determine if a formula is accessing values in other rows. To learn move about R1C1 formulas see A1 versus R1C1 formulas.
We use all of the information we extract from the worksheet to infer the attributes of each column in the worksheet. We use this information to determine the name, type, precision, formula, and other attributes of each field in the table we create from the worksheet.
We do this when you first add a table that is based on a Google Sheet.
We repeat this each time you click the "Regenerate" button.
Fortunately, the vast majority of Google Sheets formulas can be translated into equivalent Microsoft Excel formulas. Unfortunately, some Google Sheets formulas have no Microsoft Excel equivalents. These include: