Get the last X non-empty rows of a range in Google Sheets

Question

I have a range like in the following image:

Where the top headers represent the months of the year (1-12) and the left column represents the days in a month (1 to 28/29/30/31).

I would like to perform a SUM function over the last 30 days of my range such that it starts counting from the last non-empty value and goes up until it reaches the first value of that column, and then if there are still days to count, goes to the previous month and starts counting from its last value.

For example, in the image if I wanted the sum of the values of the last 6 cells of the range, it would sum up 16, 12, 8, 4, 4 and 15.

I've tried adapting the solution to a similar problem as outlined here, but I haven't been able to make it work so far.

Answer 1

Using the solution to this question about iterating over a range using Google Script, I was able to devise a working script.

The script will iterate a specific number of cells backward from the last cell of a range (it iterates all rows of a column before moving to the previous column), skips empty cells, and returns the sum of the values encountered into a new cell.

  function sumOfLast30() {
  var range = SpreadsheetApp.getActiveSheet().getRange("A1:B2");
  var numRows = range.getNumRows(); // 2
  var numCols = range.getNumColumns(); // 2
  var counter = 0;
  var counterMax = 2; // this determines how many cells get counted
  var sumValues = 0;

  for (var i = numCols; i > 0; i--) 
  {
    if (counter == counterMax) {
      break;
    }
    for (var j = numRows; j > 0; j--) {
      var selectedValue = range.getCell(j,i).getValue();
      if (selectedValue != "") 
      {
        sumValues = sumValues + selectedValue;        
        counter++;
        if (counter == counterMax) 
        {
          break;
        }
      }

    }
  }
  var target = SpreadsheetApp.getActiveSheet().getRange("C3");
  target.setValue(sumValues);
}