google-sheetsgoogle-apps-scripttriggers

How do I setup an Apps Script to OnEdit() paste Values (into another column) based on certain Triggers


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)

enter image description here

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

  1. To have a Apps Script check for 'when' the values in Columns B & C do NOT match, and "Automatically"
  2. Populate Column E with the value from Column C (rather than manually inputting the value into Column D), and
  3. When there's a value in Column E, the Apps Script then checks for 'when' the values in Columns C & E do not match, and then
  4. Overwrites 'whatever' value is in Column E with the value from Column C (so they match), and then
  5. Every time the values in Columns C & E do not match 'afterward' ...the Apps Script continues overwriting the value Column E with the value from Column C?

Here's a GIF 'attempting' to summarize the request:

Here's a GIF attempting to "Summarize" the whole request


Solution

  • Automatically copy the data if a certain condition matches

    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)