CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets-api
XacliX
XacliX

Reputation: 3

How can I build script with an onEdit Trigger for a specific range, insert note in the cell, with timestamp?

I'm a beginner at coding so any advice or guidance would be entirely helpful. I'm working on a script that uses onEdit () for a specific range/column.

Once the onEdit () triggers with an if statement I'm assuming, a customized dialog prompt will appear to insert a note. After you type the note, it will tag the data validation you selected and insert it as a note on the cell with a timestamp.

Here's a sample sheet I made for this question: https://docs.google.com/spreadsheets/d/12-b4pUK5xKqlLlOKvPxd-UZ__RaJMiEWXHPH5K9GvU4/edit?usp=sharing

Please feel free to ask questions!

 
  
  function onEdit() {
    var ss = SpreadsheetApp.getActiveSheet().getActiveRange();
    var select = ss.getDisplayValue();
       if (select = 'Phone'){
    var ui = SpreadsheetApp.getUi(); 
    var name = Session.getActiveUser();        
    var cell = ss.getDisplayValue()
    var timestamp = Utilities.formatDate(new Date(), "EST", "MMM dd");
    var pnote = ss.getNote()
    Logger.log(timestamp);
    var result = ui.prompt(
      'Would you like to add a short note?',
      '',
      ui.ButtonSet.OK_CANCEL);
    
    // Process the user's response.
    var button = result.getSelectedButton();
    var text = result.getResponseText();
    var notes = [
      [pnote],
      [timestamp +": "+name + ": "+cell +": "+text+'.']
    ];
    if (button == ui.Button.OK) {
      // User clicked "OK".
      ss.setNote(notes);
    } else if (button == ui.Button.CANCEL) {
      // User clicked "Cancel".
      ui.alert('You did not set a note.');
    } else if (button == ui.Button.CLOSE) {
      // User clicked X in the title bar.
      ui.alert('You closed the dialog.');
    }
         
    }     else {
        Logger.log("Cell was out of range");
    }
  }

//Attempt 2 (no luck) would replace the first portion of script up to if statement.
//    var ss = SpreadsheetApp.getActiveSheet().getActiveRange();
//    var column = ss.getColumn();
//    var actionColumn = [3]
//    
//    if (actionColumn.indexOf(column) = 3){

My question is how can I get this script to be triggered only when a data validation is selected in a specific column. Maybe the next work in could be having multiple if statements to customize the dialog questions & notes.

Thank you in advance!

Upvotes: 0

Views: 851

Answers (2)

ReyAnthonyRenacia
ReyAnthonyRenacia

Reputation: 17623

You have to have an if-condition inside onEdit that checks for the number of row, column.

For example you only want the onEdit to trigger on cell F5 (5,6), you can do:

function onEdit(e){
    var range = e.range;
    if(range.getRow() == 5 && range.getColumn() == 6){
        range.setNote('ROW: ' + range.getRow() + " COLUMN: "+ range.getColumn());

      //additional code . . . Do what you want..
    }
}

Just modify the code for other cells,columns,etc.

Upvotes: 1

James D
James D

Reputation: 3152

You can use the e parameter of the onEdit trigger. Read about event objects here

Add the e parameter to the function and then use an if statement to check if the edit happened on the specified column of the specified sheet.

In this example the if is true if the edit happened on column B, col B is index 2 of Sheet1. 

function onEdit(e){
  if(e.source.getSheetName() == "Sheet1" && e.range.getColumn() == 2){

Upvotes: 0

Related Questions