How to sum up values sorting them into dates using new sheets as an input source

Question

I'm not sure the question title is pretty clear, so I'll explain it better.

I have a spreadsheet I'm working on which is basically a template just for checking financial data. Even with full info copied from the original Excel file, the script only gets 4 cells to work with, which are the highlighted ones.

The first one (C3) is the client's name. The second one (C9) is how many days after the delivery date the payments are due, and if it's 2 or more payments, it is split with a /. Then, we have the delivery date on F11, and the total value on H25.

Everything I needed to do was quite easy to do, even integrating with Google Calendar to register different payments depending on the client. What I have here is the =split() of C9 in A26:26, the sum of the split cells with the delivery date right below, and the division of the total amount by the count of payments.

Then I used =transpose() to create a new matrix to properly send this data to my Google Calendar.

That said, it's kind of obvious I'm working mostly with formulas and references. What I can't seem to do now is to get the divided payment values and order them in a second sheet according to the payday. I don't need the client's name or anything, just sort the payment's values of different clients into columns (or rows, whatever is easier to accomplish), so I can have the total for that day.

Example of what I need

So I need to somehow scan trough the dates and then down the rows to add them - pretty much like I already did with the Calendar - but to sum up the total value for that day, which can change in the event of a new order, so I guess using the second row for the sum and starting from row 3 would be the best case. (Also, notice that not all values are present here, since I have every day, one by one, in the sheet, so for this example the 5th of June is there, only not in the screencap).

Expected output

Assume that I have the case above, with those four payments. If I have a new order, it would then look like this on the template:

And on the other sheet, it would keep the old data from the first example and include the new data, summing up the payments' values, like so:

This way, the new data could be entered below the previous one, or above by creating a new row. Doesn't realy matter which.

It could be done in a static way, so to speak, never removing days already gone, on dynamically, always updating the first day acording to =today() or a new Date(), which I guess is way more complicated to do.

EDIT

I forgot to mention that for every order, there is a new spreadsheet, which I'll just copy into this template. The new sheet with the expected results is just this one for every entry, so I'll need a special paste for values only as well.

Here is the full copy of the Spreadsheet with the script, except the Calendar ID.

Code

function onOpen(){ //creates custom menu tabs
  var ui = SpreadsheetApp.getUi();
      // Or DocumentApp or FormApp.
      ui.createMenu('Tratar Planilha')
          .addItem('Datas e Parcelas', 'main')
          .addItem('Adicionar à Agenda', 'criaEvento') //there's probably no need to use this
      .addToUi();
}

function main(){
  separaData();
  contaParcela();
  formatTexto();
  transpoeTabela();
};

function separaData() { //splits C9 ifnecessary, and prints the rest accordingly
  var spreadsheet = SpreadsheetApp.getActiveSheet(); 

  spreadsheet.getRange('A26').activate(); 
  spreadsheet.getCurrentCell().setFormula('=IF(C9="à vista";"0";SPLIT(C9;"/"))'); 
 
  spreadsheet.getRange('A27').activate(); 
  spreadsheet.getCurrentCell().setFormula('=IF(A26="";"";$F11+A26)'); 
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange("27:27"), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
};

function contaParcela(){ //counts number of payments and their values
  var spreadsheet = SpreadsheetApp.getActiveSheet(); 
  
  spreadsheet.getRange('A28').activate();
  spreadsheet.getCurrentCell().setFormula('=IF(A26="";"";$H25/COUNTA(26:26))'); 

  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange("28:28"), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
 
  spreadsheet.getRange('A30').activate();
  spreadsheet.getCurrentCell().setFormula('=TEXTJOIN(""; TRUE; COUNTA(26:26); " parcelas de "; A28)');


};

