google-sheetsgoogle-apps-scripttriggers

Adjust onEdit() function to collect results onto one tab (instead of separate tabs)


Adjusting the Apps Script onEdit() function to collect results into the "Scheduler" tab (instead the separate "Daily" tabs) is the next step for this request

Click Here (to review) 👉 Collect OnEdit MultiTab Trigger ... Google Sheet "Pictured" below ⤵️

Screenshot

The following Apps Script allows values to be copied onto the Sun and/or Mon tab ...using the onEdit() function

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  if (sheet.getName() === "Sun");
  var range = e.range;

  if (range.getColumn() === 9) { 
    var editedRow = range.getRow();
    var aTeam = sheet.getRange(editedRow, 10).getDisplayValue(); 

    if (!aTeam) return; 

    var lastRow = sheet.getLastRow();
    var empName = sheet.getRange(3, 1, lastRow - 2, 1).getValues(); 
    
    var targetRow = -1;
    for (var i = 0; i < empName.length; i++) {
      if (empName[i][0] === aTeam) {
        targetRow = i + 3; 
        break;
      }
    }
    if (targetRow !== -1) {
      var colB = sheet.getRange(targetRow, 2).getDisplayValue(); 
      var colC = sheet.getRange(targetRow, 3).getDisplayValue(); 
      var colD = sheet.getRange(targetRow, 4).getDisplayValue(); 

      if (colB !== colC) {
        sheet.getRange(targetRow, 4).setValue(colC);
      }
      if (colC !== colD) {
        sheet.getRange(targetRow, 4).setValue(colC);
      }
    }
  }
  if (range.getColumn() === 13) { 
    var editedRow = range.getRow();
    var bTeam = sheet.getRange(editedRow, 14).getDisplayValue(); 

    if (!bTeam) return; 

    var lastRow = sheet.getLastRow();
    var empName = sheet.getRange(3, 1, lastRow - 2, 1).getValues(); 
    
    var targetRow = -1;
    for (var i = 0; i < empName.length; i++) {
      if (empName[i][0] === bTeam) {
        targetRow = i + 3; 
        break;
      }
    }
    if (targetRow !== -1) {
      var colB = sheet.getRange(targetRow, 2).getDisplayValue(); 
      var colC = sheet.getRange(targetRow, 3).getDisplayValue(); 
      var colD = sheet.getRange(targetRow, 4).getDisplayValue(); 

      if (colB !== colC) {
        sheet.getRange(targetRow, 4).setValue(colC);
      }
      if (colC !== colD) {
        sheet.getRange(targetRow, 4).setValue(colC);
      }
    }
  }
}
function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  if (sheet.getName() === "Mon");
  var range = e.range;

  if (range.getColumn() === 9) { 
    var editedRow = range.getRow();
    var aTeam = sheet.getRange(editedRow, 10).getDisplayValue(); 

    if (!aTeam) return; 

    var lastRow = sheet.getLastRow();
    var empName = sheet.getRange(3, 1, lastRow - 2, 1).getValues(); 
    
    var targetRow = -1;
    for (var i = 0; i < empName.length; i++) {
      if (empName[i][0] === aTeam) {
        targetRow = i + 3; 
        break;
      }
    }
    if (targetRow !== -1) {
      var colB = sheet.getRange(targetRow, 2).getDisplayValue(); 
      var colC = sheet.getRange(targetRow, 3).getDisplayValue(); 
      var colD = sheet.getRange(targetRow, 4).getDisplayValue(); 

      if (colB !== colC) {
        sheet.getRange(targetRow, 4).setValue(colC);
      }
      if (colC !== colD) {
        sheet.getRange(targetRow, 4).setValue(colC);
      }
    }
  }
  if (range.getColumn() === 13) { 
    var editedRow = range.getRow();
    var bTeam = sheet.getRange(editedRow, 14).getDisplayValue(); 

    if (!bTeam) return; 

    var lastRow = sheet.getLastRow();
    var empName = sheet.getRange(3, 1, lastRow - 2, 1).getValues(); 
    
    var targetRow = -1;
    for (var i = 0; i < empName.length; i++) {
      if (empName[i][0] === bTeam) {
        targetRow = i + 3; 
        break;
      }
    }
    if (targetRow !== -1) {
      var colB = sheet.getRange(targetRow, 2).getDisplayValue(); 
      var colC = sheet.getRange(targetRow, 3).getDisplayValue(); 
      var colD = sheet.getRange(targetRow, 4).getDisplayValue(); 

      if (colB !== colC) {
        sheet.getRange(targetRow, 4).setValue(colC);
      }
      if (colC !== colD) {
        sheet.getRange(targetRow, 4).setValue(colC);
      }
    }
  }
}

