onEdit hide Rows and Columns

[Lee Mitchell]

Hi everyone and happy new year.

I am completely new to Apps Script and finding the switch from VBA very difficult. 

I have a sheet with the range A1:AU309 and have a reference number in the first row and column so that if the number shown is a "0" the row or column will hide automatically.

I have butchered a code together which although works takes about a minute to run. When the code was specifically for just hiding rows it took about 2 seconds to run. Is anyone able to help highlight where I am going wrong or an alternative code to speed up the process??

    function onEdit(e){

  var sheet = SpreadsheetApp.getActive().getSheetByName("Purchase Orders");

  var data = sheet.getDataRange().getValues();

  var numCols = sheet.getMaxColumns();

  for(var i = 1; i < data.length; i++) {

   if(data[i][0] === 1) {

      sheet.showRows(i+1); 

    } else if(data[i][0] === 0) {

      sheet.hideRows(i+1);}

      for(var ai = 0; ai < numCols; ai++){

        if(data[0][ai] == 0){

            sheet.hideColumns(ai+1);

        } else {

            sheet.showColumns(ai+1);

    }

  }

  }

  }

Many thanks

Lee

[CBMServices Web]

Yeah your script is going through every single data and deciding to hide that column or row. This is not needed if you use onEdit.

The e variable passed in will give you what row/column was changed. So if the value for that cell is 0 or 1 you can then decide to hide or show you row or column. No need to go through the whole spreadsheet.

That should speed it up considerably and be more specific action based on what changed.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/c59c80df-8f1e-4040-bedc-00017417acadn%40googlegroups.com.

[Keith Andersen]

Lee,

This will make things a nightmare if you ever have to do a search for things in those hidden rows.

Wouldn't it be better if those things that you wish to hide were simply moved to another sheet? The code to do that is no more complicated than the code you already have.

Just a thought.

Keith 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFX%2Bz3VVdTwKiCfZfH9A5keXyV%3DTRyc%2B99%3DaVMLw3w4QEW7tXw%40mail.gmail.com.

[Lee Mitchell]

Hi Keith,

The sheet is all automated data showing a supplier, product, cost and quantity from other tabs. If there is no quantity in the columns I need them to be hidden so I can pdf a PO from it.

Such a simple task using VBA macros in excel but I think I have wasted at least 3 days trying to work it out in Google sheets

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/zl4i9GXnt94/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAJ8MD4yudFGuFYUhUtdj9qXBUWvubRLQOEYjM07EVst8hSWq-A%40mail.gmail.com.

[Keith Andersen]

How are you bringing data from other tabs to this central tab? Query? Filter? V-lookup?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAEeyBvtVhummF7BgEBVF%2BbYyrqCs3y7cNTEmVOFas2bzDbs1jQ%40mail.gmail.com.

[Edward Wu]

One great piece of general advice I've gotten over the years is to try to keep your data and your "presentation/display" separate when possible/practical.

It might be easier to create a new tab that only shows the items without qtys. Then just use the new tab for the PDF.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAEeyBvtVhummF7BgEBVF%2BbYyrqCs3y7cNTEmVOFas2bzDbs1jQ%40mail.gmail.com.