How to put data from a custom dialog into a specific range in the sheet without using appendRow

user_17479547 0
Viewed 73

I'm having trouble putting data from a custom dialog into a specific range in the sheet, like this: enter image description here enter image description here

Everytime i enter new data in the dialog, it should append a new row in that particular range. So far i'm using appendRow for that matter but the problem with that is that appendRow counts in ALL columns in the sheet, so that i'm having blank cells in the particular range, like this: enter image description here

This is one of the codes i'm using :

//STUNDE EINTRAGEN VIA START
function showDialogKBSTART() {

  gotoSheetSchueler();

  var widget = HtmlService.createHtmlOutputFromFile("formKBSTART.html").setHeight(450).setWidth(450);
  
  SpreadsheetApp.getUi().showModalDialog(widget, "Stunde eintragen");
}

function appendRowFromFormSubmitKBSTART(formKBSTART) {
  
  SpreadsheetApp.getActiveSheet()
    .appendRow([formKBSTART.Datum, formKBSTART.Inhalt, formKBSTART.Sozialform, formKBSTART.Dauer, formKBSTART.Lehrkraft,, formKBSTART.Ausfall]);
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('KlassenbuchGesamt')
    .appendRow([SpreadsheetApp.getActiveSheet().getName(), formKBSTART.Datum, formKBSTART.Inhalt, formKBSTART.Sozialform, formKBSTART.Dauer, formKBSTART.Lehrkraft, formKBSTART.Ausfall]);

  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("A9:A").setNumberFormat("dd.MM.yyyy");
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("A9:F").sort({column: 1, ascending: false});

  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('KlassenbuchGesamt').getRange("B2:B").setNumberFormat("dd.MM.yyyy");
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('KlassenbuchGesamt').getRange("A2:G").sort({column: 2, ascending: false});

      Utilities.sleep(500);

  SpreadsheetApp.getActiveSpreadsheet().toast("Stunde eingetragen!","",3);

      Utilities.sleep(500);
  
  hide();
}

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <script>
    function submitForm() {
      google.script.run.appendRowFromFormSubmitKBSTART(document.getElementById("KBstart"));
      document.getElementById("form").style.display = "none";
      
    }
  </script>
</head>
<body>
  <div>
  <div id="form">
  
  <form id="KBstart">

    <label for="Datum"><b>Datum</b></label></br>
    <input type="date" id="Datum" name="Datum"><br><br>

    <label for="Inhalt"><b>Inhalt</b></label></br>
    <input type="text" id="Inhalt" name="Inhalt" size="60"><br><br>

    <label for="Sozialform"><b>Einzel oder Gruppe?</b></label><br>
        <input type="checkbox" id="Ez" name="Sozialform" value="Ez">
        <label for="Ez">Einzel</label><br>
        <input type="checkbox" id="Gr" name="Sozialform" value="Gr">
        <label for="Gr">Gruppe</label><br><br>

    <label for="Dauer"><b>Dauer</b></label></br>
       <select id="Dauer" name="Dauer">
          <option value="45">45</option>
          <option value="60">60</option>
          <option value="90">90</option>        
        </select>
    
    <div>
    </br>  

    <label for="Lehrkraft"><b>Lehrkraft</b></label></br>
      <select id="Lehrkraft" name="Lehrkraft">
          <option value="Alexander Michaelis">Alexander Michaelis</option>
          <option value="Thomas Weider">Thomas Weider</option>
          <option value="Merve Besser">Merve Besser</option>        
        </select>
    
    <div>
    </br>  

      <label for="Ausfall"><b>Ausfall?</b></label><br>
        <input type="checkbox" id="yes" name="Ausfall" value="E">
        <label for="yes">Entschuldigt</label><br>
        <input type="checkbox" id="no" name="Ausfall" value="UE">
        <label for="no">Unentschuldigt</label><br><br>

      
      <input type="button" value="Eintragen" onclick="submitForm();google.script.host.close()">
        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
      <button type="reset" value="Reset">Neu</button>
  </form>

</body>
</html>

Can someone help me with getting the data in the specific range and appending a new row without using appendRow? Thanks!

UPDATE!!: The following code was suggested:

function appendRowFromFormSubmitKBSTART(formKBSTART) {


var kb = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('KlassenbuchGesamt');


var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var col = 0; // column A
// get the number of last empty row in column 'A'
var col_data = sh.getDataRange().getValues().map(x=>x[0]);
var last_row = col_data.length - col_data.reverse().findIndex(x=>x!='') + 1;

// get the range
var myRange = sh.getRange('A' + last_row + ':F' + last_row);

// data
var myData = [
    formKBSTART.Datum,
    formKBSTART.Inhalt,
    formKBSTART.Sozialform,
    formKBSTART.Dauer,
    formKBSTART.Lehrkraft,
    formKBSTART.Ausfall
];

// set the data into the range
myRange.setValues(myData);

ss.getRange("A9:A").setNumberFormat("dd.MM.yyyy");
ss.getRange("A9:F").sort({column: 1, ascending: false});

kb.appendRow([SpreadsheetApp.getActiveSheet().getName(), formKBSTART.Datum, formKBSTART.Inhalt, formKBSTART.Sozialform, formKBSTART.Dauer, formKBSTART.Lehrkraft, formKBSTART.Ausfall]);
kb.getRange("B2:B").setNumberFormat("dd.MM.yyyy");
kb.getRange("A2:G").sort({column: 2, ascending: false});

ss.toast("Stunde eingetragen!","",3);

hide();


}

