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

Hi sorry I did not fully understood your goal. I got lost around here: 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.. Based on my understanding, you want to add the split payments based on another sheet based on its due dates. But I did not understand what would be the expected output in the event of a new order? should the new order be added in another row of your sheet? could you provided an expected output for that as well — Kristkun
– Kristkun, Commented May 17, 2021 at 15:30
And checking your script in the sample sheet. I assume that the functionality that you are raising doesn't exist yet? The adding of the split payments on another sheet. — Kristkun
– Kristkun, Commented May 17, 2021 at 15:31
Yes, this functionality is not yet implemented. I just included everything I had so far, so I wouldn't get useless information mixed in. For the unclear statement, it's something like what you described. I'll edit the question to illustrate this case. — Rodrigo Biffi
– Rodrigo Biffi, Commented May 17, 2021 at 16:34

Kristkun · Accepted Answer · 2021-05-17 18:02:54Z

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?

I created a sheet "Summary" that contains dates in row 1.
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
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.
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
I get the range of dates in "Summary" sheet using this a1notation ('1:1')
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.
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.

That's perfect. It worked out great. The only thing I'll have to do now is creating new columns for the dates in case I'm missing some at the end, but that's the easy part. Thank you very much for the help!

Collectives™ on Stack Overflow

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

Example of what I need

Expected output

EDIT

Code

1 Answer 1

You can refer to this sample code:

Sample sheet data with output:

What it does?

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Example of what I need

Expected output

EDIT

Code

1 Answer 1

You can refer to this sample code:

Sample sheet data with output:

What it does?

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related