Run Google Sheets script when formula updates cell

0
votes

After much searching, I have solved the first part of my problem: I found the below script which copies data from one range and adds it to another sheet.

However, the range that it is copying from is going to be automatically updating via a formula. So, my next challenge is - how do I get the script to run when the cell changes?

I believe there is a way to 'watch cells' for changes, but I'm really not very technical so I haven't been able to figure it out!

Potentially added complication - I believe 'on edit' scripts only run when the spreadsheet is open, is that right? If so, I'm also going to need to figure out how to get the script to run to check for new values on timed intervals.

Here's my current script: 

 function moveValuesOnly() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var source = ss.getRange("Sheet1!F1:H3");
    var destSheet = ss.getSheetByName("Feuil2"); // Déterminer l'emplacement de la première ligne vide. var destRange = destSheet.getRange(destSheet.getLastRow()+1,1); source.copyTo (destRange, {contentsOnly: true}); source.clear (); 
    }
1
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-apps-script-addontriggers
onEdit runs even if the spreadsheet is closed, but it works only if the edit has been manual (through typing), rather than a formula update. If you want your script to run automatically whenever the range is being updated - you need a workaorund with =importrange and onChange - see here or here. – ziganotschka
Maybe it would be easier for you to run the script manually / on time-driven trigger, but with the dynamically retrieved updated range definition? – ziganotschka
Thanks a lot for the input! I think the time-driven trgger is probably the best method, as would be great to keep it on one spreadsheet instead of using the importrange (which I think has a built in delay when sheet is closed). Any chance you know of a script that might be able to achieve that? – Ben Swanson

1 Answers

0
votes

How to run a script on time-driven trigger:

  • You can use the script you already have
  • You can bind to it a time-driven trigger by
    • Going on Edit -> Current Project's triggers

enter image description here

  • Selecting create new trigger

enter image description here

  • Specifying the function to which you want to bind the trigger
  • Specifying that the trigger shall be time-driven
  • Select type of time based trigger and interval as desired
  • Click on Save

enter image description here