I need to write an onEdit function for a Google Sheets script

Question

This is the script I want to trigger:

function moveValuesOnly () {


  var ss = SpreadsheetApp.getActiveSpreadsheet ();

  var source = ss.getRange ("Copy!A1:N200");

  var destSheet = ss.getSheetByName("Paste");

  var destRange = destSheet.getRange(destSheet.getLastRow()+1,1);

  source.copyTo (destRange, {contentsOnly: true});

}

**// I want moveValuesOnly to trigger once Cell A10 in the Validation tab changes to '2'**

I would greatly appreciate any help as I am a bit green at the moment.

Kind regards,

Brendon

The above is not a trigger function, its only a function I have to still run. By triggering it I will be able to run it remotely from Appsheet, but I do not know how to write the onEdit script for it? Thanks for helping. — Brendon Shields
– Brendon Shields, Commented Apr 21, 2020 at 5:14

Raserhin · Accepted Answer · 2020-04-21 09:21:14Z

0

I would suggest to have a read at Simple Triggers and Installable Triggers.

Basically you can create any function and make it trigger using the ScriptApp class and those are called installable trigger.

But there are special functions that don't need to go through ScriptApp to get triggered, you just need to change the name of the function, and that would start enabling the trigger of those functions.

Simple trigger

Just change the name of the function so it will automatically start triggering.

function onEdit(e) {


  var ss = SpreadsheetApp.getActiveSpreadsheet ();

  var source = ss.getRange ("Copy!A1:N200");

  var destSheet = ss.getSheetByName("Paste");

  var destRange = destSheet.getRange(destSheet.getLastRow()+1,1);

  source.copyTo (destRange, {contentsOnly: true});

}

Installable Trigger

Create another function to execute on time to create the trigger

function moveValuesOnly(e) {


  var ss = SpreadsheetApp.getActiveSpreadsheet ();

  var source = ss.getRange ("Copy!A1:N200");

  var destSheet = ss.getSheetByName("Paste");

  var destRange = destSheet.getRange(destSheet.getLastRow()+1,1);

  source.copyTo (destRange, {contentsOnly: true});

}

function createTrigger(){

  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('moveValuesOnly')
      .forSpreadsheet(ss)
      .onEdit()
      .create();

}

You have specified in your code that only want to trigger when the cell is A10, but this is impossible as of right now. You need to create an if statement making sure the range being edited is the one you care about.

Look at the event objects because that would be input parameter of the trigger functions (e). That object has information about the range and spreadsheet being edited.

Basically make a condition with the e object

function onEdit(e) {
  var range = e.range;
  var newValue = e.value;
  var oldValue = e.oldValue;

  var sheet = range.getSheet();

  if(sheet.getName() == "<Your sheet name>" && newValue == 2 && range.getColumn() == 1 && range.getRow() == 10){
    // Browser.msgBox("Edit entered condition");
    //
    // Do your stuff here when the condition is satisfied 

  } 
}

edited Apr 21, 2020 at 9:21

answered Apr 21, 2020 at 7:40

Raserhin

2,7061 gold badge12 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Brendon Shields Over a year ago

Thanks so much for that Raserhin. To clarify, once cell A10 changes to the value '2' it needs to trigger that script. How do I set that range and condition?

Raserhin Over a year ago

Basically you will need to build the condition yourself with the event object I left you an example with value 2 for A10 cell, insert your sheet name and you will be good to go. The onEdit trigger will always execute you can only control the flow inside the trigger.

Brendon Shields Over a year ago

Hi @Raserhin, this is where I am at the moment then:

Brendon Shields Over a year ago

function onEdit(e) { var range = e.range; var newValue = e.value; var oldValue = e.oldValue; var sheet = range.getSheet(); if(sheet.getName() == "Appsheet" && newValue == 2 && range.getColumn() == 1 && range.getRow() == 10){ Browser.msgBox("moveValuesOnly"); } }

Brendon Shields Over a year ago

TypeError: Cannot read property 'range' of undefined (line 2, file "Trigger1"

|

Collectives™ on Stack Overflow

I need to write an onEdit function for a Google Sheets script

1 Answer 1

Simple trigger

Installable Trigger

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Simple trigger

Installable Trigger

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related