function transpoeTabela(){ //transposes the matrix 

  var spreadsheet = SpreadsheetApp.getActiveSheet();
  spreadsheet.getRange('A29').activate().setFormula('=IF(A26="";"";$C3)');
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange("29:29"), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  spreadsheet.getRange('A32').setFormula('=TRANSPOSE(27:29)');
  spreadsheet.getRange('D32').setFormula('=IF(A32="";"";counta($26:$26)-counta($26:$26)+1)');
  spreadsheet.getRange('D33').activate();
  spreadsheet.getCurrentCell().setFormula('=IF(A33="";"";D32+1)');
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('D33:D'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
};


function criaEvento(){ //sends transposed info to Calendar with description.
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var calendarID = CalendarApp.getCalendarById('<MY_ID>');
  var valTotal = spreadsheet.getRange('H25').getValue();
  var valParcela = spreadsheet.getRange('A30').getValue();
  var registros = spreadsheet.getRange('A32:D52').getValues();
  Logger.log(registros);
  
  for (x=0; x <= registros.length; x++){
    var shift = registros[x];
    var data = shift[0];
    Logger.log(data);
    var empresa = shift[2];
    Logger.log(empresa);
    var nParcela = shift[3];
    var descricao = 'Total: R$' + valTotal + '\nValor da parcela: R$' + valParcela + '\nNº da parcela: ' + nParcela;
    Logger.log(descricao);
    if (data != ""){
      calendarID.createAllDayEvent(empresa,data).setDescription(descricao);
    }
    else{
      break;
    }
  }
}

function formatTexto() { //just sets cell formatting
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A31:C31').activate();
  spreadsheet.getCurrentCell().setValue('DADOS ENVIADOS AO CALENDARIO');
  spreadsheet.getActiveRangeList().setBackground('#ffff00');
};

Answer 1

You can refer to this sample code:

function addSumFormula() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Summary'); 

  spreadsheet.getRange('A2').activate();
  spreadsheet.getCurrentCell().setFormula('SUM(A3:A)'); 
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange("2:2"), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
};

function submitData(){
  var sourceS = SpreadsheetApp.getActiveSheet();
  var destinationS = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Summary'); 
  var amount = sourceS.getRange('A28').getValue();
  var dates = sourceS.getRange('A32:A').getDisplayValues().flat().filter(String);
  Logger.log(amount);
  Logger.log(dates);

  var searchRange = destinationS.getRange('1:1');
  dates.forEach(date => {
    Logger.log(date);
    var textFinder = searchRange.createTextFinder(date);
    var match = textFinder.findNext();
    if(match){
      matchCol = match.getColumn();
      //get row 3 cell of the matched column date and create a range string
      var a1Notation = match.offset(2,0).getA1Notation();
      var rangeStr = a1Notation+":"+a1Notation.replace(/[0-9]/g, '');
      Logger.log(rangeStr);
      //Get the count of data available under the matched column
      var colDataCnt = destinationS.getRange(rangeStr).getDisplayValues().flat().filter(String).length;
      Logger.log(colDataCnt);

      //Write data on the next blank range starting from row 3
      match.offset(2+colDataCnt,0).setValue(amount);
    }
  });
}

Sample sheet data with output:

• In this example, I run submitData() in sheet "Plan1". Hence 3086,25 was added in row 3 and row 4 of "Summary" sheet

What it does?

1. I created a sheet "Summary" that contains dates in row 1.
2. Create addSumFormula() that will add the sum formula in row 2 of sheet "Summary" (I manually changed the number format of row 2 to currency
3. Create submitData() that will add the data under "DADOS ENVIADOS AO CALENDARIO" based on the dates listed down in the active sheet to the "Summary" sheet.
4. I get the list of dates from this range A32:A. I used array.flat() to convert 2-d array to 1-d array. Use array.filter() to remove empty values in the array
5. I get the range of dates in "Summary" sheet using this a1notation ('1:1')
6. I loop all the list of dates obtained in step 4. Create a textfinder for each dates to find the matching column range in "Summary" sheet.
7. If matched date was found, I will count the number of data available under that particular date starting from row3. And add the new data on the next blank row.