0

I just learn to create entry data form in google sheet, I managed to get tutorial in Google and seems work fine with my little modification to my meet. but somehow I think is going slow over time after adding more entry point in my sheet form.

just clarify, all sript purely adaptation from https://thedatalabs.org/automated-data-entry-form-google-sheet/

my sheet in https://docs.google.com/spreadsheets/d/1LW6bA7xBdkdkhgE0_JTv-OKsFDNZ96LCm2yK_sodWqQ/edit?usp=sharing

// Function to submit the data to Database sheet
function submitDataX() {
     
  var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet 

  var shUserForm= myGooglSheet.getSheetByName("AUser Form"); //delcare a variable and set with the User Form worksheet

  var datasheet = myGooglSheet.getSheetByName("database palsu"); ////delcare a variable and set with the Database worksheet

  //to create the instance of the user-interface environment to use the messagebox features
  var ui = SpreadsheetApp.getUi();
  
  // Display a dialog box with a title, message, and "Yes" and "No" buttons. The user can also
  // close the dialog by clicking the close button in its title bar.
  var response = ui.alert("Submit", 'Udah yakin input data?',ui.ButtonSet.YES_NO);

  // Checking the user response and proceed with clearing the form if user selects Yes
  if (response == ui.Button.NO) 
  {return;//exit from this function
  } 
 
  //Validating the entry. If validation is true then proceed with transferring the data to Database sheet
 if (validateEntry()==true) {

    var blankRow=datasheet.getLastRow()+1; //identify the next blank row

    datasheet.getRange(blankRow, 1).setValue(shUserForm.getRange("C7").getValue()); //Nama Jabatan
    //datasheet.getRange(blankRow, 1).setValue(shUserForm.getRange("H7").getValue()); //Tipe Jabatan
    datasheet.getRange(blankRow, 2).setValue(shUserForm.getRange("C9").getValue()); //Kode Jabatan
    
    //entry kategori unit kerja
    datasheet.getRange(blankRow, 3).setValue(shUserForm.getRange("C13").getValue()); //unit kerjo-jpt madya
    datasheet.getRange(blankRow, 4).setValue(shUserForm.getRange("C15").getValue()); // unit kerjo-jpt pratama
    datasheet.getRange(blankRow, 5).setValue(shUserForm.getRange("C17").getValue()); //unit kerjo-administrator
    datasheet.getRange(blankRow, 6).setValue(shUserForm.getRange("C19").getValue());// unit kerjo-pengawas
    datasheet.getRange(blankRow, 7).setValue(shUserForm.getRange("C21").getValue());// Unit kerjo-pelaksana
    datasheet.getRange(blankRow, 8).setValue(shUserForm.getRange("C23").getValue());// unit kerjo-jbt fungsional
    
    //entry kategori ikhtisar jabatan
    datasheet.getRange(blankRow, 8).setValue(shUserForm.getRange("C25").getValue());// ikhtisar jabatan
    
    //entry kategori kualifikasi jabatan
    datasheet.getRange(blankRow, 9).setValue(shUserForm.getRange("C29").getValue());// pendidikan formaal
    datasheet.getRange(blankRow, 10).setValue(shUserForm.getRange("C31").getValue());// pendidikan dan pelatihan
    datasheet.getRange(blankRow, 11).setValue(shUserForm.getRange("C33").getValue());// pengalaman kerja

    //////////BAGIAN URAIAN TUGAS

    //Uraian TUGAS 1
    datasheet.getRange(blankRow,12).setValue(shUserForm.getRange("B46").getValue()); // isian uraian tugas
    datasheet.getRange(blankRow,13).setValue(shUserForm.getRange("C46").getValue()); // isian hasil kerja
    datasheet.getRange(blankRow,14).setValue(shUserForm.getRange("D46").getValue()); // isian jumlah hasil
    datasheet.getRange(blankRow,15).setValue(shUserForm.getRange("E46").getValue()); // isian waktu penyelesaian
    datasheet.getRange(blankRow,16).setValue(shUserForm.getRange("F46").getValue()); // isian waktu efektif
    datasheet.getRange(blankRow,17).setValue(shUserForm.getRange("G46").getValue()); // isian kebutuhan pegawai
    datasheet.getRange(blankRow,18).setValue(shUserForm.getRange("C48").getValue()); // isian hasil kerja luar
    datasheet.getRange(blankRow,19).setValue(shUserForm.getRange("B51").getValue()); datasheet.getRange(blankRow,20).setValue(shUserForm.getRange("C51").getValue()); // isian bahan kerja
    datasheet.getRange(blankRow,21).setValue(shUserForm.getRange("B54").getValue()); datasheet.getRange(blankRow,22).setValue(shUserForm.getRange("C54").getValue()); // isian perangkat kerja
    datasheet.getRange(blankRow,23).setValue(shUserForm.getRange("C56").getValue()); // isian tanggung jawab
    datasheet.getRange(blankRow,24).setValue(shUserForm.getRange("C58").getValue()); // isian wewenang
    
    datasheet.getRange(blankRow,25).setValue(shUserForm.getRange("B61").getValue()); datasheet.getRange(blankRow,26).setValue(shUserForm.getRange("C61").getValue()); datasheet.getRange(blankRow,27).setValue(shUserForm.getRange("D61").getValue()); //isian korelasi jabatan

    //Uraian TUGAS 2
    datasheet.getRange(blankRow,26).setValue(shUserForm.getRange("B68").getValue()); // isian uraian tugas
    datasheet.getRange(blankRow,27).setValue(shUserForm.getRange("C68").getValue()); // isian hasil kerja
    datasheet.getRange(blankRow,28).setValue(shUserForm.getRange("D68").getValue()); // isian jumlah hasil
    datasheet.getRange(blankRow,29).setValue(shUserForm.getRange("E68").getValue()); // isian waktu penyelesaian
    datasheet.getRange(blankRow,30).setValue(shUserForm.getRange("F68").getValue()); // isian waktu efektif
    datasheet.getRange(blankRow,31).setValue(shUserForm.getRange("G68").getValue()); // isian kebutuhan pegawai
    datasheet.getRange(blankRow,32).setValue(shUserForm.getRange("C70").getValue()); // isian hasil kerja luar
    datasheet.getRange(blankRow,33).setValue(shUserForm.getRange("B73").getValue()); datasheet.getRange(blankRow,34).setValue(shUserForm.getRange("C73").getValue()); // isian bahan kerja
    datasheet.getRange(blankRow,35).setValue(shUserForm.getRange("B76").getValue()); datasheet.getRange(blankRow,36).setValue(shUserForm.getRange("C76").getValue()); // isian perangkat kerja
    datasheet.getRange(blankRow,37).setValue(shUserForm.getRange("C78").getValue()); // isian tanggung jawab
    datasheet.getRange(blankRow,38).setValue(shUserForm.getRange("C80").getValue()); // isian wewenang
    
    datasheet.getRange(blankRow,40).setValue(shUserForm.getRange("B83").getValue()); datasheet.getRange(blankRow,41).setValue(shUserForm.getRange("C83").getValue()); datasheet.getRange(blankRow,27).setValue(shUserForm.getRange("D83").getValue()); //isian korelasi jabatan

//Uraian TUGAS 3
    datasheet.getRange(blankRow,26).setValue(shUserForm.getRange("B68").getValue()); // isian uraian tugas
    datasheet.getRange(blankRow,27).setValue(shUserForm.getRange("C68").getValue()); // isian hasil kerja
    datasheet.getRange(blankRow,28).setValue(shUserForm.getRange("D68").getValue()); // isian jumlah hasil
    datasheet.getRange(blankRow,29).setValue(shUserForm.getRange("E68").getValue()); // isian waktu penyelesaian
    datasheet.getRange(blankRow,30).setValue(shUserForm.getRange("F68").getValue()); // isian waktu efektif
    datasheet.getRange(blankRow,31).setValue(shUserForm.getRange("G68").getValue()); // isian kebutuhan pegawai
    datasheet.getRange(blankRow,32).setValue(shUserForm.getRange("C70").getValue()); // isian hasil kerja luar
    datasheet.getRange(blankRow,33).setValue(shUserForm.getRange("B73").getValue()); datasheet.getRange(blankRow,34).setValue(shUserForm.getRange("C73").getValue()); // isian bahan kerja
    datasheet.getRange(blankRow,35).setValue(shUserForm.getRange("B76").getValue()); datasheet.getRange(blankRow,36).setValue(shUserForm.getRange("C76").getValue()); // isian perangkat kerja
    datasheet.getRange(blankRow,37).setValue(shUserForm.getRange("C78").getValue()); // isian tanggung jawab
    datasheet.getRange(blankRow,38).setValue(shUserForm.getRange("C80").getValue()); // isian wewenang
    
    datasheet.getRange(blankRow,40).setValue(shUserForm.getRange("B83").getValue()); datasheet.getRange(blankRow,41).setValue(shUserForm.getRange("C83").getValue()); datasheet.getRange(blankRow,27).setValue(shUserForm.getRange("D83").getValue()); //isian korelasi jabatan

    // date function to update the current date and time as submittted on
    //datasheet.getRange(blankRow, 25).setValue(new Date()).setNumberFormat('yyyy-mm-dd h:mm'); //Submitted On
    
    //get the email address of the person running the script and update as Submitted By
    //datasheet.getRange(blankRow, 26).setValue(Session.getActiveUser().getEmail()); //Submitted By
    
    ui.alert(' "Data Baru Tersimpan - Jabatan :' + shUserForm.getRange("C7").getValue() +' "');
  
  //Clearnign the data from the Data Entry Form

    //shUserForm.getRange("C7").clear();
    //shUserForm.getRange("C9").clear();
    //shUserForm.getRange("C11").clear();
    //shUserForm.getRange("C13").clear();
    //shUserForm.getRange("C15").clear();
    //shUserForm.getRange("C17").clear();
    //shUserForm.getRange("C18").clear();

  //clear data rombongan ABK
  //shUserForm.getRange("C24").clear();
  //shUserForm.getRange("C25").clear();
      
 }
}
New contributor
rizanoar is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
0

