0

I'm using a sheet where I can open a custom dialog which gives the entered data back to the sheet in a specific range and column. For every new entry I use "AppendRow".

My problem is that i want to use multiple dialogs in one sheet, which should work independent of each other. What I mean is that I want to "restrict" the appendrow-method to a specific range in the sheet, so when I use another dialog - which gives the data back to another range in the sheet - the different appendRow-commands work independent of each other - so leaving no blank rows in the particular range.

Right now when I enter data in the first dialog and then in the second one, the data in the second range (linked to the second dialog) has a blank row. When I enter data in the first dialog after that, it will leave a blank row the first range and so on.

Can anybody help?

Here is my code which I'm using (it's the second dialog, but the first one works the same way just with different inputs):

function showDialogNOTIZ() {
  
  var widget = HtmlService.createHtmlOutputFromFile("formNOTIZ.html").setHeight(250).setWidth(700);
  SpreadsheetApp.getUi().showModalDialog(widget, "Neue Notiz / Neuen Zwischenstand anlegen");
}

function appendRowFromFormSubmitNOTIZ(formNOTIZ) {

    SpreadsheetApp.getActiveSheet().appendRow([,,,,,,,formNOTIZ.Datum, formNOTIZ.Inhalt, formNOTIZ.Erstelltvon]); 
  
    SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("H9:H").setNumberFormat("dd.MM.yyyy");
   
    SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("H9:M").sort({column: 7, ascending: false});
   
    SpreadsheetApp.getActiveSpreadsheet().toast("Eintrag erfolgreich!","",3);
    
}

Here are some screenshots of a example spreadsheet:

What I have is this: enter image description here

enter image description here

enter image description here

But what I want is this: enter image description here

Here is the code for the example: enter image description here

enter image description here

TheMaster
  • 45,448
  • 6
  • 62
  • 85
axis9011
  • 33
  • 4
  • `appendRow` is always atomic/independant. So not sure what you're asking. – TheMaster Dec 12 '21 at 14:20
  • yeah it was hard for me to describe the problem (native german) :D I will make a sheet as an example ! – axis9011 Dec 12 '21 at 14:40
  • Questions should be self contained. While external links are welcome(note: *Google sheets [exposes your email address](https://meta.stackoverflow.com/questions/394304/)*), consider adding a [table](https://meta.stackexchange.com/questions/356997/) or screenshots or csv text(like [this](https://stackoverflow.com/a/64186520/) by *export*ing as csv) to show your data structure. – TheMaster Dec 12 '21 at 14:47
  • i edited the post and added some screenshots – axis9011 Dec 12 '21 at 15:06
  • So basically, you want the last range in a specific column. In that case, this is a duplicate of: https://stackoverflow.com/questions/17632165/determining-the-last-row-in-a-single-column – TheMaster Dec 12 '21 at 15:22
  • Posting code in images does not allow us to copy it and paste in our script editors. [format code](https://meta.stackexchange.com/questions/22186/how-do-i-format-my-code-blocks) – Cooper Dec 12 '21 at 15:47
  • @Cooper I've reopened. Feel free to post as answer. Editing answers into questions seems a bit off. – TheMaster Dec 12 '21 at 16:57
  • @Cooper Alternatively, OP posted another question. Feel free to add a answer there and close this as duplicate. – TheMaster Dec 12 '21 at 17:02

0 Answers0