javascriptgoogle-sheetsuser-input

How to ignore empty rows when submitting to database


I have a problem when I submit the form which had empty rows so that in the database the empty rows are still inputted even though they are empty which causes the next input to skip the empty row.


Solution

  • You mean something like this?

    function submitFR() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var formSS = SpreadsheetApp.openById("1OWqOlqy_pfZgmtgOx8i-b-V_u3IBZnsOklahh4pXGSM").getSheetByName("Form");
      var datasheet = SpreadsheetApp.openById("1OonemP29xCjyChjYUYJWWGKyjo-BFFUVSJhoyx6MQEg").getSheetByName("Database Jurnal");
    
      var rowsToSubmit = [];
    
      // Loop through rows 22 to 25
      for (var row = 22; row <= 25; row++) {
        var values = [
          formSS.getRange("B" + row).getValue(),
          formSS.getRange("D" + row).getValue(),
          formSS.getRange("E" + row).getValue(),
          formSS.getRange("L" + row).getValue(),
          formSS.getRange("M" + row).getValue(),
          formSS.getRange("N" + row).getValue(),
          formSS.getRange("O" + row).getValue(),
          formSS.getRange("P" + row).getValue(),
          formSS.getRange("R" + row).getValue(),
          formSS.getRange("S" + row).getValue(),
        ];
    
        // Check if any values are non-empty in the row before adding to the list
        if (values.some(value => value !== "")) {
          rowsToSubmit.push(values); // Add non-empty rows to the array
        }
      }
    
      // Write the non-empty rows to the datasheet
      if (rowsToSubmit.length > 0) {
        datasheet.getRange(datasheet.getLastRow() + 1, 1, rowsToSubmit.length, rowsToSubmit[0].length).setValues(rowsToSubmit);
      }
    }