Export to google sheets based on user input filters

[victorfarinella]

Hi everyone, 

I'm trying to adapt this solution (example 1) made by @alafontant that uses appsScript and automation/actions to create a google sheet reports based on defined regions (west, south...). 
The appsScript select the columns from the customers database based on the region column(which is the key column of a dimension table called reps):

LIST(

SELECT(customers[full_name], [reference_id] = [region].[rep_id]),

SELECT(customers[company_name], [reference_id] = [region].[rep_id]),  .....

But I'm struggling to do it using dynamic filters that are selected by the user before creating the report.

Has anyone tried this before?

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Export-to-Google-Sheets-from-AppSheet/m-p/634570

I know it is possible using .csv or .pdf reports, but in my case it's not an option...

Thank you!

[MultiTech]

---

@victorfarinella wrote:

LIST(

SELECT(customers[full_name], [reference_id] = [region].[rep_id]),

SELECT(customers[company_name], [reference_id] = [region].[rep_id]),  .....

---

It looks like you've already got references connecting things together, now it's just a matter of extending these connections to make getting the data you need easier.

Check out the following topics:

• chaining dereferences
• list dereferences

[victorfarinella]

Thanks for your answer!

Select sounded a bad idea anyway... hahaha
I will take a look in this topics you mentioned, but let me ask another thing...
For csv export, I saw a solution using filter database (to hold the filters) and a slice of the main database to apply these filter, like this:
https://www.youtube.com/watch?v=EVqKU0k0gVo

It's possible to do something like this for googlesheets "export"?

[Markus_Malessa]

I have done something similar, but rather than using apps script I created a separate table for ReportRequests where I predefined what values need to be set including email addresses for recipicients. Then I created a bot for Adds Only for this table with an Excel attachment and inside the template (template is created in GSheets) I define the column labels, then do a <<Start:>> expression and include a Select statement against my table from where I would like to pull the data from filtering by the data that is designated in the ReportRequests form. It does take a little bit of time to set up, however appears to work relatively well. You can then decide how long you would like to keep rows in the ReportRequests table and even set up a bot that automatically deletes rows of a certain age.