Google sheets move row depending on value AND edit values

Question

I've been reading answers on Stack for ages and finally decided to join! So here is my first question:

I found a google apps script here for google sheets that moves a row to another sheet depending on a cell value. This is great. I have tested it and it is working for me with a couple of alterations - first to use a checkbox, and second to copy all columns excluding the 1st column with the checkbox.

However, for it to be really useful to me, what I'd like is to be able to edit a couple of values before adding the row to the new sheet.

So it would be like this:

1. if Checkbox = true, then copy row, all columns excluding checkbox
2. Set value of 'location' to 'London' (column 8 for example)
3. Set value of 'date' to today (column 12 for example)
4. Add edited row to new sheet at the bottom

Can anyone help me to achieve this? I'm guessing it must be quite simple but I am new to apps script so don't know how I'd do it.

Here is the basic script for moving the row that I found from a 2011 post here:

function onEdit(event) {
  // assumes source data in sheet named Needed
  // target sheet of move to named Acquired
  // test column with checkbox is col 1 or A
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "Needed" && r.getColumn() == 1 && r.getValue() == true) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Acquired");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 2, 1, numColumns).copyTo(target);
  }
}

Hope someone can help. Thanks in advance!

Answer 1

Try this code, please:

function onEdit(event) {
  // assumes source data in sheet named Needed
  // target sheet of move to named Acquired
  // test column with checkbox is col 1 or A
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "Needed" && r.getColumn() == 1 && r.getValue() == true) {
    var row = r.getRow();
    var numColumns = s.getLastColumn() - 1;
    var data = s.getRange(row, 2, 1, numColumns).getValues();
    data[0][8] = 'London';
    data[0][12] = new Date();
    var targetSheet = ss.getSheetByName("Acquired");
    targetSheet.getRange(targetSheet.getLastRow() + 1, 1, 1, numColumns).setValues(data);
  }
}

Answer 2

Please, try this and let me know how you go. :)

function onEdit(event) {
//assumes source data in sheet named Needed
//target sheet of move to named Acquired
//test column with checkbox is col 1 or A
var spreadsheet = event.source;
var sheet = spreadsheet .getActiveSheet();
var range = spreadsheet.getActiveRange();
var sheetName = sheet.getName();
var col = range.getColumn();
var value = range.getValue();

if (sheetName === "Needed" && col === 1 && value === true) {
var row = range.getRow();
var numColumns = sheet.getLastColumn();
var targetSheet = spreadsheet.getSheetByName("Acquired");
var lastRow = targetSheet.getLastRow();
var target = targetSheet.getRange(lastRow + 1, 1, 1, numColumns - 1);
//added number of rows and columns to select a range instead of a single cell
var values = sheet.getRange(row, 2, 1, numColumns - 1).getValues();

values[0][7] = values [0][1];
//access value of row 1, column 8 and set to value of row 1, column 2
//the values are captured in an array and are zero-based. The first column is column 0 for example.

values[0][11] = new Date();
//access value of row 1, column 12 and set to date-time value of 'now'.

target.setValues(values);
}
}