While the size of the spreadsheet might have a performance effect on your script it is minimal compared to the execution time required by all the calls to the Google Apps Script Spreadsheet service (getRange, getValue, setValue), actually the best practices suggest use batch operations. More specifically, instead of reading/writing one value at a time, read/write several of them at once. One way to reduce the read operations is by using getDataRange and getValues to read all the sheet values at once, and in this case as your script is writing the values on a new row at the end of the data range, you might use appendRow.


A not so "complicate" way to improve the performance of your script is to add

var values = shUserForm.getDataRange().getValues();

after

var shUserForm= myGooglSheet.getSheetByName("AUser Form"); //delcare a variable and set with the User Form worksheet

then replace shUserForm.getRange(a1Notation).getValue() by values[row][column]

were row is the number of your the corresponding reference and column is the column number, i.e. the column number for A is 1, for B is 2 and so on.

Additionally to the above you could improve even further your script by replacing all code lines like datasheet.getRange(blankRow, 1).setValue(shUserForm.getRange("C7").getValue()); by

var rowContent = [values[][],  /* add here as many as needed values[][] separated by commas*/ ];
datasheet.appendRow(rowContent);

Don't forget to put the corresponding row and column values inside [][]

References

1
  • 1
    thank you so much, it worked
    – rizanoar
    yesterday

Your Answer

rizanoar is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.