possible to combine ref_row with math expressions?

[Dohman89]

I was wondering if it is possible to use math expressions on ref_row to duplicate the effect query has in google sheets.

example of google sheets formula   =query({B1:E},"Select Col1,sum(Col2),sum(Col3),sum(Col4) where Col1 matches '"&textjoin("|",1,A1:A)&"' group by Col1") or even a less complex query excluding the match from it.

I have seen comments like "Why use sheet formulas, app sheet can do it". But the workbook I have uses the above formula or similar for the majority of the data manipulation and I cant find a practical way in appsheet to duplicate the desired effect. creating virtual columns for each individual column i want to use math expressions in isnt an option(would be 100s if not over 1000 by the end)

Heres a link to the workbook for a better grasp on the project and desired outputs.

https://docs.google.com/spreadsheets/d/1-DL2rajYF8ibajgpoDFp0ZnEvpAGcXyNFphlGq4Ug6c/edit?usp=drivesd...

[Marc_Dillon]

example of google sheets formula   =query({B1:E},"Select Col1,sum(Col2),sum(Col3),sum(Col4) where Col1 matches '"&textjoin("|",1,A1:A)&"' group by Col1")

---

As you're aggregating on the "Col1" dimension, you could create a separate table of unique Col1 values, and set up each of your sums as a column on this table.

Don't know what this has to do with ref_rows.

[Dohman89]

Sounds like you are recommending writing expressions in each individual Column, which isnt very practical when dealing with 100s of columns.

Which takes me to what it has to do with ref_rows. Ref_rows seem to be the simplest and most effective way to grab all columns that meet a criteria. Arguably the most powerful function when dealing with spreadsheets is query, and ref_rows is the closest thing i could find that does just the basic function of query.

But ill take anything that can duplicate or come relatively close to the simplicity/effectiveness of what a query function can accomplish.

So i guess its more of a yes or no question that is this possible an app sheet to do?

[Marc_Dillon]

REF_ROWS() doesn't return all columns, it only returns a List of key values.

The same way you have to specify the aggregation method for each of the 100 columns in your query(), [like sum(col1) , sum(col2), etc...], you would have to specify each of those 100 columns within Appsheet, but as separate columns with specific expressions.

So with my suggestion above, you would setup columns with SUM([related...][col1] ) , SUM([related...][col2] ) , etc...

You could also write the query() in a new sheet, and load that into your app as a new table.

[Dohman89]

With that expression SUM([related] [Col1],SUM([related] [Col2]), ect be a single expression typed once that is applies to all the listed Columns or one that has to be typed in each individual column?

I have helper sheets to help generate functions with strings, so something long isnt an issue. its just going into every individual column to input an expression is, especially if i wanted to tweak it slightly or something isnt working right means alot of expressions to double check vs just 1.

I do have query's for filtering generically, im just seeing how far i can take this by allowing users to essentially filter the data anyway the want to wheres its between two dates, or games played, against said opponent or any other criteria they wish thats available.

Basically an "in session" way to manipulate data that doesnt actually effect the source data if that makes since. If its not really possible, i would rather not spend countless hours trying to do something that cant be done.

I thought maybe something with webhooks, but again thats something i would have to teach myself how to do, and would rather not if it still does not accomplish my goal.

[Marc_Dillon]

a single expression typed once that is applies to all the listed Columns or one that has to be typed in each individual column?

---

One expression per column. There is no "list of columns".

You can set up a data table for the user to enter their filter parameters. Use that to apply to the in-app expressions, or in your query() formula. The latter would be difficult to do for a multi-user app.

I can't imagine any way a webhook would help you here.

But in the literal since, it is returning all the columns that meet the criteria.

---

No, REF_ROWS() literally returns a List of key values. Appsheet displays what is called an "inline view" for any virtual columns of type List and base-type of Ref, which you can configure to display any of the columns of the referenced table/records.

[Dohman89]

I guess im probably not using the correct terminology. But in the literal since, it is returning all the columns that meet the criteria.