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

enter image description here

enter image description here

I want only these highlighted with blue to change to zero but what happens is this

enter image description here

  • Perhaps this post is of use: webapps.stackexchange.com/a/70696/29140 – Jacob Jan Tuinstra Sep 14 '20 at 10:15
  • 1
    Welcome to webapps. May I offer some constructive comments for your future reference. Please describe your problem at the outset so that it can be quickly and simply understood. If you include code, ensure that you explain its purpose and relationship to your problem. Also, please include any code as text. The images of your spreadsheet are good, but there is no indication of which columns and rows they occupy; would you please add this into screenshots. These things will help volunteers quickly and easily come to grips with your question . Please ask if you want any clarification. – Tedinoz Sep 15 '20 at 4:01

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;
      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
  return false;




enter image description here

  • From user255009: OMG thank you so much it really works. But though I specified "N9:N14", I want the zeros to appear in each empty cell (except the blocked out "FOC" and "First Time") and the Total Column and Row. – Tedinoz Sep 20 '20 at 5:11
  • @User255009 I have worked through the entire range (including the blocked out cells in "FOC" and "1st Time"). It would be possible to exclude those cells from the iteration but the additional code to do this didn't seem justified. – Tedinoz Sep 20 '20 at 23:50
  • @User255009 You originally said that you wanted to put a zero in all the blue cells, but then you said I want the zeros to appear in each empty cell. These two statements do not mean the same thing: "a zero in every blue cell" would overwrite any existing value BUT a zero in every EMPTY cell would not overwrite any existing value. Would you please clarify: if a blue cell contains a value, do you want to overwrite it to insert zero? – Tedinoz Sep 24 '20 at 7:59
  • From user255009: it works perfectly. – Tedinoz Sep 24 '20 at 8:10
  • From user255009: This question/answer covers a single range/table, however I have multiple ranges and summaries. There are three "Tally Sheets": "N9:T14" (as for this question), "N19:T24" and "N29:T34". There are also six "Change of Record" ranges (three sets of two): "Y9:Y14", "Y19:Y24", "Y29:Y34", "AC9:AC14","AC19:AC24" and "AC29:AC34". My goal is that each cell in each range should be set to zero. – Tedinoz Sep 24 '20 at 8:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.