Google Apps Script - Cannot read property "5" from undefined

Question

Trying to hide rows in Google sheet after a task has been marked as closed and then writing a value "1" into a cell, so that the hidden rows can be ignored the next time the function runs.

The code fails on the line,

var status = range[i][5];

When I run the debugger, I can see it has read the correct value from the sheet. It is also showing the correct array of rows to hide.

Any help apprreciated, the rest of the code is below.

function HideClosed() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Form responses 5");
    var numRows = sheet.getLastRow();
    var lastCol = sheet.getLastColumn();
    var startRow = 2;
    var range = sheet.getRange(2, 1, numRows-startRow,lastCol).getValues();  //Get all values except the header rows

    var rowsToHide = [];

    for (var i = 2; i < numRows; i++){
        var status = range[i][5];  // checks column 6 for status
        var hidden = range[i][34];  // checks column 35 for hidden status

        if (status == "Closed" && hidden != "1") {  // checks for Closed jobs //that have not been hidden yet
            rowsToHide.push(i+1);
        }
    }

    var L = rowsToHide.length;

    for (i = L ; i>0; i--){
        sheet.hideRows(rowsToHide[i-1]);
        sheet.getRange(rowsToHide[i][34]).setValue("1");   // writes 1 to column 35 after hiding //row
    }
}

@Cooper - yeah, that worked, thanks.

It's very slow though, if it didn't time out, it took over 3 mins to hide 15 rows. From reading other threads on here, it seems to be a common enough problem..

Made a few changes to it today to try speed it up, but getting a "Cannot find function hide Rows error" now on the last line. Everything else looks good on the debugger.

function HideClosedWN() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Form responses 5");
var numRows = sheet.getLastRow();
var lastCol = sheet.getLastColumn();

var startRow = 2;
var range = sheet.getRange(2, 1, numRows-
startRow,lastCol).getValues();  //Get all values except the header rows

var rowsToHide = [];
for (var i=0; i<numRows-startRow; i++){

var status=range[i][5];  //  checks column 6 for status
var hidden=range[i][34];  // checks column 35 for hidden status

if (status == "Closed" && hidden == 1){  
rowsToHide.push(i+1);
}
}

sheet.getRange(2,1,rowsToHide.length,lastCol).hideRows(2,rowsToHide.length); }

Cooper Cooper · Accepted Answer · 2017-10-03T21:53:01

I think this might run:

function HideClosed() 
{
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Form responses 5");
    var numRows = sheet.getLastRow();
    var lastCol = sheet.getLastColumn();
    var startRow = 2;
    var range = sheet.getRange(2, 1, numRows-startRow,lastCol).getValues();  //Get all values except the header rows
    var rowsToHide = [];
    for (var i=0;i<numRows-startRow-1;i++)
    {
        var status=range[i][5];  // checks column 6 for status
        var hidden=range[i][34];  // checks column 35 for hidden status
        if (status=="Closed" && hidden!=1) 
        {  
          rowsToHide.push(i+1);
        }
    }
    var L=rowsToHide.length;
    for (i=rowsToHide.length-1; i>=0;i--)
    {
        sheet.hideRows(rowsToHide[i]);
        sheet.getRange(rowsToHide[i],35).setValue(1);   // writes 1 to column 35 after hiding //row
    }
}

Google Apps Script - Cannot read property "5" from undefined

1 Answers