Google Apps Script: Copy Cell Value To Another Sheet With Condition To Copy And Position The Value

I have a spreadsheet with 4 sheets: 'List', 'Map N1', 'Map N2' and 'Map N3'. It is supposed to work as a control for a deposit of Metallic Units. 'List' has these columns: -MU (Met

Solution 1:

You can use setValue() instead of moveTo/copyTo.

 targetSheet.getRange(row,column).setValue(s.getRange(r.getRow(), 1, 1, 1).getValue()); 

Here is the rest of your code if you will need it.

It basically uses the reference in cell to get the correct sheet, column and row. As your columns are not original, I added a loop to cycle through the first row on the Map sheet and find the correct column.

function onEdit(event) {
varss= SpreadsheetApp.getActiveSpreadsheet();
vars= event.source.getActiveSheet();
varr= event.range;
if(s.getName() === "Lista" && r.getColumn() === 9 && r.getValue() === "Posicionada") {
  varsheetname= s.getRange(r.getRow(),3).getValue();
  varcolumnRef= s.getRange(r.getRow(),4).getValue();
  varrowref= s.getRange(r.getRow(),5).getValue();
  vartargetSheet= ss.getSheetByName("Mapa " + sheetname);
  varheaders= targetSheet.getRange(1, 1, 1, targetSheet.getLastColumn()).getValues().toString().split(",");
  varrows= targetSheet.getRange(1, 1, targetSheet.getLastRow(), 1).getValues().toString().split(",");

  //Getting The correct Columnfor (vari=1; i < headers.length; i++) {
    if (headers[i] === columnRef) {
      break;
    }
  }

  varcolumn= i + 1;

  //Getting the correct rowfor (vari=1; i < rows.length; i++) {
    if (Number(rows[i]) === rowref) {
      break;
    }
  }

  varrow= i + 1;

  targetSheet.getRange(row,column).setValue(s.getRange(r.getRow(), 1, 1, 1).getValue()); 
}
}

Solution 2:

function onEdit(event) {      
  varrange = event.range;
  var fromRow = range.getRow();
  var fromCol = range.getColumn()
  ...
  s.getRange(fromRow, fromCol, 1, 1).copyTo(target);