0

I have looked at solutions for this but all the ones I have found seem to be way more advanced than I need. All I am looking for is a way to know how many cells in column A are populated. I don't want to use a formula within the sheet, I want to be able to calculate the answer 'on-the-fly' in my script.

Is that possible?

John Cassell
  • 107
  • 2
  • 3
  • 9

2 Answers2

1

Well, you can try something like this this

var sheet = SpreadsheetApp.openById(SPREADSHEET_ID)
    .getSheetByName(SHEET_NAME);
var range = sheet.getRange(startingCellRow, startingCellColumn, noOfRows, noOfColumns);
var datas = range.getValues();
var count = 0;
for (data in datas) {
    for (cell in data) {
        if (!(typeof cell === "undefined")) {
            count++;
        }
    }
}
Logger.log(count)

Incase someone needs formula :

=COUNTIF(<start_cell>:<end_cell>; "<>")

For example : =COUNTIF(A1:A10; "<>")

For full reference and Credits :

google docs count cells that contain any text

count empty values in array

Umair Mohammad
  • 4,489
  • 2
  • 20
  • 34
1

If you want something short you can take advantage of Array.filter():

var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getRange('A:A').getValues().flatten();
// Filters data to return an array of empty values and counts them
var count = data.filter(function(e) { return e[0] == ""; }).length;

Array.prototype.flatten = function() {
  return this.join('@').split('@');
};

Edit 2 : Sorry, you actually want to know how many are populated, so :

var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getRange('A:A').getValues().flatten();
// Filters data to return an array of 'truthy' values and counts them
var count = data.filter(Boolean).length;

Array.prototype.flatten = function() {
  return this.join('@').split('@');
};

Edit 3 : Snippet

Array.prototype.flatten = function() {
  return this.join('@').split('@');
};

// Simulates column array returned from getValues()
var colSampleArray = [[1],[],[],[""],[],[2],[],[],[""],[3],[],[4],[],[5],[]];
// Converts 2D Array into Plain Array
var data = colSampleArray.flatten();
// Filters data to return an array of 'truthy' values and counts them
var count = data.filter(Boolean).length;
window.alert(count);
k4k4sh1
  • 784
  • 6
  • 12
  • Hi, thanks for the reply. I have tried this (Your 2nd comment) and the 'data' variable shows (in a MsgBox) as all the values in column A grouped together. The 'Count' returns 1001 - which i think is just the number of current rows. I only have 5 cells populated in column A so I would like to get '5' returned – John Cassell Feb 01 '18 at 11:55
  • Hi, that’s strange.. I’ll try again on my PC and I’ll let you know! – k4k4sh1 Feb 01 '18 at 12:06
  • Updated again : actually we are working on 2D arrays. So I added the prototype function flatten which converts it to a plain array. – k4k4sh1 Feb 01 '18 at 12:26
  • Hi sorry for late reply, I have tried your updated code and I get the error 'TypeError: Cannot find function flatten in object ID' – John Cassell Feb 02 '18 at 15:48
  • Hi, I added a snippet to let you test it yourself. Is there a column header named "ID" in your sheet? Are you really testing the count against a 2D Array? The error says that it can't find the function in the given Object, probably because the Object constructor is not Array. – k4k4sh1 Feb 03 '18 at 07:00