Here's a GIF of the Script working on each "Daily" tab (Mon & Tue)

enter image description here

How do I adjust the Apps Script (when an Edit is made to Column I or M of the Sun or Mon tab) ...so that

  1. Instead of collecting data into Column D (of the Sun or Mon tab)
  2. Sun's Data is collected into Column D (of the Scheduler tab)
  3. Mon's Data is collected into Column G (of the Scheduler tab)

Here's an example of me "Manually" doing what I need the App Script to do (when an Edit occurs on either the Mon or Tue tab)

Note: Values are "Pasted" into the row of the 'corresponding' Employee ID - ie: Employee A

enter image description here

Markdown table (version) of Scheduler Tab ⤵️ ...incase the GIFs are too small

Sun Sun Sun Mon Mon Mon
Name Start Result Manual Value Start Result Manual Value
Employee A 2:00 PM 1:00 PM 1:00 PM 8:00:00 PM 7:00:00 PM 7:00:00 PM
Employee B 3:00 PM 3:00 PM 9:00:00 PM 9:00:00 PM
Employee C 1:00 PM 1:00 PM 7:00:00 PM 7:00:00 PM
Employee D 4:00 PM 4:00 PM 6:00:00 PM 6:00:00 PM

Sun (tab)

Sun Sun Sun
Name Start Result Manual Value Start Time S-Team Formula Start Time Sb-Team Formula
Employee A 2:00 PM 2:00 PM 2:00:00 PM Employee A Yes 1:00:00 PM Employee C Yes
Employee B 3:00 PM 3:00 PM 3:00:00 PM Employee B Yes 4:00:00 PM Employee D Yes
Employee C 1:00 PM 1:00 PM Yes Yes
Employee D 4:00 PM 4:00 PM Yes Yes
Yes Yes

Mon (tab)

Mon Mon Mon
Name Start Result Manual Value Start Time M-Team Formula Start Time Mb-Team Formula
Employee A 8:00 PM 8:00 PM 8:00:00 PM Employee A Yes 7:00:00 PM Employee C Yes
Employee B 9:00 PM 9:00 PM 9:00:00 PM Employee B Yes 6:00:00 PM Employee D Yes
Employee C 7:00 PM 7:00 PM Yes Yes
Employee D 6:00 PM 6:00 PM Yes Yes
Yes Yes

Solution

  • Here's the AppsScript that answered / resolved my question.

    function onEdit(e) {
      const sheet = e.range.getSheet();
      const sheetName = sheet.getName();
      const sheetConfig = { // Configure the sheets and columns should be updated in "Scheduler".
        "Sun": { targetColumn: 4 }, // Column D
        "Mon": { targetColumn: 7 }  // Column G
      };
      if (!(sheetName in sheetConfig)) return; // Only proceed if the edit is in a configured sheet.
      const scheduleSheet = e.source.getSheetByName("Scheduler");
      const range = e.range;
      const column = range.getColumn();
      const row = range.getRow();
      if (row < 3 || (column !== 9 && column !== 13)) return; // Only proceed if the edit is in I3:I or M3:M.
      const nameColumn = column + 1; // Determine the adjacent column.
      const nameCell = sheet.getRange(row, nameColumn).getValue();
      const updatedValue = range.getValue();
      if (!nameCell) return; // Skip if no name is present
      const scheduleNames = scheduleSheet.getRange("A3:A").getValues().flat(); // Get all names from Schedule!A3:A.
      const scheduleRow = scheduleNames.indexOf(nameCell); // Find row number where the name exists in Schedule!A3:A.
      if (scheduleRow === -1) return; // Skip if name not found
      const targetColumn = sheetConfig[sheetName].targetColumn; // Determine target column (based on configuration).
      scheduleSheet.getRange(scheduleRow + 3, targetColumn).setValue(updatedValue); // Update the corresponding column in "Schedule"
    }