derray87
Goto Top

GoogleSheets - Script zum Sortieren und Zusammenfassen von Zahlungen

Hallo zusammen,

ich bin ein absoluter Neuling was Programmieren / Skripten angeht und alles was ich hierbei kann, habe ich mir im Internet zusammengelesen und bin wirklich kein Profi.
Ich hoffe das mir die Schwarmintelligenz, bei meinem aktuellen Problem weiterhelfen kann, da ich hier noch nichts passendes im Forum gefunden habe.

Ich habe eine GoogleSheets-Tabelle.
In dieser Tabelle befinden sich für jeden Tag einzelne Reiter die man sich wie folgt vorstellen kann:

Spalte A = Kundenummer
Spalte B = Kundenname
Spalte C = Zahlungsnummer
Spalte D = Zahlungsbetrag

Jede Zeile ist ein einzelner Datensatz zu einem Kunden (siehe Bild 1).

1

Nun möchte ich ein Script schreiben, was den aktuellen Reiter Zeile für Zeile durchliest und dabei die Kundennummer, die Zahlungsnummer und den Zahlungsbetrag speichert.
Anschließend soll das Script für jede Kundennummer ein neuen Reiter generieren und in diesem Reiter jede Zahlungsnummer mit dem zugehörigen Zahlungsbetrag darstellen und zum Schluss alle Zahlungsbeträge addieren.
Der Reiter sollte wie der Kundenname + Datum heißen (siehe Bild 2).

2

Ich bin für jede Hilfe Dankbar.

EDIT: Eine Pivot-Tabelle würde das zwar auch hinbekommen, aber leider muss ich für jeden Kunden eine PDF-Erstellen und via. E-Mail versenden.

Viele Grüße

Content-Key: 71746607111

Url: https://administrator.de/contentid/71746607111

Printed on: March 3, 2024 at 15:03 o'clock

Mitglied: 8030021182
8030021182 Nov 13, 2023 updated at 13:31:33 (UTC)
Goto Top
Hi.
Programmieren überflüssig für so etwas gibt es schon "ewig und drei Tage" sogenannte Pivot-Tabellen die das schön mit Summen etc. zusammenfassen: Create & Use Pivot-Tables

Gruß Katrin
Member: DerRay87
DerRay87 Nov 13, 2023 at 13:32:08 (UTC)
Goto Top
Ich bin für jede Hilfe Dankbar.

PS: Alle diese Informationen könnte ich mir auch einfach mit einer Pivot-Tabelle ziehen, aber ich brauche die gesammelten Informationen um diese später als PDF-Ausdruck via. E-Mail zu versenden.
Wie ich das mit einem Script lösen kann weiß ich bereits.


Danke im Voraus und viele Grüße
Mitglied: 8030021182
8030021182 Nov 13, 2023 updated at 13:35:31 (UTC)
Goto Top
Wie ich das mit einem Script lösen kann weiß ich bereits.
Dann braucht du uns ja nicht mehr face-smile.

p.s. Es gibt schöne Rechnungsprogramme out of the box face-smile.
Member: DerRay87
DerRay87 Nov 13, 2023 at 13:35:41 (UTC)
Goto Top
Hallo katrin11,

ich meine damit, dass ich weiß wie ich ein Sheet-Reiter via. Script als PDF-Ausdrucke und via. E-Mail versende.
Member: colinardo
Solution colinardo Nov 13, 2023, updated at Nov 14, 2023 at 12:57:22 (UTC)
Goto Top
Servus @DerRay87.

Habe dir mal ein Beispiel zusammengestellt:

screenshot

screenshot

screenshot

Google Sheets AppsScript-Function (Quelldaten liegen im Beispiel auf dem Sheet mit dem Namen "Daten", kann im Code in Zeile 5 angepasst werden):

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) + ")");  
  }
}

Eine alternative Code-Variante arbeitet mit Filtern:

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();
}

Viel Erfolg!
Grüße Uwe
Member: DerRay87
DerRay87 Nov 14, 2023 at 14:27:22 (UTC)
Goto Top
Hallo colinardo,

erst einmal vielen DANK für deinen sehr hilfreichen Beitrag.
Ich habe mir dein 1. Script als Grundlage genommen und es an meine Bedürfnisse angepasst.

Leider habe ich aber noch ein kleines Problem.

In deinem Script werden die folgenden Zellen die folgenden Werte geschrieben:

A2 = Kundennummer; B2 = Kundenname; C2 = Zahlungsnummer; D2 = Auszahlungsbetrag (welcher am Ende Summiert wird, wenn es mehr als eine Zahlungsnummer bzw. Auszahlungsbetrag pro Kundenummer gibt.

Nun zu meinen Anpassungen:

Die Kundenummern werden nun in die Zelle "C16" und der Kundenname in die Zelle "D16" geschrieben wird --> dies funktioniert so weit auch gut.

Die Zahlungsnummer wird nun in die Zelle "B19" und der Auszahlungsbetrag in die Zelle "C19" geschrieben --> auch dies funktioniert auch gut.

Mein Problem ist, dass wenn die Kundenummer mehr als eine Zahlungsnummer und Auszahlungsbetrag hat, dann werden die Zellen "B19" und "C19" direkt mit den neuen Werten überschrieben.


Vielen Dank im Voraus und freundliche Grüße
Ray
Member: colinardo
Solution colinardo Nov 14, 2023 updated at 15:56:48 (UTC)
Goto Top
Servus.
Mein Problem ist, dass wenn die Kundenummer mehr als eine Zahlungsnummer und Auszahlungsbetrag hat, dann werden die Zellen "B19" und "C19" direkt mit den neuen Werten überschrieben.
Dann hast du das Skript noch nicht ganz verstanden.
Das Array mit den Daten für den Kunden wird in folgender Zeile
cSheet.getRange(2,1,groupdata[group].length,4).setValues(groupdata[group]);
auf das neue Sheet geschrieben.
Der erste Parameter steht für den Zeilenindex, der zweite für die Spaltenindex, der dritte bestimmt die Anzahl der Zeilen die eingefügt werden, und der vierte die Anzahl der Spalten.
Somit wird nichts überschrieben und automatisch alle vorhandenen Daten an der definierten Zelle untereinander weg geschrieben.

Für deine obige Beschreibung für die Ausgabezellen sähe die Anpassung so aus:

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) + ")");  
    }
}

Ergebnis

screenshot


Grüße Uwe
Member: DerRay87
DerRay87 Nov 17, 2023 at 10:27:41 (UTC)
Goto Top
Hallo Colinardo,

noch einmal tausend Dank für deine Hilfe, das ist genau das was ich benötigt habe und ich habe auch noch was dabei gelernt.


Viele Grüße
Ray
Member: colinardo
colinardo Nov 17, 2023 updated at 10:30:31 (UTC)
Goto Top
Gerne ­čĹŹ

Schönes Wochenende.