On Change Trigger Not Running Script On Change

Question

I have a script to update named ranges when new rows of data are added to the spreadsheet in question:

function updateNamedRanges() {

      // get to the right place
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName('ga weekly data pull');

      //now update the named ranges if they have changed in length
       var openEnded = ["gaCampaign", "gaMedium", "gaSource", "gaSubscriptions", "gaUsers", "gaWeek"];

      for(i in openEnded) {
        var r = ss.getRangeByName(openEnded[i]);
        var rlr = r.getLastRow();
        var s = r.getSheet();
        var slr = s.getMaxRows();
        if(rlr==slr ) continue; // ok as is-skip to next name
        var rfr = r.getRow();
        var rfc = r.getColumn();
        var rnc = r.getNumColumns();
        var rnr = slr - rfr + 1;
        ss.removeNamedRange(openEnded[i]);
        ss.setNamedRange( openEnded[i], s.getRange(rfr, rfc, rnr, rnc ));
      }   
      sheet.getRange("D2").setValue(0); // this gets all the formulas in the sheet to update - just changing any cell
  }

Then, within Aps Script editor I go Resources > Current Projects Triggers > Run updateNamedRanges > From Spreadsheet > On change.

Now, if I manually add in a row of data the script runs - great!

But I'm pulling in data with the Google Analytics add on. This add on expands the tab in question when the length of data is longer than the sheet. But when this happens the script does not update.

Is there anything I can do here?

As a backup I'm thinking if I can figure out how to get GAS to add a row from the bottom of the sheet that might do it but that seems like a workaround. Before I go down that path is there a better way?

Regarding my last paragraph that won't even work since I can't get the script to run in the first place — Doug Fir
– Doug Fir, Commented May 16, 2015 at 16:39

Zig Mandel · Accepted Answer · 2015-11-30 12:34:52Z

6

as you found out, apps script triggers only work when apps script does the changes. yea its lame. if an api outside of apps script modifies the sheet, they wont trigger.

your only option is to use a time trigger to detect a change and process the entire sheet again (since you dont know what changed). One way to achieve this more efficiently is to remember (in a script property) the last modified date from triggers. then a 1minute time trigger checks if modified date is now bigger than the last one saved. if so process the entire sheet.

edited Nov 30, 2015 at 12:34

answered May 17, 2015 at 12:39

Zig Mandel

19.9k5 gold badges32 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ScampMichael · Accepted Answer · 2015-05-17 22:23:36Z

1

Run it on a time trigger that runs every minute until Google addresses the issues of not catching the on change event and/or not being able to define open-ended named ranges.

Edited for running the script on open

To keep the sheet from recalculating everytime it is opened whether needed or not.

above the loop place:

var recalc = false;

within the loop below if(rlr==slr ) continue;

recalc = true;

recalculate the sheet only if necessary:

if(recalc) {sheet.getRange("D2").setValue(0)};

edited May 17, 2015 at 22:23

answered May 17, 2015 at 12:49

ScampMichael

3,7382 gold badges19 silver badges23 bronze badges

5 Comments

Doug Fir Over a year ago

I ended up using an onOpen() event within the script itself. So if the add on adds data each week at 7am, then when someone opens it the script updates. Adding a timer every minute would presumably update the script each minute and that would make using the sheet tricky for the end user since a fair amount of formula run when the ranges update. But, was my understanding of triggers correct? Should Resources > Current Projects Triggers > Run updateNamedRanges > From Spreadsheet > On change have worked and it's just a GAS issue rather than my understanding of it?

ScampMichael Over a year ago

the on open event could slow the loading time but it would probably be immaterial if no rows added since the last time the sheet was opened. your understanding of the trigger was correct. The event should trigger anytime a change is made no matter what the source. this is a bug and should be reported as such.

ScampMichael Over a year ago

when I mentioned that the time to execute was immaterial if nothing had changed I forgot that you were recalculating the sheet. it may take some time and is not necessary in every case. See edited answer.

Doug Fir Over a year ago

Thanks again I have updated my script now. Thanks also for helping me get this far with previous answers. I expect to be recycling this script many times in different sheets. I somewhat follow the loop and the logic and I'm hoping it sinks in more each time I reuse it. But what is this doing: if(rlr==slr ) continue; ? Also, I understand WHAT using the recalc variable does but I don't get HOW? The loop will always run on open no? If so I don't get how recalc will ever be false :-/

ScampMichael Over a year ago

if(rlr==slr ) continue; if true the last row of the sheet is the same as the last row in the range and does not need to be extended therefore we do not need to do anything below this line. Continue means stop here and start over at the top of the loop. if all is OK the loop will never process below below continue and therefore as recalc=true is below continue it will not be set to true

Collectives™ on Stack Overflow

On Change Trigger Not Running Script On Change

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related