Google Docs - spreadsheet loop

Question

This one might be a bit basic and easy to answer, but I've been pulling out my hair for a while now!

I've built the following code - which is semi-pseudo as I can't find the right way to make things work!

var s = "Test";

function onEdit(event)
{
  var ss = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  if (ss.getName() == s)
  {
    results = {"Currently On": 0, "Next Up": 0, "On Hold": 0, "Waiting on someone else":     0, "zDone": 0};
    last = ss.getMaxRows();
    start = ss.getRange("F3:"+last).getValues();
    var output = "J11";
    for (x=0;x<start.length;x++)
    {
     results[start[x]]++;
    }

    for (y=0;y<results.length;y++)
    {
      row = ss.getRow(output);
      row.value = results[y];
      output++;
    }
  }
}

I've got an example of the data in this image

The basic idea is to run through all the possible categories of each task and keep a numeric list on the side of how many of each there are. I'd also like to make it dynamic (so I don't have to hard code in the list of categories) but I'm more interested in just making it work for the moment.

The Google Apps debugger is very frustrating!

Thanks for your help all!

Answer 1

Firstly, this particular use case would be easily achievable with a spreadsheet formula, eg:

=QUERY(A2:F;"select F, count(A) where F != '' group by F label count(A) 'Count'";1)

but there may be a reason why you want to do this with GAS.

So secondly, this is where I think there may be some syntax issues:

last = ss.getMaxRows();

I would just use var last = ss.getLastRow() here.

start = ss.getRange("F3:"+last).getValues();

The range reference would evaluate to something like "F3:100", which is a valid reference in GSheets (don't know about whether GAS can handle it), but nevertheless you really want something like "F3:F100", so I would use var start = ss.getRange("F3:F"+last).getValues();.

results[start[x]]++;

When you create an array from a getValues() call it is a 2D array, so you would need to use results[start[x][0]]++;.

With the next loop and the output variable, I must admit I'm a bit lost with what you're doing there. How did you want your result table laid out?

Answer 2

You have

output = "J11";

And then you do

ss.getRow(output);
output++;

This is invalid.First of all, ss is a Sheet under which there is not getRow method. So, what you should really be doing is something like this

var row = 11 ;
var col = 10 ;  //Col J
for (y=0;y<results.length;y++)
{
  ss.getRange(row,col,1,1).setValue(results[y]);
  row++:
}

Answer 3

Like AdamL, I suggest that this is better handled within the native capability of the spreadsheet. Seems to me that you want a pivot table, which would update dynamically. Alternatively a formula like =countif(F:F,"Currently On" ) would meet your immediate request. =Unique(F:F) will give you the list of categories in an array