OnChange Trigger that will fire when there's an update on a specified column

Question

I have 2 Spreadsheets, namely Source and Destination. In Source, I have listed my sample products and their designated prices and are copied over to Destination using IMPORTRANGE function. Now, I'm trying to fire the OnChange trigger when there is an update on 'Destination Sheet 1'B:B only. Here's my code:

function testFunction(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Destination Sheet 1");
  if (e.source.getSheetName = "Destination Sheet 1" && e.range.getRow() > 1 && e.range.getColumn() == 2){
    var range = ss.getRange(e.range.getRow(), 3, ss.getLastRow());
    range.setValue("There's an update");
  }
}

I've also added the function testFunction on OnChange Trigger.

However, based on Event Objects Documentation there's no option for me to get the range that was updated from the events object. By "range", I mean the Destination Sheet 1'B:B. I'm getting this error:

"TypeError: Cannot read property 'getRow' of undefined at testFunction(Code:3:64)"

Desired Outcome (After an update on the pricing in 'Destination Sheet 1'!B:B):

I'm still fairly new to google scripts and I've really tried everything that I can think of but it is not getting me anywhere. Any help is greatly appreciated. Thank you in advance!

UPDATE 1: It seems that there is no way of triggering the OnChange trigger when there is an update on a sheet that is on IMPORTRANGE function.

ALTERNATIVE: Since it doesn't work, I was thinking of using OnEdit trigger instead on Source to handle that edit and apply my desired outcome on Destination. However, I'm having permission issues.

Exception: You do not have permission to call SpreadsheetApp.openById. Required permissions: https://www.googleapis.com/auth/spreadsheets

Here's my code:

function onEdit(e){
  aSourceFunction(e);
}

function aSourceFunction(e) {
  //IF stmt to make sure that OnEdit Trigger is not triggerred anywhere
  if (e.source.getSheetName = "Source Sheet 1" && e.range.getRow() > 1 && e.range.getColumn() == 2){

    //my attempt to call Destination Sheet 1 from Destination 
    var ss = SpreadsheetApp
    .openByID("1QMtU6VbNQyDLXMHmaLBBDePH5l")
    .setActiveSheet("Destination Sheet 1");
    
    //desired outcome
    ss.getRange(e.range.getRow(), 4, ss.getLastRow()).setValue("There's an update");
  }
}

Also, I'm not sure if my concern is related but I've read somewhere that my code shouldn't have @OnlyCurrentDoc but I don't have that anywhere in my codes even in my Manifest file.

{
  "timeZone": "Asia/Hong_Kong",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8"
}

Here's the scope of it:

Update 2: It worked! Initially, I have a simple OnEdit trigger. However, according to this, you need to have an installable OnEdit trigger for it to work (also fixed my code for the errors) and when I ran the code, the "permission prompt" showed up for me to access the Destination Spreadsheet. Here's my code for anyone who needs it:

function aSourceFunction(e) {
  //IF stmt to make sure that OnEdit Trigger is not triggerred anywhere
  if (e.source.getSheetName = "Source Sheet 1" && e.range.getRow() > 1 && e.range.getColumn() == 2){

    
    //my attempt to call Destination Sheet 1 from Destination 
    var ss = SpreadsheetApp.openById("1QMtU6VbNQyDLXMHmaLBBDePH5l-4wDPUNQ827gb3jXY");
    var sheet = ss.getSheetByName("Destination Sheet 1");
    
    
    //desired outcome
    sheet.getRange(e.range.getRow(), 4).setValue("There's an update");
  }
}

Note: Go to Edit>Current project's trigger>Add Trigger using aSourceFunction as your function and OnEdit as the trigger.

@TheMaster I'm changing the values manually. I was thinking about using OnEdit because it automatically fires when you do it manually but I need my trigger to be on OnChange. — Ryan, Sep 08 '20 at 08:25
Don't edit your question with answer/workaround. Add a answer below instead. — TheMaster, Sep 09 '20 at 07:20
*It seems that there is no way of triggering the OnChange trigger when there is an update on a sheet that is on IMPORTRANGE function.* False. You can trigger it, but it just won't tell which range was changed — TheMaster, Sep 09 '20 at 07:22

Rubén · Answer 1 · 2020-09-08T13:41:30.780

0

From the question

However, based on Event Objects Documentation there's no option for me to get the range that was updated from the events object. By "range", I mean the Destination Sheet 1'B:B.

You are right, there is no way to get the range that was udpdated from the change event object.

But if you already know that range of interest is 'Destination Sheet 1'!B:B you can use:

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var range = spreadsheet.getRange("Destination Sheet 1!B:B");

Regarding the error message it occurs because the change event object doesn't include a range property.

Please bear in mind that (from Installable Triggers)

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.

Regarding how to make the OnChange trigger to fire on updates on Column B only, you can try to use getActiveRange() but sometimes it doesn't work. There is an open issue related to this getActiveRange() incorrect in onChange trigger for some column/row operations

answered Sep 08 '20 at 04:39

Rubén

34,714
9
70
166

1

thank you for your answer! Really appreciate it! One question though, how can I make the OnChange trigger to fire on updates on Column B only? – Ryan Sep 08 '20 at 08:47
1

You can try to use `getActiveRange()` but sometimes it doesn't work. There is an open issue related to this [getActiveRange() incorrect in onChange trigger for some column/row operations](https://issuetracker.google.com/issues/36760104) – Rubén Sep 08 '20 at 13:38
2

@Rubén Checked already. Doesn't work with importrange. – TheMaster Sep 08 '20 at 14:42
Thank you both for taking the time to answer my question. If this will not work, the alternative that I'm thinking of, is to handle that manual edit using OnEdit trigger in Source. However, I can't seem to do that because of permission issues. I'll update my post so you can check. Thank you again. :) – Ryan Sep 09 '20 at 04:30

OnChange Trigger that will fire when there's an update on a specified column

1 Answers1