Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- // get base data
- var dataRange = 'IMPORTRANGE("' + dataShtId + '", "VolunteerActivity")';
- var selQuery = 'select Col1,Col2,SUM(Col3),Col4 where YEAR(Col4) = ' + reportYear + ' ';
- if (reportVolunteer != "") {
- selQuery = selQuery + "and Col1='" + reportVolunteer + "'";
- }
- if (reportTeam != "") {
- selQuery = selQuery + "and Col2='" + reportTeam + "'";
- }
- selQuery = selQuery + 'group by Col1,Col2,Col4 order by month(Col4) ';
- selQuery = selQuery + "label Col1 'Volunteer', Col2 'Team', SUM(Col3) 'Count', Col4 'Month'";
- var querytext = "";
- querytext = '=QUERY(' + dataRange + ', "' + selQuery + '")';
- Logger.log(querytext);
- =QUERY(IMPORTRANGE("190b6pmeTAEPpmRM66lRHpYa3oyo55Wb9nqEiLkoo7bY", "VolunteerActivity")
- , "select Col1,Col2,SUM(Col3),Col4 group by Col1,Col2,Col4 order by month(Col4) label Col1 'Volunteer', Col2 'Team', SUM(Col3) 'Count', Col4 'Month'")
- // get base data
- var dataRange = "IMPORTRANGE('" + dataShtId + "', 'VolunteerActivity')";
- var selQuery = "select Col1,Col2,SUM(Col3),Col4 where YEAR(Col4) = " + reportYear + " ";
- if (reportVolunteer != "") {
- selQuery = selQuery + 'and Col1="' + reportVolunteer + '"';
- }
- if (reportTeam != "") {
- selQuery = selQuery + 'and Col2="' + reportTeam + '"';
- }
- selQuery = selQuery + 'group by Col1,Col2,Col4 order by month(Col4) ';
- selQuery = selQuery + 'label Col1 "Volunteer", Col2 "Team", SUM(Col3) "Count", Col4 "Month"';
- var querytext = "";
- querytext = "=QUERY(" + dataRange + ", '" + selQuery + "')";
- Logger.log(querytext);
- =QUERY(IMPORTRANGE('190b6pmeTAEPpmRM66lRHpYa3oyo55Wb9nqEiLkoo7bY', 'VolunteerActivity')
- , 'select Col1,Col2,SUM(Col3),Col4 where YEAR(Col4) = 2019 group by Col1,Col2,Col4 order by month(Col4) label Col1 "Volunteer", Col2 "Team", SUM(Col3) "Count", Col4 "Month"')
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement