List of upcoming events - sorting and filtering from Google Forms submissions

[Michael Cleland]

Hi 

I have form submissions on a Google Sheet where people post events (event name, date of event etc)

I'd like to have a Google Script to pull data from the form submissions sheet and copy rows to a new Google Sheet, but ...

1. Only pull in rows where the eventDate = today's date or a future date
2. This should only pull in 6 rows of data (ie., maximum of 6 events from today or future dates)
3. Sort the events by the date of the event

Why am I doing this? To use Google Data Studio to create a "Whats on" events screen for our the foyer of our organisation.

I've created a Google Data Studio report that will pull in the data from a spreadsheet, but I've tried to use IMPORTRANGE and FILTER formulas in Google Sheets, but it often fails.

Thanks for your advice :-)

[Clark Lind]

You should be able to do this using a =Query.  I haven't played with the Scalar functions of the query language, but it seems you should be able to write a query something like this: (untested)

=query(sheetname!range, "select * where datediff(now(), todate(your_date_column_name)) >= 0, order by your_date_column_name limit 6" , 1 )   

If the difference between dates is zero or greater, it should return today (zero) and out into the future. If I'm doing it backwards, try changing the order and put now() second.

Haven't tried this, but here is the reference. The 1 on the end assumes only 1 header row.