how is the appscript code for editing data base but the data is the table?-CodePudding

First, I want to apologize if my english is not very good. I am a math teacher whom also use appscript to make teacher administration and student report. For teacher administration ,I make the student mark data base which can be save,find,search, edit, and delete. The sheet name to input student mark is "Nilai Harian" and will be recorded into "DatabaseNH". The teacher get used to spreadsheet table for inputting the student mark. in my appscript code, the function save ,find ,search are works, but the edit function still not work. With the code in my "edit function", I just can edit the first row, but the rest still the same data. How can I fix it? I attached my table and appscript code. thank you for your help and answer.

    function editnh() {
  
var Sheet = SpreadsheetApp.getActiveSpreadsheet();  
var nh = Sheet.getSheetByName('Nilai Harian'); 
var datanh = Sheet.getSheetByName('DatabaseNH');

var ui= SpreadsheetApp.getUi();

var response=ui.alert("Ubah",'Apakah anda ingin mengubah nilai?',ui.ButtonSet.YES_NO);
if (response==ui.Button.NO)
{
  return;
  }

var str = nh.getRange("D1").getValues();
var values=datanh.getDataRange().getValues();
var valuesnh=datanh.getRange(1,1,datanh.getLastRow(),1).getValues();

var valuesFound=false;

for (var i=0;i<values.length;i  ){

var rowValue=values[i]
var columnvalue=valuesnh[i]

if(rowValue[0]==str)

if (columnvalue[0]==str)
{
   var iRow=i 1;

     datanh.getRange(iRow,2).setValue(nh.getRange(4,4,39,4).getValue());
     datanh.getRange(iRow,3).setValue(nh.getRange(4,5,39,5).getValue());
     datanh.getRange(iRow,4).setValue(nh.getRange(4,6,39,6).getValue());
     datanh.getRange(iRow,5).setValue(nh.getRange(4,7,39,7).getValue());
     datanh.getRange(iRow,6).setValue(nh.getRange(4,8,39,8).getValue());
     datanh.getRange(iRow,7).setValue(nh.getRange(4,9,39,9).getValue());
     datanh.getRange(iRow,8).setValue(nh.getRange(4,10,39,10).getValue());
     datanh.getRange(iRow,9).setValue(nh.getRange(4,11,39,11).getValue());
     datanh.getRange(iRow,10).setValue(nh.getRange(4,12,39,12).getValue());
     datanh.getRange(iRow,11).setValue(nh.getRange(4,13,39,13).getValue());
     datanh.getRange(iRow,12).setValue(nh.getRange(4,14,39,14).getValue());
     datanh.getRange(iRow,13).setValue(nh.getRange(4,15,39,15).getValue());
     datanh.getRange(iRow,14).setValue(nh.getRange(4,16,39,16).getValue());
     datanh.getRange(iRow,15).setValue(nh.getRange(4,17,39,17).getValue());

    datanh.getRange(iRow,16).setValue(new Date()).setNumberFormat('yyyy-mm-dd h:mm');
    datanh.getRange(iRow,17).setValue(Session.getActiveUser().getEmail());


    ui.alert("Data sudah diperbaharui!");
    
    

    valuesFound=true;
      return;
}
    
    }

nh.getRange("D4:D39").clear();
nh.getRange("E4:E39").clear();
nh.getRange("F4:F39").clear();
nh.getRange("G4:G39").clear();
nh.getRange("H4:H39").clear();
nh.getRange("I4:I39").clear();
nh.getRange("J4:J39").clear();
nh.getRange("K4:K39").clear();
nh.getRange("L4:L39").clear();
nh.getRange("M4:M39").clear();
nh.getRange("N4:N39").clear();
nh.getRange("O4:O39").clear();
nh.getRange("P4:P39").clear();
nh.getRange("Q4:Q39").clear();

if (valuesFound==false){ui.alert("Data tidak ditemukan")}
}

