Help Converting Gsheet formula to Appsheet Formula

[butlershvac]

Hey community

I'm getting a "Computed Value" error under Auto Compute. This is happening bc Appsheet is not converting the Gsheet formula. This is preventing my app from functioning properly.

I'm no pro, but the formula works fine on the Gsheet.... any help with converting it manually would be much appreciated. Thanks

=ARRAYFORMULA(ifs(REGEXMATCH(C4:C,$G$1),(($G$2+(G4:G-1)*$K$2))*H4:H*I4:I+(J4:J*$L$2*H4:H)+(K4:K*$M$2*H4:H), REGEXMATCH(C4:C,$H$1),(($H$2+(G4:G-1)*$K$2))*H4:H*I4:I+(J4:J*$L$2*H4:H)+(K4:K*$M$2*H4:H), REGEXMATCH(C4:C,$I$1),(($I$2+(G4:G-1)*$K$2))*H4:H*I4:I+(J4:J*$L$2*H4:H)+(K4:K*$M$2*H4:H), REGEXMATCH(C4:C,$J$1),(($J$2+(G4:G-1)*$K$2))*H4:H*I4:I+(J4:J*$L$2*H4:H)+(K4:K*$M$2*H4:H),REGEXMATCH(C4:C,L1),((0+(G4:G-1)*$K$2))*H4:H*I4:I+(J4:J*$L$2*H4:H)+(K4:K*$M$2*H4:H),REGEXMATCH(C4:C,$M$1),((0+(G4:G-1)*$K$2))*H4:H*I4:I+(J4:J*$L$2*H4:H)+(K4:K*$M$2*H4:H)))

[MultiTech · Accepted solution]

I'll be honest pretty difficult to help reformat a formula like this without any of the column names or anything like that. Next time if you could provide the table and column names, that would be helpful. Also it helps if you can describe the intent of what you're trying to shoot for, as usually it's easier to drive a formula when you know the essence of what someone's shooting for.I

Regardless of that, I figured I'd give it to Appster and see what it could do with it; here's what it had to say.

______________________________________________________

Converting your **ARRAYFORMULA with IFS and REGEXMATCH** from Google Sheets to an **AppSheet expression** requires breaking it into parts since AppSheet doesn't support `ARRAYFORMULA` directly.

### **Understanding the Formula's Structure**

Your formula applies different calculations based on pattern matching in column **C**. This needs to be rewritten in AppSheet using:

- `IFS()` for conditional logic.

- `FIND()` or `CONTAINS()` instead of `REGEXMATCH()`.

- Column-level calculations, since AppSheet does not auto-apply expressions like an `ARRAYFORMULA`.

### **AppSheet Equivalent Formula**

In **AppSheet**, formulas are applied per row, so assuming you are using a **virtual column** (or an App formula in a real column), the equivalent expression would look like:

```appsheet

IFS(

  CONTAINS([Column_C], "Pattern1"), 

    ([G_2] + ([Column_G] - 1) * [K_2]) * [Column_H] * [Column_I] + ([Column_J] * [L_2] * [Column_H]) + ([Column_K] * [M_2] * [Column_H]),

  CONTAINS([Column_C], "Pattern2"), 

    ([H_2] + ([Column_G] - 1) * [K_2]) * [Column_H] * [Column_I] + ([Column_J] * [L_2] * [Column_H]) + ([Column_K] * [M_2] * [Column_H]),

  CONTAINS([Column_C], "Pattern3"), 

    ([I_2] + ([Column_G] - 1) * [K_2]) * [Column_H] * [Column_I] + ([Column_J] * [L_2] * [Column_H]) + ([Column_K] * [M_2] * [Column_H]),

  CONTAINS([Column_C], "Pattern4"), 

    ([J_2] + ([Column_G] - 1) * [K_2]) * [Column_H] * [Column_I] + ([Column_J] * [L_2] * [Column_H]) + ([Column_K] * [M_2] * [Column_H]),

  CONTAINS([Column_C], "L1"),

    (0 + ([Column_G] - 1) * [K_2]) * [Column_H] * [Column_I] + ([Column_J] * [L_2] * [Column_H]) + ([Column_K] * [M_2] * [Column_H]),

  CONTAINS([Column_C], "M1"),

    (0 + ([Column_G] - 1) * [K_2]) * [Column_H] * [Column_I] + ([Column_J] * [L_2] * [Column_H]) + ([Column_K] * [M_2] * [Column_H])

)

```

### **Breaking It Down**

1. **Replacing REGEXMATCH with CONTAINS()**  

   - `REGEXMATCH(C4:C, $G$1)` in Google Sheets is checking if column **C** contains the text in `$G$1`.

   - The **AppSheet equivalent** is `CONTAINS([Column_C], "Pattern")`.

2. **Applying IFS() for conditional logic**  

   - `IFS()` works similarly in AppSheet and Google Sheets: it returns the first matching condition.

3. **Handling List Computation Instead of ArrayFormula**  

   - Since **AppSheet does not have ARRAYFORMULA**, the formula **must be placed in a Virtual Column** or **as an App Formula in a real column**.

4. **Using Direct Column References**  

   - `[Column_G]` replaces `G4:G` (assuming this is a row-level calculation).

### **Next Steps**

1. Replace **Pattern1, Pattern2, etc.** with the actual values stored in `G1, H1, I1, J1, L1, M1`.

2. Verify that `Column_G, Column_H, Column_I, Column_J, Column_K, K_2, L_2, M_2` are named correctly.

3. Use a **Virtual Column** if you don’t need to store the computed value, or set it as an **App Formula** in a real column if you want it saved.

This should now work correctly in AppSheet while maintaining the same logic as your **Google Sheets** formula. Let me know if you need adjustments!

______________________________________________________

You can continue the conversation here if you want: 

https://chatgpt.com/share/67b48063-20e4-8003-ad79-e11e24e2d197

Hope it helps!