1

I have an issue with onSelectionChange function - it just stopped working for me.

function onSelectionChange(e) {
  // Set background to red if a single empty cell is selected.
  var range = e.range;
  if(range.getNumRows() === 1 
      && range.getNumColumns() === 1 
      && range.getCell(1, 1).getValue() === "") {
    range.setBackground("red");
  }
}

Whatever do I put here it is not running. Even if it is just msgBox or something else. In Logger just after it triggers, it says "runtime 0 seconds" and I receive error "Maximum execution time exceeded". What's going on?

Rubén
  • 34,714
  • 9
  • 70
  • 166
kazbag
  • 11
  • 3
  • 1
    In my environment, when I tested your script, I could confirm that the script worked. When I selected a cell, the background color is changed to red color. So for example, when you create new Spreadsheet and test the script, what result will you get? But if this was not the direct solution of your issue, I apologize. By the way, I cannot understand about `Whatever do I put here it is not running. Even if it is just msgBox or something else.`. Can I ask you about the detail of it? – Tanaike Jul 09 '20 at 06:24
  • Thank you for answer pal. I've also tested it on a brand new project, even a new Google account and result was same. Referring to the part you don't get - I was thinking about that, if I put just a Logger or MsgBox or even some kind of simple function (i.e. sum 2+2), result was the same - it was not working and still I was receiving timeout and error. Fortunately it suddenly started working and I have no idea why. I was researching whole of Internet and people were saying that there are problems with new v8 runtime - sometimes it's working and sometimes is not. – kazbag Jul 09 '20 at 07:03
  • Thank you for replying. I have to apologize for my poor English skill. Unfortunately, I cannot understand about `Whatever do I put here it is not running. Even if it is just msgBox or something else..` and `if I put just a Logger or MsgBox or even some kind of simple function (i.e. sum 2+2), result was the same`. Can I ask you about the detail of them? – Tanaike Jul 09 '20 at 07:11
  • By the way, for example, is your issue related to this thread? https://stackoverflow.com/q/62647068/7108653 – Tanaike Jul 09 '20 at 07:13
  • That's no problem :) Part you can't understand is just about this onSelectionChange trigger. Imagine that you have function x which returns "hello world" and it's not working. You have decided to change body of your function to "hello jupiter" and it's still not working. You can not run this function and have no idea why. It's all. Maybe I wasn't clear. And about your second comment - yes it is but only partly. This user is talking about running sidebar from onselectionchange trigger and it's probably impossible because of permissions. My problem was, that I can't just run onSelectionChange. – kazbag Jul 09 '20 at 07:23
  • Thank you for replying. I deeply apologize my comments were not useful for your situation. – Tanaike Jul 09 '20 at 07:41

2 Answers2

0

onSelectionChange(e) is a new trigger that has been implemented very recently

It still seems to be a bit buggy - multiple users report that it sporadiously stops working and then works again.

So, most likely for you the issue is also only temporarily.

If not: this issue has already been filed on Google's Public Issue Tracker.

It has been closed because for the original poster the trigger started working again, but if for you it does not - you can comment on the issue and ask for it to be reopened again.

ziganotschka
  • 25,866
  • 2
  • 16
  • 33
0

This works:

function onSelectionChange(e) {
  Logger.log(JSON.stringify(e));
  if(e.range.rowStart==e.range.rowEnd && e.range.columnStart==e.range.columnEnd && e.range.isBlank()) {
    e.range.setBackground("red");
  }
}
Cooper
  • 59,616
  • 6
  • 23
  • 54