Google-sheets – Change a working function from onOpen() to onEdit()

google sheetsgoogle-apps-script

I currently have a working function that uses the onOpen() trigger to run. I am needing to make this an on edit instead of onOpen function so that a when a change is made in my master sheet, the child sheets get updated with the new information.

I need the child sheet to run the function as soon as any data is entered into the master sheet column a. I know after reading similar questions that I need to make an event trigger, however, I am not sure how this is done. I have gone into the "resources" tab and attempted to set an onEdit trigger, but it seems like if the current spreadsheet is edited then the function will be ran. If I need it to run when the master sheet is edited, should I move the script to that sheet?

Best Answer

Yes, if you want a script to be triggered by updates to spreadsheet A, then it should be bound to that spreadsheet. It can still access the other spreadsheets by their URL or Id. For example, this function copies the cells F2:H6 of the present sheet to the cells A1:C5 of another one.

function copyStuff() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName('MasterSheet1')
  var ss2 = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/...');
  var targetSheet = ss2.getSheetByName('Sheet1');
  targetSheet.getRange("A1:C5").setValues(sourceSheet.getRange("F2:H6").getValues());
}

Related Solutions

Google-sheets – Changing an onEdit function to run onOpen

Rename your script to a normal function, something like setColor. Rewrite the script to contain all the proper references. After that, you can select the proper trigger in the script editor:

enter image description here

Select the onOpen trigger for the function.

The second this you can do, is to create a menu item in the active spreadsheet, like this:

var menu = [{name: "Set Color", functionName: "setColor"}];
ss.addMenu("Extra", menu);

If you add it to the existing script, it will be added on opening of the file. While your at it, use the following script:

function foramtName() {
  var ss = SpreadsheetApp.getActiveSheet();
  var mRows = ss.getMaxRows()-1, mCols = ss.getMaxColumns();
  var cRange = ss.getRange(2, 1, mRows, mCols);

  if (ss.getName() == "e404") {
    var aData = ss.getRange(2, 4, mRows, 4).getValues(); 
    var colors = new Array();    
    for(i in aData) {
      colors[i] = new Array();
      for(var j=0; j<mCols; j++) {
        if(aData[i].reduce(function(a, b) {return a + b}) === 0) {
          colors[i][j] = 'red';
        } else {
          colors[i][j] = 'white';
        }
      }       
    }
    cRange.setBackgrounds(colors);
  }
}

Google-sheets – How to use triggers with functions that require parameters from the spreadsheet

How to use triggers with functions that require parameters from the spreadsheet?

AFAIK, you cannot. The solution is to create one single function, for example called RefreshAll, inside which you call all the wrapper functions (GetValue) containing the actual parameters which need to be passed to the function that actually does the job (RealFunctionThatDoesTheJob).

Note: Sorry if this sounds complicated. If you only have one set of parameters (as opposed to 3, in the example below), you can do away with GetValue and call RealFunctionThatDoesTheJob directly from within the function that is being triggered (RefreshAll).

Lastly, to have your triggers send to your spreadsheet those values that are dependent on parameters, you need to have the script push the values to the spreadsheet using (currentSheet.getRange(Row,Column).setValue(Value);)

Here is the example:

function RefreshAll() {

  var currentSheet = SpreadsheetApp.openById("ID").getSheetByName("MySheet");

  var Value1 = GetValue1();
  currentSheet.getRange(Row,Column).setValue(Value1);

  var Value2 = GetValue2();
  currentSheet.getRange(Row,Column).setValue(Value2);   

  var Value3 = GetValue3();
  currentSheet.getRange(Row,Column).setValue(Value3);

}

function GetValue1() {
  return RealFunctionThatDoesTheJob("Parameter1","Parameter2","Parameter3");
}

function GetValue2() {
  return RealFunctionThatDoesTheJob("Parameter4","Parameter5","Parameter6");
}

function GetValue3() {
  return RealFunctionThatDoesTheJob("Parameter7","Parameter8","Parameter9");
}

Best Answer

Related Solutions

Google-sheets – Changing an onEdit function to run onOpen

Google-sheets – How to use triggers with functions that require parameters from the spreadsheet

Related Topic