Copy a single row onEdit of Column A to "New" to another Google spreadsheet - NOT tab in the same sheet

Viewed 176

I want to be able to select "New" from either a drop down menu or check box which will then copy that row only to another sheet in another file on google drive. I can get the script to work within the "same" spreadsheet, but having trouble getting it to copy to another external spreadsheet.

function addSingleJobtoDriver1Sheet(e) {
  // assumes source data in sheet named driver 1
  // target sheet of move to named todays work
  // getColumn with check-boxes is currently set to colu 1 or A
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "Driver 1" && r.getColumn() == 1 && r.getValue() == "New") {
    var row = r.getRow();
    var numColumns = s.getLastColumn()1;
    var spreadSheet = SpreadsheetApp.openById("1-2Kuv9XGD-srJ_TwZpvCCxiW0qghyl12IelpdeNWlIc");
    var targetSheet = ss.getSheetByName("Todays Work");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);

  } 
}

I already have existing onEdits, so I assume I do what I have already done with other onEdit scrips and add it like I have here eg: addSingleJobtoDriver1(e)

function onEdit(e) {
  activeCheckboxes(e)
  moveToField(e)
  addSingleJobtoDriver1(e)

}

function activeCheckboxes(e) {
  var sheet = e.range.getSheet();
  if (sheet.getName() !== 'NEW JOB FORM') return; 
  if (e.range.getColumn() !== 5) return;
  if (e.value == 'TRUE') {
    switch (e.range.getRow()) {
      case 28:
        RecordSingleJobEntry()
        break;
      case 29:
        sheet.showRows(31,5);
        break;
      case 33:
        AddRecurringJobToDatabase()
        break;
      case 4:
        lookupJobNo()
        break;
      case 6:
        ClearForm()
    }
  } else {
    switch (e.range.getRow()) {
      case 27:
        sheet.hideRows(30,5);
        break;
    }
  }
}

Any help is much appreciated. I'm pulling my hair out.

Thank you :)

1 Answers

This function requires an installable onEdit trigger since it requires permission to write to another file. Also note you cannot call this function directly from the script editor because it requires an event object to populate the parameter e.

enter image description here

Sorry I gave you the wrong code

Source Sheet:

enter image description here

Related