UPDATE2: Here is the code in its simplest form:

function showDialogKBSTART() {

  var widget = HtmlService.createHtmlOutputFromFile("formKBSTART.html").setHeight(450).setWidth(450);
  
  SpreadsheetApp.getUi().showModalDialog(widget, "Stunde eintragen");
}




function appendRowFromFormSubmitKBSTART(formKBSTART) {


var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var col = 0; // column A
// get the number of last empty row in column 'A'
var col_data = sh.getDataRange().getValues().map(x=>x[0]);
var last_row = col_data.length - col_data.reverse().findIndex(x=>x!='') + 1;

// get the range
var myRange = sh.getRange('A' + last_row + ':F' + last_row);

// data
var myData = [
    formKBSTART.Datum,
    formKBSTART.Inhalt,
    formKBSTART.Sozialform,
    formKBSTART.Dauer,
    formKBSTART.Lehrkraft,
    formKBSTART.Ausfall
];

// set the data into the range
myRange.setValues(myData);



}

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <script>
    function submitForm() {
      google.script.run.appendRowFromFormSubmitKBSTART(document.getElementById("KBstart"));
      document.getElementById("form").style.display = "none";
      
    }
  </script>
</head>
<body>
  <div>
  <div id="form">
  
  <form id="KBstart">

    <label for="Datum"><b>Datum</b></label></br>
    <input type="date" id="Datum" name="Datum"><br><br>

    <label for="Inhalt"><b>Inhalt</b></label></br>
    <input type="text" id="Inhalt" name="Inhalt" size="60"><br><br>

    <label for="Sozialform"><b>Einzel oder Gruppe?</b></label><br>
        <input type="checkbox" id="Ez" name="Sozialform" value="Ez">
        <label for="Ez">Einzel</label><br>
        <input type="checkbox" id="Gr" name="Sozialform" value="Gr">
        <label for="Gr">Gruppe</label><br><br>

    <label for="Dauer"><b>Dauer</b></label></br>
       <select id="Dauer" name="Dauer">
          <option value="45">45</option>
          <option value="60">60</option>
          <option value="90">90</option>        
        </select>
    
    <div>
    </br>  

    <label for="Lehrkraft"><b>Lehrkraft</b></label></br>
      <select id="Lehrkraft" name="Lehrkraft">
          <option value="Alexander Michaelis">Alexander Michaelis</option>
          <option value="Thomas Weider">Thomas Weider</option>
          <option value="Merve Besser">Merve Besser</option>        
        </select>
    
    <div>
    </br>  

      <label for="Ausfall"><b>Ausfall?</b></label><br>
        <input type="checkbox" id="yes" name="Ausfall" value="E">
        <label for="yes">Entschuldigt</label><br>
        <input type="checkbox" id="no" name="Ausfall" value="UE">
        <label for="no">Unentschuldigt</label><br><br>

      
      <input type="button" value="Eintragen" onclick="submitForm();google.script.host.close()">
        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
      <button type="reset" value="Reset">Neu</button>
  </form>

</body>
</html>

So basically its this: enter image description here

google-apps-script google-sheets
1 Answers

Obviously you need range.setValues() method. Something like this:

var myRange = SpreadsheetApp.getActiveSheet().getRange('A9:F9');

var myData = [
    formKBSTART.Datum,
    formKBSTART.Inhalt,
    formKBSTART.Sozialform,
    formKBSTART.Dauer,
    formKBSTART.Lehrkraft,
    formKBSTART.Ausfall
];

myRange.setValues([myData]); // <--- square brackets!

Of course you need to calculate the destination range every time. First it can be A9:F9 then it will be A10:F10, etc.

You can find the first empty cell at the end of a column this way:

var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var col = 0; // column A
var col_data = sh.getDataRange().getValues().map(x=>x[col]);
var last_row = col_data.length - col_data.reverse().findIndex(x=>x!='')+1;

Thus the part of the code could look like this:

var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

// get the number of first empty row at the end of column 'A'
var col_data = sh.getDataRange().getValues().map(x=>x[0]);
var last_row = col_data.length - col_data.reverse().findIndex(x=>x!='') + 1;

// get the range
var myRange = sh.getRange('A' + last_row + ':F' + last_row);

// data
var myData = [
    formKBSTART.Datum,
    formKBSTART.Inhalt,
    formKBSTART.Sozialform,
    formKBSTART.Dauer,
    formKBSTART.Lehrkraft,
    formKBSTART.Ausfall
];

// set the data into the range
myRange.setValues([myData]); // <---- square brackets!

Another approach: you can remove the empty cells every time (and move the data up) in particular ranges. I think it's a less efficient approach, though.

user_142654690
Related