Copying and Paste multiple rows into next empty Row on another sheet

0
votes

I have the following script:

function onEdit(event) {
  // assumes source data in sheet named Needed
  // target sheet of move to named Acquired
  // test column with yes/no is col 5 or E
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  if(s.getName() == "IN" && r.getColumn() == 7 && r.getValue() == "Y") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("ORDERS");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).copyTo(target);

  }
}

This is the original post where i found the script: https://support.google.com/docs/forum/AAAABuH1jm0hR40qh02UWE/?hl=en&gpf=%23!topic%2Fdocs%2FhR40qh02UWE

I want to make a few small adjustments to the code, but don't know where to start.

Presently, it copies the whole row when a "Y" is entered into column G and places the row contents on lastrow of ORDERS!.

What i want it to do is:

1) only copy columns B,C and E on lastrow of ORDERS! 2) delete values in E and F on IN! after code has run for that specific row (don't want it deleting rows that I haven't put a "Y" against) 3) Is there a way to have a button instead, that when the button is clicked it copies all the rows with "Y" at once?

Here's a link to my sheet if you want to have a play: https://docs.google.com/spreadsheets/d/1Peo5_5QmkxVyL7j5bmgtMs9BL16cvsGhhOSuRV_TsAo/edit?usp=sharing

Best regards manc

1
google-apps-scriptcopymultiple-columnsrows

1 Answers

1
votes

I believe your goal as follows.

  • When the column "G" is Y, you want to copy the values of the columns "B", "C" and "E" to the last row of the sheet ORDERS.
    • You want to copy all rows that the column "G" is Y when the script is run.
  • You want to delete the content of columns "E" and "F" of the copied rows in the sheet of IN, when the script is run.
  • You want to run the script by clicking a button on the sheet.

For this, how about this answer?

Modification points:

  • In your script,
    • When the script is run by clicking a button on the sheet, the event object cannot be used.
    • Your script copies one row of the active range.
    • The columns "E" and "F" of the copied rows are not deleted.

It is required to modify above modification points. When above points are reflected to the script, it becomes as follows.

Modified script:

Please copy and paste the following script to the script editor. And please prepare a button which is drawing and/or image, and assign the function run to the button. By this, when the button is clicked, the script is run. And the columns "B", "C" and "E" of all rows that the column "G" is Y are copied from the sheet "IN" to the last row of the sheet "ORDERS".

function run() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var srcSheet = ss.getSheetByName("IN");
  var dstSheet = ss.getSheetByName("ORDERS");

  // 1. Retrieve the values from "A2:G" of sheet "IN".
  var srcValues = srcSheet.getRange("A2:G" + srcSheet.getLastRow()).getValues();

  // 2. Create an object for putting values and deleting the contents of the columns "E" and "F".
  var obj = srcValues.reduce((o, [,b,c,,e,,g], i) => {
    if (g == "Y") {
      o.values.push([b, c, e]);
      o.ranges.push(`E${i + 2}:F${i + 2}`);
    }
    return o;
  }, {values: [], ranges: []});

  // 3. Copy the values to the sheet "ORDERS".
  dstSheet.getRange(dstSheet.getLastRow() + 1, 1, obj.values.length, obj.values[0].length).setValues(obj.values);

  // 4. Delete the contents of the columns "E" and "F" of sheet "IN".
  srcSheet.getRangeList(obj.ranges).clearContent();
}

Note:

  • About the button for running the Google Apps Script, I think that this site is useful. Ref In your case, please create a button on the sheet "IN" and assign the function run to the button. By this, when the button is clicked, the script works.
  • Please use this script with V8.

References: