google apps script - Trying to work in sheets with large dataset -

i have automated dashboard in sheet "my dashboard" in google drive.

the data dashboard comes google analytics (ga) via api. data have been able pull in using google sheets ga add on.

my source data 1 of tables in dashboard pretty large - large fit within sheet itself.

so, limited scripting skills , of forum , online tutorials created script in google-apps-script queries ga api , returns of data need , puts csv file in same directory main dashboard.

so now, in "dashboard" folder in drive have 2 files: "my dashboard" - sheet , "my data" csv file. could, if wanted, instead output results of api call sheet assumed csv file more efficient.

i opened "my data" csv file in gsheet , called "combined". here sample of data like:

ga:year ga:month    ga:medium   ga:source   ga:campaign ga:goal1completions 2013    5   (none)  (direct)    (not set)   116 2013    5   (not set)   adperio silvercontact?dp    0 2013    5   (not set)   conde*it    _medium=email   0 2013    5   (not set)   hearst  (not set)   0 2013    5   (not set)   stackersocial   stackersocial   0 2013    5   12111   9591    201fhn000xrggszt3aeaa11uisat000.    0 2013    5   12111   9591    201fhn00brt.k.ay0hvf3q1uiqgl000.    0 2013    5   12111   9591    201fhn00ck619obe3osbzp1uiqgx000.    0 2013    5   12111   9591    201fhn00dfxjxnuu1jx25m1uizkc000.    0 

there ~ 65k rows of data.

now, in dashboard need table groups , aggregates data in "combined" sheet (or use csv somehow?). preferred go formula typically e.g.

=sum(filter(ga:goal1completions, ga:year="2015"... ))

pulling in , querying data proving difficult , i'm hoping advice.

i cannot import data dashboard since alert exceeding sheet maximum size of 200k cells. must rely on formula import data , run calculation each time. here example of i'm using now:

=sum(filter(importrange("https://docs.google.com/spreadsheets/d/xxxxxxxx/edit?usp=sharing","combined!f2:f"), importrange("https://docs.google.com/spreadsheets/d/xxxxxxxx/edit?usp=sharing","combined!a2:a")=year(g$17), importrange("https://docs.google.com/spreadsheets/d/xxxxxxxx/edit?usp=sharing","combined!b2:b")=month(g$17), importrange("https://docs.google.com/spreadsheets/d/xxxxxxxx/edit?usp=sharing","combined!c2:c")="(direct)", importrange("https://docs.google.com/spreadsheets/d/xxxxxxxx/edit?usp=sharing","combined!d2:d")="(none)")) 

so each parameter in function in cell must import column of data , subset it.

i'm sure there must better way. right work it's exceptionally slow.

avenues thinking about:

1. someone on mentioned using cache service here. how work or how integrate importrange() functions above?
2. is there benefit having csv file or should output results gsheet outset?
3. in script run data, before outputting csv file (or gsheet if that's better?) have data array before conversion file. there fancy can here, such querying array directly within sheet? bearing in mind scripting skills pretty basic.
4. presumably create function call ga api individually each cell function in, returning results in each individual cell (so in example above, function call ga api year=year(g17)&month=month(g17)&medium=[some_other_cell_reference). option result in more api calls result in smaller data work on. not sure if that's idea or going wrong direction entirely.

i hope i've communicated problem sufficiently. need find more efficient way of querying external data in dashboard.

instead of importing 1 column @ time using multiple importrange , filter use 1 single importrange pull columns need in single step , use query operations need.

in same way include importrange inside filter, can include inside query

i.e. simplicity add 1 filtering criteria

`=query(importrange("key","range"),"select sum(col6) col1 ='"&g$17&'")`