GoogleSheets - Script zum Sortieren und Zusammenfassen von Zahlungen

function GroupPaymentsByClients {
  // get active spreadsheet
  let wb = SpreadsheetApp.getActiveSpreadsheet();
  // get sheet with name "Daten" 
  let sheet = wb.getSheetByName("Daten");  
  // get formated current date 
  let cDate = new Date().toLocaleDateString("de-DE");  
  // array will hold grouped data
  let groupdata = {};
  // get data values from sheet
  let sheetdata = sheet.getRange(1,1,sheet.getLastRow(),4).getValues();
  // iterate through data
  for(r = 1 ; r < sheetdata.length;r++){
    if (sheetdata[r][0] != ""){  
      if(!groupdata[sheetdata[r][0]]){
        groupdata[sheetdata[r][0]] = [];
      }
      groupdata[sheetdata[r][0]].push(sheetdata[r]);
    }
  }
  // for each group of data
  for(var group in groupdata){
    // define sheet name
    var sheetName = "Kunde_" + group + "_" + cDate;  
    // create new sheet for client if needed
    var cSheet = wb.getSheetByName(sheetName) ?? wb.insertSheet();
    // clear sheets contents
    cSheet.clear();
    // set name of sheet
    cSheet.setName(sheetName);
    // copy headings to new sheet
    sheet.getRange(1,1,1,4).copyTo(cSheet.getRange("A1"));  
    // insert data
    cSheet.getRange(2,1,groupdata[group].length,4).setValues(groupdata[group]);
    // define sum row
    let sumRow = cSheet.getLastRow()+1;
    // add sum row with title and formula
    cSheet.getRange(sumRow,3).setValue("Gesamt:");  
    cSheet.getRange(sumRow,4).setFormula("=SUM(D2:D" + (sumRow-1) + ")");  
  }
}

function GroupPaymentsByClientsWithFilter() {
  // get active spreadsheet
  let wb = SpreadsheetApp.getActiveSpreadsheet();
  // get sheet with name "Daten"  
  let sheet = wb.getSheetByName("Daten");  
  // get formated current date 
  let cDate = new Date().toLocaleDateString("de-DE");  
  // array will hold unique client numbers
  let clientnumbers = [];
  // get last used row
  let lastRow = sheet.getLastRow();
  // get unique client numbers
  for(r = 2 ; r <= lastRow;r++){
    var cNum = sheet.getRange(r,1).getValue();
    if (cNum != "" && clientnumbers.indexOf(cNum) == -1){  
      clientnumbers.push(cNum);
    }
  }
  // get used range of sheet
  let usedRange = sheet.getRange(1,1,sheet.getLastRow(),4);
  // get/create data filter
  let filter = usedRange.getFilter() ?? usedRange.createFilter();
  // foreach client number
  clientnumbers.forEach(c => {
    // define name for new sheet
    let sheetName = "Kunde_" + c + "_" + cDate;  
    // create new sheet for client if needed
    let cSheet = wb.getSheetByName(sheetName) ?? wb.insertSheet();
    // clear sheets contents
    cSheet.clear();
    // set name of sheet
    cSheet.setName(sheetName);
    // filter data for client
    filter.setColumnFilterCriteria(1,(SpreadsheetApp.newFilterCriteria().whenTextEqualTo(c)));
    // copy filtered 4 columns data to target sheet
    sheet.getRange(1,1,sheet.getLastRow(),4).copyTo(cSheet.getRange("A1"));  
    // define sum row
    let sumRow = cSheet.getLastRow()+1;
    // add sum row with title and formula
    cSheet.getRange(sumRow,3).setValue("Gesamt:");  
    cSheet.getRange(sumRow,4).setFormula("=SUM(D2:D" + (sumRow-1) + ")");  
  });
  // remove filter from source
  filter.remove();
}

cSheet.getRange(2,1,groupdata[group].length,4).setValues(groupdata[group]);

function GroupPaymentsByClients() {
  // get active spreadsheet
  let wb = SpreadsheetApp.getActiveSpreadsheet();
  // get sheet with name "Daten" 
  let sheet = wb.getSheetByName("Daten");  
  // get formated current date 
  let cDate = new Date().toLocaleDateString("de-DE");  
  // array will hold grouped data
  let groupdata = {};
  // get data values from sheet
  let sheetdata = sheet.getRange(1,1,sheet.getLastRow(),4).getValues();
  // iterate through data
  for(r = 1 ; r < sheetdata.length;r++){
    if (sheetdata[r][0] != ""){  
      if(!groupdata[sheetdata[r][0]]){
        groupdata[sheetdata[r][0]] = [];
      }
      groupdata[sheetdata[r][0]].push(sheetdata[r].slice(1));
    }
  }
  // for each group of data
  for(var group in groupdata){
    // define sheet name
    var sheetName = "Kunde_" + group + "_" + cDate;  
    // create new sheet for client if needed
    var cSheet = wb.getSheetByName(sheetName) ?? wb.insertSheet();
    // clear sheets contents
    cSheet.clear();
    // set name of sheet
    cSheet.setName(sheetName);
    // write out client number and name
    cSheet.getRange("C16").setValue(group);  
    cSheet.getRange("D16").setValue(groupdata[group][0][0]);  
    // insert data starting from B19
    for(var i = 0;i < groupdata[group].length;i++){
      cSheet.getRange(19+i,2,1,2).setValues([groupdata[group][i].slice(1)]);
    }
    // write sum only if more than 1 dataset
    if(groupdata[group].length > 1){
      // define sum row
      let sumRow = cSheet.getLastRow()+1;
      // add sum row with title and formula
      cSheet.getRange(sumRow,2).setValue("Gesamt:");  
      cSheet.getRange(sumRow,3).setFormula("=SUM(C19:C" + (sumRow-1) + ")");  
    }
}