How can I find a sheet name by looking up a column-CodePudding

I'm very new to scripts (i.e a total novice).I'm trying to update a work tracker used by several members of a team. It basically logs cases on a separate tab for each person.

I have managed to set up a code that moves a row to another tab when a checkbox is ticked (i.e. when a case is closed they can automatically move the row of data to a "Closed" tab). What I'm having trouble with is giving the team the option of moving it back should they close it in error. I can make it work but it only moves the data back to the named sheet in the script. How do I get it to find the sheet name by looking up the agent's name? The agents name is listed in column D.

Here is the code I've used this is where I believe I need the help:

var targetSheet = ss.getSheetName("Bob");

Many thanks Loz

function onEdit(event) {
  // assumes source data in sheet named main
  // target sheet of move to named Completed
  // getColumn with check-boxes is currently set to column 5 or E
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "Bob","Billy","Anna","Emma","Joe" && r.getColumn() == 5 && r.getValue() == true) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Closed");
    var target = targetSheet.getRange(targetSheet.getLastRow()   1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  } else if(s.getName() == "Closed" && r.getColumn() == 5 && r.getValue() == false) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetName("Bob");
    var target = targetSheet.getRange(targetSheet.getLastRow()   1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}

CodePudding user response：

I'm assuming you are unchecking a checkbox in column E in the "Closed" sheet and want to move the row back to the original agent. And the name of the agent or sheet name is in column D of that row. Then you can do this.

let agent = e.range.offset(0,-1).getValue();
let targetSheet = ss.getSheetName(agent);

Reference

Range.offset()

CodePudding user response：

Getting sheet name from agent's column in sheet Closed I think

Assumed header row is one and Agent's column name is Agent

function onEdit(e) {
  const sh = e.range.getSheet();
  const names = ["Bob", "Billy", "Anna", "Emma", "Joe"];
  const idx = names.indexOf(sh.getName());
  if (~idx && e.range.columnStart == 5 && e.value == "TRUE") {
    var tsh = e.source.getSheetByName("Closed");
    var trg = tsh.getRange(tsh.getLastRow()   1, 1);
    sh.getRange(e.range.rowStart, 1, 1, sh.getLastColumn()).moveTo(trg);
    sh.deleteRow(e.range.rowStart);
  } else if (sh.getName() == "Closed" && e.range.columnStart == 5 && e.value == "FALSE") {
    let hA = sh.getRange(1, 1, 1, sh.getLastColumn()).getValues();
    let col = hA.forEach((h, i) => { col[j] = i   1; });
    var tsh = e.source.getSheetName(sh.getRange(e.range.rowStart,col["Agent"]).getDisplayValue());//Agent is supposed to be agents column name
    var trg = tsh.getRange(tsh.getLastRow()   1, 1);
    sh.getRange(e.range.rowStart, 1, 1, sh.getLastColumn()).moveTo(trg);
    sh.deleteRow(e.range.rowStart);
  }
}

If you wish a better answer then please provide image of Closed and one of the Named Sheets