How do I expand or reduce formulas depending on cell content

Question

I have a table consisting of several sheets. The table should be processed daily by several users at the same time. The main editing is done in Sheet1. Sheet2 consists only of a query and formulas.

Example Sheet 1:

• Sheet 1 consists of 2500 rows and 17 columns.

Starting with row 12 (row 11 is the header row. The rows above contain formula references), columns A, B are to be filled in by the user. The columns C-H contain various formulas (e.g. GoogleFinance queries). The columns G-M must be filled in again by the user. Finally, there are formulas in the columns N-Q again.

Goal: Automatic expansion / deletion of formulas in columns C-H and N-Q, depending on whether content has been added / removed in columns A and B. The deletion process should be performed line by line to also delete remaining user content in the columns G-M.

The sheet schould also be sortable.

Example Sheet 2:

• Sheet 2 consists of 1500 rows and 11 columns.

Starting with line 9 (line 9 is also the header line. The rows above contain formula references), the columns A-I are filled with results of a query (which is located in cell A9). The query obtains certain contents from Sheet 1 and is dynamic. The columns J-K contain formulas.

Goal: Automatically extend / delete the formulas in the columns J-K, depending on whether content was added / removed in the columns A-I (by the query).

For extending, deleting and sorting I used the code examples below.

Unfortunately, the codes shown do not meet the requirements described in the constellation. I would therefore be very grateful for a better solution.

function fillDownFormulaTD(){

  Sheet = "sheet1";

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(Sheet);
  var lr = ss.getLastRow();
  var Rng1 = ss.getRange(1, 2, lr-1);

  ss.getRange("").setFormula('');
  ss.getRange("").copyTo(Rng1);         
}

---

function removeEmptyRows(){    
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Ticker-Datenbank');
  var maxRows = sheet.getMaxRows(); 
  var lastRow = sheet.getLastRow();

  sheet.deleteRows(lastRow+1, maxRows-lastRow-20);            
}

---

function Sortieren(){

  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A11:Q11')
  var currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  spreadsheet.getActiveRange().sort({column: 3, ascending: true});
  spreadsheet.getRange('A11').activate();
 }

Answer 1

This is a quick solution for Sheet2 (an answer for Sheet#1 is in the works).

For Sheet2, rather than manually populate every row of Columns J and I with formula, use an arrayformula that will "automatically" expand/contract according to the rows with data.

1. Delete ALL the formula (cells that are blank and non-blank) for every row>9 in Column J and Column I.
2. In Cell J10, insert this formula
   =arrayformula(IF($C10:$C="";"";HYPERLINK($J$1&$A10:$A&$J$2&$B10:$B;$J$3)))
3. In cell I10, insert this formula
   =arrayformula(IFERROR(VLOOKUP($C10:$C;'Geprüfte Ticker'!$C:$P;14;FALSE);""))

---

This code addresses the Sheet1 scenario.

The code is technically accurate and will work but there are two other issues the OP may wish to consider.

1. The spreadsheet is "processed daily by several users at the same time". There is a risk that a blank cell that detected in Column A or Column B, and which would be an indicator to delete the while row, is only temporary - the user may intend to correct spelling or enter new data. An "ideal" basis for deleting a row would be to detect a blank cell in both Column A **and ** Column B, but this is not always possible.

2. Even with only test data, recalculation time for the spreadsheet is long - @around 10+ seconds - the time for the live spreadsheet is not known. It's not known what effect this code will have on recalculation.

---

    function onEdit(e) {
      // setup spreadsheet and sheet
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var tsName = "onedittest";
      var targetsheet = ss.getSheetByName(tsName);

      // list event data
      // Logger.log(JSON.stringify(e)); //DEBUG

      //get the formukla ranges and formulas
      // set 1 = Column C-H
      // set 2 = Column N-P
      // Column Q = arrayformula - leave as is
      var rR1 = targetsheet.getRange("C12:H12");
      var fR1 = rR1.getFormulasR1C1();
      var rR2 = targetsheet.getRange("N12:P12");
      var fR2 = rR2.getFormulasR1C1();

      // collect event data
      var editR = e.range.getRow();
      var editRS = e.range.rowStart;
      var editRE = e.range.rowEnd;
      var editC = e.range.getColumn();
      var editCS = e.range.columnStart;
      var editCE = e.range.columnEnd;
      //Logger.log("DEBUG: edited rowstart = "+editRS+", rowend = "+editRE+", columnstart = "+editCS+", columnend = "+editCE);
      //Logger.log("DEBUG: edited row = "+editR+", edited column = "+editC);

      var editedsheet = e.range.getSheet().getSheetName();
      // Logger.log("DEBUG: sheet name = "+editedsheet);

      if (editedsheet === tsName  && editC>= 1 && editC<= 2 && editR>12) {

        // this range/cell is a trigger
        // Logger.log("DEBUG: match");
        var editCell = targetsheet.getRange(editRS, editCS);
        // Logger.log("DEBUG: the edited cell is "+editCell.getA1Notation());
        //Logger.log("DEBUG: the old value = "+e.oldValue+", and the new value = "+e.value);

        //now split Logic#1 and Logic#2
        // Logic#1 = AND there is data in the edited cell (that is, the edit did not make the cell blank), 
        // then copy (or recopy) the formulas from row 12 Columns C-H and N-Q onto the edited row.
        if (editCell.length !=0){
          // copy formulas
          // Logger.log("DEBUG: copy the formulas");
          var formulaset1 = targetsheet.getRange(editRS,3,1,6);
          formulaset1.setFormulasR1C1(fR1);  
          // Logger.log("DEBUG: set formulas for range 1")
          var formulaset2 = targetsheet.getRange(editRS,14,1,3);
          formulaset2.setFormulasR1C1(fR2);  
          // Logger.log("DEBUG: set formulas for range 2")

// Sort the data
      // get the number of rows of data
      var lastheaderRow = 11;
      var Avals = ss.getRange("A12:A").getValues();
      var Alast = Avals.filter(String).length;
      //Logger.log("DEBUG: Number of rows of data = "+Alast+", so last row = "+(Alast+lastheaderRow));

      // define the sort range
      var sortRange = targetsheet.getRange(lastheaderRow, 1, Alast+1, 17);
      // Logger.log("DEBUG: the range = "+range.getA1Notation());

      // sort by Column C, ascending
      sortRange.sort({column: 3, ascending: true});

          }
          else{
            // Logic#2 - delete the row
            // Logger.log("DEBUG: delete the row");
            targetsheet.deleteRow(editRS);
          }      
      }
      else
      {
        //not the right sheet, not the column, not the right row
        // Logger.log("DEBUG: do nothing - not matched");    
      }
    }