Question 240797 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Speed up my google script function in google sheet

Good day!

I am a beginner in google script app, I just made a form in the google sheet but it always takes minutes to automate transfer data on the other sheet using app script. I search on how to use cache script or batch operation but got a hard time finding a website to learn. Can anyone help me? thank you very much.

here is my script:

// Function to submit the data to Database sheet
function submitData() {

  var myGoogleSheet= SpreadsheetApp.getActiveSpreadsheet(); //delcare a variable and set with active google sheet

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

  var datasheet = myGoogleSheet.getSheetByName("Database"); ////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", 'Do you want to submit the 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("B2").getValue());//Full Name
      datasheet.getRange(blankRow, 2).setValue(shUserForm.getRange("F2").getValue());// Nickname
      datasheet.getRange(blankRow, 3).setValue(shUserForm.getRange("B3").getValue());// Position
      datasheet.getRange(blankRow, 4).setValue(shUserForm.getRange("D3").getValue());// Age
      datasheet.getRange(blankRow, 5).setValue(shUserForm.getRange("F3").getValue());// Sex
      datasheet.getRange(blankRow, 6).setValue(shUserForm.getRange("B4").getValue());//Residence Adrress
      datasheet.getRange(blankRow, 7).setValue(shUserForm.getRange("B5").getValue());//Office
      datasheet.getRange(blankRow, 8).setValue(shUserForm.getRange("B6").getValue());//Office Address
      datasheet.getRange(blankRow, 9).setValue(shUserForm.getRange("B7").getValue());//Office Tel. No.
      datasheet.getRange(blankRow, 10).setValue(shUserForm.getRange("D7").getValue());// Mobile No
      datasheet.getRange(blankRow, 11).setValue(shUserForm.getRange("F7").getValue());// Email
      datasheet.getRange(blankRow, 12).setValue(shUserForm.getRange("B8").getValue());// Language/Dialect spoken:
      datasheet.getRange(blankRow, 13).setValue(shUserForm.getRange("B11").getValue());// Undergraduate 1
      datasheet.getRange(blankRow, 14).setValue(shUserForm.getRange("D11").getValue());// U-Year Graduated 1
      datasheet.getRange(blankRow, 15).setValue(shUserForm.getRange("F11").getValue());//U-School 1
      datasheet.getRange(blankRow, 16).setValue(shUserForm.getRange("B12").getValue());// C-Degree 1
      datasheet.getRange(blankRow, 17).setValue(shUserForm.getRange("D12").getValue());// C-Year Graduated 1
      datasheet.getRange(blankRow, 18).setValue(shUserForm.getRange("F12").getValue());//C-School 1
      datasheet.getRange(blankRow, 19).setValue(shUserForm.getRange("B13").getValue());// C-Degree 2
      datasheet.getRange(blankRow, 20).setValue(shUserForm.getRange("D13").getValue());// C-Year Graduated 2
      datasheet.getRange(blankRow, 21).setValue(shUserForm.getRange("F13").getValue());//C-School 2
      datasheet.getRange(blankRow, 22).setValue(shUserForm.getRange("B14").getValue());// C-Degree 3
      datasheet.getRange(blankRow, 23).setValue(shUserForm.getRange("D14").getValue());// C-Year Graduated 3
      datasheet.getRange(blankRow, 24).setValue(shUserForm.getRange("F14").getValue());//C-School 3
      datasheet.getRange(blankRow, 25).setValue(shUserForm.getRange("B15").getValue());// MS-Degree 1
      datasheet.getRange(blankRow, 26).setValue(shUserForm.getRange("D15").getValue());// MS-Year Graduated 1
      datasheet.getRange(blankRow, 27).setValue(shUserForm.getRange("F15").getValue());// MS-School 1
      datasheet.getRange(blankRow, 28).setValue(shUserForm.getRange("B16").getValue());// MS-Degree 2
      datasheet.getRange(blankRow, 29).setValue(shUserForm.getRange("D16").getValue());// MS-Year Graduated 2
      datasheet.getRange(blankRow, 30).setValue(shUserForm.getRange("F16").getValue());// MS-School 2
      datasheet.getRange(blankRow, 31).setValue(shUserForm.getRange("B17").getValue());// MS-Degree 3
      datasheet.getRange(blankRow, 32).setValue(shUserForm.getRange("D17").getValue());// MS-Year Graduated 3
      datasheet.getRange(blankRow, 33).setValue(shUserForm.getRange("F17").getValue());// MS-School 3
      datasheet.getRange(blankRow, 34).setValue(shUserForm.getRange("B18").getValue());// Dip-Degree 1
      datasheet.getRange(blankRow, 35).setValue(shUserForm.getRange("D18").getValue());// Dip-Year Graduated 1
      datasheet.getRange(blankRow, 36).setValue(shUserForm.getRange("F18").getValue());// Dip-School 1
      datasheet.getRange(blankRow, 37).setValue(shUserForm.getRange("B19").getValue());// Dip-Degree 2
      datasheet.getRange(blankRow, 38).setValue(shUserForm.getRange("D19").getValue());// Dip-Year Graduated 2
      datasheet.getRange(blankRow, 39).setValue(shUserForm.getRange("F19").getValue());// Dip-School 2
      datasheet.getRange(blankRow, 40).setValue(shUserForm.getRange("B20").getValue());// Dip-Degree 3
      datasheet.getRange(blankRow, 41).setValue(shUserForm.getRange("D20").getValue());// Dip-Year Graduated 3
      datasheet.getRange(blankRow, 42).setValue(shUserForm.getRange("F20").getValue());// Dip-School 3
      datasheet.getRange(blankRow, 43).setValue(shUserForm.getRange("B21").getValue());// Ph.D-Degree 1
      datasheet.getRange(blankRow, 44).setValue(shUserForm.getRange("D21").getValue());// Ph.D-Year Graduated 1
      datasheet.getRange(blankRow, 45).setValue(shUserForm.getRange("F21").getValue());// Ph.D-School 1
      datasheet.getRange(blankRow, 46).setValue(shUserForm.getRange("B22").getValue());// Ph.D-Degree 2
      datasheet.getRange(blankRow, 47).setValue(shUserForm.getRange("D22").getValue());// Ph.D-Year Graduated 2
      datasheet.getRange(blankRow, 48).setValue(shUserForm.getRange("F22").getValue());// Ph.D-School 2
      datasheet.getRange(blankRow, 49).setValue(shUserForm.getRange("B23").getValue());// Ph.D-Degree 3
      datasheet.getRange(blankRow, 50).setValue(shUserForm.getRange("D23").getValue());// Ph.D-Year Graduated 3
      datasheet.getRange(blankRow, 51).setValue(shUserForm.getRange("F23").getValue());// Ph.D-School 3
      datasheet.getRange(blankRow, 52).setValue(shUserForm.getRange("B25").getValue());// Position 1
      datasheet.getRange(blankRow, 53).setValue(shUserForm.getRange("D25").getValue());// No. of Years 1
      datasheet.getRange(blankRow, 54).setValue(shUserForm.getRange("F25").getValue());// Agency/Organization 1
      datasheet.getRange(blankRow, 55).setValue(shUserForm.getRange("B26").getValue());// Position 2
      datasheet.getRange(blankRow, 56).setValue(shUserForm.getRange("D26").getValue());// No. of Years 2
      datasheet.getRange(blankRow, 57).setValue(shUserForm.getRange("F26").getValue());// Agency/Organization 2
      datasheet.getRange(blankRow, 58).setValue(shUserForm.getRange("B27").getValue());// Position 3
      datasheet.getRange(blankRow, 59).setValue(shUserForm.getRange("D27").getValue());// No. of Years 3
      datasheet.getRange(blankRow, 60).setValue(shUserForm.getRange("F27").getValue());// Agency/Organization 3
      datasheet.getRange(blankRow, 61).setValue(shUserForm.getRange("B28").getValue());// Position 4
      datasheet.getRange(blankRow, 62).setValue(shUserForm.getRange("D28").getValue());// No. of Years 4
      datasheet.getRange(blankRow, 63).setValue(shUserForm.getRange("F28").getValue());// Agency/Organization 4
      datasheet.getRange(blankRow, 64).setValue(shUserForm.getRange("B29").getValue());// Position 5
      datasheet.getRange(blankRow, 65).setValue(shUserForm.getRange("D29").getValue());// No. of Years 5
      datasheet.getRange(blankRow, 66).setValue(shUserForm.getRange("F29").getValue());// Agency/Organization 5
      datasheet.getRange(blankRow, 67).setValue(shUserForm.getRange("B32").getValue());// Specialization 1
      datasheet.getRange(blankRow, 68).setValue(shUserForm.getRange("D32").getValue());// No. of Years of Experience 1
      datasheet.getRange(blankRow, 69).setValue(shUserForm.getRange("F32").getValue());// No. of Years Lecturing the Topic 1
      datasheet.getRange(blankRow, 70).setValue(shUserForm.getRange("B33").getValue());// Specialization 2
      datasheet.getRange(blankRow, 71).setValue(shUserForm.getRange("D33").getValue());// No. of Years of Experience 2
      datasheet.getRange(blankRow, 72).setValue(shUserForm.getRange("F33").getValue());// No. of Years Lecturing the Topic 2
      datasheet.getRange(blankRow, 73).setValue(shUserForm.getRange("B34").getValue());// Specialization 3
      datasheet.getRange(blankRow, 74).setValue(shUserForm.getRange("D34").getValue());// No. of Years of Experience 3
      datasheet.getRange(blankRow, 75).setValue(shUserForm.getRange("F34").getValue());// No. of Years Lecturing the Topic 3
      datasheet.getRange(blankRow, 76).setValue(shUserForm.getRange("B35").getValue());// Sub-Specialization 1
      datasheet.getRange(blankRow, 77).setValue(shUserForm.getRange("D35").getValue());// SS No. of Years of Experience 1
      datasheet.getRange(blankRow, 78).setValue(shUserForm.getRange("F35").getValue());// SS No. of Years Lecturing the Topic 1
      datasheet.getRange(blankRow, 79).setValue(shUserForm.getRange("B36").getValue());// Sub-Specialization 2
      datasheet.getRange(blankRow, 80).setValue(shUserForm.getRange("D36").getValue());// SS No. of Years of Experience 2
      datasheet.getRange(blankRow, 81).setValue(shUserForm.getRange("F36").getValue());// SS No. of Years Lecturing the Topic 2
      datasheet.getRange(blankRow, 82).setValue(shUserForm.getRange("B37").getValue());// Sub-Specialization 3
      datasheet.getRange(blankRow, 83).setValue(shUserForm.getRange("D37").getValue());// SS No. of Years of Experience 3
      datasheet.getRange(blankRow, 84).setValue(shUserForm.getRange("F37").getValue());// SS No. of Years Lecturing the Topic 3
      datasheet.getRange(blankRow, 85).setValue(shUserForm.getRange("B41").getValue());// Title 1
      datasheet.getRange(blankRow, 86).setValue(shUserForm.getRange("D41").getValue());// No. of Days 1
      datasheet.getRange(blankRow, 87).setValue(shUserForm.getRange("F41").getValue());//Conducted by 1
      datasheet.getRange(blankRow, 88).setValue(shUserForm.getRange("B42").getValue());// Title 2
      datasheet.getRange(blankRow, 89).setValue(shUserForm.getRange("D42").getValue());// No. of Days 2
      datasheet.getRange(blankRow, 90).setValue(shUserForm.getRange("F42").getValue());//Conducted by 2
      datasheet.getRange(blankRow, 91).setValue(shUserForm.getRange("B43").getValue());// Title 3
      datasheet.getRange(blankRow, 92).setValue(shUserForm.getRange("D43").getValue());// No. of Days 3
      datasheet.getRange(blankRow, 93).setValue(shUserForm.getRange("F43").getValue());//Conducted by 3
      datasheet.getRange(blankRow, 94).setValue(shUserForm.getRange("B44").getValue());// Title 4
      datasheet.getRange(blankRow, 95).setValue(shUserForm.getRange("D44").getValue());// No. of Days 4
      datasheet.getRange(blankRow, 96).setValue(shUserForm.getRange("F44").getValue());//Conducted by 4
      datasheet.getRange(blankRow, 97).setValue(shUserForm.getRange("B45").getValue());// Title 5
      datasheet.getRange(blankRow, 98).setValue(shUserForm.getRange("D45").getValue());// No. of Days 5
      datasheet.getRange(blankRow, 99).setValue(shUserForm.getRange("F45").getValue());//Conducted by 5
      datasheet.getRange(blankRow, 100).setValue(shUserForm.getRange("B48").getValue());// Title 6
      datasheet.getRange(blankRow, 101).setValue(shUserForm.getRange("D48").getValue());// No. of Days 6
      datasheet.getRange(blankRow, 102).setValue(shUserForm.getRange("F48").getValue());//Conducted by 6
      datasheet.getRange(blankRow, 103).setValue(shUserForm.getRange("B49").getValue());// Title 7
      datasheet.getRange(blankRow, 104).setValue(shUserForm.getRange("D49").getValue());// No. of Days 7
      datasheet.getRange(blankRow, 105).setValue(shUserForm.getRange("F49").getValue());//Conducted by 7
      datasheet.getRange(blankRow, 106).setValue(shUserForm.getRange("B50").getValue());// Title 8
      datasheet.getRange(blankRow, 107).setValue(shUserForm.getRange("D50").getValue());// No. of Days 8
      datasheet.getRange(blankRow, 108).setValue(shUserForm.getRange("F50").getValue());//Conducted by 8
      datasheet.getRange(blankRow, 109).setValue(shUserForm.getRange("B51").getValue());// Title 9
      datasheet.getRange(blankRow, 110).setValue(shUserForm.getRange("D51").getValue());// No. of Days 9
      datasheet.getRange(blankRow, 111).setValue(shUserForm.getRange("F51").getValue());//Conducted by 9
      datasheet.getRange(blankRow, 112).setValue(shUserForm.getRange("B52").getValue());// Title 10
      datasheet.getRange(blankRow, 113).setValue(shUserForm.getRange("D52").getValue());// No. of Days 10
      datasheet.getRange(blankRow, 114).setValue(shUserForm.getRange("F52").getValue());//Conducted by 10
      datasheet.getRange(blankRow, 115).setValue(shUserForm.getRange("B56").getValue());// Licensed Profession/s 1
      datasheet.getRange(blankRow, 116).setValue(shUserForm.getRange("D56").getValue());// License No. 1
      datasheet.getRange(blankRow, 117).setValue(shUserForm.getRange("F56").getValue());// Issued on: 1
      datasheet.getRange(blankRow, 118).setValue(shUserForm.getRange("B57").getValue());// Licensed Profession/s 2
      datasheet.getRange(blankRow, 119).setValue(shUserForm.getRange("D57").getValue());// License No. 2
      datasheet.getRange(blankRow, 120).setValue(shUserForm.getRange("F57").getValue());// Issued on: 2
      datasheet.getRange(blankRow, 121).setValue(shUserForm.getRange("B58").getValue());// Licensed Profession/s 3
      datasheet.getRange(blankRow, 122).setValue(shUserForm.getRange("D58").getValue());// License No. 3
      datasheet.getRange(blankRow, 123).setValue(shUserForm.getRange("F58").getValue());// Issued on: 3
      datasheet.getRange(blankRow, 124).setValue(shUserForm.getRange("B59").getValue());// Licensed Profession/s 4
      datasheet.getRange(blankRow, 125).setValue(shUserForm.getRange("D59").getValue());// License No. 4
      datasheet.getRange(blankRow, 126).setValue(shUserForm.getRange("F59").getValue());// Issued on: 4
      datasheet.getRange(blankRow, 127).setValue(shUserForm.getRange("B60").getValue());// Licensed Profession/s 5
      datasheet.getRange(blankRow, 128).setValue(shUserForm.getRange("D60").getValue());// License No. 5
      datasheet.getRange(blankRow, 129).setValue(shUserForm.getRange("F60").getValue());// Issued on: 5
      datasheet.getRange(blankRow, 130).setValue(shUserForm.getRange("B63").getValue());// AIPO Membership 1
      datasheet.getRange(blankRow, 131).setValue(shUserForm.getRange("D63").getValue());// National/Chapter 1
      datasheet.getRange(blankRow, 132).setValue(shUserForm.getRange("F63").getValue());// Position 1
      datasheet.getRange(blankRow, 133).setValue(shUserForm.getRange("B64").getValue());// AIPO Membership 2
      datasheet.getRange(blankRow, 134).setValue(shUserForm.getRange("D64").getValue());// National/Chapter 2
      datasheet.getRange(blankRow, 135).setValue(shUserForm.getRange("F64").getValue());// Position 2
      datasheet.getRange(blankRow, 136).setValue(shUserForm.getRange("B65").getValue());// AIPO Membership 3
      datasheet.getRange(blankRow, 137).setValue(shUserForm.getRange("D65").getValue());// National/Chapter 3
      datasheet.getRange(blankRow, 138).setValue(shUserForm.getRange("F65").getValue());// Position 3
      datasheet.getRange(blankRow, 139).setValue(shUserForm.getRange("B66").getValue());// AIPO Membership 4
      datasheet.getRange(blankRow, 140).setValue(shUserForm.getRange("D66").getValue());// National/Chapter 4
      datasheet.getRange(blankRow, 141).setValue(shUserForm.getRange("F66").getValue());// Position 4
      datasheet.getRange(blankRow, 142).setValue(shUserForm.getRange("B67").getValue());// AIPO Membership 5
      datasheet.getRange(blankRow, 143).setValue(shUserForm.getRange("D67").getValue());// National/Chapter 5
      datasheet.getRange(blankRow, 144).setValue(shUserForm.getRange("F67").getValue());// Position 5
      datasheet.getRange(blankRow, 145).setValue(shUserForm.getRange("B70").getValue());// Other Major Affiliations 1
      datasheet.getRange(blankRow, 146).setValue(shUserForm.getRange("D70").getValue());// OM National/Chapter 1
      datasheet.getRange(blankRow, 147).setValue(shUserForm.getRange("F70").getValue());// OM Position 1
      datasheet.getRange(blankRow, 148).setValue(shUserForm.getRange("B71").getValue());// Other Major Affiliations 2
      datasheet.getRange(blankRow, 149).setValue(shUserForm.getRange("D71").getValue());// OM National/Chapter 2
      datasheet.getRange(blankRow, 150).setValue(shUserForm.getRange("F71").getValue());// OM Position 2
      datasheet.getRange(blankRow, 151).setValue(shUserForm.getRange("B72").getValue());// Other Major Affiliations 3
      datasheet.getRange(blankRow, 152).setValue(shUserForm.getRange("D72").getValue());// OM National/Chapter 3
      datasheet.getRange(blankRow, 153).setValue(shUserForm.getRange("F72").getValue());// OM Position 3
      datasheet.getRange(blankRow, 154).setValue(shUserForm.getRange("B73").getValue());// Other Major Affiliations 4
      datasheet.getRange(blankRow, 155).setValue(shUserForm.getRange("D73").getValue());// OM National/Chapter 4
      datasheet.getRange(blankRow, 156).setValue(shUserForm.getRange("F73").getValue());// OM Position 4
      datasheet.getRange(blankRow, 157).setValue(shUserForm.getRange("B74").getValue());// Other Major Affiliations 5
      datasheet.getRange(blankRow, 158).setValue(shUserForm.getRange("D74").getValue());// OM National/Chapter 5
      datasheet.getRange(blankRow, 159).setValue(shUserForm.getRange("F74").getValue());// OM Position 5
      datasheet.getRange(blankRow, 160).setValue(shUserForm.getRange("B78").getValue()); // Title of Award 1
      datasheet.getRange(blankRow, 161).setValue(shUserForm.getRange("D78").getValue());// Year Received 1
      datasheet.getRange(blankRow, 162).setValue(shUserForm.getRange("F78").getValue());// Given by 1
      datasheet.getRange(blankRow, 163).setValue(shUserForm.getRange("B79").getValue()); // Title of Award 2
      datasheet.getRange(blankRow, 164).setValue(shUserForm.getRange("D79").getValue());// Year Received 2
      datasheet.getRange(blankRow, 165).setValue(shUserForm.getRange("F79").getValue());// Given by 2
      datasheet.getRange(blankRow, 166).setValue(shUserForm.getRange("B80").getValue()); // Title of Award 3
      datasheet.getRange(blankRow, 167).setValue(shUserForm.getRange("D80").getValue());// Year Received 3
      datasheet.getRange(blankRow, 168).setValue(shUserForm.getRange("F80").getValue());// Given by 3
      datasheet.getRange(blankRow, 169).setValue(shUserForm.getRange("B81").getValue()); // Title of Award 4
      datasheet.getRange(blankRow, 170).setValue(shUserForm.getRange("D81").getValue());// Year Received 4
      datasheet.getRange(blankRow, 171).setValue(shUserForm.getRange("F81").getValue());// Given by 4
      datasheet.getRange(blankRow, 172).setValue(shUserForm.getRange("B83").getValue());// Date Accomplished: 
      datasheet.getRange(blankRow, 173).setValue(shUserForm.getRange("B84").getValue());// Submitted by:

  // date function to update the current date and time as submittted on
    datasheet.getRange(blankRow, 174).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 with

    datasheet.getRange(blankRow, 175).setValue(Session.getActiveUser().getEmail()); 

  //Clearning the data from the Data Entry Form

    shUserForm.getRange("B2").clear();
    shUserForm.getRange("F2").clear();
    shUserForm.getRange("B3").clear();
    shUserForm.getRange("D3").clear();
    shUserForm.getRange("F3").clear();
    shUserForm.getRange("B4").clear();
    shUserForm.getRange("B5").clear();
    shUserForm.getRange("B6").clear();
    shUserForm.getRange("B7").clear();
    shUserForm.getRange("D7").clear();
    shUserForm.getRange("F7").clear();
    shUserForm.getRange("B8").clear();
    shUserForm.getRange("B11").clear();
    shUserForm.getRange("D11").clear();
    shUserForm.getRange("F11").clear();
    shUserForm.getRange("B12").clear();
    shUserForm.getRange("D12").clear();
    shUserForm.getRange("F12").clear();
    shUserForm.getRange("B13").clear();
    shUserForm.getRange("D13").clear();
    shUserForm.getRange("F13").clear();
    shUserForm.getRange("B14").clear();
    shUserForm.getRange("D14").clear();
    shUserForm.getRange("F14").clear();
    shUserForm.getRange("B15").clear();
    shUserForm.getRange("D15").clear();
    shUserForm.getRange("F15").clear();
    shUserForm.getRange("B16").clear();
    shUserForm.getRange("D16").clear();
    shUserForm.getRange("F16").clear();
    shUserForm.getRange("B17").clear();
    shUserForm.getRange("D17").clear();
    shUserForm.getRange("F17").clear();
    shUserForm.getRange("B18").clear();
    shUserForm.getRange("D18").clear();
    shUserForm.getRange("F18").clear();
    shUserForm.getRange("B19").clear();
    shUserForm.getRange("D19").clear();
    shUserForm.getRange("F19").clear();
    shUserForm.getRange("B20").clear();
    shUserForm.getRange("D20").clear();
    shUserForm.getRange("F20").clear();
    shUserForm.getRange("B21").clear();
    shUserForm.getRange("D21").clear();
    shUserForm.getRange("F21").clear();
    shUserForm.getRange("B22").clear();
    shUserForm.getRange("D22").clear();
    shUserForm.getRange("F22").clear();
    shUserForm.getRange("B23").clear();
    shUserForm.getRange("D23").clear();
    shUserForm.getRange("F23").clear();
    shUserForm.getRange("B25").clear();
    shUserForm.getRange("D25").clear();
    shUserForm.getRange("F25").clear();
    shUserForm.getRange("B26").clear();
    shUserForm.getRange("D26").clear();
    shUserForm.getRange("F26").clear();
    shUserForm.getRange("B27").clear();
    shUserForm.getRange("D27").clear();
    shUserForm.getRange("F27").clear();
    shUserForm.getRange("B28").clear();
    shUserForm.getRange("D28").clear();
    shUserForm.getRange("F28").clear();
    shUserForm.getRange("B29").clear();
    shUserForm.getRange("D29").clear();
    shUserForm.getRange("F29").clear();
    shUserForm.getRange("B32").clear();
    shUserForm.getRange("D32").clear();
    shUserForm.getRange("F32").clear();
    shUserForm.getRange("B33").clear();
    shUserForm.getRange("D33").clear();
    shUserForm.getRange("F33").clear();
    shUserForm.getRange("B34").clear();
    shUserForm.getRange("D34").clear();
    shUserForm.getRange("F34").clear();
    shUserForm.getRange("B35").clear();
    shUserForm.getRange("D35").clear();
    shUserForm.getRange("F35").clear();
    shUserForm.getRange("B36").clear();
    shUserForm.getRange("D36").clear();
    shUserForm.getRange("F36").clear();
    shUserForm.getRange("B37").clear();
    shUserForm.getRange("D37").clear();
    shUserForm.getRange("F37").clear();
    shUserForm.getRange("B41").clear();
    shUserForm.getRange("D41").clear();
    shUserForm.getRange("F41").clear();
    shUserForm.getRange("B42").clear();
    shUserForm.getRange("D42").clear();
    shUserForm.getRange("F42").clear();
    shUserForm.getRange("B43").clear();
    shUserForm.getRange("D43").clear();
    shUserForm.getRange("F43").clear();
    shUserForm.getRange("B44").clear();
    shUserForm.getRange("D44").clear();
    shUserForm.getRange("F44").clear();
    shUserForm.getRange("B45").clear();
    shUserForm.getRange("D45").clear();
    shUserForm.getRange("F45").clear();
    shUserForm.getRange("B48").clear();
    shUserForm.getRange("D48").clear();
    shUserForm.getRange("F48").clear();
    shUserForm.getRange("B49").clear();
    shUserForm.getRange("D49").clear();
    shUserForm.getRange("F49").clear();
    shUserForm.getRange("B50").clear();
    shUserForm.getRange("D50").clear();
    shUserForm.getRange("F50").clear();
    shUserForm.getRange("B51").clear();
    shUserForm.getRange("D51").clear();
    shUserForm.getRange("F51").clear();
    shUserForm.getRange("B52").clear();
    shUserForm.getRange("D52").clear();
    shUserForm.getRange("F52").clear();
    shUserForm.getRange("B56").clear();
    shUserForm.getRange("D56").clear();
    shUserForm.getRange("F56").clear();
    shUserForm.getRange("B57").clear();
    shUserForm.getRange("D57").clear();
    shUserForm.getRange("F57").clear();
    shUserForm.getRange("B58").clear();
    shUserForm.getRange("D58").clear();
    shUserForm.getRange("F58").clear();
    shUserForm.getRange("B59").clear();
    shUserForm.getRange("D59").clear();
    shUserForm.getRange("F59").clear();
    shUserForm.getRange("B60").clear();
    shUserForm.getRange("D60").clear();
    shUserForm.getRange("F60").clear();
    shUserForm.getRange("B63").clear();
    shUserForm.getRange("D63").clear();
    shUserForm.getRange("F63").clear();
    shUserForm.getRange("B64").clear();
    shUserForm.getRange("D64").clear();
    shUserForm.getRange("F64").clear();
    shUserForm.getRange("B65").clear();
    shUserForm.getRange("D65").clear();
    shUserForm.getRange("F65").clear();
    shUserForm.getRange("B66").clear();
    shUserForm.getRange("D66").clear();
    shUserForm.getRange("F66").clear();
    shUserForm.getRange("B67").clear();
    shUserForm.getRange("D67").clear();
    shUserForm.getRange("F67").clear();
    shUserForm.getRange("B70").clear();
    shUserForm.getRange("D70").clear();
    shUserForm.getRange("F70").clear();
    shUserForm.getRange("B71").clear();
    shUserForm.getRange("D71").clear();
    shUserForm.getRange("F71").clear();
    shUserForm.getRange("B72").clear();
    shUserForm.getRange("D72").clear();
    shUserForm.getRange("F72").clear();
    shUserForm.getRange("B73").clear();
    shUserForm.getRange("D73").clear();
    shUserForm.getRange("F73").clear();
    shUserForm.getRange("B74").clear();
    shUserForm.getRange("D74").clear();
    shUserForm.getRange("F74").clear();
    shUserForm.getRange("B78").clear();
    shUserForm.getRange("D78").clear();
    shUserForm.getRange("F78").clear();
    shUserForm.getRange("B79").clear();
    shUserForm.getRange("D79").clear();
    shUserForm.getRange("F79").clear();
    shUserForm.getRange("B80").clear();
    shUserForm.getRange("D80").clear();
    shUserForm.getRange("F80").clear();
    shUserForm.getRange("B81").clear();
    shUserForm.getRange("D81").clear();
    shUserForm.getRange("F81").clear();
    shUserForm.getRange("B83").clear();
    shUserForm.getRange("B84").clear();

  }
}

