Feeling Lucky?

Create Simple Data Entry in Google Sheets

 Bismillah,

Assalamualaikum,


Hello everyone, in this article I will discuss how to make simple data entry in Google Sheets. Data Entry is the process of entering new data into the database. In this tutorial, we will also discuss how to update and delete existing data.


  1. Creating Display

    1. Creating display interface can be done by setting the size of rows and columns, adding background color, adding table lines and setting the size and type of font.



  1. Data validation

    1. In the column to be input, data validation adjustments are made. This feature can be accessed on the Data > Data Validation.

    2. Data Validation for Date


  1. Data Validation for Email



  1. Creating a Button

    1. Creating a button can be done by Insert > Drawing.

    2. Duplication of the drawing button can be done by going to the edit mode of the button you want to copy, select the button, copy with ctrl + C, save and close, then open a new Drawing menu with Insert > Drawing, paste with ctrl + V

    3. Adjust the position of the button on the display .

    4. Gridlines can be removed using the View > Show > Gridlines.


Create a button with the Drawing feature


Display after setting the position of the Button


Gridlines button that has been removed



  1. Creating Database sheet

    1. Database Sheet is used to store data that has been inputted by Data Entry.

    2. The order of the columns is Nickname, Name, Date, Phone, Email and Address.

    3. While the sheet containing the Data Entry is renamed to "User Form"


Change the name of the sheet containing the Data Entry to "User Form"



Display the "Database" Sheet


  1. Create a Save script

    1. Open the Tools menu > Script Editor, if not, please try Extensions > Apps Script.

    2. If the Script Editor or Apps Script is not available, make sure your file is in Google Sheets format, not Excel format, by going to File > Save As Google Sheets.

    3. Please paste the script below in Apps Script.


function submitData (){

 

var ss = SpreadsheetApp.getActiveSpreadsheet();

var formSS = ss.getSheetByName("User Form");

var dataSheet = ss.getSheetByName("Database");

 

var values = [[

formSS.getRange("D7").getValue(),

formSS.getRange("D9").getValue(),

formSS.getRange("D11").getValue(),

formSS.getRange("D13").getValue(),

formSS.getRange("D15").getValue(),

formSS.getRange("D17").getValue(),

]]

 

dataSheet.getRange(dataSheet.getLastRow()+1, 1, 1, 6).setValues(values);

 

 

formSS.getRange("D7").clear();

formSS.getRange("D9").clear();

formSS.getRange("D11").clear();

formSS.getRange("D13").clear();

formSS.getRange("D15").clear();

formSS.getRange("D17").clear();

 

SpreadsheetApp.getUi().alert("Data Saved Successfully");

}


  1. Copy the text “submitData”

  2. Click the three dots on the “Save” button, click “Assign script”, paste the text “submitData”.


Assign script on the "Save" button



  1. Create a Clear Script

    1. Please paste the script below into Apps Script.


function clearForm(){

 

  var myGoogleSheets = SpreadsheetApp.getActiveSpreadsheet();

  var shUserform = myGoogleSheets.getSheetByName("User Form");

 

  var ui = SpreadsheetApp.getUi();

 

  var response  = ui.alert("Reset Confirmation", "Do you want to reset this form?", ui.ButtonSet.YES_NO);

 

  if (response == ui.Button.YES){

 

    shUserform.getRange("D4").clear();

    shUserform.getRange("D7").clear();

    shUserform.getRange("D9").clear();

    shUserform.getRange("D11").clear();

    shUserform.getRange("D13").clear();

    shUserform.getRange("D15").clear();

    shUserform.getRange("D17").clear();

 

  }

}


  1. Copy the text “clearForm”

  2. Click the three dots on the “Clear” button, click “Assign script”, paste the text “clearForm”.


  1. Create a Search script

    1. Please paste the script below into Apps Script.


var SPREADSHEET_NAME = "Database";

var SEARCH_COL_IDX = 0;

var RETURN_COL_IDX = 0;

 

function searchStr(){

 

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var formSS = ss.getSheetByName("User Form");

 

  var str = formSS.getRange("D4").getValue();

  var values = ss.getSheetByName("Database").getDataRange().getValues();

 

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

      var row = values[i];

      if (row [SEARCH_COL_IDX] == str ){

 

        formSS.getRange("D7").setValue(row[0]);

        formSS.getRange("D9").setValue(row[1]);

        formSS.getRange("D11").setValue(row[2]);

        formSS.getRange("D13").setValue(row[3]);

        formSS.getRange("D15").setValue(row[4]);

        formSS.getRange("D17").setValue(row[5]);

 

 

        return row [RETURN_COL_IDX];

      }

  }

      

  }


  1. Copy the text “searchStr”

  2. Click the three dots on the “Search” button, click “Assign script”, paste the text “searchStr”.


  1. Create an Update script

    1. Please paste the script below into Apps Script.


