Admission form || School Management System || CRUD
This script contains four functions to manage student records in a Google Sheets spreadsheet using Google Apps Script. Here’s a brief overview of each function:
addNewStudent(data):
- Adds a new student to the spreadsheet.
- Checks if the student already exists based on a unique document number.
- If the student exists, returns an error message.
- If the student does not exist, adds a new row with the student’s data and formats some cells.
- Optionally sends an SMS notification if the student’s phone number and an API key are available.
- Returns the new student ID and the SMS response message (if applicable).
updateFunST(data):
- Updates an existing student's data in the spreadsheet.
- Checks if the student exists based on a unique ID.
- If the student exists, updates their data.
- If the student does not exist, returns an error message.
- Formats some cells after updating the data.
- Returns a success or failure message.
deleteFunST(data):
- Deletes a student’s record in the spreadsheet.
- Marks the student’s record as "DELETE" based on their unique ID.
- If the student exists, marks the record as deleted.
- If the student does not exist, returns an error message.
- Returns a success or failure message.
readFunST(data):
- Reads a student’s data from the spreadsheet based on their unique ID.
- Returns the matched student’s data.
Put the following code inside the
Code.gs file below the previous codes:Please note, the following code works with the specified spreadsheet. Make a copy of the spreadsheet and ensure that you do not rearrange the columns, delete any sheets, or rename any sheets.
After making a copy, get the spreadsheet ID and open the userSMS spreadsheet. Go to the "codygo" sheet and replace the highlighted value with the new one.
Code.gs
//Add new student
function addNewStudent(data){
var ws = SpreadsheetApp.openById(data.adreg).getSheetByName("addatastore");
var dat = ws.getDataRange().getValues();
for(var i =2;i<dat.length;i++){
if(dat[i][22]==data.nf9.toString()){
var exid =dat[i][0];
dat.length =0
var rv =["exist","Document no."+data.nf9+" is already exist with id-"+exid+"."];
return rv;
}}
dat.length =0
var dt = Utilities.formatDate(new Date(),Intl.DateTimeFormat().resolvedOptions().timeZone, 'dd-MM-yyyy HH:mm:ss');
var lr= ws.getLastRow()+1;
var newId ="E"+zz(lr-2,6)
var m = '"';
var space = '" "';
var leave = '"Leaved"';
var pending = '"Pending"';
var due = '"Dues"';
var del = '"DELETE"';
var adv = '"Advance"';
var age = "=int(YEARFRAC(e"+lr+",today()))";
var monthly="=arrayformula(sum(if(AK"+lr+"<>"+pending+",if(AK"+lr+"<>"+leave+",if(AK"+lr+"<>"+del+",BP"+lr+":BT"+lr+")))))";
var allPaid = "=SUMIF(payhis!F:F,"+m+newId+m+",payhis!J:J)";
var allMargin = "=SUMIF(payhis!F:F,"+m+newId+m+",payhis!K:K)";
var yearly = "=SUM(BV"+lr+":CL"+lr+")";
var afterPaid = "=MINUS(BH"+lr+",BF"+lr+")";
var afterMargin = "=MINUS(BI"+lr+",BG"+lr+")";
var status1 = "=IF(BJ"+lr+">0,"+due+","+adv+")";
var status2 = "=IF(BL"+lr+">0,"+due+","+adv+")";
var setnote = "=concatenate(BN2,"+space+", BO"+lr+")";
var hisbk = "Admit in class "+data.nf44+" - "+data.nf45+" on "+dt;
ws.appendRow([newId,data.nf50,data.nf1,data.nf4,data.nf5,age,data.nf11,
data.nf12,data.nf16,data.nf17,data.nf18,data.nf19,data.nf20,data.nf21,data.nf27,data.nf28,
data.nf29,data.nf30,data.nf31,data.nf14,data.nf15,data.nf8,data.nf9,data.nf51,data.nf36,
data.nf37,data.nf32,data.nf33,data.nf34,data.nf35,data.adnote,data.nf39,data.nf40,
data.nf41,data.nf44 +" - "+data.nf45,dt,data.nf44,data.nf45,hisbk,,data.nf49,data.nf22,
data.nf24,data.nf23,data.nfe,data.nf25,data.nf52,data.nf26,data.bkn,data.bknac,data.ifsc,
data.nf10,data.nf09,data.nf2,data.nf3,data.nf6,data.nf7,allPaid,allMargin,yearly,afterPaid,
afterMargin,status1,,status2,setnote,data.pnote,data.nf46,data.nf47,data.nf48,data.nf43,data.nfoth,monthly,
,data.nf42]);
var lastRow = ws.getLastRow();
ws.getRange('c'+ lastRow +':bo'+lastRow).setNumberFormat('@STRING@');
var apikey = ws.getRange(1,76).getValue();
if(data.nf14!=""&&apikey!=""&&ws.getRange(1,89).getValue()=="YES"){
var smsb ="Dear "+data.nf1+", your addmission is successfully taken with id-"+newId+"." ;
var url = "https://www.fast2sms.com/dev/bulkV2?authorization="+apikey+"&sender_id=TXTIND&message="+
smsb+"&route=v3&numbers="+data.nf14+"";
var options = {"async": true,"crossDomain": true,"method": "GET"}
var response = UrlFetchApp.fetch(url, options);
var rd = JSON.parse(response.getContentCode())
var rv = [newId+" | "+rd.message];
}else{
var rv = [newId,""];}
return rv;
}
//update student data
function updateFunST(data) {
var ws = SpreadsheetApp.openById(data.adreg).getSheetByName("addatastore");
var code = "E"+data.enro;
var dt = Utilities.formatDate(new Date(),Intl.DateTimeFormat().resolvedOptions().timeZone, 'dd-MM-yyyy HH:mm:ss');
var dat = ws.getRange(3,1,ws.getLastRow()+1,1).getValues();
for(var i =2;i<dat.length;i++){
if(dat[i][0]==code){
dat.length =0
var d = i+3;
ws.getRange(d, 2).setValue(data.nf50);
ws.getRange(d, 3).setValue(data.nf1);
ws.getRange(d, 4).setValue(data.nf4);
ws.getRange(d, 5).setValue(data.nf5);
ws.getRange(d, 7).setValue(data.nf11);
ws.getRange(d, 8).setValue(data.nf12);
ws.getRange(d, 9).setValue(data.nf16);
ws.getRange(d, 10).setValue(data.nf17);
ws.getRange(d, 11).setValue(data.nf18);
ws.getRange(d, 12).setValue(data.nf19);
ws.getRange(d, 13).setValue(data.nf20);
ws.getRange(d, 14).setValue(data.nf21);
ws.getRange(d, 15).setValue(data.nf27);
ws.getRange(d, 16).setValue(data.nf28);
ws.getRange(d, 17).setValue(data.nf29);
ws.getRange(d, 18).setValue(data.nf30);
ws.getRange(d, 19).setValue(data.nf31);
ws.getRange(d, 20).setValue(data.nf14);
ws.getRange(d, 21).setValue(data.nf15);
ws.getRange(d, 22).setValue(data.nf8);
ws.getRange(d, 23).setValue(data.nf9);
ws.getRange(d, 24).setValue(data.nf51);
ws.getRange(d, 25).setValue(data.nf36);
ws.getRange(d, 26).setValue(data.nf37);
ws.getRange(d, 27).setValue(data.nf32);
ws.getRange(d, 28).setValue(data.nf33);
ws.getRange(d, 29).setValue(data.nf34);
ws.getRange(d, 30).setValue(data.nf35);
ws.getRange(d, 31).setValue(data.adnote);
ws.getRange(d, 32).setValue(data.nf39);
ws.getRange(d, 33).setValue(data.nf40);
ws.getRange(d, 34).setValue(data.nf41);
ws.getRange(d, 37).setValue(data.nf44);
ws.getRange(d, 38).setValue(data.nf45);
ws.getRange(d, 42).setValue(data.nf22);
ws.getRange(d, 43).setValue(data.nf24);
ws.getRange(d, 44).setValue(data.nf23);
ws.getRange(d, 45).setValue(data.nfe);
ws.getRange(d, 46).setValue(data.nf25);
ws.getRange(d, 47).setValue(data.nf52);
ws.getRange(d, 48).setValue(data.nf26);
ws.getRange(d, 49).setValue(data.bkn);
ws.getRange(d, 50).setValue(data.bknac);
ws.getRange(d, 51).setValue(data.ifsc);
ws.getRange(d, 52).setValue(data.nf10);
ws.getRange(d, 53).setValue(data.nf09);
ws.getRange(d, 54).setValue(data.nf2);
ws.getRange(d, 55).setValue(data.nf3);
ws.getRange(d, 56).setValue(data.nf6);
ws.getRange(d, 57).setValue(data.nf7);
ws.getRange(d, 67).setValue(data.pnote);
ws.getRange(d, 68).setValue(data.nf46);
ws.getRange(d, 69).setValue(data.nf47);
ws.getRange(d, 70).setValue(data.nf48);
ws.getRange(d, 71).setValue(data.nf43);
ws.getRange(d, 72).setValue(data.nfoth);
ws.getRange(d, 75).setValue(data.nf42);
var systemNote = "ID:"+code+",Mode:Update, Date:"+dt;
ws.getRange(d, 39).setValue(systemNote);
ws.getRange(d, 40).setValue(dt);
var lastRow = ws.getLastRow();
ws.getRange('c'+ lastRow +':be'+lastRow).setNumberFormat('@STRING@');
var result = ["pass","Data successfully updated."];
return result;}}
dat.length =0
var result =["fail","ID "+code+" not exist."];
return result;}
//Delete student
function deleteFunST(data) {
var ws = SpreadsheetApp.openById(data.adreg).getSheetByName("addatastore");
var code = "E"+data.enro;
var lr= ws.getLastRow()+1;
var flag=1;
for(var i = 2;i <=lr;i++){
if(code == ws.getRange(i,1).getValue()){
flag=0;
ws.getRange(i,1).setValue("DELETE");
ws.getRange(i,37).setValue("DELETE");
ws.getRange(i,38).setValue("");
var result = ["pass","Deletation is successfull."];
return result;}}
if(flag==1){
var result=["fail","ID "+code+" not exist."];
return result;}};
//read student data by id
function readFunST(data) {
var fromSheet = SpreadsheetApp.openById(data.adreg).getSheetByName('addatastore');
var lr = fromSheet.getLastRow();
var fromData = fromSheet.getDataRange().getValues();
var idv = "E"+zz(data.enro,6);
var matchrows = [];
for (var i = 2; i < lr; i++) {
if(fromData[i][0] === idv){
matchrows.push(fromData[i]);
}}
return matchrows;
}
Next code will be available in the next post