Google Sheets second call help! / Solved

Bigsykings · March 25, 2022, 12:19am

hello dear friends, I have a script and an application, I have a search value called “nis” that I want to ask you here, and secondly, I created another variable “no” in my application, so I want to search with both “nis” and “no”. I could not solve it, I am sharing the script and ai files with you, I would be very grateful if you could save me from this situation, thank you in advance for your help

Note: Searching with “nis” is fine, but I want to search with “no” as well.

function doGet(e) {
  return ceksheet(e);
}

function doPost(e) {
  return ceksheet(e);
}

function ceksheet(e){
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName(e.parameter.SH);  

//------------------UPDATE DATA PASSWORD SISWA------------------
   if (e.parameter.func == "UPDATE") {
    var nis  = e.parameter.NIS;
    var nama = e.parameter.NAMA;
    var kelas= e.parameter.KELAS;
    var jur  = e.parameter.JURUSAN;
    var jur1  = e.parameter.JURUSAN1;
    var no  = e.parameter.NO;
 
    var lr  = sh.getLastRow();
     
    for(var row=1;row<=lr;row++){
      var data_nis = sh.getRange(row, 1).getValue();
      if(data_nis==nis){
        sh.getRange(row, 2).setValue(nama);   //edit data nama
        sh.getRange(row, 3).setValue(kelas);  //edit data kelas
        sh.getRange(row, 4).setValue(jur);    //edit data jurusan
        sh.getRange(row, 5).setValue(jur1);    //edit data jurusan1
             
        return ContentService.createTextOutput("Data Berhasil di UPDATE"); 
      } 
    }     
  } 
  
  //-----------------------DELETE DATA---------------
  if (e.parameter.func == "DELETE") {
    var nis  = e.parameter.NIS;
    var lr  = sh.getLastRow();
     
    for(var row=1;row<=lr;row++){
      var data_nis = sh.getRange(row, 1).getValue();
      if(data_nis==nis){
        sh.deleteRow(row);
             
        return ContentService.createTextOutput("Data Berhasil di DELETE"); 
      } 
    }     
  } 
  
  //------------------CARI DATA SINGLE RECORD------------------ 
  if (e.parameter.func == "CARI") {
    var nis = e.parameter.NIS;

    var data=false;
    var lr= sh.getLastRow();
    for(var row=1;row<=lr;row++){     
      var data_nis = sh.getRange(row, 1).getValue();
      if(data_nis==nis){                           //jika data NIS
        data=true;
        var hasil = sh.getRange(row, 2).getValue() +"|"+ sh.getRange(row, 3).getValue()+"|"+ sh.getRange(row, 4).getValue()+"|"+ sh.getRange(row, 4).getValue()+"|"+ sh.getRange(row, 5).getValue();
      }
    }
    if (data){
      var result= "Found|"+hasil;
    }
    else{
      var result="Not Found";
    }
    return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.TEXT);
  }
  
  //-----------------GET ALL-----------------
  if (e.parameter.func="GETALL") {
    var rg=sh.getDataRange().getValues();
    var data ="";
    for (var row=1; row<rg.length; ++row) {
      data += row +". "+ rg[row].join(' - ')+'|\n';
     }
   return ContentService.createTextOutput(data).setMimeType(ContentService.MimeType.TEXT); 
  }  
  
}

application: UPDATE_DELETE(1).aia (7.6 KB)

script :
nisandnoScript.txt (2.6 KB)

Google Sheet

MY APP

search2819×630 136 KB

Searching is done on this line, it gave an error every time I tried, I couldn’t add " no " as a second search.

Still-learning · March 25, 2022, 2:40am

Have you tried this code?

Credit : @TimAi2

Very easy to use.

For calling instead of script you could use quiz query method

Still-learning · March 25, 2022, 6:58am

For calling use gviz with this query

Select A, B, C, D, E WHERE A = int OR E = int

Here int refers the number

When web got text convert the table into CSV then remove 1St and last item. Simple

Bigsykings · March 25, 2022, 3:21pm

Hello, first of all, I examined the place you shared, but it didn’t work as a solution because the script I have and the operations there are different, and frankly, I couldn’t quit because I don’t have much knowledge. Now the process I want from you is very simple, I want to search through two rows, the first script I shared is “nis”, the data is in 1 row and the second is " The data named “no” is in room 6 row and when I add the data number 6 “no” to the script, it gives an error. I am sharing it with you again for your review…

function doGet(e) {
  return ceksheet(e);
}

function doPost(e) {
  return ceksheet(e);
}

