I'm currently using this code I got from Google. But the problem is it changes not only the range I specify which is N9:N14 but it goes til N54 whats wrong with this code
I want only these highlighted with blue to change to zero but what happens is this
Delete .offset(0,0,ss.getDataRange().getNumRows() ).
"ss.getDataRange().getNumRows()" returns the total number of rows that contain data. Your script is running to row 54 so that means that there are 40 rows of data in the spreadsheet (14+40=54), which means that the value of the the number of rows in the offset = 40 rows.
This is completely irrelevant to your range, since it is specified in A1 Notation.
The solution then is simply to delete the offset.
ADDENDUM - 21 Sept 2020
Though the OP referenced the range "N9:N14", the intention (confirmed by the use of the OFFSET method) was to test and update all the cells in the range between Cells N9 and T14.
This addendum shows a classic iteration through the rows and columns. The range is defined using getRange(row, column, numRows, numColumns) doc ref, and cell values are tested for blank or zero (both of which may be a valid outcome).
function wa14625101() {
/*
// note: cells in Column4 (FOC), rows 1-4 and Column5 (1st Time), rows 1, 2, 5, 6 are blocked out
// these can be made zero value without any risk
// more technical work would be required to exclude these from the loop
*/
var ss = SpreadsheetApp.getActive().getSheetByName("POS");
// the first blank cell is N9 (CT11-Cash)
// number of data rows = 6
// number of data columns = 7
// establish variables for the range
var startRow = 9;
var startCol = 14; // (Column N)
var numRows = 6;
var numCols = 7
// define the range
var range = ss.getRange(startRow, startCol, numRows, numCols)
// Logger.log("DEBUG: the range = "+range.getA1Notation());
// get the values of cells in the range
var values = range.getValues();
// loop through the rows and columns
for (var i = 0; i < numRows; i++) {
for (var j = 0; j < numCols; j++) {
// test if valie is blank or zero
if (values[i][j] === "" || values[i][j] === 0) {
// this cell is empty or zero therefore assign value of 0
Logger.log("EMPTY: i = "+i+", j = "+j+", row# "+(startRow+i)+", column = "+(startCol+j)+", value = "+values[i][j]);
// if = true, then set value to 0
values[i][j] = 0;
}
else{
Logger.log("NOT EMPTY and not zero - do nothing: i = "+i+", j = "+j+", row# "+(startRow+i)+", column = "+(startCol+j)+", value = "+values[i][j]);
// if = false, then do nothing
}
}
}
// update the range for any adjusted values
range.setValues(values);
return false;
}
Before
After
