Google Spreadsheet Script to have Group by Query with WeekOfYear

Question:

( by Kent Wagner )

Ok, So, I have a form that people will be filling out weekly by Wednesday of each week for the previous 7 days (Starting the previous Thursday).

I am wanting to take the information that is gathered and have a formula that calculates totals of column C based on the date ranges of Column A

I would like to then display each weeks results on a new row...  I will give the example of how I see it working below

What I am trying to do is create some metrics for our organization and view it in a helpful format.

Sample of the actual data from the form

A: (timestamp)

C: (number would like totaled based on that 7 day range - Thurs to Wed.)

      A          B       C

7/13/13           15     

7/14/13            9  

7/15/13           11

7/19/13           10  

7/20/13           10

The Sheet with the formula would then produce:

Column A: (week number based on 52 week range - or whatever is easiest)

Column B: (total for week)

Column C: (I'm dreaming now, but monthly average would be really cool)

 A    B    C 

32   35

33   20   11

Solution:

Have a look at the following screenshot of Sheet1:

Have a look at the following screenshot of Sheet2:

I have the following formula in Cell A1 of Sheet2:

=query(arrayformula(if({1,0};WeekOfYear('Sheet1'!A:A);'Sheet1'!C:C));"select Col1,sum(Col2) where Col1 is not null group by Col1 label Col1 'Week No.',sum(Col2) 'Total' ")

the above formula is a custom function WeekofYear that I have written and inserted in Script.


Have a look at the following code:

///////////////////////////////////////


function weekOfYear() {
  if (arguments.length < 1)
    return "#NA Please input date(s)";
  else if (arguments.length > 1)
    return "#NA Input Can't be more than one arguement";
  
  var values = arguments[0];
  var returnvalues = [];
  for(var i=0;i<values.length;i++)
  {
    
    if ((values[i][0] instanceof Date) == false){
      returnvalues.push([""]);
    }
    else
    {
      var x = new Date(values[i][0].getFullYear(),0,1);
      returnvalues.push([ Math.ceil((((values[i][0] - x) / 86400000) + x.getDay()-1)/7) ]);
    }
  }
  if(returnvalues.length)
    return returnvalues;
  else
  {
    if ((values instanceof Date) == false){
      return "";
    }
    else
    {
      var x = new Date(values.getFullYear(),0,1);
      return Math.ceil((((values - x) / 86400000) + x.getDay()-1)/7);
    }
  }    

}

///////////////////////////////////////

put the above code in your script editor, and then you can use it directly in your Google Spreadsheet or you can use its functionality in the Google Apps Script.


And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive