0

I want to ask about how to make a trigger that run when there are changes on different spreadsheet (Source spreadsheet) and on specific column.
I have 2 different spreadsheet (Source Spreadsheet and Target spreadsheet) and the trigger will working on target spreadsheet when there are changes that happened in the specific column of the source spreadsheet. I've just made the code, but it's not working. This code was made on the target spreadsheet. I've already use the onEdit() and onChange() trigger, but nothing happen or run this script.
This code still show some error like:

TypeError: e.source.openById is not a function


Here is the code that I've been made on the target spreadsheet: 
function inChange(e) {
 var source_spreadsheet_sheetName = e.source.openById('').getName(); //ID of source spreadsheet
 var Row = e.source.getActiveCell().getRow();
 var Column = e.source.getActiveCell().getColumn();
 if (source_spreadsheet_sheetName == '' && Row >= 2 && Column == 2) { //the name of sheet from source spreadsheet and the specific column that has changes on it.
  myFunction(); //It will run the process of this myFunction script that I've been made on target spreadsheet
 }
}
Asking Bob
  • 43
  • 1
  • 7
  • Is the trigger set up correctly? Which sheet is the trigger set up for? – TheMaster Oct 30 '22 at 20:11
  • the trigger was set up for the target spreadsheet and its already correctly set up, because I run a different kind of trigger before this case and it's working perfectly fine. – Asking Bob Oct 30 '22 at 20:35
  • What type of trigger are you using? – Cooper Oct 30 '22 at 20:49
  • I use the onChange @Cooper – Asking Bob Oct 31 '22 at 02:49
  • I don't know my question is duplicating. Because with the suggested similar question, it has a difference purpose. Can you explain where the duplicates part or the detail? – Asking Bob Oct 31 '22 at 02:53
  • If the trigger is set for the target spreadsheet, it would respond to changes made in the target spreadsheet, not the source spreadsheet. – TheMaster Oct 31 '22 at 04:07
  • Could you [edit] to explain how you set up the trigger in the first place. – TheMaster Oct 31 '22 at 04:50
  • @TheMaster I apologize for my misunderstanding. So, if the trigger set on the target spreadsheet, then it will responding to changes in the target spreadsheet only and not the source spreadsheet – Asking Bob Oct 31 '22 at 07:16
  • and if I want to detect changes that happen on the source spreadsheet, then I have to set up the trigger on the source spreadsheet and the code that will run have to change to transferring data to the target spreadsheet, right? @TheMaster – Asking Bob Oct 31 '22 at 07:18
  • If you're setting the trigger manually, yes. However you can programmatically set up onChange for a different spreadsheet. – TheMaster Oct 31 '22 at 07:21
  • I think I set the trigger manually by using add trigger in the apps script. Would you explain how the programmatically for a different spreadsheet set up? – Asking Bob Oct 31 '22 at 07:28

2 Answers2

3

Your trigger runs. But it fails on first line of code because you're calling a method that you cannot.

Several problems.

Problem #1

var source_spreadsheet_sheetName = e.source.openById('').getName();

the openById probably does not work because source is already of type Spreadsheet so you can directly call getName on it.

It should be

var source_spreadsheet_sheetName = e.source.getName();

Problem #2

There is no getActiveCell on a Spreadsheet, only on a Sheet.

// if you know the sheet name in advance
let sheet = e.source.getSheetByName(YOUR_SHEET_NAME)
// or if you don't, get the active sheet (and don't use the line before)
let sheet = e.source.getActiveSheet()

let row = sheet.getActiveCell().getRow();
let column = sheet.getActiveCell().getColumn();

Problem #3

if (source_spreadsheet_sheetName == ''

A sheet name cannot be empty so this will never be true

ValLeNain
  • 2,232
  • 1
  • 18
  • 37
0

source_spreadsheet == '' cannot be true because it's the name of a spreadsheet

Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Sorry for the bad variables name, it's for the sheet name I thought. Because the first line, I try to get the sheet name of source spreadsheet. – Asking Bob Oct 31 '22 at 02:47