Table Entry Line

[mtarler]

So I have suggested that excel add an 'ENTRY LINE' to tables.  See that suggestion here:

https://feedbackportal.microsoft.com/feedback/idea/ae0a8991-71fa-ed11-a81c-000d3ae5b6f4

I image it would look something like this (EDIT: updated image):

So you can enter data at the top and hit enter and it would be added to the table without scrolling to the bottom.

Well, in the mean time, I made the script and that button is fully functional and because it is a script it will work Both online and in the app.  See attached file.

Please let me know you LIKE it with a thumbs up or better yet leave a comment to let me know if you tried it out.  Also welcoming feedback and improvements.  I'm already considering to expand this to cycle through all tables on the sheet.  Here is the present script (updated script):

/**
 * This script inserts the row above a table into the table.
 */
function main(workbook: ExcelScript.Workbook) {
  let wksht = workbook.getActiveWorksheet();
  //Unlock worksheet
  let wkshtProtect = wksht.getProtection().getProtected();
  if (wkshtProtect) {wksht.getProtection().unprotect(); };

  //find active cell and 3x3 around that cell as 'activeBubble' to find correct table
  let activeCell = workbook.getActiveCell();
  let aMinRow = activeCell.getRowIndex() - 1;
  if (aMinRow < 0) { aMinRow = 0; };
  let aMinCol = activeCell.getColumnIndex() - 1;
  if (aMinCol < 0) { aMinCol = 0; };
  let activeBubble = wksht.getRangeByIndexes(aMinRow, aMinCol, 3, 3).getAddress();

  //Check each table if row above overlaps activeBubble
  let tCount = wksht.getTables().length;
  let foundTable = 0;
//  let tbl = wksht.getTables().shift()  
  wksht.getTables().forEach( (tbl) => {
    foundTable= Math.abs(foundTable); 
    let newData = tbl.getHeaderRowRange().getOffsetRange(-1, 0);
    if (wksht.getRange(newData.getAddress()).getIntersection(activeBubble)) {
      //let remFormat = newData.getFormat();
      //tbl.addRows(-1, newData.getValues());
      tbl.addRow();
      let newRow = tbl.getRange().getLastRow();   //.getTotalRowRange().getLastRow();
      
      let n = newData.getColumnCount();

      for (let i = 0; i< n; i++) { 
        //selectively inserts data from input line (i.e. do not overwrite table formulas)
        if ( !newRow.getCell(0, i).getFormula().toString().startsWith("=") ) {
          newRow.getCell(0, i).copyFrom(newData.getCell(0, i), ExcelScript.RangeCopyType.values); 
        };
        //selectively clear input line (i.e. do not clear formulas)
        if ( !newData.getCell(0, i).getFormula().toString().startsWith("=") ) {
          newData.getCell(0, i).setValue(""); //.clear
        };
      };
      //put cursor back on 'start' of input line
      newData.getCell(0,0).select();
    } else {foundTable++};
  });
  //console.log("tCount=" + tCount + " foundTable=" + foundTable);
  if (tCount == foundTable){
    //console.log("no table found next to last active cell.");
    //return;
    throw 'no table found next to last active cell'; 
  }
  //TEST CODE SECTION - IGNORE
  
  
  //if it was protected, re-protect
  if (wkshtProtect) {wksht.getProtection().protect();};
}

