-2

This post was closed, but I have not been able to find a comparable problem elsewhere.

I'm trying to work out a script that will hide rows from an onChange() trigger. When a cell becomes "0", I want the row that cell is in to become hidden. Comments from my first post have taught me that the object passed from onChange does not contain a range. Is there a workaround that would solve this problem?

My spreadsheet has an input sheet for the backend and an output sheet for the frontend that goes to the client. I need an onChange trigger, so that as data goes to the frontend it nicely format for emailing to the client. Most importantly, I need empty ('0) rows to be hidden.

I'm new and just learning, so what I've written isn't working because onChange objects do not include a range. Thank you.

function onChange(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Client");
  var cell = e.range;
  var VALUE = cell.getValue(); 
  if(VALUE == 0){
        sheet.hideRow(cell);
  }
}

I've also tried:

function onChange(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Client");
  var cell = e.getValue(); 
  if(cell == 0){
        sheet.hideRow(cell);
  }
}
yujiroads
  • 19
  • 2

2 Answers2

0

There is no range value returned by an onChange trigger. Here's what the event object looks like.

{"authMode":"FULL","changeType":"INSERT_ROW","source":{},"triggerUid":"","user": {"email":","nickname":""}}

Addtionally keep in mind onChange requires an installable trigger.

onChange Event Object

Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Thanks. Using the above options, how can I identify the cell or row that has changed in order to hide it? – yujiroads Dec 28 '20 at 03:09
  • source is the active Spreadsheet so you can use any methods found [here](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet) – Cooper Dec 28 '20 at 03:21
  • Unfortunately, my post has been closed. But now that you've given me a direction, I'll get to work on this. Much appreciated. If I get stuck again, I may reach out. Thank you. – yujiroads Dec 28 '20 at 04:24
0

It seems like you have the onChange onEdit triggers confused. The onChange trigger fires when a "larger" or structural change occurs, rather than the on changed values. From the docs:

An installable change trigger runs when a user modifies the structure of a spreadsheet itself—for example, by adding a new sheet or removing a column.

You can see from the event object specifications that there is no value passed to the programmer from the onChange trigger, and therefore the type of functionality you're looking for is not easily done.

You want to use the onEdit trigger which will function closer to what you are looking for. It will fire on any value change and give you the new and old values. onEdit is also a "simple trigger" so there is no need to install additional triggers like you do for onChange.

Using onEdit your code will look something like this:

function onEdit(e) {
  val range = e.range;
  val sheet = SpreadsheetApp.getActiveSheet();

  if (e.value == 0) {
    var rowNum = e.range.getRow();
    sheet.hideRow(rowNum);
  }
}
joshmeranda
  • 3,001
  • 2
  • 10
  • 24
  • Thanks, but I am using onChange. I do have it installed. I need this to work without a manual onEdit of the cells. As Cooper mentioned, it seems there is no range value returned by the trigger, so usinge e.range won't work. Any thoughts on how to do this with onChange? Thanks – yujiroads Dec 28 '20 at 03:03
  • @yujiroads My point is that onChange will only notify you that the sheet was changed in some large structural way (ie rows deleted or added). onChange will not expose any range or data to you so it is not possible with the onChange trigger. You want onEdit because it exposes the mutated and stale data to you while being called whenever data is changed – joshmeranda Dec 29 '20 at 15:18
  • I updated the answer to be a bit more clear as to why `onEdit` is probably better here than `onChange` – joshmeranda Dec 29 '20 at 15:22
  • Thanks Puffin, I am new at this, so maybe I don't understand something... but onChange seems to be successfully triggering. Specifically, I am ticking a checkbox on Sheet 2, which alters the values in a column on Sheet 1. This will trigger onChange. So, I'm giving a try to using "source" as the value returned from onChange. It looks like I am close but struggling with an array... my continuation of this post is here: https://stackoverflow.com/questions/63720612/what-does-the-range-method-getvalues-return-and-setvalues-accept. Am I still missing something? Thanks again. – yujiroads Dec 29 '20 at 17:51
  • @yujiroads Huh, the documentation does not specify any "source" property on the passed event object. `onEdit` has a source property which is the spreadsheet where the edit occurred. Is there any reason that you **need** to use the onChange trigger? it should not be able to do what you are attempting unless the documentation is out of date – joshmeranda Dec 29 '20 at 23:17
  • "source" property is working for onChange and the script is coming closer to doing what I need. onEdit won't work because I have two sheets. One is for data input the other is for output. I need to structure the output sheet without anyone actually touching it. It's locked. Right now my for loop is running endlessly and I do not see why it should. I will be posting another question about it as soon as the system lets me post a question again. I guess my first two questions weren't of interest! Thank you. – yujiroads Dec 30 '20 at 05:20