Google Apps Script: Copy cell value to another she

问题:

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 (Metallic Unit)

-Weight

-Position (Level)

-Position (Letter)

-Position (Number)

-Arrival date and time

-Departure date and time

-Destination

-Status

-Observations

When a MU arrive, the data is entered and the column "Status" receive "Awaiting positioning" as value. When the MU is positioned within the deposit, the "Status" change to "Positioned". What I need to do is:

-When the "Status" change to "Positioned", copy the value of the respective MU column to one of the Maps, depending on the "Position (Level)". If the "Position (Level)" is "N1", it goes to "Map N1", "N2" to "Map N2" and so it goes.

-The Map sheets look like this:

-The MU value should position in the sheet according to the "Position (Letter)" and "Position (Number)" values. That way, the MU below should go to the sheet just like this image:

I've searched and managed to find this code:

function onEdit(event) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var s = event.source.getActiveSheet();
    var r = event.source.getActiveRange();
    if(s.getName() == "List" && r.getColumn() == 9 && r.getValue() == "Posicionada") {
      var targetSheet = ss.getSheetByName("Map N1");
      var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
      s.getRange(1, 1, 1, 1).copyTo(target);    
    }
  }

The idea was to use this code to figure out how to copy the value automatically, and then adapt it to the positioning thing. But This isn't even copying the values to the other sheet.

回答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) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = event.source.getActiveSheet();
var r = event.range;
if(s.getName() === "Lista" && r.getColumn() === 9 && r.getValue() === "Posicionada") {
  var sheetname = s.getRange(r.getRow(),3).getValue();
  var columnRef = s.getRange(r.getRow(),4).getValue();
  var rowref = s.getRange(r.getRow(),5).getValue();
  var targetSheet = ss.getSheetByName("Mapa " + sheetname);
  var headers = targetSheet.getRange(1, 1, 1, targetSheet.getLastColumn()).getValues().toString().split(",");
  var rows = targetSheet.getRange(1, 1, targetSheet.getLastRow(), 1).getValues().toString().split(",");

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

  var column = i + 1;

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

  var row = i + 1;

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

回答2:

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