Effortlessly Automate Data Entry with Google Sheets
Effortlessly Automate Data Entry with Google Sheets can manage school student records with proper data entry. It can transform your work and save valuable time and resources. managing records can be a difficult task, especially for schools, businesses, or organizations that do a lot of work. But with the power of automation and Google Sheets, you can improve your expense management process easily without wasting more time.
In this tutorial, we have explained all step-by-step code with examples that how to use Apps script code to insert, update,delete records in database by selecting sheet name
Submit the Record in a database using the Apps script code
function submit() { var ss=SpreadsheetApp.getActiveSpreadsheet(); var form =ss.getSheetByName("New"); var classname=form.getRange("F13").getValue(); var datasheet=ss.getSheetByName(classname); var ui=SpreadsheetApp.getUi(); if (classname =="") { ui.alert("classname field is blank."); return;//to exit from this function } var response=ui.alert("Confirm", "Do you want to register this student?", ui.ButtonSet.YES_NO); if (response==ui.Button.NO)  { return;//to exit from this function  }  var blankRow=datasheet.getLastRow()+1; //identify the next blank row  datasheet.getRange (blankRow, 1).setValue(form.getRange("F7").getValue());  datasheet.getRange (blankRow, 2).setValue(form.getRange("F9").getValue());  datasheet.getRange (blankRow, 3).setValue(form.getRange("F11").getValue());  datasheet.getRange (blankRow, 4).setValue(form.getRange("I11").getValue());  datasheet.getRange (blankRow, 5).setValue(form.getRange("F13").getValue());  datasheet.getRange (blankRow, 6).setValue(form.getRange("I13").getValue());  datasheet.getRange (blankRow, 7).setValue(form.getRange("F15").getValue());  datasheet.getRange (blankRow, 8).setValue(form.getRange("I15").getValue());  datasheet.getRange (blankRow, 9).setValue(form.getRange("F17").getValue());  datasheet.getRange (blankRow, 10).setValue(form.getRange("F19").getValue());  datasheet.getRange (blankRow, 11).setValue(form.getRange("F21").getValue());  datasheet.getRange (blankRow, 12).setValue(form.getRange("F23").getValue());  datasheet.getRange (blankRow, 13).setValue(form.getRange("G23").getValue()); var database=ss.getSheetByName("DATABASE"); var blankRow=database.getLastRow()+1; //identify the next blank row  database.getRange (blankRow, 1).setValue(form.getRange("F7").getValue());  database.getRange (blankRow, 2).setValue(form.getRange("F9").getValue());  database.getRange (blankRow, 3).setValue(form.getRange("F11").getValue());  database.getRange (blankRow, 4).setValue(form.getRange("I11").getValue());  database.getRange (blankRow, 5).setValue(form.getRange("F13").getValue());  database.getRange (blankRow, 6).setValue(form.getRange("I13").getValue());  database.getRange (blankRow, 7).setValue(form.getRange("F15").getValue());  database.getRange (blankRow, 8).setValue(form.getRange("I15").getValue());  database.getRange (blankRow, 9).setValue(form.getRange("F17").getValue());  database.getRange (blankRow, 10).setValue(form.getRange("F19").getValue());  database.getRange (blankRow, 11).setValue(form.getRange("F21").getValue());  database.getRange (blankRow, 12).setValue(form.getRange("F23").getValue());  database.getRange (blankRow, 13).setValue(form.getRange("G23").getValue());       form.getRange("F9").clearContent();;  //ADMISSION DATE       form.getRange("F11").clearContent();   //NAME       form.getRange("I11").clearContent();   //DOB       form.getRange("F13").clearContent();  //CLASS       form.getRange("I13").clearContent();   //GENDER       form.getRange("F15").clearContent(); //ADDRESS       form.getRange("I15").clearContent(); //CATEGORY       form.getRange("F17").clearContent();  //EMAIL       form.getRange("F19").clearContent();  // FATHER NAME       form.getRange("F21").clearContent();  // PHONE NO       form.getRange("F23").clearContent(); // FEE       form.getRange("G23").clearContent();  // STATUS      ui.alert('Student Registered'); }
Update Record in a database using Apps script code()
function update() {   var SEARCH_COL_IDX = 0;   var RETURN_COL_IDX = 0;   var ss=SpreadsheetApp.getActiveSpreadsheet();   var form = ss.getSheetByName("Update"); //Form Sheet   var ui=SpreadsheetApp.getUi();   var response=ui.alert("!!! Warnning", "Are you sure want to update this record?", ui.ButtonSet.YES_NO);   if (response==ui.Button.NO)   {    return;//to exit from this function   }   var datasheet =form.getRange("F7:F7").getValues()[0][0];   ["Class VI", "Class VII", "Class VIII", "Class IX","Class X","DATABASE"].forEach(function(s)  {    var values = ss.getSheetByName(s).getDataRange().getValues();    for (var i = 0; i < values.length; i++)    {      var row = values[i];      if (row[SEARCH_COL_IDX]==datasheet)     {      var INT_R= i+1;      var values1= [[form.getRange("F7").getValue(),             form.getRange("F9").getValue(),             form.getRange("F11").getValue(),             form.getRange("I11").getValue(),             form.getRange("F13").getValue(),             form.getRange("I13").getValue(),             form.getRange("F15").getValue(),             form.getRange("I15").getValue(),             form.getRange("F17").getValue(),             form.getRange("F19").getValue(),             form.getRange("F21").getValue(),             form.getRange("F23").getValue(),             form.getRange("G23").getValue()]];      ss.getSheetByName(s).getRange (INT_R,1,1,13).setValues(values1);      return row[RETURN_COL_IDX];     }  } });  SpreadsheetApp.getUi().alert('Data updated'); }
Delete Record in a database using Apps script code
function Delete()
{ Â var ss = SpreadsheetApp.getActiveSpreadsheet();
var form = ss.getSheetByName(“Delete”);
//Form Sheet  var ui=SpreadsheetApp.getUi();
var response=ui.alert(“!!! Warning”, “Are you sure want to delete this record? once delete can not be recovered”, ui.ButtonSet.YES_NO);
if (response==ui.Button.NO)
{
return;//to exit from this function
} // Process the user’s response.
if (response ==ui.Button.YES)
{
var datasheet= form.getRange(“F7”).getValue();
[“Class VI”, “Class VII”, “Class VIII”, “Class IX”, “Class X”,“DATABASE”].forEach(function (s)
{
var values = ss.getSheetByName(s).getDataRange().getValues();
for (var i = 0; i < values.length; i++)
{
var row =values[i];
if(row[SEARCH_COL_IDX]==datasheet)
{
var INT_R =i+1
ss.getSheetByName(s).deleteRow(INT_R);
form.getRange(“F7”).clearContent();
form.getRange(“F9”).clearContent(); //ADMISSION DATE
form.getRange(“F11”).clearContent(); Â Â //NAME
form.getRange(“I11”).clearContent(); Â Â //DOB
form.getRange(“F13”).clearContent(); Â //CLASS
form.getRange(“I13”).clearContent(); Â Â //GENDER
form.getRange(“F15”).clearContent(); //ADDRESS
form.getRange(“I15”).clearContent(); //CATEGORY
form.getRange(“F17”).clearContent(); Â //EMAIL
form.getRange(“F19”).clearContent(); Â // FATHER NAME
form.getRange(“F21”).clearContent(); Â // PHONE NO
form.getRange(“F23”).clearContent(); // FEE
form.getRange(“G23”).clearContent(); Â // STATUS
return row[RETURN_COL_IDX]; Â Â }}})}
ui.alert(‘Record deleted successfully!’);
}
Effortlessly Automate Data Entry: Conclusion
Effortlessly Automate Data Entry with Google Sheets can significantly streamline your workflow, save valuable time, and reduce the risk of human error. By leveraging Google Sheets’ built-in functions, add-ons, and integrations with tools like Zapier or Google Apps Script, you can create a seamless process that handles repetitive tasks with minimal effort. Whether you’re managing complex datasets, tracking daily expenses, or updating inventories, automation allows you to focus more on analyzing and utilizing data rather than just entering it. Embrace the power of automation with Google Sheets and take your productivity to the next level.