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).
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).
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
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).
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).
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
Please also mark the comments that contributed to the solution of the article
Content-ID: 71746607111
Url: https://administrator.de/contentid/71746607111
Printed on: October 5, 2024 at 09:10 o'clock
9 Comments
Latest comment
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
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
Wie ich das mit einem Script lösen kann weiß ich bereits.
Dann braucht du uns ja nicht mehr .p.s. Es gibt schöne Rechnungsprogramme out of the box .
Servus @DerRay87.
Habe dir mal ein Beispiel zusammengestellt:
Google Sheets AppsScript-Function (Quelldaten liegen im Beispiel auf dem Sheet mit dem Namen "Daten", kann im Code in Zeile 5 angepasst werden):
Eine alternative Code-Variante arbeitet mit Filtern:
Viel Erfolg!
Grüße Uwe
Habe dir mal ein Beispiel zusammengestellt:
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
Servus.
Das Array mit den Daten für den Kunden wird in folgender Zeile
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:
Ergebnis
Grüße Uwe
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]);
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
Grüße Uwe