function updateData (){

 

var SPREADSHEET_NAME = "Database";

var SEARCH_COL_IDX = 0;

var RETURN_COL_IDX = 0;

 

var ss = SpreadsheetApp.getActiveSpreadsheet();

var formSS = ss.getSheetByName("User Form");

var dataSheet = ss.getSheetByName("Database");

 

var str = formSS.getRange("D4").getValue();

var values = ss.getSheetByName("Database").getDataRange().getValues();

 

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

    var row = values [i];

    if (row [SEARCH_COL_IDX] == str){

        var INT_R = i+1

 

        var values1 = [[

        formSS.getRange("D7").getValue(),

        formSS.getRange("D9").getValue(),

        formSS.getRange("D11").getValue(),

        formSS.getRange("D13").getValue(),

        formSS.getRange("D15").getValue(),

        formSS.getRange("D17").getValue(),

        ]];

 

dataSheet.getRange(INT_R, 1, 1, 6).setValues(values1);

SpreadsheetApp.getUi().alert("Data Updated");

 

return row [RETURN_COL_IDX];

 

    }

 

}

}


  1. Copy the text “updateData”

  2. Click the three dots on the “Update” button, click “Assign script”, paste the text “updateData”.


  1. Create a Delete script

    1. Please paste the script below into Apps Script.


function rowDelete (){

 

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var formSS = ss.getSheetByName("User Form");

  var dataSheet = ss.getSheetByName("Database");

 

  var ui = SpreadsheetApp.getUi();

  var response = ui.alert('Delete Confirmation','Are you sure to delete this data?', ui.ButtonSet.YES_NO);

 

  if(response == ui.Button.YES){

 

    var str = formSS.getRange("D4").getValue();

    var values = ss.getSheetByName("Database").getDataRange().getValues();

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

      var row = values [i];

      if(row [SEARCH_COL_IDX] == str){

 

        var INT_R = i+1

 

        dataSheet.deleteRow(INT_R);

 

        formSS.getRange("D7").clear();

        formSS.getRange("D9").clear();

        formSS.getRange("D11").clear();

        formSS.getRange("D13").clear();

        formSS.getRange("D15").clear();

        formSS.getRange("D17").clear();

 

        return row [RETURN_COL_IDX];

 

      }

 

    }

 

  }

 

 

}



  1. Copy the text “rowDelete”

  2. Click the three dots on the “Delete” button, click “Assign script”, paste the text “rowDelete”.



Thus, a simple data entry tutorial. Hopefully useful, thank you.


Wassalamualaikum

Comments

Post a Comment

Popular posts from this blog

How to use For Loops in Apps Script

Image
  Hello everyone, In this article we will try to create a looping code in Google Apps Script with For Loop. The use of looping code is to run the same formula several times without writing it one by one.  Let's create rows number in the right side of this table start with 1 : So, to do this, we could write for loops code in our script. Basic form of for loops code is : for ( var ...; ....; ...){   }   “var ….;” is to declare the variable and the initial number. The middle “….. ;” is to state the maximum or minimum number and to determine the number of iterations. The right  “….. ;” is to state the formula of looping, can be plus or minus.   Here is the code that I add to Apps Script to create number 1 to 4 in cell E4 to E7   for ( var i = 0 ; i < 4 ; i ++  ){      activeSheet . getRange ( i + 4 , 5 ). setValue ( i + 1 );                }   i = 0; it means the initial number of the variable i is 0. i < 4; it means the variable i can’t equal or more than 4. i++; it means in
Read more

Bagaimana cara bayar pajak 5 tahunan? (Lokasi di Samsat Blitar Kota)

Image
Hai,  pada kesempatan kali ini aku akan berbagi bagaimana cara membayar pajak 5 tahunan. Assalamualaikum warohmatullohi wabarokatuh.. Jadi kemarin tanggal 29 Oktober 2022, saya menunaikan kewajiban membayar pajak 5 tahunan sepeda motor. Karena saya domisili di Kota Blitar, jadi saya membayar pajak di kantor Samsat Blitar jalan Melati. Berkas yang wajib disiapkan adalah bpkb,  stnk (lepasin dulu dari pastiknya) dan ktp sesuai nama yang di stnk. Kemu dian,  ke fotocopyan depan samsat, bilang saja untuk pajak 5 tahunan. Kemarin  ibuknya udah paham, jadi saya langsung menyerahkan berkas bpkb, stnk dan ktp yang sudah disiapkan tadi Biaya untuk proses ini 12rb ya teman. Dari fotokopian ini berkas2 yang sudah disiapkan tadi akan dirapihkan dan dimasukkan ke map berwarna merah. Selanjutnya adalah menuju ke tempat cek fisik roda 2, disini karena masih tutup, map map dari orang lain yang juga akan melakukan perpanjangan 5 tahunan ditumpuk di depan loket.  Ohya numpuk mapnya di atas ya, kemarin s
Read more

Create Your First Code in Apps Script

Image
Hello everyone, In this article we will try to make a code in Apps Script to get data or value from a cell in Google Sheets and store it in another cell. Apps Script is a coding platform in G Suite that can be used to build simple applications on top of existing apps such as Sheets, Docs, Forms, etc. With Apps Script, you only need a browser and a google account to start scripting. Apps Script allows you to extend and manipulate Google apps like Drive, Sheets, Docs, and Gmail, so you can run the process the way you want.  Create Apps Script File in Your Google Drive Open your google drive account. Right click > More > Google Apps Script. If there is no Google Apps Script, select Connect more apps and search for Google Apps Script. Open your Google Apps Script. At the top of the page, you can rename your Apps Script File. The name of your script file at the top of the page You can rename your script file Create a Google Sheets File in your G Drive and open it. Set B1 Cell as a dat
Read more