[https://drive.google.com/file/d/1-6hDfNO-dakhiLdt5knApZ8FbZPEFvgJ/view?usp=sharing][https://drive.google.com/file/d/134BTk2OxKonDNLJtbwWgz3rVS9jWNHsr/view?usp=sharing][https://drive.google.com/file/d/1fcZ-NwJzjMj8kTAkDfMHr8i0YEAjdxD2/view?usp=sharing][https://drive.google.com/file/d/15Ep3ulOgE0Bc0Q5FSZ4kJqsX6jjhSj1X/view?usp=sharing]

CodePudding user response：

The issue most likely comes from the return after all the setValue calls as it will stop the loop and end the function execution.

Note : consider using setValues (plural) instead of multiple setValue calls as it will be much faster. For more information see : https://developers.google.com/apps-script/guides/support/best-practices#use_batch_operations

CodePudding user response：

Here is more detailed explanation for the case

I would like to create students daily assessment data which is used to input, save, edit and remove students score. for example; when a teacher wants to input a student score for Chapter 1, the sheet will appear as followed : [https://drive.google.com/file/d/152T21UFyzcoQ4ofcd56cnxN7eMcTj_7w/view?usp=sharing][1]

the data is input "Daily Assesment" sheet and will be saved as Database in "databaseNH" sheet [https://drive.google.com/file/d/1mOLSD5ZwznLvHQEwRLl3L-MWU4Gekhvb/view?usp=sharing][2]

Sometimes teachers do remedials assessment and change the data, and they do as picture below [https://drive.google.com/file/d/1lX0BY86DAhfouBjjMD9Gmwx0QDWwuyv4/view?usp=sharing][3]

for editing the data, the function is formulated as this code below

    function editnh() {
  
var Sheet = SpreadsheetApp.getActiveSpreadsheet();  
var nh = Sheet.getSheetByName('Daily Assesment'); 
var datanh = Sheet.getSheetByName('DatabaseNH');

var ui= SpreadsheetApp.getUi();

var response=ui.alert("Edit",'Do you want to edit the data?',ui.ButtonSet.YES_NO);
if (response==ui.Button.NO)
{
  return;
  }

var str = nh.getRange("D1").getValues();
var values=datanh.getDataRange().getValues();
var valuesnh=datanh.getRange(1,1,datanh.getLastRow(),1).getValues();

var valuesFound=false;

for (var i=0;i<values.length;i  ){

var rowValue=values[i]
var columnvalue=valuesnh[i]

if(rowValue[0]==str)

if (columnvalue[0]==str)
{
   var iRow=i 1;

     datanh.getRange(iRow,2).setValue(nh.getRange(4,4,39,4).getValue());
     datanh.getRange(iRow,3).setValue(nh.getRange(4,5,39,5).getValue());
     datanh.getRange(iRow,4).setValue(nh.getRange(4,6,39,6).getValue());
     datanh.getRange(iRow,5).setValue(nh.getRange(4,7,39,7).getValue());
     datanh.getRange(iRow,6).setValue(nh.getRange(4,8,39,8).getValue());
     datanh.getRange(iRow,7).setValue(nh.getRange(4,9,39,9).getValue());
     datanh.getRange(iRow,8).setValue(nh.getRange(4,10,39,10).getValue());
     datanh.getRange(iRow,9).setValue(nh.getRange(4,11,39,11).getValue());
     datanh.getRange(iRow,10).setValue(nh.getRange(4,12,39,12).getValue());
     datanh.getRange(iRow,11).setValue(nh.getRange(4,13,39,13).getValue());
     datanh.getRange(iRow,12).setValue(nh.getRange(4,14,39,14).getValue());
     datanh.getRange(iRow,13).setValue(nh.getRange(4,15,39,15).getValue());
     datanh.getRange(iRow,14).setValue(nh.getRange(4,16,39,16).getValue());
     datanh.getRange(iRow,15).setValue(nh.getRange(4,17,39,17).getValue());

    datanh.getRange(iRow,16).setValue(new Date()).setNumberFormat('yyyy-mm-dd h:mm');
    datanh.getRange(iRow,17).setValue(Session.getActiveUser().getEmail());


    ui.alert("Data has been edited!");
    
    

    valuesFound=true;
      return;
}
    
    }

nh.getRange("D4:D39").clear();
nh.getRange("E4:E39").clear();
nh.getRange("F4:F39").clear();
nh.getRange("G4:G39").clear();
nh.getRange("H4:H39").clear();
nh.getRange("I4:I39").clear();
nh.getRange("J4:J39").clear();
nh.getRange("K4:K39").clear();
nh.getRange("L4:L39").clear();
nh.getRange("M4:M39").clear();
nh.getRange("N4:N39").clear();
nh.getRange("O4:O39").clear();
nh.getRange("P4:P39").clear();
nh.getRange("Q4:Q39").clear();

if (valuesFound==false){ui.alert("Data not found")}
}

Using this code, if the data in cell "D1"= cell "C1:C", then the data will be edited in cell "D4 : Q39". See the pic below: [https://drive.google.com/file/d/1x2Bjhw0MIJeVcqhe4laomfvbZOD46uBn/view?usp=sharing][4]

However; it turns out that the code is still wrong since not all of the data is edited, it's only the data in the first row as the picture below

[https://drive.google.com/file/d/1AxuYuRuctZNePGkbgyYuBuG2yiXQY1B2/view?usp=sharing][5]

Had the code been correct, when the data is edited, sheet "databaseNH" should have been = data in sheet "daily assesment"

[https://drive.google.com/file/d/1RiU3booygyXGIZKCcx6DP1hc8JOunxdJ/view?usp=sharing][1]

Thank you for your attention