Forum Discussion
Table Entry Line
~~~~~~~~~~~~~~~~~~
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).
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)
}
}
- mtarlerSep 11, 2023Silver Contributor
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();}; }
- SergeiBaklanSep 13, 2023Diamond Contributor
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()