onEdit() does not trigger if using VLOOKUP

Question

-I have a spreadsheet named "Locations".

-Column A of that sheet has list data validation that are found on "Projects" spreadsheet of the same file.

-I have IFERROR/VLOOKUP function on various columns on "Locations" to automatically fill those cells

-The data on "Projects" is linked to another Google Sheet file using IMPORTRANGE. That google sheet file is a master list that is constantly changed and multiple sheets are linked to.

**What I am trying to achieve is once a value on column A of "Locations" is selected and VLOOKUP takes place on various columns, I want those cells to copy paste special values only. I was able to have the code to work (listed below). But if I have 5000 rows, then that will be a pain to manually change the variables. Any simpler script to achieve this?

I am not really a programmer so any help from the expert will be appreciated.

I've tried onEdit Range but doesn't seem to recognize the VLOOKUP as an edit event.

function onEdit(e) {
var sheet = SpreadsheetApp.getActiveSheet();
  
var r3 = sheet.getRange("C3").getValue();
if (r3 != 0){
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('C3:E3').activate();
  spreadsheet.getRange('C3:E3').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('J3:M3').activate();
  spreadsheet.getRange('J3:M3').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('Q3').activate();
  spreadsheet.getRange('Q3').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('S3').activate();
  spreadsheet.getRange('S3').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);}
  
var r4 = sheet.getRange("C4").getValue();
if (r4 != 0){
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('C4:E4').activate();
  spreadsheet.getRange('C4:E4').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('J4:M4').activate();
  spreadsheet.getRange('J4:M4').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('Q4').activate();
  spreadsheet.getRange('Q4').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('S4').activate();
  spreadsheet.getRange('S4').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);}

var r5 = sheet.getRange("C5").getValue();
if (r5 != 0){
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('C5:E5').activate();
  spreadsheet.getRange('C5:E5').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('J5:M5').activate();
  spreadsheet.getRange('J5:M5').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('Q5').activate();
  spreadsheet.getRange('Q5').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('S5').activate();
  spreadsheet.getRange('S5').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);}

}

PySeeker PySeeker · Accepted Answer · 2019-08-24T18:10:32

Could you explain your question a bit more? I tried your code, but it does not copy any value in the spreadsheet.

What you try to do, is that a user is selecting a cell and then the function copys the vlaues automatically?

Or just, when he is using Vlookup? Is that also a function? OnEdit trigger just works on user events.

function onEdit(e) {
var sheet = SpreadsheetApp.getActiveSheet();

  var actRng = e.source.getActiveRange();
  var editColumn = actRng.getColumn();
  var index = actRng.getRowIndex();
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
  var updateCol = headers[0].indexOf("Project List"); updateCol = updateCol+1;
  if (index > 1 &&editColumn == updateCol) { // only timestamp if 'Last Updated' header exists, but not in the header row itself!
    var cell1 = sheet.getRange(index, updateCol+2, 1, 3)
    #                          row, column, num of rows, num of colums
    cell1.setValue("Test1");
    var cell2 = sheet.getRange(index, updateCol+10, 1, 4)
    cell2.setValue("Test2");
    var cell3 = sheet.getRange(index, updateCol+17, 1, 1)
    cell3.setValue("Test3");
  }
}

onEdit() does not trigger if using VLOOKUP

1 Answers