CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Antor Cha
Antor Cha

Reputation: 83

Run a script every time there is a change in a cell

I want to run this sript every time there is a change in notes!C4 and automatically copy the value to notes!D4 cell

function Copy() {
  var ss = SpreadsheetApp.getActive().getSheetByName("notes") ;
  ss.getRange('notes!C4').copyTo(ss.getRange('notes!D4'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
}

Update 1

If this it can't be done, I have in another spreadsheet a script that copies the link http://openinsider.com/screener?s= every time I run it to L2 cell of that sheet.

I'm taking that value with an =ImportRange to the actual spreadsheet. How can I copy that link to notes!D4 that's in another spreadsheet to the actual one?

function Refresh() {
  var ss = SpreadsheetApp.getActive().getSheetByName("sheet1") ;
  var cell = ss.getRange("L2");
  cell.clearContent();
  SpreadsheetApp.flush();
  Utilities.sleep(5000); // You have 5 second to check that the cell has cleared
  cell.setValue('http://openinsider.com/screener?s='); 
}

Update 2

I've tried this, the first time the script was charging but it didn't make anything. I'm new to google scripts and I don't know how to make it working.

function onEdit(e) { 

  if (e.range.getA1Notation() === 'C4') {
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("notes").getRange('D4').CopyPasteType.PASTE_VALUES;
  }
}

Upvotes: 1

Views: 6003

Answers (1)

Kristkun
Kristkun

Reputation: 5953

As mentioned by @Cooper, you just need to use onEdit(e) Simple Trigger to run your script whenever there is a cell being modified.

I will just refer on your latest update, you can refer to this sample code:

function onEdit(e) {
  var ss = e.source;
  var cell = e.range;

  if(cell.getA1Notation() === "C4" && ss.getActiveSheet().getName() == "notes"){
    ss.getActiveSheet().getRange("D4").setValue(e.value);
  }
}

What it does?

  1. Get the spreadsheet object using Google Sheets events source parameter
  2. Get the range object using Google Sheets events range parameter
  3. Check if the modified cell is in sheet notes and in cell C4, I get the sheet object using Spreadsheet.getActiveSheet() method and get its sheet name using Sheet.getName(). This will make sure that your function will only run when Sheet notes!C4 was modified.
  4. Set the value of the cell D4 using Range.setValue(value). To maximize the event object, I used value parameter in Google Sheets events.

Additional Tips:

Please be mindful of the methods available in each classes/objects that you are using. For example in this code:

SpreadsheetApp.getActiveSpreadsheet().getSheetByName("notes").getRange('D4').CopyPasteType.PASTE_VALUES;

Upvotes: 4

Related Questions