First, remember that Google Apps Script is a variant of Javascript, not Java. If you're starting out with Google Apps Script, Javascript tutorials should be your first stop. (Codecademy for example.) That will quickly get you familiar with variables, functions, objects, methods, declarations, scope - all the problems you've run into here.
The Google Apps Script editor will check the syntax of your code whenever you save it. If you have syntax errors, the save will fail. On this piece of code, though, there are no syntax errors. However, that's not the same as being semantically correct - the code does not mean what you think it does.
Let's start by reformatting your code to expose logical structure. Take a look at the indenting - it provides clues about the scope of your functions. I've annotated it to highlight key aspects.
/**
* CountColb is a function at global scope. You'll see its name in the debug
* controls of the editor (see Fig 1 below).
*/
function CountColb() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0]; //** Always uses "Sheet 1"
/**
* CountColA is in the local scope of CountColb. It does not appear in the
* debug controls, because the debugger can only invoke functions at global
* scope. It's available only to other code in the same local scope - this
* is great for encapsulation, if that is what you're after.
*/
function CountColA() {
/**
* Since CountColA is in CountColb local scope, it has access to other
* local scope variables - in this case ss and sheet. In terms of scope,
* this is called "closure". Within CountColb local scope, there can be
* only one "ss". If we declare it a second time here, within CountColA,
* it will in fact use the same storage location as the version first
* declared in CountColb.
*/
//var ss = SpreadsheetApp.getActiveSpreadsheet();//goes to active spreadsheet
//SpreadsheetApp.setActiveSheet(ss.getSheets()[0]);//goes to sheet tab 0 = first sheet, 1 = sheet 2
/**
* This statement gets all the populated cell values from the sheet
* into an array of arrays. While spreadsheet rows and column number
* from 1 to n, javascript arrays index from 0 to n-1.
*/
var data = ss.getDataRange().getValues();
/**
* This is a down-counting for loop, properly set up. It will start
* at the highest-numbered row, and work back to the first.
*/
for (var i = data.length - 1; i >= 0; i--) {
if (data[i][0] != null && data[i][0] != '') {
/**
* The first time we find that the first element in a row is neither
* null nor a blank string, we will return the spreadsheet row number.
* If the content of the spreadsheet remains constant, this function
* will ALWAYS return the same result. (Is that what you want?)
*/
return i + 1;
}
}
}
/**
* Here is another variable, x, in CountColB local scope. The value of x
* is being set to a function (we can do that in Javascript). Since we are
* supplying the function keyword, a name 'CountColA', and a function body
* enclosed in braces, this is an entirely new function. While it has the
* same name that was previously used in CountColB local scope, that is not
* a problem for the javascript interpreter because the function name here
* is x, NOT CountColA.
*
* This does not invoke CountColA and assign the return value to x.
*/
var x = function CountColA() {
/**
* Because of closure, the x in here is the same as the x out at
* CountColB local scope. The statement below will throw an exception
* (crash) because it is passing a function (this function) as the
* third parameter to getRange, which expects a number.
*/
var range = sheet.getRange(1, 1, x, 3);
values = range.getValues();
Logger.log(values);
}
}
/**
* We've reached the end of CountColB without any statements that
* invoke CountColA or x, the two functions within CountColB.
* As a result, CountColB does nothing.
*/
Fig 1: CountColb in debug controls
Working Example
It's not clear what "cover the full range of the table" means, but let's presume the intent is to count the all cells that contain values. This function will do that:
/**
* Counts all non-blank cells in sheet 1 of the spreadsheet.
*
* @return {number} count of non-blank cells
*/
function countCells() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0]; //** Always uses "Sheet 1"
var data = ss.getDataRange().getValues();
var result = 0;
// For every row
for (var row = (data.length - 1) ; row >=0 ; row--) {
// ... and every column in that row
for (var col = (data[0].length - 1) ; col >=0 ; col--) {
// ... check for existence and content of a cell's contents
if ((data[row][col] !== null) && (data[row][col] !== '')) {
// ... and count it
result++;
}
}
}
// Return the count of non-blank cells
return result;
}
