Google Sheets script won't show/hide columns

Question

I'm trying to get google sheets to hide columns from a gantt chart based on the current dates of projects. The chart consists of one column for each day of 2018, every column in the chart contains a cell in row 12 which displays the value of the date (DD-MM-YY).

What I want the script to do is fetch the minimum start date of all projects in the sheet (cell AH411 calculates this value) and look for a matching value in one of the 365 columns of the gantt chart. Once found, I thought it best to first hide the entire chart and then make it show every column based on the start of the project (min) and the amount of days until the final project in the sheet ends (amount - calculated in cell AH413).

Here is what I came up with:

function myFunction() {
var sheet =SpreadsheetApp.openById("sheetID").getSheetByName("sheetname");  
var data = sheet.getDataRange().getValues;
var min = sheet.getRange("AH411").getValue();
var amount = sheet.getRange("AH413").getValue();
var rng = sheet.getRange("AG:OG");

for (var i = 32; i<=data.width; i++) {
    if (data[i][12] == min) {
      sheet.hideColumn(rng)
      sheet.showColumns(i, amount);
    }
  }
}

It gives me no errors but when I execute the script nothing happens, even though there are more than enough projects there. Any help?

Rubén Rubén · Accepted Answer · 2018-08-06T18:04:44

Replace

data.width

by

data.length

because

data is an Array object which has length as a built-in property while width isn't.

Google Sheets script won't show/hide columns

1 Answers