CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Mariusz
Mariusz

Reputation: 9

I would like to update my script form google sheets to get range of active row

Good morning everyone :),

I'm struggling withe a problem for some time and I was wondering that anyone can help me here.

I wrote some script that is copying and pasting rows from one sheet to another when Q column checkbox is TRUE. Please see code below : 

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  if(ss.getActiveCell().getColumn() == 16 && ss.getActiveCell().getRow() > 1 && ss.getActiveCell().isChecked()) {

  var source = ss.getRange("TRACKER!B2:N2");
  var destSheet = ss.getSheetByName("ARCHIVE");
  var destRange = destSheet.getRange(destSheet.getLastRow()+1,2);
  source.copyTo(destRange,{contentsOnly: true});
  source.clear();

  }
}

My QQ is how to change last script input :

source.clear();

That instead of clearing it just delete whole row, and also how to change

var source = ss.getRange("TRACKER!B2:N2");

That B2:N2 will be change into B?:N? where "?" depending which row in column Q was selected.

Thank You in advance

Have a good day

Mariusz

Upvotes: 0

Views: 242

Answers (2)

Mariusz
Mariusz

Reputation: 9

Hello ADW and thank you for quick reply,

I have update the code like this : 

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  if(ss.getActiveCell().getColumn() == 16 && ss.getActiveCell().getRow() > 1 && ss.getActiveCell().isChecked()) {   
    var source = ss.getRange("TRACKER!B" + ss.getActiveCell().getRow()+ ":N"+ss.getActiveCell().getRow());
    var destSheet = ss.getSheetByName("ARCHIVE");
    var destRange = destSheet.getRange(destSheet.getLastRow()+1,2);
    source.copyTo(destRange,{contentsOnly: true});
    ss.deleteRow(ss.getActiveCell().getRow());    
  }
}

and it seems to work fine now but nevertheless once again thank you for the code it surly help me to solve this query.

Upvotes: 0

ADW
ADW

Reputation: 4247

When the onEdit() trigger fires,

Apps Script passes the function an event object as an argument, typically called e. The event object contains information about the context that caused the trigger to fire.

So you could change your script to:

function onEdit(e) {

  var activeSheet = e.source.getActiveSheet(); // Assumes the active sheet is called 'TRACKER'

  var activeCell = e.source.getActiveCell();

  if ( activeCell.getColumn() == 17 && activeCell.getRow() > 1 && activeCell.isChecked() ) {

    var source = activeSheet.getRange(activeCell.getRow(), 2, 1, 13); // Columns B through N

    var destSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ARCHIVE");

    var destRange = destSheet.getRange(destSheet.getLastRow() + 1, 2);

    source.copyTo(destRange,{contentsOnly: true});

    source.clearContent(); // To clear just the contents and not delete the row

  }
}

Column Q = column 17.

This script assumes that the active sheet with the checkboxes is called 'TRACKER'

Ref docs for onEdit()

Ref docs for getting cells, ranges in spreadsheets and copying and clearing them.

Upvotes: 2

Related Questions