CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Aimée Sim
Aimée Sim

Reputation: 13

Replacing data in a cell when a criteria is met with a list

I'd like to be able to replace the content of a cell with a list.

At the moment, I have a drop down list in the first column that when the user selects Accept, it copies the row, into another sheet and deletes the old row.

However, in the new location, it has copied the drop down list from the previous location (first column) which has over written the new drop down(in the first column on sheet 2) list that contains Complete, Cancel.

I'm trying to either detect the word Accept and over write the file with a list with Complete and Cancel as options or copy the whole row not including the first column.

Below is my current code. I've only started using Google script today so my knowledge in this code is very limited. Everything I've found so far is from google searches and a little bit of luck.

Thanks in advance for any help.

function onEdit(event) 
{
  // assumes source data in sheet named Form responses 1
  // target sheet of move to named Commissions in review
  // 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() == "Form responses 1" && r.getColumn() == 1 && r.getValue() != "") 
  {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Commissions in review");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 2);
    s.getRange(row, 1, 1, numColumns).copyTo(target);
  }

  if(s.getName() == "Commissions in review" && r.getColumn() == 1 && r.getValue() == "Accept") 
  {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Accepted Work list");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
  if(s.getName() == "Commissions in review" && r.getColumn() == 1 && r.getValue() == "Delete") 
  {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    s.deleteRow(row);
  }
}

Upvotes: 1

Views: 127

Answers (1)

Alan Wells
Alan Wells

Reputation: 31300

Can't you just change the column starting point? Right now your range references start with column 1:

var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).moveTo(target);

Maybe try:

var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 2);
s.getRange(row, 2, 1, numColumns-1).moveTo(target);

Target starts in column 2. Range starts in column 2, and the numColumns is subtracted by 1.

Upvotes: 1

Related Questions