How do you cut and paste a row to another sheet using Java Apps Script in Google Sheets?

Question

I have attached the following link to my Google spreadsheet.

Spreadsheet

In the sheet called "Qualifications" is a list of employees and personal data. When an employee leaves, the user will click the "Employee Left" button which opens up Column B. The intention is that the user will then add next to the employee's name, the date the employee left and once the user presses enter the respective row will be cut and pasted into the sheet titled "Qualifications - Employees Left" and Column B will be hidden again. This is my way of trying to archive the data and still have access to it just in case the employee returns.

I have started an apps script on row 194 of the Script Editor. I was initially going to just hide the row but I don't want all of the little arrows when a row is hidden in Google Sheets. I am now struggling with the code and would appreciate some help.

Answer 1

If I understand you correctly, you want to:

(1) Unhide column B when the button Employee Left is clicked (in Qualifications).

(2) If the user writes a date in the column Date Left (in Qualifications), all the data in the corresponding row getting cut & paste to the first empty row in Qualifications - Employees Left, and column B gets hidden again.

I modified your script so that it accomplishes these tasks.

First, it looks like the function EmployeeLeft you wrote already does what it's supposed to do, but I reworked it a bit. I'm not sure why you want to activate and then unhide column C, but anyway, whatever suits you. This is the one I wrote:

function EmployeeLeft() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  var colIndex = 2;
  sheet.showColumns(colIndex);
  sheet.getRange("B5").activate();
}

Then comes the main part. In order to accomplish point (2), I wrote a function inside an onEdit trigger. I am aware you already have an onEdit function, but I think there shouldn't be any problem integrating this code inside it.

Basically, this function checks whether the edited cell is from sheet Qualifications, from column B, not from a header row, and that the edited value is a valid Date. If all these conditions are met, the edited row is removed from Qualifications and its values are copied to the first empty row in Qualifications - Employees Left. Finally, column B becomes hidden again, until Employee Left is clicked again.

Here is the function:

function onEdit(e) {
  var ss = e.source;
  var sheet = ss.getActiveSheet();
  var sheetName = "Qualifications"
  var range = e.range;
  var editedColumn = range.getColumn();
  var editedRow = range.getRow();
  var column = 2;
  var date = range.getValue();
  // Object.prototype.toString.call(date) === '[object Date]' --> checks if value is date
  // editedColumn == column && editedRow > 4 --> checks if edited cell is from 'Date Left'
  // sheet.getName() == sheetName --> checks if edited sheet is 'Qualifications'
  if(Object.prototype.toString.call(date) === '[object Date]' && editedColumn == column && editedRow > 4 && sheet.getName() == sheetName) {
    var numCols = sheet.getLastColumn();
    var row = sheet.getRange(editedRow, 1, 1, numCols).getValues();
    var destinationSheet = ss.getSheetByName("Qualifications - Employees Left");
    // Get first empty row:
    var emptyRow = destinationSheet.getLastRow() + 1;
    // Copy values from 'Qualifications'
    destinationSheet.getRange(emptyRow, 1, 1, numCols).setValues(row);
    sheet.deleteRow(editedRow);
    sheet.hideColumns(column);
  }
}

Take into account that there is some data in some rows far below the headers in "Qualifications - Employees Left". Make sure that you remove all these data so that values get copied in the appropriate rows.

Update

Here I share a copy of the spreadsheet with the correct script attached. I'd advice you to copy the full script and paste it in the file you're working on, removing all previous content:

https://docs.google.com/spreadsheets/d/1XCrcSaDwwmKTHLIxL9vbx0_t6NJew983d9YDol-Y1dw/edit?usp=sharing

• The function onEdit(e) is added on top, including both, the one I wrote and the one you had already written.
• Regarding the function Employee Left, I commented out the one you wrote and added mine below. If you prefer yours, just remove mine and uncomment yours.

I hope this is of any help, and please tell me if that works for you.

Answer 2

This code will delete complete the row with active cell, and add it to the new sheet at the end. Remember to replace the values containing <> with actual ones.

function remove_and_reinsert_row() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var active_range = sheet.getActiveRange();
   var last_column = sheet.getLastColumn();
   var tbd_row_data = sheet.getRange(active_range.getRowIndex(), 1, 1,last_column).getValues();
   sheet.deleteRow(active_range.getRowIndex());

   var new_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("existing sheet name");
   var last_active_row = new_sheet.getLastRow();
   new_sheet.getRange(last_active_row+1, 1, 1,tbd_row_data[0].length).setValues(tbd_row_data);
}

This will use row with active range, i.e. and cell which is currently active and delete the row , inserting it into new sheet. You can use trigger on the column and see whenever someone changes value in the column, and trigger the function using app script triggers.

For hiding a column, you can use following function:

  sheet.hideCoumns(colIndex);

Hope this helps,