Copying And Pasting Each New Value To The Next Empty Row On Two Different Sheets - Google Sheets

Question

I have one Google Sheet with multiple different sheets in it. Every time I add text to a cell (let's say any cell from G2:I1000) of the first sheet I want the value of a few other cells to appear in the next empty row on the second sheet. For example, if I add text to I5 on the first sheet, I then want to automatically paste the values of the cells A5:B5 to the next empty row on the second sheet (also in column A and B).

Hello, did your issue get solved? Remember that if you want to mark your question as solved you can accept whatever answer provided a solution to your problem. If that's not the case and your issue is not solved, please consider explaining why that's not the case so that this community can help you. — Iamblichus

Iamblichus Iamblichus · Accepted Answer · 2019-11-18T08:29:47

As far as I understand, you want to do the following:

Every time a value in the range G2:I1000 from a certain sheet is edited, you want to copy the first two cells (A, B) in the edited row.
You want to past those two values to the first empty row in another sheet.

You can use an onEdit trigger trigger to accomplish this. This function runs every time the spreadsheet it is bound to is edited. But to avoid values getting copied all the time, several conditions have to be met before copying the values from one sheet to another:

The edited row index is greater than 1 and lower than 1001.
The edited column is G, H or I.
The edited sheet is the one you want to copy values from.

After meeting those conditions, the function should get values A, B and copy them to the first empty row in another sheet. This can easily be achieved using appendRow.

The following code does all these things you want to do:

function onEdit(e) {
  var editedSheet = e.source.getActiveSheet(); // Gets sheet that was edited
  var destSheet = e.source.getSheetByName("Destination"); // Change this according to your preferences
  var range = e.range; // Gets range that was edited
  var editedRow = range.getRow();
  var editedCol = range.getColumn();
  var editedSheetName = editedSheet.getName();
  var originName = "Origin"; // Change this according to your preferences
  if (editedRow > 1 && editedRow < 1001 && editedCol > 6 && editedCol < 10 && editedSheetName == originName) {
    var firstCol = 1;
    var numCols = 2;
    var numRows = 1;
    var copyValues = editedSheet.getRange(editedRow, firstCol, numRows, numCols).getValues()[0];
    destSheet.appendRow(copyValues);
  }
}

Take into account that I've named sheet the following way. Please change this according to your preferences:

Origin is the sheet to copy values from.
Destination is the sheet to copy values to.

Update:

If you want data to be copied only if the corresponding value in L column is equal to "YES", then you should change this line of code:

if (editedRow > 1 && editedRow < 1001 && editedCol > 6 && editedCol < 10 && editedSheetName == originName) {

To this one:

if (editedCol == 12 && range.getValue() == "YES" && editedSheetName == originName && editedRow > 1 && editedRow < 1001) {

Where editedCol should be equal to the index of column L (12), and the value of the edited range should be "YES".

In case you want data to get copied in both situations (that is, if columns G, H, I are edited AND also if column L is edited to "YES"), then the line should be changed to this:

if (((editedCol == 12 && range.getValue() == "YES") || (editedCol > 6 && editedCol < 10)) && (editedSheetName == originName && editedRow > 1 && editedRow < 1001)) {

Which merges both condition expressions into one, and copies data if at least one of them is true.

I hope this is of any help.

Copying And Pasting Each New Value To The Next Empty Row On Two Different Sheets - Google Sheets

1 Answers

Update: