Excel Script, input data in blank column/ rows

Question

I have this problem in my excel script formula. It is working fine.

The only problem is that I need to input starting row... And if for example there is data or the column/row is not empty, it will overwrite the existing data.

Here is the excel script.

jkpieterse · Answer

MadPrince&nbsp;Not the prettiest solution, but perhaps something like this?https://jkp-ads.com/articles/excel-office-script-examples.asp#Prompting_for_user_input&nbsp;

sergeibaklan · Answer

MadPrince&nbsp;
As variant
    const startRow = sheet
        .getUsedRange()
        .getLastRow()
        .getRowIndex() + 1

madprince · Answer

SergeiBaklan&nbsp;This did the trick.. thank you.

sergeibaklan · Answer

MadPrince&nbsp;, you are welcome.
&nbsp;
In attached file I tried to imitate entire script
function main(workbook: ExcelScript.Workbook) {

const arrayvar = workbook
        .getTable("Table1")
        .getRangeBetweenHeaderAndTotal()
        .getValues()

const sheet = workbook.getActiveWorksheet()
    const rangeToFill = sheet
        .getCell(
            sheet
            .getUsedRange()
            .getLastRow()
            .getRowIndex() + 1, 0)
        .getResizedRange(arrayvar.length, arrayvar[0].length - 1)

for ( let i = 0; i &lt; arrayvar.length; i++) {
        rangeToFill
            .getRow(i)
            .setValues( Array.of( arrayvar[i]) )
    }
}
&nbsp;

sergeibaklan · Answer

MadPrince&nbsp;
Or
function main(workbook: ExcelScript.Workbook) {

const arrayvar = workbook
    .getTable("Table1")
    .getRangeBetweenHeaderAndTotal()
    .getValues()

const sheet = workbook.getActiveWorksheet()
  let rangeToFill: ExcelScript.Range = sheet
    .getCell(
      sheet
        .getUsedRange()
        .getLastRow()
        .getRowIndex(), 0)
    .getResizedRange(0, arrayvar[0].length - 1)

arrayvar.map(x =&gt; {
    rangeToFill = rangeToFill.getRowsBelow(1);
    rangeToFill.setValues(Array.of(x))
  } )

}

Forum Discussion

Excel Script, input data in blank column/ rows

5 Replies

Resources