[#2]

@mtarler 

I voted for your suggestion.

[#3]

@mtarler 

Idea is interesting, but the script is unshared. Could you add it to the post or share by other way?

[#4]

@mtarler 

Okay, I found it on the feedback. Will play a bit and vote.

[#5]

I thought you could/would find it in the file but good point I added it here too.  I'm still learning what is shared in the workbook itself vs. lives online

[#6]

@mtarler 

it's shared through your OneDrive permissions, not with the file.

I upvoted and added few comments.

[#7]

I think here would be better place for discussion and would love to improve the script. This script is ideally only a temp solution as I hope they add something native. That said I'm pasting your comments here:
~~~~~~~~~~~~~~~~~~
Idea is good. However
- shift() method gives us first created in that worksheet table, better to work with concrete table
- that doesn't work on filtered table
- it's better to keep fields in added row where the formula is expected blank and expand formula in added row as it is in related column. Perhaps even if not blank it's better to give priority for the fromula in table column
~~~~~~~~~~~~~~~~~~

as for shift(), i was having trouble implementing an Index and trying to make it general (i.e. you don't have to edit the script for every page so right now this is limited to or at least best if only 1 table is on the sheet. I am open to other suggestions. I was also considering looping through all tables on the sheet but that could also have issues. Maybe a combination of looping all tables and then checking the potential entry line for a particular formatting or something to 'mark' it as an entry line.

I will have to test it on a filtered table. not sure why it doesn't work but will see if I can find a work around

I hear what you are saying about the formula. this was created in a specific application I'm using it for and in that application it is actually better to have the formula on the input and the value in the table because it was looking up the highest previous/existing value already entered in the table. Although still possible in the table it is 'excessive' and once entered it is good to be locked as a value instead of a formula. that said, to do the other will also be more complicated as I think it will have to be done cell by cell instead of the whole row. Maybe a combination is possible using the 'format' idea above that cells with the right format are copied and other are not (i.e. default formula in table should auto-populate and not be over-written).

[#8]

@mtarler 

shift(). I don't like an idea to loop all tables in active worksheet. Perhaps it's better getTable("TableName") but all depends on scenario. If to loop when at least ignore empty row under the each table.

filtering. Didn't play with the script itself and don't know what the solution is. But believe it exists.

formula. Didn't test but I guess now we break auto-fill of the table formula. Perhaps we may check any row in the table, if formula exists in the column when push it, otherwise value from the upper row. Yes, that's cell by cell but it shall be not critical from the performance point of view, we work only with one row. Recursion allows not care about number of cells.

In general that's interesting task, thank you for initiating it. Will try to play with script as well when have bit more time. If any results will share.

[#9]

@mtarler 

I modified a bit for the single table not to add empty row and to skip cells if the table has formula in related column

/**
 * This script inserts the row above a table into the table.
 */
function main(workbook: ExcelScript.Workbook) {
  let wksht = workbook.getActiveWorksheet()
  let tbl = wksht.getTable("Table1")

  const newData = tbl
    .getHeaderRowRange()
    .getOffsetRange(-1, 0)

  if ( !newData
        .getValueTypes()
        .every(v => v[0] == ExcelScript.RangeValueType.empty) ) {

    tbl.addRow()
    const newRow = tbl
      .getRangeBetweenHeaderAndTotal()
      .getLastRow()
    const n = newRow.getColumnCount()
    const content = newRow.getFormulas()
    for (let i = 0; i < n; i++) {
      if( !content[0][i].toString().startsWith("=") ) {
        newRow
          .getCell(0, i)
          .copyFrom( newData.getCell(0, i) )
      }
    }
    newData.clear(ExcelScript.ClearApplyTo.contents)
  }
}

[#10]

@SergeiBaklan I've updated my script to:

a) find and only apply to a table next to the active cell

b) not overwrite table formulas (and still copy value only if formula in the insert line)

c) still do not delete formula from insert line

d) throw error (i.e. comment in log) if active cell is not next to a table

e) after entering data and clearing input line, set cursor to 1 cell on that line

I also created 2 'companion' scripts: 1 to clear the input line (i.e. change your mind and don't want that data) and 1 to use the input line as a filter for the table (actually need to finish this script)

Here is the new look:

and the updated code:

/**
 * This script inserts the row above a table into the table.
 */
function main(workbook: ExcelScript.Workbook) {
  let wksht = workbook.getActiveWorksheet();
  //Unlock worksheet
  let wkshtProtect = wksht.getProtection().getProtected();
  if (wkshtProtect) {wksht.getProtection().unprotect(); };

  //find active cell and 3x3 around that cell as 'activeBubble' to find correct table
  let activeCell = workbook.getActiveCell();
  let aMinRow = activeCell.getRowIndex() - 1;
  if (aMinRow < 0) { aMinRow = 0; };
  let aMinCol = activeCell.getColumnIndex() - 1;
  if (aMinCol < 0) { aMinCol = 0; };
  let activeBubble = wksht.getRangeByIndexes(aMinRow, aMinCol, 3, 3).getAddress();

  //Check each table if row above overlaps activeBubble
  let tCount = wksht.getTables().length;
  let foundTable = 0;
//  let tbl = wksht.getTables().shift()  
  wksht.getTables().forEach( (tbl) => {
    foundTable= Math.abs(foundTable); 
    let newData = tbl.getHeaderRowRange().getOffsetRange(-1, 0);
    if (wksht.getRange(newData.getAddress()).getIntersection(activeBubble)) {
      //let remFormat = newData.getFormat();
      //tbl.addRows(-1, newData.getValues());
      tbl.addRow();
      let newRow = tbl.getRange().getLastRow();   //.getTotalRowRange().getLastRow();
      
      let n = newData.getColumnCount();

      for (let i = 0; i< n; i++) { 
        //selectively inserts data from input line (i.e. do not overwrite table formulas)
        if ( !newRow.getCell(0, i).getFormula().toString().startsWith("=") ) {
          newRow.getCell(0, i).copyFrom(newData.getCell(0, i), ExcelScript.RangeCopyType.values); 
        };
        //selectively clear input line (i.e. do not clear formulas)
        if ( !newData.getCell(0, i).getFormula().toString().startsWith("=") ) {
          newData.getCell(0, i).setValue(""); //.clear
        };
      };
      //put cursor back on 'start' of input line
      newData.getCell(0,0).select();
    } else {foundTable++};
  });
  //console.log("tCount=" + tCount + " foundTable=" + foundTable);
  if (tCount == foundTable){
    //console.log("no table found next to last active cell.");
    //return;
    throw 'no table found next to last active cell'; 
  }
  //TEST CODE SECTION - IGNORE
  
  
  //if it was protected, re-protect
  if (wkshtProtect) {wksht.getProtection().protect();};
}

[#11]

@mtarler 

Looks great. That's interesting idea with table near the active cell. As variant for bubble area it could be

    const activeBubble = activeCell
        .getResizedRange(2, 2)
        .getOffsetRange(
            (activeCell.getRowIndex() == 0) ? 0 : -1,
            (activeCell.getColumnIndex() == 0) ? 0 : -1
        )
        .getAddress()