How can you reverse the order of a splice?

Question

I am trying to copy certain cells from a sheet when a date is entered and then paste them into another sheet.

The following is an image of the last 4 columns of my source sheet. The onEdit date is column AC:

I want to copy columns B, I, AA, AC, AB in that order and paste them into my target sheet. That is AC before AB. You can see by the following image that the Events are pasting into my target sheet in the order B, I, AA, AB, AC:

The target sheet has columns A to K but I am only pasting into columns A to F.

I have tried reversing the splice order in the script but I just can't get anything to work. I tried reversing the columns in the source sheet but column AB is a dropdown and I don't know how to get an onEdit trigger working on a dropdown.

This is my current script without the reverse order:

// Copy and paste from Events/Incidents sheet to Internal Audit sheet
 function onEdit(e) {
   var spreadsheet = e.source;
  var sheet = range.getSheet();
  var sourcesheetname = "Events/Incidents"
  var range = e.range;
  var row = range.getRow();
  var column = range.getColumn();
  var editedColumn = range.getColumn();
  var editedRow = range.getRow();
  var column = 29;
  var date = range.getValue();
  if(Object.prototype.toString.call(date) === '[object Date]' && editedColumn == column && editedRow > 2 && sheet.getName() == sourcesheetname) {
  sheet.hideColumns(27,3);
  var targetsheetname = "Internal Audit Register";
  var target = e.source.getSheetByName(targetsheetname);
  var numCols = sheet.getLastColumn();
  var values = sheet.getRange(row, 1, 1, numCols).getValues()[0];
  values.splice(9, 17) //Remove Columns J to Z
  values.splice(2, 6); //Remove Columns C to H
  values.splice(0, 1); //Remove Column A
  var lastRow = getLastRow(target);
  var lastCol = target.getLastColumn();
  values.unshift("Event/Incident");
  target.getRange(lastRow + 1, 1, 1, values.length).setValues([values]);  // Modified
  if (lastRow >= 3) target.getRange(lastRow, 9, 1, 3).copyTo(target.getRange(lastRow + 1, 9, 1, 3), SpreadsheetApp.CopyPasteType.PASTE_FORMULA);  // Added  // Append new row  
  target.getRange(lastRow, values.length).offset(1, 0).activate();
  target.getRange(lastRow + 1, 1, 1, values.length + 2).setBorder(true, true, true, true, true, true, "white", SpreadsheetApp.BorderStyle.SOLID)
  }
 }

Is what I am trying to do, possible?

Tanaike Tanaike · Accepted Answer · 2020-05-22T00:00:31

You want to achieve the order of columns B, I, AA, AC, AB instead of B, I, AA, AB, AC for values.

For this, how about this modification?

From:

values.splice(9, 17) //Remove Columns J to Z
values.splice(2, 6); //Remove Columns C to H
values.splice(0, 1); //Remove Column A

To:

values.splice(9, 17) //Remove Columns J to Z
values.splice(2, 6); //Remove Columns C to H
values.splice(0, 1); //Remove Column A
values = [...values.slice(0, 3), values[4], values[3]];  // <--- Added

At values.splice(0, 1), the order is B, I, AA, AB, AC. So I would like to propose to achieve your goal by adding [...values.slice(0, 3), values[4], values[3]].

How can you reverse the order of a splice?

1 Answers

From:

To: