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

Question

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]

I have to apologize for my poor English skill. Although I saw your sample images, I cannot understand your question. Can I ask you about the detail of your question? For example, your provided images are your expected input situation? If my understanding is correct, can you provide your expected situation? — Tanaike, Aug 12 '22 at 00:37
thank you very much for your attention, I'll try to explain more detail about it. thank you — indri widistira, Aug 16 '22 at 03:49

score 0 · Answer 1 · answered Aug 12 '22 at 12:49

0

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

answered Aug 12 '22 at 12:49

edg

930
6
17

yes i think the problem comes from the return. Do I have to change the position of return?thank you very much for your help – indri widistira Aug 16 '22 at 03:51
You can remove the return. Your function doesn't need it, it would if your were expecting an output value from it. – edg Aug 16 '22 at 05:16
thank you very much, but if i remove return , I change one data in cell, the data in the whole column entirely change. How can I fix it? – indri widistira Aug 16 '22 at 05:48
If you want to stop the loop (not the function) after editing one row you can use `break` instead of `return` – edg Aug 16 '22 at 06:33

score 0 · Answer 2 · answered Aug 16 '22 at 05:27

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

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

2 Answers2