Apps Script onEdit call function

Question

I am trying to trigger a function (I named it "importData") when a user updates cell B2 in a sheet. The importData function works when run it manually and when I create a generic onEdit trigger in the view menu, but I want it to be specific to editing that specific cell.

I wrote the following onEdit which works to do everything but call the function that I want. Could anyone point out what I'm doing wrong?

Thank you. The onEdit and the importData follow:

    function onEdit(){
          var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
          var activeCell = ss.getActiveCell();      
          if (activeCell.getRow() == 2 && activeCell.getColumn() == 2) {         
            importData();
          }     
        }


//This function retrieves data from Form Responses.
//It filters based on user input.

function importData() {

  var sourceSS = SpreadsheetApp.openById("1RCObWVpLUyRJsvHLfA89GNUBrKQIvzt_6Sp7Ftq8Gm4");
  var sourceSheet = sourceSS.getSheets()[2];
  var sourceRange = sourceSheet.getRange('C2:I');
  var values = sourceRange.getValues();

  var targetSS = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = targetSS.getActiveSheet();
  var personSearchRange = targetSheet.getRange("B2");
  var projectSearchRange = targetSheet.getRange("D2");
  var taskCompletedSearchRange = targetSheet.getRange("B3");

  var filterValues = new Array();
  for (var i = 0; i < values.length; i++){
    if (values [i][4].match(taskCompletedSearchRange.getValue()) && values [i][0].match(projectSearchRange.getValue()) && values [i][5].match(personSearchRange.getValue())){
      filterValues.push(values[i])
    }
}

  var clearRange = targetSheet.getRange(6, 1, targetSheet.getLastRow(), targetSheet.getLastColumn());
  clearRange.clearContent();

  var targetRange = targetSheet.getRange(targetSheet.getLastRow()+1, 1, filterValues.length, filterValues[0].length);
  targetRange.setValues(filterValues);

}

Thank you. I tried that a few ways and still not executing the function. — Paul Enestvedt
no errors and no logs. When I view the log, it says "No logs. Use logger API to add logs to your project." I'm not sure what that means. In any case, it just does nothing. Everything stays as it was except for my edit of B2. — Paul Enestvedt
Try naming the function something else and use an installable onEdit trigger. Simple triggers will not work on scripts that require authorization. — Cooper

Paul Enestvedt Paul Enestvedt · Accepted Answer · 2018-02-07T19:49:11

Per Cooper's comment, onEdit doesn't work with my function because I am accessing another spreadsheet. That requires authorization. Unfortunately, there is no error, it just won't do it. I followed Cooper's suggestion and used an installable trigger which is blunt but ... Thanks for your comments/suggestions.

Apps Script onEdit call function

1 Answers