Advertisement
Guest User

Untitled

a guest
Jun 27th, 2019
86
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.80 KB | None | 0 0
  1. // get base data
  2. var dataRange = 'IMPORTRANGE("' + dataShtId + '", "VolunteerActivity")';
  3. var selQuery = 'select Col1,Col2,SUM(Col3),Col4 where YEAR(Col4) = ' + reportYear + ' ';
  4. if (reportVolunteer != "") {
  5. selQuery = selQuery + "and Col1='" + reportVolunteer + "'";
  6. }
  7. if (reportTeam != "") {
  8. selQuery = selQuery + "and Col2='" + reportTeam + "'";
  9. }
  10. selQuery = selQuery + 'group by Col1,Col2,Col4 order by month(Col4) ';
  11. selQuery = selQuery + "label Col1 'Volunteer', Col2 'Team', SUM(Col3) 'Count', Col4 'Month'";
  12. var querytext = "";
  13. querytext = '=QUERY(' + dataRange + ', "' + selQuery + '")';
  14. Logger.log(querytext);
  15.  
  16. =QUERY(IMPORTRANGE("190b6pmeTAEPpmRM66lRHpYa3oyo55Wb9nqEiLkoo7bY", "VolunteerActivity")
  17. , "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'")
  18.  
  19. // get base data
  20. var dataRange = "IMPORTRANGE('" + dataShtId + "', 'VolunteerActivity')";
  21. var selQuery = "select Col1,Col2,SUM(Col3),Col4 where YEAR(Col4) = " + reportYear + " ";
  22. if (reportVolunteer != "") {
  23. selQuery = selQuery + 'and Col1="' + reportVolunteer + '"';
  24. }
  25. if (reportTeam != "") {
  26. selQuery = selQuery + 'and Col2="' + reportTeam + '"';
  27. }
  28. selQuery = selQuery + 'group by Col1,Col2,Col4 order by month(Col4) ';
  29. selQuery = selQuery + 'label Col1 "Volunteer", Col2 "Team", SUM(Col3) "Count", Col4 "Month"';
  30. var querytext = "";
  31. querytext = "=QUERY(" + dataRange + ", '" + selQuery + "')";
  32. Logger.log(querytext);
  33.  
  34. =QUERY(IMPORTRANGE('190b6pmeTAEPpmRM66lRHpYa3oyo55Wb9nqEiLkoo7bY', 'VolunteerActivity')
  35. , '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