1

Looking for help with adapting the following script (or show your version?) to copy cell(s) from Column B Sheet2 (where D contains "NOT_ON_WORKSHEET") (onEdit or not) to the last row of the 4th Column(D) of Sheet1 instead of current 1st Column(A). Tried to use setValue, but can't apply it without errors.

function onEdit(e) {
var ss = e.source,
    sheet = ss.getActiveSheet();
if (sheet.getName() !== 'Sheet2' || e.range.columnStart !== 4 || e.value !== 'NOT_ON_WORKSHEET') return;
e.source.getSheetByName('Sheet1')
  .appendRow(e.range.offset(0, -2, 1, 1)
  .getValues()[0]);
}

Visual explanation

SHEET 1:
    Column A | Column(s)…  | Column D
    ...      | ...         | #Name#
    =================================
    ...      |             | Qwe
             | ...         | Asd
    ...      | ...         | Zxc 
             |             | <"little bug" must be copied here (Not row, only one cell B)>
       
SHEET 2:
    Column A | Column B    | Column D
    ...      | #Name#      | #Is on Sheet1?#
    =================================
             | Qwe         | 
    ...      | Asd         |
             | Zxc         |
    ...      | little bug  | NOT_ON_WORKSHEET

Current script copies cells from B(Sheet2) only to Column A instead of D(Sheet1)(for ogirinal tnx JPV)

Community
  • 1
  • 1
Stz
  • 61
  • 10
  • Can't you just change the second offste parameter from -2 to 1? – Robin Gertenbach Nov 30 '17 at 20:47
  • @Robin Gertenbach, if I change this to 1, then it will just copy other cells, from column "D" (columnStart !== 4) of Sheet2 instead of "B", which is needed, that's the reason of -2 (B). Anyways it will still copy any of choosen (1 or -2 or -4 or any else) cell's to **Column A -Sheet1**, but I need it to copy Cells to **Column D -Sheet1**, because of structure of our working sheet - column A already contains other important info :-( – Stz Dec 01 '17 at 08:17

1 Answers1

1

Okay I've finally made it. It's working well with topic's problem.

function onEdit(e) {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
 if (sheet.getName() !== '2' || e.range.getColumn() !== 4 || e.value !== 'Yes') return;
  var value = e.range.offset(0,-2,1,1).getValue();
  sheet = e.source.getSheetByName('1');
  var grd = sheet.getRange("D:D").getValues();
  var maxIndex = grd.reduce(function(maxIndex, row, index) {
  return row[0] === "" ? maxIndex : index;
  }, 0);
  sheet.getRange(maxIndex+2,e.range.getColumn()-2,1,1).setValue(value);
}
Stz
  • 61
  • 10