I am wanting to run a function in script apps that automatically looks for a word in a column and automatically archives that row to another tab

Question

I want to automatically have a row archive to another tab if column D contains the word Deprovisioned. I am not manually editing the cell, I am copy and pasting from a CSV export so the columns come pre-filled with information. I tried the below script, but received an error saying undefined. I am not sure if this has something to do with the fact that I am not manually editing anything on the sheet?

Below is the error I get

    var s = event.source.getActiveSheet();  undefined.

Below is the script I am using

    function onEDIT(event) {                    
    // assumes source data in sheet named Needed                    
    // target sheet of move to named Acquired                   
    // test column with yes/no is col 4 or D                    
    var ss = SpreadsheetApp.getActiveSpreadsheet();                 
    var s = event.source.getActiveSheet();                  
    var r = event.source.getActiveRange();                  
    if(s.getName() == "Copy of Zoominfo Usage" && r.getColumn() == 4 && 
    r.getValue() == "DEPROVISIONED") {                  
    var row = r.getRow();                   
    var numColumns = s.getLastColumn();                 
    var targetSheet = ss.getSheetByName("ZOOMINFO ARCHIVE");                    
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);                 
    s.getRange(row, 1, 1, numColumns).moveTo(target);                   
    s.deleteRow(row);                   
    }                   
    }

Got it. My question is why am I getting the error undefined? It is referencing the source.TypeError: Cannot read property 'source' of undefined onEDIT @ Code.gs:6 — Laura W., Feb 10 '22 at 18:12
I wasn't and then I did because it wasn't doing anything, Im guessing I might need to re do the copy and paste of the sheet back on there so it can recognize the edit? I have not done that since setting up the on edit. — Laura W., Feb 10 '22 at 18:51
If you run it from the script editor then you must supply the event object — Cooper, Feb 10 '22 at 18:53

score 1 · Answer 1 · answered Feb 10 '22 at 18:52

1

function onEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == "Copy of Zoominfo Usage" && e.range.columnStart == 4 && e.value == "DEPROVISIONED") {
    let tsh = e.source.getSheetByName("ZOOMINFO ARCHIVE");
    let tgt = tsh.getRange(tsh.getLastRow() + 1, 1);
    sh.getRange(e.range.rowStart, 1, 1, sh.getLastColumn()).moveTo(tgt);
    sh.deleteRow(e.range.rowStart);
  }
}

answered Feb 10 '22 at 18:52

Cooper

59,616
6
23
54

Where am I putting that? Am I replacing the current code I have with what you put? If so, that still did not work. Or am I supposed to insert your code into a part of the code I am currently using? – Laura W. Feb 10 '22 at 19:11
I would replace you code since it's named incorrectly to begin with. Unless you created an installable trigger – Cooper Feb 10 '22 at 19:19
It is still not working for some reason. Do you want the link to the spreadsheet? – Laura W. Feb 10 '22 at 20:17
It's working for me. Check you executions you're probably getting errors – Cooper Feb 10 '22 at 20:33
This is a real trivial onEdit so not much to go wrong. I put a datavalidation in column 4 to generate the edits and appropriate e.values with. – Cooper Feb 10 '22 at 20:35

Rubén · Answer 2 · 2022-04-07T00:44:50.503

If you are trying to use simple edit trigger instead of onEDIT you should use onEdit.

An edit trigger, simple or installable will be triggered only when a user uses the Google Sheets UI to edit a cell or range. A paste will trigger the script but the script is not prepared to manage pasting multiple cells

r.getValue() returns only the value of top-left cell
r.getRow() returns only the row of the top row.

It might work when pasting a single row.

It will not work when calling the script from the script editor, as the function requires the event parameter, which is provide automatically when the function is called by the trigger but not when it's called form the script editor.

Please bear in in mind that the active sheet and active range will be the range that has being edited by the user using the Google Sheets UI. When running the function from the script editor, if the Google Apps Script project is opened from the bounded spreadsheet they will be the sheet / range that are active for the user running the function, but if Google Apps Script project were opened from other means like the https://script.google.com then the the methods calling the active sheet / range might return the first sheet / first cell or throw an error.

References

https://developers.google.com/apps-script/guides/triggers

I am wanting to run a function in script apps that automatically looks for a word in a column and automatically archives that row to another tab

2 Answers2

Linked

Related