Generate multiple rows of data based on 1 row input - Google Sheets

Question

I'm trying to create an automation in my backend in Google sheets dashboard to make it easy for me to scale a certain report that I'm creating.

Basically, I want to Generate multiple rows of data based on 1-row input done in a certain place. This is basically regarding recurring income and recurring expenses that are repeating monthly or quarterly. So the generation of multiple rows should be based on "Recurring months time", "Recurring income" and "Recurring expense". The main thing is to add dates also here - by the amount of time it keeps repeating. The loop and repetitions should go on until a decided date in 2019-2020 etc.(Say 1 Jan 2019)

Detailed example below, the input table is done by an individual - whereas the output table is generated automatically by the script, code and formulae

---

Hope this explains my problem statement clearly. I think we will have to use "Google Apps Script" here to create the loop from the input statements.

Any approach and help in solving this would be highly appreciated.

Answer 1

Here is some code, it's should do the trick.

function generateReccuringExpenses(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var inputSheet = ss.getSheetByName("Input Table");
  var outputSheet = ss.getSheetByName("Output Table");
  var endYear = 2020;

  // We considere your data to be on a sheet with a header, starting on A1 cell
  var data = inputSheet.getRange(2, 1, inputSheet.getLastRow()-1, inputSheet.getLastColumn()).getValues();

  for(var i = 0; i < data.length; i++){

    var row = data[i];

    if(row[3] > 0){

      var elements = new Array();
      var d = new Date(row[0]);

      while(d.getFullYear() < endYear){

        var test = d.getFullYear();
        var month = d.getMonth() +1;
        var dateString = month+"/"+d.getDate()+"/"+d.getFullYear();
        var newRow = [dateString, row[1],row[2]];
        elements.push(newRow);

        var n = d.getMonth() + row[3];

        if(n > 11){

          d.setMonth(n -11);
          d.setYear(d.getYear() + 1);

        }
        else{

          d.setMonth(n);

        }

      }

      var outputRange = outputSheet.getRange(outputSheet.getLastRow()+1, 1, elements.length, outputSheet.getLastColumn());
      outputRange.setValues(elements);

    }

  }

  var range = outputSheet.getRange(2, 1, outputSheet.getLastRow()+1, outputSheet.getLastColumn());
  range.sort(1); // Sort by date (column 1)

}

You can sort the result with the Range.sort() method or with the Spreadsheet tool.