function ceksheet(e){
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName(e.parameter.SH);  

//------------------UPDATE DATA PASSWORD SISWA------------------
   if (e.parameter.func == "UPDATE") {
    var nis  = e.parameter.NIS;
    var nama = e.parameter.NAMA;
    var kelas= e.parameter.KELAS;
    var jur  = e.parameter.JURUSAN;
    var jur1  = e.parameter.JURUSAN1;
    var no  = e.parameter.NO;
 
    var lr  = sh.getLastRow();
     
    for(var row=1;row<=lr;row++){
      var data_nis = sh.getRange(row, 1).getValue();
      if(data_nis==nis){
        sh.getRange(row, 2).setValue(nama);   //edit data nama
        sh.getRange(row, 3).setValue(kelas);  //edit data kelas
        sh.getRange(row, 4).setValue(jur);    //edit data jurusan
        sh.getRange(row, 5).setValue(jur1);    //edit data jurusan1
             
        return ContentService.createTextOutput("Data Berhasil di UPDATE"); 
      } 
    }     
  } 
  
  //-----------------------DELETE DATA---------------
  if (e.parameter.func == "DELETE") {
    var nis  = e.parameter.NIS;
    var lr  = sh.getLastRow();
     
    for(var row=1;row<=lr;row++){
      var data_nis = sh.getRange(row, 1).getValue();
      if(data_nis==nis){
        sh.deleteRow(row);
             
        return ContentService.createTextOutput("Data Berhasil di DELETE"); 
      } 
    }     
  } 
  
  //------------------CARI DATA SINGLE RECORD------------------ 
  if (e.parameter.func == "CARI") {
    var nis = e.parameter.NIS;

    var data=false;
    var lr= sh.getLastRow();
    for(var row=1;row<=lr;row++){     
      var data_nis = sh.getRange(row, 1).getValue();
      if(data_nis==nis){                           //jika data NIS
        data=true;
        var hasil = sh.getRange(row, 2).getValue() +"|"+ sh.getRange(row, 3).getValue()+"|"+ sh.getRange(row, 4).getValue()+"|"+ sh.getRange(row, 4).getValue()+"|"+ sh.getRange(row, 5).getValue();
      }
    }
    if (data){
      var result= "Found|"+hasil;
    }
    else{
      var result="Not Found";
    }
    return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.TEXT);
  }
  
  //-----------------GET ALL-----------------
  if (e.parameter.func="GETALL") {
    var rg=sh.getDataRange().getValues();
    var data ="";
    for (var row=1; row<rg.length; ++row) {
      data += row +". "+ rg[row].join(' - ')+'|\n';
     }
   return ContentService.createTextOutput(data).setMimeType(ContentService.MimeType.TEXT); 
  }  
  
}

There is data_nis in the UPDATE section and row1 with it, I can bring and update the data below from the application.

if (e.parameter.func == "UPDATE") {
    var nis  = e.parameter.NIS;
    var nama = e.parameter.NAMA;
    var kelas= e.parameter.KELAS;
    var jur  = e.parameter.JURUSAN;
    var jur1  = e.parameter.JURUSAN1;
    var no  = e.parameter.NO;
 
    var lr  = sh.getLastRow();
     
    for(var row=1;row<=lr;row++){
      var data_nis = sh.getRange(row, 1).getValue();
      if(data_nis==nis){
        sh.getRange(row, 2).setValue(nama);   //edit data nama
        sh.getRange(row, 3).setValue(kelas);  //edit data kelas
        sh.getRange(row, 4).setValue(jur);    //edit data jurusan
        sh.getRange(row, 5).setValue(jur1);    //edit data jurusan1
             
        return ContentService.createTextOutput("Data Berhasil di UPDATE"); 
      } 
    }     
  }

from here i can see only data same
** **

//------------------CARI DATA SINGLE RECORD------------------ 
  if (e.parameter.func == "CARI") {
    var nis = e.parameter.NIS;

    var data=false;
    var lr= sh.getLastRow();
    for(var row=1;row<=lr;row++){     
      var data_nis = sh.getRange(row, 1).getValue();
      if(data_nis==nis){                           //jika data NIS
        data=true;
        var hasil = sh.getRange(row, 2).getValue() +"|"+ sh.getRange(row, 3).getValue()+"|"+ sh.getRange(row, 4).getValue()+"|"+ sh.getRange(row, 4).getValue()+"|"+ sh.getRange(row, 5).getValue();
      }
    }
    if (data){
      var result= "Found|"+hasil;
    }
    else{
      var result="Not Found";
    }
    return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.TEXT);
  }

The issue that I can’t solve is the 6 numbers with the row, that is, with the “no”.

Searching in “nis”, that is, row number 1 and when I add it, I get an error every time

@Still-learning @Taifun @TimAi2

Still-learning · March 25, 2022, 3:36pm

May be other could help you because me too not strong with these js codes…

However if you want to run two way matching if possible try 2D lib

But i feel gviz is the easiest one

search : use gviz
update/ delet : use script code

Still-learning · March 25, 2022, 3:49pm

just test this with either tag1or tag2 no

https://docs.google.com/spreadsheets/u/0/d/1bHH_TRT5aVaqVCiMk49-GAvfvVqZmEkH8pTbUabEnd8/gviz/tq?tqx=out:html&tq= select A, B, C, D, E WHERE A = 320 OR E = 502

if either one number correct then it will return the data as mentioned col. If not it will return the header alone

if you are trying in app thn in the place of html you can use csv and in the web got text we could use list from csv table to list block to convert into list item of data

the result is (based on above link)

320 is not in tag1 but 502 is in tag2 so it returns data else header alone will return

TimAi2 · March 26, 2022, 5:15pm

@Still-learning

See here, now an extension for it, and recently updated script:

Still-learning · March 26, 2022, 5:38pm

That’s is fantastic… no worry now . Even newbees can easily adopt this. Awesome !!!

I will stick on to this herethen .

Bigsykings · March 26, 2022, 11:45pm

Thank you very much for your support guys, I have started my project, of course, if I get stuck in some places, I will ask for help, thank you for your good intentions.

@TimAi2 @Still-learning

Kodular Together strong family

system · April 25, 2022, 11:46pm

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.