Display Virtual column in Google Sheet or convert the formula

[Žymantas]

Hello all,

I'm creating a stock management app using this as a template. https://blog.google/products/google-cloud/how-to-build-an-inventory-management-apps-no-coding-necess...

The issue I'm facing is that I can't display/copy Virtual column (Current Stock) values to Google Sheet.

Formula I'm using works fine on the app: 

[Quantity] -
SUM(
SELECT(
Equipment Provided[Quantity],
[Name] = [_THISROW].[Name]
)) 

From the info I've gathered it seems that there is no easy way to replicate the values to Google Sheet doc. I've tried to create a new column and make it "=" to the virtual one, but the values don't copy: https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Make-Virtual-Column-Show-in-Google-Sheet/m-p/28...

Is there any walk-around on maybe a different solution on how this could be done? Also, I was thinking about converting the Appsheet formula to Google Sheet formula, but I'm not sure if that would be functioning properly. Thanks in advance for any help regarding this!

[graham_howe · Accepted solution]

If you need the sheet to be updated in real time, rather than as each row is updated, then it would probably be better to put the formula in the sheet rather than AppSheet. Without seeing exactly what your data structure is I can't give an immediate example of the formula, but Google Sheets does support QUERY function - Google Docs Editors Help which is a very powerful tool and can certainly do the equivalent of selects in AppSheet.

[graham_howe · Accepted solution]

If you need the sheet to be updated in real time, rather than as each row is updated, then it would probably be better to put the formula in the sheet rather than AppSheet. Without seeing exactly what your data structure is I can't give an immediate example of the formula, but Google Sheets does support QUERY function - Google Docs Editors Help which is a very powerful tool and can certainly do the equivalent of selects in AppSheet.

[Žymantas]

Hi Graham, thank you for the promt reply. Right now I wouldn’t be able to share my sheet, but the idea of what I’m trying to do is in this template:

https://blog.google/products/google-cloud/how-to-build-an-inventory-management-apps-no-coding-necess...
If you could check this part: Step 3: Calculate the real-time inventory level that would be great! I need equivalent formula for Google sheet. I will check your suggested Query function, but because of lack of experience in this field, it can take ages for me 😄
Thanks again 🙂 

[Žymantas · Accepted solution]

I found the solution with Google Sheet formulas, since there was no way to replicate the virtual column. Graham, thanks again for your assistance. 

[mahh1998]

Hello Zymantas

What formula did you end up using? I have the same problem atm