Copy value of active cell to a different/non active sheet - How to determine which target/destination sheet to use

Question

This script copies the value of the active cell, to another sheet based on the value in the cell next to it.

If I write the exact cell in my script, it works, but every time the script is run, it will be based on a different cell.

I also need to fine tune the destination.

Here it is:

function copytoTabs() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Booking In');
    var data = sheet.getActiveCell();
    var value = ss.getSheetByName('Booking In').getActiveCell().getA1Notation();
    var operator = data.offset(0, 1).getValue();

    if (operator == "Michelle") {
        var ts = SpreadsheetApp.getActiveSpreadsheet();
        var tss = ts.getSheetByName('MICHELLE Schedule');
        ts.setActiveSheet(ts.getSheetByName('MICHELLE Schedule'));
        tss.getRange(1, 2).activate();
        tss.getRange(value).copyTo(tss.getActiveRange(),
        SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
    }
    else if (operator == "Georgia") {
        ss.setActiveSheet(ss.getSheetByName("GEORGIA Schedule"));
        ss.getCurrentCell().offset(0, 1, 4, 1).activate();
        ss.getRange('\'Booking In\'!P12').copyTo(ss.getActiveRange(),
        SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
    }
    else if (operator == "Julie") {
        ss.setActiveSheet(ss.getSheetByName("JULIE Schedule"));
        ss.getCurrentCell().offset(0, 1, 4, 1).activate();
        ss.getRange('\'Booking In\'!P12').copyTo(ss.getActiveRange(),
        SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
    }

    ss.setActiveSheet(ss.getSheetByName('Booking In'), true);
}

Answer 1

Instead of using multiple if / else blocks, you can association a sheet tab name with the operator name in an object. Then look up the sheet tab name by operator name.

function copytoTabs() {
  var activeCell,objectOfMappedValues,operator,sheet,sourceSs,targetSheetTabName,trgtSh;

  sourceSs = SpreadsheetApp.getActiveSpreadsheet();

  objectOfMappedValues = {//This is an object literal - the word literal means that the values are
    //hard coded here in the function as opposed to being set with code
    "Michelle":"MICHELLE Schedule",
    "Georgia":"GEORGIA Schedule",
    "Julie":"JULIE Schedule"
  }

  activeCell = sourceSs.getActiveCell();
  Logger.log('activeCell: ' + activeCell)

  operator = activeCell.offset(0, 1).getValue();//Get the value of one cell

  targetSheetTabName = objectOfMappedValues[operator];//Get the sheet tab name for this operator
  Logger.log('targetSheetTabName: ' + targetSheetTabName)

  trgtSh = ts.getSheetByName(targetSheetTabName);//Get the sheet tab to be the target to set a value
  Logger.log('trgtSh.getName(): ' + trgtSh.getName())

  trgtSh.getRange(activeCell.getA1Notation()).copyTo(trgtSh.getActiveRange())

}

This code may not be everything that you are asking for, but hopefully it will advance you to the final solution.