Moving Rows from one Google Sheet Tab to Another AND Referencing Original Tab

Question

I can currently move rows from a tab to a 'Completed' tab when Column A's dropdown reads 'Done'. Column A in the 'Completed' tab is then replaced by the date the row was moved. The new problem is that I want Column B (Category) in the 'Completed' tab to tell me what the original tab name that the row moved from was. I will have several departments, sorted by tabs, completing tasks that will all be stored on the one 'Completed' tab. Is this possible?

Here is the link to the google Sheet: https://docs.google.com/spreadsheets/d/1_2Do2Eu4Hb61seupPJcWLynyEtT5xQtvkO8DEQaRJU0/edit?usp=sharing

Answer 1

You can simply add another line with unshift(). Then the code will be like this:

function onEdit(e) {
  let range = e.range;
  let col = range.getColumn();
  let row = range.getRow();
  let val = range.getValue();
  let sourceName = e.source.getActiveSheet().getName();

  if (col == 1 && val == 'Done') {
    let today = Utilities.formatDate(new Date(), "GMT-4", "M/d/yy");
    let ss = SpreadsheetApp.getActiveSpreadsheet();
    let sourceSheet = ss.getSheetByName(sourceName);
    let data = sourceSheet.getRange(row, 2, 1, 4).getValues();
    data[0].unshift(sourceName);
    data[0].unshift(today);

    let targetSheet = ss.getSheetByName('Completed');
    targetSheet.appendRow(data[0]);
    sourceSheet.deleteRow(row);
  }
}

Answer 2

I also got this to work:

function onEdit(e) {
var sheet = e.source.getActiveSheet();
var range = e.range;
var row = range.getRow();
var doneValue = "Done";

// Check if edited cell is in column 1 (A) and contains "Done"
if (range.getColumn() == 1 && e.value && e.value.toLowerCase() ==     doneValue.toLowerCase()) {
var targetSheet = e.source.getSheetByName("Completed");
var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1,       1);
var date = new Date();
var timezone = "GMT-4"; // Timezone to adjust the date
date.setHours(date.getHours() - 4); // Adjusting for GMT-4 timezone
var formattedDate = Utilities.formatDate(date, timezone,      "MM/dd/yyyy");

// Move the whole row to the "Completed" tab and delete from the      original tab
sheet.getRange(row, 1, 1, sheet.getLastColumn()).moveTo(targetRange);
sheet.deleteRow(row);

// In the "Completed" tab, column 6 shows the date the row was moved
var completedRow = targetSheet.getLastRow();
targetSheet.getRange(completedRow, 6).setValue(formattedDate);

// In the "Completed" tab, column 7 shows the name of the original tab
var originalSheetName = sheet.getName();
targetSheet.getRange(completedRow, 7).setValue(originalSheetName);
  }
 }