I tried using cache but It did’nt work, I think I wrote it wrong. thanks

Tags:

2

Answers


  1. 0

    RECOMMENDATION

    Based on your provided code, I’ve applied some changes to simplify the process being executed by the setValue() and clear() functions:

    1. First, regarding the multiple instances of datasheet.getRange().setValue() I’ve split up this process into two lines of code. The first line would be as follows:
    var data = shUserForm.getDataRange().getValues().flat(Infinity).filter(x => x != "");

    To explain the line of code, I’ve used the getDataRange() function instead of getRange() as it automatically gets the range wherein data is bound or present. Next, we use the flat() function after getting the values to ensure that the resulting array is converted to a 1D array, and then finally, the filter() function ensures that all blank or whitespace elements are omitted or removed.

    1. After getting the data from the "User Form" sheet, the second line would be as follows:
    datasheet.getRange(blankRow, 1, 1, data.length).setValues([data]);

    This line of code then gets the number of columns depending on the number of elements contained on the data array, which will then be set onto the "Database" sheet (as a 2D array, as a requirement in using the setValues() function).

    1. Finally, to simplify the clearing of data values from the "User Form" spreadsheet, we will use this code:
    shUserForm.getDataRange().clear();

    You can check this simplified script for your reference:

    function submitData() {
  var myGoogleSheet = SpreadsheetApp.getActiveSpreadsheet(); //delcare a variable and set with active google sheet
  var shUserForm = myGoogleSheet.getSheetByName("User Form"); //delcare a variable and set with the User Form worksheet
  var datasheet = myGoogleSheet.getSheetByName("Database"); ////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", 'Do you want to submit the 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
  }
  // gets all the values from the User Form sheet, then stores it on a 1D array
  var data = shUserForm.getDataRange().getValues().flat(Infinity).filter(x => x != "");
  //Validating the entry. If validation is true then proceed with transferring the data to Database sheet
  if (validateEntry() == true) {
    var blankRow = datasheet.getLastRow() + 1;
    // gets the length of the data array to set it as the number of columns needed to set the data values
    datasheet.getRange(blankRow, 1, 1, data.length).setValues([data]);
  }
  // clearing all the data values from the User Form sheet
  shUserForm.getDataRange().clear();
}

    OUTPUT

    This is how it would look like on the "Database" sheet:
    enter image description here

    REFERENCES

    Login or Signup to reply.
  2. 0

    I believe your goal is as follows.

    • You want to reduce the process cost of your script.

    When I saw your script, it seemed that you wanted to retrieve the values from the scattered cells. In this case, how about using Sheets API? When Sheets API is used in your script, it becomes as follows.

    Sample script:

    Please copy and paste the following script to the script editor of Google Spreadsheet. And, please enable Sheets API at Advanced Google services.

    function myFunction() {
  const srcSheetName = "User Form"; // This is from your script.
  const dstSheetName = "Database"; // This is from your script.

  // These ranges are from your script.
  const srcRange = ["B2", "F2", "B3", "D3", "F3", "B4", "B5", "B6", "B7", "D7", "F7", "B8", "B11", "D11", "F11", "B12", "D12", "F12", "B13", "D13", "F13", "B14", "D14", "F14", "B15", "D15", "F15", "B16", "D16", "F16", "B17", "D17", "F17", "B18", "D18", "F18", "B19", "D19", "F19", "B20", "D20", "F20", "B21", "D21", "F21", "B22", "D22", "F22", "B23", "D23", "F23", "B25", "D25", "F25", "B26", "D26", "F26", "B27", "D27", "F27", "B28", "D28", "F28", "B29", "D29", "F29", "B32", "D32", "F32", "B33", "D33", "F33", "B34", "D34", "F34", "B35", "D35", "F35", "B36", "D36", "F36", "B37", "D37", "F37", "B41", "D41", "F41", "B42", "D42", "F42", "B43", "D43", "F43", "B44", "D44", "F44", "B45", "D45", "F45", "B48", "D48", "F48", "B49", "D49", "F49", "B50", "D50", "F50", "B51", "D51", "F51", "B52", "D52", "F52", "B56", "D56", "F56", "B57", "D57", "F57", "B58", "D58", "F58", "B59", "D59", "F59", "B60", "D60", "F60", "B63", "D63", "F63", "B64", "D64", "F64", "B65", "D65", "F65", "B66", "D66", "F66", "B67", "D67", "F67", "B70", "D70", "F70", "B71", "D71", "F71", "B72", "D72", "F72", "B73", "D73", "F73", "B74", "D74", "F74", "B78", "D78", "F78", "B79", "D79", "F79", "B80", "D80", "F80", "B81", "D81", "F81", "B83", "B84"];

  var ui = SpreadsheetApp.getUi();
  var response = ui.alert("Submit", 'Do you want to submit the data?',ui.ButtonSet.YES_NO);
  if (response == ui.Button.NO || !validateEntry()) return;

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcValues = Sheets.Spreadsheets.Values.batchGet(ss.getId(), { ranges: srcRange.map(r => `'${srcSheetName}'!${r}`) }).valueRanges.map(({ values }) => values[0][0]);
  const values = [...srcValues, new Date(), Session.getActiveUser().getEmail()];
  const dstSheet = ss.getSheetByName(dstSheetName);
  dstSheet.getRange(dstSheet.getLastRow() + 1, 1, 1, values.length).setValues([values]).offset(0, 173, 1, 1).setNumberFormat('yyyy-mm-dd h:mm');
  ss.getSheetByName(srcSheetName).getRangeList(srcRange).clear();
}

    • When this script is run, I think that the same result is obtained with your showing script by reducing the process cost.

    • In my test, this script is finished in about 3 seconds.

    Note:

    • In this sample script, it supposes that your showing script works fine while the process cost is high. Please be careful about this.

    References:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search