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 ⤵️
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)
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
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
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 |
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"
}