Generate a number of rows based on input and adding SUM formula to last row

Question

I am trying to add a rent roll to my Google sheets real estate investment calculator. Since each property this is used for will have a different number of units, I want to generate a row for each rental unit in the property based on a user input.

For example: User inputs "12" for number of units, and 12 rows are generated that are numbered 1-12.

This will be in column A. In column B, the user will then input the amount of rent collected for each unit. The total rent from the generated rows will be added and displayed in a specific cell that can be referenced in another formula.

Here's an example https://docs.google.com/spreadsheets/d/1KCeyLCcQZPBwzuuq8v8nn0flOlIx89-Tyjv5GjR4koI/edit?usp=sharing

Anyone have a suggestion on how to do this?

Answer 1

You can use this sample onEdit simple trigger:

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var cell = e.range;
  
  //Check if modified cell is Sheet1!B3
  if(sheet.getName() == 'Sheet1' && cell.getA1Notation() == 'B3'){
    //Change formula in Sheet1!B4
    var unit = cell.getValue();
    var formula = '=sum(B8:B'+(8+unit-1)+')';
    Logger.log(formula);
    sheet.getRange('B4').setFormula(formula);

    //Clear output in Sheet1 starting from row 8 for Column A and B (including format)
    sheet.getRange(8,1,sheet.getLastRow(),2).clear();

    //Create an output data
    var output = [];
    for(var i=0; i<unit; i++){
      output.push([i+1,""]);
    }
    output.push(["Total",formula]);
    Logger.log(output);
    sheet.getRange(8,1,output.length,output[0].length).setValues(output);

    var yellow = '#ffff00';
    var orange = '#ff9900';
    //Add background color 'orange' to column A 
    sheet.getRange(8,1,unit,1).setBackgroundColor(orange);
    //Add background color 'yellow' to column B 
    sheet.getRange(8,2,unit,1).setBackgroundColor(yellow);
    
  }
}

What it does?

1. Get the active sheet and the modified cell using the Google Sheets event objects e.source and e.range
2. Check if the modified cell is in Sheet1!B3
3. Get the value of the modified cell
4. Create a SUM formula based on the input value
5. Set the formula in Sheet1!B4
6. Clear the content of Sheet1 for Column A and B starting in row 8
7. Create a 2-d array [(Unit#),(Empty Cell Rent)]. Append ["Total", (SUM formula)] at the end of the array
8. Write the 2-d array output in Sheet1, starting from Column A row 8
9. Set background colors for column A and column B

Note:

Since we used Google Sheets event objects, you cannot run this function in your Apps Script editor. Event object is undefined. If you want to debug the code, include logs in the code then modify a cell in your sheet to trigger the onEdit() which contains the event object then check the execution tab for the latest logs

Output: