Google Apps Script - why .getNextDataCell() skips data

2
votes

I'm looking to start at the top of a column and perform CTRL+SHIFT+DOWN to arrive at a non-empty cell on row 15.

There is data in that column on rows 1, 15, and 16 only. When I use the following script to start on row 1 and try to go down to 15, my script skips rows 15 & 16 and goes to the bottom of the sheet, row 2000.

When I try to Record Macro and perform this function, it works fine while I'm recording, but skips the non-empty cells when I try to call the macro.

Any help would be much appreciated!

function DOWN() {
    var spreadsheet = SpreadsheetApp.getActive();
    spreadsheet.getRange('Q1').activate();
    spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
}
2
google-apps-scriptgoogle-sheets
Where is column of There is data in that column on rows 1, 15, and 16 ONLY.? In your script, the column "Q" is used. So when column is not the column "Q", getNextDataCell(SpreadsheetApp.Direction.DOWN) doesn't return "Q15". How about this? – Tanaike
getNextDataCell can't to be used for consecutive next, it will go to first and last for some consecutive not blank cell, for example if c4, c5 and c6 are not blank cells, so if you first are in c1, getnextdatacell give you c4, and from c4 getnextdatacell give c6. – user11982798
Welcome, Matt L. I may have misunderstood you, but with data in only cells Q1, Q15 and Q16, I then ran script DOWN, and it is fine. Moved the cursor to Q15 as expected – Tedinoz
Based on the behaviour you describe, I would say column Q is empty, and it is another column the one that has data in rows 1, 15 and 16. Please check that, or share a copy of the spreadsheet you are working on so that we can be sure that's the case. – Iamblichus
This doesn't make any sense. If there is text, it should not skip it. Can you share your spreadsheet? – Iamblichus

2 Answers

0
votes

Foiled again! Before this step in my process, I copied and pasted the column as values. The formula in Q2:Q had been =if(H2="","",Today()). When I copy and paste as values, there were hidden characters that caused the apparently blank cells to "contain data". Though I'm not sure why there would be hidden characters, this appears to be the answer to that problem. If anybody knows how to avoid the hidden characters, I'd appreciate any tips you have. I also find it strange that outside of a script, I could do CTRL + DOWN and it would skip to the only visible data.

0
votes

Try this one:

function TestNextNonBlank() {
  var spreadsheet = SpreadsheetApp.getActive();
  var mysheet=spreadsheet.getSheetByName('MainSheet');
  var myrange=mysheet.getRange('Q1'); 

  myrange=nextNonBlank(myrange); //First Non Blank
  Logger.log(myrange.getA1Notation()); //Give Q15

  myrange=nextNonBlank(myrange); //Second Non Blank
  Logger.log(myrange.getA1Notation()); //Give Q16

  myrange=nextNonBlank(myrange); //Third Non Blank
  Logger.log(myrange.getA1Notation()); //Give Q994

};


function nextNonBlank(theRange)
{
  if (theRange.offset(1, 0).isBlank()==false) return theRange.offset(1, 0);
  theRange=theRange.offset(1, 0);
  if (theRange.offset(1, 0).isBlank()==false) return theRange.offset(1, 0);
  theRange=theRange.getNextDataCell(SpreadsheetApp.Direction.DOWN);
  return theRange;
}

To Avoid the invalid argument error, below I have modified the script of function, after this function reach the max row, it return the range in the max row position. To have the script run well, we must ensure the valid sheet and the valid position of non blank cell:

function nextNonBlank(theRange)
{
  if (theRange.getSheet().getMaxRows()==theRange.getRowIndex()){
     return theRange; 
  }
  //save the range as last range
  var storedRange=theRange;
  if (theRange.offset(1, 0).isBlank()==false) return theRange.offset(1, 0);
  theRange=theRange.offset(1, 0);
  if (theRange.offset(1, 0).isBlank()==false) return theRange.offset(1, 0);
  theRange=theRange.getNextDataCell(SpreadsheetApp.Direction.DOWN);
  //if the reach range is blank, back to use the stored range
  if (theRange.isBlank()) theRange=storedRange;
  return theRange;
}