CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets-apigoogle-sheets-macros
João Miguel Santos
João Miguel Santos

Reputation: 105

Hide rows if all columns are blank Google Sheets App Script

I'm trying to get an automated script that checks if the columns to the right of "D9" are blank and if all of them are, hide the row. My idea was to iterate with a for loop and go row by row and check this, therefore replacing the Row argument in range with 'i' but it seems that for some reason, if I put the A1notation it works but not with 'row' and 'column' arguments. I've just started messing with this a few days ago so I'm certain I'm doing something wrong so if you could point me in the right direction and point me why this doesn't work, that would be great :)

So this works:

function SelectLastColumn() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange("D9").activate();
var currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection()
.getNextDataRange(SpreadsheetApp.Direction.NEXT).activate();
currentCell.activateAsCurrentCell();

This doesn't:

function SelectLastColumn() { var spreadsheet =
SpreadsheetApp.getActive(); spreadsheet.getRange(9,4).activate(); var
currentCell = spreadsheet.getCurrentCell(); 
spreadsheet.getSelection()
.getNextDataRange(SpreadsheetApp.Direction.NEXT).activate();
currentCell.activateAsCurrentCell();

Upvotes: 0

Views: 625

Answers (1)

Tanaike
Tanaike

Reputation: 201338

About the reason that above script works and below script doesn't work, how about this answer? In your script, spreadsheet of var spreadsheet = SpreadsheetApp.getActive(); is Spreadsheet as an object. When getRange() is used for this situation, it is required to be careful the following points.

  • When getRange() is used by a1Notation, it can be used for Spreadsheet and Sheet.
  • When getRange() is used by row and column, it can be used for only Sheet.

From above points, in the case of your script, when your script is modified as follows, your both scripts can be used.

From :

var spreadsheet = SpreadsheetApp.getActive();

To :

var spreadsheet = SpreadsheetApp.getActiveSheet();

Reference :

If I misunderstand your question, I'm sorry.

Upvotes: 1

Related Questions