Background (of the request):
Columns F-H are small excerpt of a Manpower spreadsheet.
Every shift is full of Start Time "Adjustments" ...and the ability to make adjustments to Employee "Start Times" in the Table (Columns F-H) ...while and Apps Script makes adjustments to the raw data (Columns A-E) is the key 🗝️
I'm hoping the Step By Step Markdown Table section proves helpful. Aside from that, there is a Link to Google Sheet ⤵️ ...and a GIF showing a simple Start Time Adjustment is also provided.
Apps Script pastes Value (into another column) when "Result" value is different than "Start" (value)
Start: Table consists of Cells A1 to H7
A | B | C | D | E | F | G | H | |
---|---|---|---|---|---|---|---|---|
1 | ||||||||
2 | Name | Start | Result | Manual Value | PASTE Value (Script) | Start Time | A-Team | Formula |
3 | Employee A | 2:00 PM | 2:00 PM | 2:00:00 PM | Employee A | Yes | ||
4 | Employee B | 3:00 PM | 3:00 PM | 3:00:00 PM | Employee B | Yes | ||
5 | Yes | |||||||
6 | Yes | |||||||
7 | Yes |
Step 1 >> 1:00pm << is entered into Cell F4 ** note: value in C4 [1:00pm] no longer matches B4 [3:00pm] **
A | B | C | D | E | F | G | H | |
---|---|---|---|---|---|---|---|---|
1 | ||||||||
2 | Name | Start | Result | Manual Value | PASTE Value (Script) | Start Time | A-Team | Formula |
3 | Employee A | 2:00 PM | 2:00 PM | 2:00:00 PM | Employee A | Yes | ||
4 | Employee B | 3:00 PM | 1:00 PM | 1:00:00 PM | Employee B | No | ||
5 | Yes | |||||||
6 | Yes | |||||||
7 | Yes |
Step 2: (unmatching) value in C4 is "Manually" entered into D4 >> 1:00pm <<
A | B | C | D | E | F | G | H | |
---|---|---|---|---|---|---|---|---|
1 | ||||||||
2 | Name | Start | Result | Manual Value | PASTE Value (Script) | Start Time | A-Team | Formula |
3 | Employee A | 2:00 PM | 2:00 PM | 2:00:00 PM | Employee A | Yes | ||
4 | Employee B | 3:00 PM | 1:00 PM | 1:00 PM | 1:00:00 PM | Employee B | No | |
5 | Yes | |||||||
6 | Yes | |||||||
7 | Yes |
Step 3: >> Employee B << is Cut/Pasted into Cell G5
Formula in F5 recognizes "Manual" entry in D4 and displays >> 1:00pm << (in Cell F5)
A | B | C | D | E | F | G | H | |
---|---|---|---|---|---|---|---|---|
1 | ||||||||
2 | Name | Start | Result | Manual Value | PASTE Value (Script) | Start Time | A-Team | Formula |
3 | Employee A | 2:00 PM | 2:00 PM | 2:00:00 PM | Employee A | Yes | ||
4 | Employee B | 3:00 PM | 1:00 PM | 1:00 PM | 1:00:00 PM | No | ||
5 | 1:00:00 PM | Employee B | Yes | |||||
6 | Yes | |||||||
7 | Yes |
Step 4: Cell F5 is changed from 1:00pm to >> 8:00pm <<
Cells D4 [1:00pm] & C4 [8:00pm] are compared [Do Not Match]
A | B | C | D | E | F | G | H | |
---|---|---|---|---|---|---|---|---|
1 | ||||||||
2 | Name | Start | Result | Manual Value | PASTE Value (Script) | Start Time | A-Team | Formula |
3 | Employee A | 2:00 PM | 2:00 PM | 2:00:00 PM | Employee A | Yes | ||
4 | Employee B | 3:00 PM | 8:00 PM | 1:00 PM | 1:00:00 PM | No | ||
5 | 8:00:00 PM | Employee B | No | |||||
6 | Yes | |||||||
7 | Yes |
Step 5: (unmatching) value in C4 is "Manually" entered into D4 >> 8:00pm <<
A | B | C | D | E | F | G | H | |
---|---|---|---|---|---|---|---|---|
1 | ||||||||
2 | Name | Start | Result | Manual Value | PASTE Value (Script) | Start Time | A-Team | Formula |
3 | Employee A | 2:00 PM | 2:00 PM | 2:00:00 PM | Employee A | Yes | ||
4 | Employee B | 3:00 PM | 8:00 PM | 8:00 PM | 1:00:00 PM | No | ||
5 | 8:00:00 PM | Employee B | No | |||||
6 | Yes | |||||||
7 | Yes |
Instead of "Manually" entering the value into "Column D" ...is it possible
Here's a GIF 'attempting' to summarize the request:
I have crafted a Google Apps script which I believe would solve your request. From the GIF you've shared, you only enter values within col F and G. therefore I set the column F as a trigger point wherein if you enter a value in col F. it will then activate the condition of checking whether the values from col B and C matches. if it doesn't, it will copy the values to Col E. However, I have noticed there are columns where you use formulas to copy values (Column C and F). Please note that this could be a limitation to Apps script, I would suggest that you try to use apps script for complete automation instead.
Try this code:
function onEdit(e) {
var sheet = e.source.getActiveSheet();
var range = e.range;
if (range.getColumn() === 6) {
var editedRow = range.getRow();
var aTeam = sheet.getRange(editedRow, 7).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();
if (colB !== colC) {
sheet.getRange(targetRow, 5).setValue(colC);
}
}
}
}
NOTE: after running the code, you'll see some expected errors. Try to edit or add values in column F to see if it meets your expected result. Additionally, if you will add another name or entry in col A to C, you should always copy and paste the formula you have within col C to copy the data from col F.
Reference: onEdit(e)