Google Sheet Script: Find row number of a cell in another sheet range by last row unique value then update columns of target row

Question

I have two sheets, one is a form responses, the other is with unique work codes. When there is a form submit, I would like to copy and paste the time stamp of last work code in sheet "timedata" in column D where workcode matches. I also would like to do the same thing for the STATUS; copy the status from the answer and paste to sheet "timedata" in column E where the work code matches. Here is my file: https://docs.google.com/spreadsheets/d/1h2_tdStLOGSEtBToXKeb6FGCcVRhokyr7KRIv4uRgm4/edit?usp=sharing

I have a script as following but not sure how to continue;

function lastName() {
  var wb = SpreadsheetApp.getActiveSpreadsheet();
  var ss1 = wb.getSheets()[0];
  var ui = SpreadsheetApp.getUi();

  var lr = ss1.getLastRow();
  var lc = ss1.getLastColumn();
  var dl = ss1.getRange(lr, lc);
  var cl = ss1.getRange(lr, lc - 1);
  var bl = ss1.getRange(lr, lc - 2);
  var al = ss1.getRange(lr, lc - 3);
  var ss2 = wb.getSheetByName('timedata');

  var mfns = ss2.getRange("A:A").getValues();
  var i = 1;
  mfns.forEach(function(eachmfns) {
    if (cl.getValue() == mfns) {
    var dataRange = ss2.getRange(2, 1, 20, 5);
    var ss2 = wb.getSheetByName('timedata');
    dataRange = ss2.getRange(2 + i, 1 + 3);
    dataRange.setValue(al);
    i++; //to get the next row
}});
}

I would appreiate if anyone help me until this phase. If I can come until this point I believe I can calculate between the START time and STOP response time of a work so update TOTAL work time.

Thank you in advance.

Edit SOLUTION: The link to my file works, Form is not available but the script works when you run it. Please copy it your gDrive and try it out!

Answer 1

Required Reading:

• Array
• 2D array
• Best Practices

Notes:

• getValues() returns a two dimensional array indexed by rows and columns.(Array[0][1] = first row, second column)
• Avoiding getValue*() calls inside a loop can greatly increase performance.
• Use batch operations

Sample script:

function lastName() {
  var wb = SpreadsheetApp.getActiveSpreadsheet();
  var ss1 = wb.getSheets()[0];
  var ui = SpreadsheetApp.getUi();

  var lr = ss1.getLastRow();
  var lc = ss1.getLastColumn();
  const [al, bl, cl, dl] = ss1.getRange(lr, 1, 1, lc).getValues();
  var ss2 = wb.getSheetByName('timedata');

  var mfnsRng = ss2.getDataRange();
  const mfns = mfnsRng.getValues();//[[A1,B1..],[A2,B2..],[A3,B3..]...]
  mfns.forEach(function(row) {//row=currentRow=[A1,B1,C1..]
    if (cl === row[0]) {//row[0] = A1
      row[3] = al;//row[3] =Col D
      row[4] = dl;
    }
  });
  mfnsRng.setValues(mfns);//set modified values back
}

Answer 2

According to what I have understood of your problem, I believe you are basically trying to put the data of the last element of sheet 1 in the matching workcode element in sheet 2. If this is not what you wanted or if I missunderstood your question please let me know and I will update this answer accordingly.

However, if I am right, this is the solution to your issue (details explained in the comments of this piece of code):

Solution

function makeItWork(){
  
  var wb = SpreadsheetApp.getActiveSpreadsheet();
  var ss2 = wb.getSheetByName('timedata'); 
  var ss1 = wb.getSheets()[0];
  var lr = ss1.getLastRow();
  var lc = ss1.getLastColumn();
  
  // Get the desired values of the last added item including its workcode
  var timestamp = ss1.getRange(lr,1).getValue();
  var status = ss1.getRange(lr,4).getValue();
  var workcode = ss1.getRange(lr, 3).getValue();
  
  // Get the column values of workcode to then see which one matches with the last added item
  var columnValues = ss2.getRange('A:A').getValues(); 
  
  // Get values returns an Array with the elements nested in their own arrays,
  // we need to flat them to be able to do the array search later
  var searchValues = columnValues.flat();
  
  // Returns the row number of the adecuate workcode if it can match it to the one of the last element
  var searchResult = searchValues.indexOf(String(workcode)); 

  if(searchResult != -1){
  // If it found a match, add the data of the last element of sheet 1 in the right cells acording to the workcode match
  ss2.getRange(searchResult, 4).setValue(timestamp);
  ss2.getRange(searchResult, 5).setValue(status);
  }

}

I hope this has helped you, please let me know if you need anything else or if you did not understood something.