Google Sheets Script Count formula [duplicate]

问题:

This question already has an answer here:

• Determining the last row in a single column 16 answers

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?

回答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

回答2:

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);