How can Google Sheets Form Update Records from Results Using Google App script?

Question

I have a program that filters and updates data from an existing sheet.
The program works as follows:
1. Find and filter out the required value
2. Enter data in [Adjustment] column then update to database in Record sheet.
I tried to try but my program doesn't seem to work.
I tried to edit the program code but when run it will affect the other columns and the [adjustment] column value is entered wrong.
This is my link program

function Searchold(){
   var ss = SpreadsheetApp.getActiveSpreadsheet ();
var shtRecords = ss. getSheetByName ("RECORD");
var shtForm = ss. getSheetByName ("TEST") ;
var records = shtRecords. getDataRange () . getValues ();
var sField = shtForm. getRange ("A3").getValue ();
var sValue = shtForm.getRange ("A6").getValue();
var sCol = records [0].lastIndexOf(sField);
var results = records.filter(function(e){return sValue == e[sCol] });
if(results.length==0){SpreadsheetApp.getUi().alert("not found  values");}
else{
  shtForm.getRange(9,1,results.length,results[0].length).setValues(results);
}
}

function Updatenew(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var shtRecords = ss.getSheetByName("RECORD");
  var shtForm = ss.getSheetByName("TEST");
  var LastRow = shtForm.getRange("A8").getNextDataCell(SpreadsheetApp.Direction.DOWN).getLastRow();
  var newData = shtForm.getRange(9,1,LastRow -1,7).getValues();

  for(var i =0; i<newData.length;i++){
    var oldData= shtRecords.getDataRange().getValues();
    for(var j= 0;j<oldData.length;j++){
      if(newData[i][0] ==oldData[j][0]){
      
        var newData2 = [newData[i]];
        shtRecords.getRange(j + 1,1,1,newData2[0].length).setValues(newData2);
      }
    }
  }

}

Can you help me with the update program? Sincerely thank you

Answer 1

Modification points:

• When I saw your showing script of Updatenew, I think that each row of var oldData = shtRecords.getDataRange().getValues() is used in each loop of for (var i = 0; i < newData.length; i++) {}. By this, each row is overwritten by each row of newData. By this, all searched rows in "RECORD" sheet are the same value. I thought that this might be the reason for your issue.
• var oldData = shtRecords.getDataRange().getValues(); can be used one call.

In order to avoid this issue by modifying your script, as one of several methods, how about the following modification?

From:

for (var i = 0; i < newData.length; i++) {
  var oldData = shtRecords.getDataRange().getValues();
  for (var j = 0; j < oldData.length; j++) {
    if (newData[i][0] == oldData[j][0]) {
      var newData2 = [newData[i]];
      shtRecords.getRange(j + 1, 1, 1, newData2[0].length).setValues(newData2);
    }
  }
}

To:

var oldData = shtRecords.getDataRange().getValues();
for (var j = 0; j < oldData.length; j++) {
  for (var i = 0; i < newData.length; i++) {
    if (newData[0][0] == oldData[j][0]) {
      var newData2 = newData.splice(0, 1);
      shtRecords.getRange(j + 1, 1, 1, newData2[0].length).setValues(newData2);
      break;
    }
  }
}

Note:

• At the above modification, setValues is used in a loop. In this case, the process cost becomes high. If you want to reduce the process cost of the script, how about using Sheets API? When Sheets API is used, how about the following modification? Please enable Sheets API at Advanced Google services.

  • To

      var temp = newData.slice();
      var data = shtRecords.getDataRange().getValues().reduce((ar, r, i) => {
        if (temp[0][0] == r[0]) {
          var t = temp.splice(0, 1);
          t[0][2] = Utilities.formatDate(t[0][2], Session.getScriptTimeZone(), "dd/MM/yyyy");
          t[0][4] = Utilities.formatDate(t[0][4], Session.getScriptTimeZone(), "dd/MM/yyyy");
          ar.push({ range: `'RECORD'!A${i + 1}`, values: t });
        }
        return ar;
      }, []);
      Sheets.Spreadsheets.Values.batchUpdate({ data, valueInputOption: "USER_ENTERED" }, ss.getId());