0

This code works as intended when you manually change a cell.

function onEdit(event) {
// assumes source data in sheet named Prepsheet
// target sheet of move to named TopUp Required

var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = event.source.getActiveSheet();
var r = event.source.getActiveRange();
if(s.getName() == "PrepSheet" && r.getColumn() == 15 && r.getValue() == 0) {
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("TopUp Required");
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).copyTo(target);

 }
}

My problem is I am using =sum(A2-B2) to change the cell. if cell then =0 it should run the script, but doesn't because the cell wasnt manually changed.

How could I modify code to do this? Or how would you do it

Appreciate any help

Tom Sawkins
  • 351
  • 1
  • 12

2 Answers2

1

The workaround would be using the installable onChange trigger in combination with a second spreadsheet with an =IMPORTRANGE() function.

Explanation:

While an onEdit trigger does not detect any cell content update caused by a cell formula at all, onChange can detect certain changes, such as the update occuring through the =IMPORTRANGE() formula.

Workflow:

  1. Create a second, empty spreadsheet
  2. Assign to a cell in a sheet of the second Spreadsheet the formula =IMPORTRANGE(IMPORTRANGE(spreadsheet_url, range_string), whereby spreadsheet_url is the URL of the first spreadsheet that contains all your data and formulas (e.g. =sum(A2-B2)) and range_string the range of interest (e.g. "PrepSheet!O1:O" for column 15 in sheet PrepSheet)
  3. Attach a script to the second spreadsheet, rather then to the original spreadsheet
  4. Use Scriptproperties to store cell old cell values and compare to the new values - in order to detect which cell has been edited

Sample:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("name"); // the name of the sheet containing the =IMPORTRANGE() formula
var origin=SpreadsheetApp.openById('ID of original spreadsheet');
var s=origin.getSheetByName("PrepSheet");
var lastRow=s.getLastRow();
var range=sheet.getRange(1,1,lastRow,1); //the column into which you imported column 15 from the original spreadsheet

function initialSetUp(){//run this function only once, unless your range of interest changes
 //change if required
  var values=range.getValues(); 
  for(var i=0;i<values.length;i++){
      PropertiesService.getScriptProperties().setProperty('values '+i, values[i][0]);
  }
}

function triggeredOnChange() {
  var values=range.getValues(); 
  var numColumns = s.getLastColumn();
  var targetSheet = origin.getSheetByName("TopUp Required");
  for(var i=0;i<values.length;i++){
    var scriptValue=PropertiesService.getScriptProperties().getProperty('values '+i);
    var newValue=values[i][0];    
    if(newValue!=scriptValue && newValue==0){ 
       Logger.log(scriptValue);
        Logger.log(newValue);
      var row = i+1;
      var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
      s.getRange(row, 1, 1, numColumns).copyTo(target);
    }
  }
}
ziganotschka
  • 25,866
  • 2
  • 16
  • 33
  • Problem here is [importrange refreshes only every 30 minutes](https://stackoverflow.com/questions/49268415/), so you might as well as just use a time trigger on the spreadsheet rather than creating a elaborate setup between two spreadsheets to accomplish the same. However onChange triggering on formula import is a original discovery >upvoted – TheMaster Apr 14 '20 at 14:53
  • @TheMaster: Thank you for your feedback! This is interesting, because when I use `IMPORTRANGE` to import from one spreadsheet on my drive to another one - it takes only few seconds to update. But maybe it takes longer if ones imports from somewhere else? – ziganotschka Apr 14 '20 at 15:03
  • @TheMaster: I did not know about the official refresh time, just my own testing experience :-) – ziganotschka Apr 14 '20 at 15:16
  • @zig Testing, I can confirm, but it seems a manual update in the master spreadsheet seems necessary. For example, A1=now() and B1=now()-"today date" and B1 is imported from master to slave. Then slave doesn't update B1 in seconds. Coming back to my original comment, if a manual edit is necessary in the master in any case, we can listen to `onEdit` in the master instead of this elaborate master>slave setup. What do you think? – TheMaster Apr 14 '20 at 15:23
  • @TheMaster I am not sure if I follow you. My point was that when an update is being made with a formula (including the `=importrange()` formula) or script, it is not detected by the `onEdit` trigger. However, an update by formula or script is detected by `=importrange()` and `=importrange()` in its turn is detected by `onChange`. But when it comes to a time trigger, this is certainly also a good option depending on the situation - unless there has been more then one update between two trigger runs. – ziganotschka Apr 14 '20 at 15:51
  • I meant why use `importrange` at all? Why the second spreadsheet? OP's question was he was using `=sum(A2-B2)` in say `C2`. C2 change will not be detected by onEdit or onChange. But for C2 to change, A2 or B2 must be changed manually. This can be detected by onEdit and you can use scriptProperties as shown in your answer. – TheMaster Apr 14 '20 at 15:59
  • I was not sure either `A2` is being set manually - it could also be set by a script or another formula. – ziganotschka Apr 14 '20 at 16:09
0

onEdit triggers only fire on user edits.

onEdit() reference

Using all event object parameters should result in faster execution.

function onEdit(e) {
  var sh=e.range.getSheet();  
  if(sh.getName()=="PrepSheet" && e.range.columnStart==15 && e.value==0) {
    var tsh=e.source.getSheetByName("TopUp Required");
    var target=tsh.getRange(tsh.getLastRow() + 1,1);
    sh.getRange(e.range.rowStart,1,1,sh.getLastColumn()).copyTo(target);
  }
}

But of course, none of this will change the fact that onEdit triggers only occur for user edits.

Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Cooper thanks anyway. what about combining an if statement to run a script =if(H2=0,scriptname(); ? something like adding date with if statement – Tom Sawkins Dec 17 '19 at 07:43
  • You can try it. But my guess is that it will not work. – Cooper Dec 17 '19 at 07:49
  • Cooper ok didnt work - could onEdit run the above script if i edited H manually - it would look in column 15 and if there is a 0 copies line to Topup Required ? – Tom Sawkins Dec 17 '19 at 09:12