Populate Column A with TEXT before Splice

0
votes

I have the following onEdit(e) Google Apps Script which populates columns A:D in the next row of another sheet. I now want it to populate columns B:E and in Column A of the same row to populate with the text "SOP":

 {
  var spreadsheet = e.source;
  var sheet = spreadsheet.getActiveSheet();
  var sourcesheetname = "SOP Register"
  var range = e.range;
  var sheet = range.getSheet();
  var row = range.getRow();
  var column = range.getColumn();
  var editedColumn = range.getColumn();
  var editedRow = range.getRow();
  var column = 7;
  var date = range.getValue();
  if(Object.prototype.toString.call(date) === '[object Date]' && editedColumn == column && editedRow > 3 && sheet.getName() == sourcesheetname) {
    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(7)
    values.splice(0, 0)
    values.splice(1, 0)
    values.splice(2, 3); // Removing undesired values
    var lastRow = target.getLastRow();
    var lastCol = target.getLastColumn();
    target.appendRow(values); // Append new row  
    sheet.hideColumns(6,2);
   }
}

Below is my Source Sheet:

Source Data

Below is the target sheet data after the script:

enter image description here

This is my desired outcome:

enter image description here

This is a sample of my spreadsheet. The script code starts at line 67.

Sample Spreadsheet

Just not sure how to do this.

1
google-apps-scriptgoogle-sheets
Some images of your spreadsheet might be helpful. – Cooper
@Cooper - I have added images – McChief
Please can you provide access to a sanitised copy of your sheet with an example of your desired output? – Rafa Guillermo
I have now added a sample of the spreadsheet and the desired outcome – McChief
@RafaGuillermo I have now added a sample of the spreadsheet and the desired outcome – McChief

1 Answers

1
votes

Answer:

Use array.unshift() to append the desired string to the beginning of the array before you append the row.

More Information:

As per the MDN documentation:

The unshift() method adds one or more elements to the beginning of an array and returns the new length of the array.

So before you append your new row to your second sheet, you need to use unshift() to add the string "SOP" to the beginning:

{
  // ...
  var lastRow = target.getLastRow();
  var lastCol = target.getLastColumn();
  values.unshift("SOP"); // << Add this
  target.appendRow(values); // Append new row  
  sheet.hideColumns(6,1);
  sheet.hideColumns(7,1);
}

I hope this is helpful to you!

References: