Google App Script : Finding The Last Blank Row

Question

I'm trying to finish out a Script That Automatically hides all rows that are 'BLANK' except one (For entry of new data). I've actually got it working completely except for one of my pages called "Roster".

If you would like to see my sheet for context this is an editable version: https://docs.google.com/spreadsheets/d/126hhh5su-WgHQQ_RmI8yvlrwITPeEIAegopHAooGaK0/edit?usp=sharing

The problem with 'Roster' is that my script checks for blank Rows but is not ignoring formulas that evaluate to blank.

So here is my "hider" This works just fine Except with sheets with Formulas that extend to the bottom of the like "=output!c1,c2,c3 etc...

function HideRows(SheetName, FirstCol, LastCol, CopyOn) {
  // important variables
  var s = SpreadsheetApp.getActiveSpreadsheet(); // Calls Spreadsheet API
  // set's location for the code to work
  var Width = LastCol - FirstCol;
  // Sheetname called by function
  var sheet = s.getSheetByName(SheetName);
  // Gets total number of Rows and Cols
  var maxRow = sheet.getMaxRows();
  // Gets total Number of Rows and Cols That aren't BLANK
  var LastRow = sheet.getDataRange().getLastRow(); // Gets the total number of Rows with data
  var LastCol = sheet.getDataRange().getLastColumn(); // Gets the total number of Cols with data
  // Gets the Number of blank rows By subtracting the amount "BLANK" Rows from "ALL" Rows
  var numBlank = maxRow - LastRow; // Gets the difference between the Max and Last Row
  var blankRow = maxRow + 1 - numBlank;
  var numRows = numBlank - 1;
  var Blank = blankRow - LastRow;
  var On = CopyOn;
  // Checks For AutoFill Variable
  if (On) {
    CopyPaste(numBlank, LastRow, numRows, sheet, Width, Blank);
  } else {
    NoCopyPaste(sheet, LastRow, maxRow, numRows);
  }
}

function NoCopyPaste(sheet, LastRow, maxRow, numRows) {
  if (sheet.isRowHiddenByUser(LastRow)) {
    sheet.hideRows(maxRow + 1 - numRows, numRows);
    Logger.log(sheet.isRowHiddenByUser(LastRow));
  } else {
    sheet.showRows(LastRow + 1);
    sheet.hideRows(maxRow + 1 - numRows, numRows);
    Logger.log(sheet.isRowHiddenByUser(LastRow));
  }
}

function CopyPaste(numBlank, LastRow, numRows, sheet, Width, Blank) {
  if (numBlank < 1) {
    sheet.showRow(LastRow + 1);
    AutoFill(LastRow, FirstCol, Width);
  } else if (numBlank > 1) {
    sheet.hideRows(maxRow - numRows, numRows);
  } else if (numBlank === 1) {
    sheet.getRange(1, 1);
  }
}

function AutoFill(LastRow, FirstCol, Width) {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange("A1").offset(LastRow - 3, FirstCol, 1, Width).activate();
  var destinationRange = spreadsheet.getActiveRange().offset(0, 0, 3);
  spreadsheet.getActiveRange().autoFill(destinationRange, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  spreadsheet.getCurrentCell().offset(1, 0, 2, 4).activate();
}
HideRows("output", 0, 10, false);

Now I have done some Research and found this wonderful solution from Jean Pierre : https://productforums.google.com/forum/#!topic/docs/-Xr6dud_Nak

It works Perfectly for Roster and correctly identifies the last row in the sheet, However, It Finds undefined in Sheets that only have text output and just have an Empty Cells at the end (No Formula's).

I'm trying to edit the "for" statement found in that answer so that it works for both types of sheets.

var SheetName = "output";
        function getData() {
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var sh = ss.getSheetByName(SheetName);
        var data = sh.getDataRange().getValues();
      
      var lastRow = getLastRow(data);
        Logger.log("Last Row is: " + lastRow);
        Logger.log(data);
      }
     
    function getLastRow(data) {
      for (var i = 0; i < data.length; i++) {
           var len = data[i].toString().replace(/,/g, "").length;
        Logger.log([i])
        if ( len === undefined){
           var lastRow = i + 1;
             break;
        } else if (len < 1) { 
             var lastRow = i;  
           }  
      }
      return lastRow;
      Logger.log(lastRow)
    }

This is my best attempt but it still does not work for sheets with no formulas. Any help would be apprecieted!

Provide [mcve]. You just dumped every thing in your question. It should be [mcve] Also, Have you looked at the top-voted Q/As in this tag? https://stackoverflow.com/questions/4169914/selecting-the-last-value-of-a-column — TheMaster, Dec 11 '18 at 15:18

score 0 · Accepted Answer · answered Dec 23 '18 at 23:13

I think you are tying yourself in knots trying to find a programmatic method to apply to a spreadsheet that has so many different formats and is so inconsistent from sheet to sheet.

The spreadsheet also suffers from containing redundant data and time could be better spent normalising the data (building and utilising master files/lists of data) and linking sheets using that data. "Roster", for example, could be almost exclusively comprised by dropdown lists.

I've taken the approach of building simple routines that apply to each sheet, or that can be combined using the so_hide_Various function to process all sheets in one go.

function so_hide_Various() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheetstuff = new Array;
    sheetstuff = [{
            sheetName: "output"
        },
        {
            sheetName: "Roster"
        }
    ];
    // Logger.log("sheetstuff rows: "+sheetstuff.length);// DEBUG
    for (var z = 0; z < sheetstuff.length; z++) {
        var sheet = ss.getSheetByName(sheetstuff[z].sheetName);
        switch (sheetstuff[z].sheetName) {
            case "output": // output style
                so_hide_Output();
                break;
            case "Roster": // Roster style
                so_hide_Roster();
                break;
        }
    }
}

function so_hide_Output() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var SheetName = "output";
    var sh = ss.getSheetByName(SheetName);
    var lrow = sh.getLastRow();
    var maxrow = sh.getMaxRows();
    //Logger.log("the last row is "+lrow+", and the max row is "+maxrow); // DEBUG
    var starthide = lrow + 2;
    sh.showRows(1, maxrow);
    sh.hideRows(starthide, maxrow - starthide + 1);
}

function so_hide_Roster() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var SheetName = "Roster";
    var sheet = ss.getSheetByName(SheetName);
    var Avals = sheet.getRange("B3:B").getValues();
    var Alast = Avals.filter(String).length;
    var maxrow = sheet.getMaxRows();
    var starthide = (Alast + 2 + 1 + 1); // add the two rows because of start on row 3 plus one blank row plus one for first hidden row
    //Logger.log("Alast = "+Alast+", starthide:= "+starthide+", and the max row is "+maxrow);//DEBUG
    var rowstodelete = maxrow - starthide + 1;
    sheet.showRows(1, maxrow);
    sheet.hideRows(starthide, rowstodelete);
}

Holy Cow, I just Saw This, Thank you so much. Yeah, I was Trying to code a swiss army knife that can do it all. Seriously this is blowing my mind! Thank you! — Michael Walker, Jan 10 '19 at 01:33

Google App Script : Finding The Last Blank Row

1 Answers1