Is it possible? Running onEdit/onChange from one spreadsheet when the Edit occurs on another spreadsheet

Question

I have 3 spreadsheets to store data for a sign in/sign out sheet. the sign in/out sheet(sheet1), the time tracker sheet(sheet2) and the mirror sheet(sheet3) The mirror sheet is to compare new list with old list. The issue I'm wanting to solve is to make it so that the code isn't on sheet1. I want it on sheet2, but the issue is the sheet that has all the triggers and stuff is sheet1. But because it's what people will be using, I don't want them to be able to find the code and mess with it, so I want sheet2 to be controlling everything with the code. Here is the part of the code that I think is relevant to this issue. it works fine when it's on Sheet1, but putting it on sheet2 and it doesn't work. I put setActiveSpreadsheet in hopes it is all that was needed, but apparently not.

function onEdit(e) {
  var sos = SpreadsheetApp.openById("sheet1 id");
  var sas = SpreadsheetApp.setActiveSpreadsheet(sos);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s1 = ss.getActiveSheet();
  var row = getCurrentRow()
  var Location = getValue('C' + row)
  var LocationA = getValue('A' + row)
  var LocationB = getValue('B' + row)
  var passiveRow = findRows(1,LocationA,2,LocationB,5,"");
  if(Location !== 'HOME' && Location !== "" && Location !== 'LOCATION' && passiveRow == "") {
  addRecord(LocationA, LocationB, Location, new Date());
  }

  else if(Location !== 'HOME' && Location !== "" && Location !== 'LOCATION'  && passiveRow !== "") {
    setValue('E' + passiveRow, new Date());
    setValue('F' + passiveRow, getDaysHoursAndMinutes(getValue('D' + passiveRow),getValue('E' + passiveRow)));
    addRecord(LocationA, LocationB, Location, new Date());    
  }

  else if(Location === 'HOME' && passiveRow !== "") {
    setValue('E' + passiveRow, new Date());
    setValue('F' + passiveRow, getDaysHoursAndMinutes(getValue('D' + passiveRow),getValue('E' + passiveRow)));
  }
}

///Functions to automatically add and replace dropdown boxes
function onChange(e){
  checkAndPlace();
  settingMirror();
  var sos = SpreadsheetApp.openById("sheet1 id");
  var sas = SpreadsheetApp.setActiveSpreadsheet(sos);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s1 = ss.getActiveSheet();
  var FirstEmptyA = getFirstEmptyRow('A:A');
  var FirstEmptyC = getFirstEmptyRow('C:C');
  var dif = Math.abs(FirstEmptyA - FirstEmptyC);
  var EmptyArow =s1.getRange(FirstEmptyA ,3)
  var validationList = SpreadsheetApp.newDataValidation().requireValueInList(['HOME','SCHOOL','BANK','MALL'], true).build()
  if(FirstEmptyA - FirstEmptyC < 0){
    var rangedif =s1.getRange(FirstEmptyA+1,3,dif)
    rangedif.clearDataValidations();
    rangedif.clearContent();
  }

  else if(FirstEmptyC - FirstEmptyA < 0) {
    var rangedif =s1.getRange(FirstEmptyC +1,3,dif)
    rangedif.setDataValidation(validationList); 
    rangedif.setValue('OASIS');
  }
}

function getFirstEmptyRow(range) {
  var spr = SpreadsheetApp.getActiveSpreadsheet();
  var column = spr.getRange(range);
  var values = column.getValues();
  var ct = 0;
  while ( values[ct] && values[ct][0] != "" ) {
    ct++;
  }
  return (ct);
}

So to sum it up, I'm trying to make it so that one spreadsheet's onEdit code can trigger when another spreadsheet is edited. Without any code being on the other spreadsheet.

Rubén Rubén · Accepted Answer · 2019-05-19T22:34:08

That is impossible, but...

You can add an installable onEdit(onChange trigger on the "other" spreadsheet and make it do the required changes on "this" spreadsheet.

Is it possible? Running onEdit/onChange from one spreadsheet when the Edit occurs on another spreadsheet

1 Answers