EN VI

Google-apps-script - How to write the data to the last row of the sheet?

I have the following function that creates invoices and at the end lists the created invoices in the invoicesSheet:

function processDocuments() {
var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Rechnungsvorlage'), true);  

// Display a dialog box with a message and "Yes" and "No" buttons.
// The user can also close the dialog by clicking the close button in its title bar.
var ui = SpreadsheetApp.getUi();
var response = ui.alert(
    'VG Abrechnung jetzt erstellen?',
    ui.ButtonSet.YES_NO
);

// Process the user's response.
if (response == ui.Button.YES) {
    Logger.log('The user clicked "Yes."');
} else {
    Logger.log('The user clicked "No" or the close button in the dialog\'s title bar.');
    return;
}

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const customersSheet = ss.getSheetByName(CUSTOMERS_SHEET_NAME);
  const productsSheet = ss.getSheetByName(PRODUCTS_SHEET_NAME);
  const transactionsSheet = ss.getSheetByName(TRANSACTIONS_SHEET_NAME);
  const invoicesSheet = ss.getSheetByName(INVOICES_SHEET_NAME);
  const invoiceTemplateSheet = ss.getSheetByName(INVOICE_TEMPLATE_SHEET_NAME);

  // Gets data from the storage sheets as objects.
  const customers = dataRangeToObject(customersSheet);
  const products = dataRangeToObject(productsSheet);
  const transactions = dataRangeToObject(transactionsSheet);

  ss.toast('Creating Invoices', APP_TITLE, 1);
  const invoices = [];

  // Iterates for each customer calling createInvoiceForCustomer routine.
  customers.forEach(function (customer) {
    ss.toast(`Erstelle Rechnung für ${customer.customer_name}`, APP_TITLE, 1);
    let invoice = createInvoiceForCustomer(
      customer, products, transactions, invoiceTemplateSheet, ss.getId());
    invoices.push(invoice);
  });
  // Writes invoices data to the sheet.
   invoicesSheet.getRange(2, 1, invoices.length, invoices[0].length).setValues(invoices);
}

I tried using getLastRow to change the last line so that the existing entries are not overwritten. Unfortunately without success. Can someone help?

Solution:

One way is to use my appendRows_() utility function, like this:

  // Appends invoices data to the sheet.
  appendRows_(invoicesSheet, invoices);

For that to work, you will need to paste the appendRows_() and getLastRow_() utility functions in your script project.