Search code examples
exceloffice-scripts

Select next none empty cell in a column - Office Scripts Excel

I have a list of IDs that I need 76 blank cells between. I figured out how to get the blank cells but how do I get to select the next ID and do the same thing over and over? It's going to take me hours to just select the next ID and run the blank cells script....complete noob and self taught over here...

    function main(workbook: ExcelScript.Workbook) {
    const selectedSheet = workbook.getActiveWorksheet();
    const selectedRange = workbook.getSelectedRange();
    if (selectedRange == null) {
        console.log(`No cells in worksheet selected.`);
        return;
    }
    if (selectedRange.getCellCount() != 1) {
        console.log(`More than one cell selected`);
        return;
    }
    // console.log(`Selected range for the worksheet: " ${selectedRange.getAddress()}`);
    // Insert a row below the selected cell
    const targetRow = selectedRange.getOffsetRange(1, 0).getEntireRow();
    targetRow.insert(ExcelScript.InsertShiftDirection.down); 
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down);
    targetRow.insert(ExcelScript.InsertShiftDirection.down)}
Solution

  • Loop through name list in reverse order and insert blank cells (rows).

    function main(workbook: ExcelScript.Workbook) {
  const selectedSheet = workbook.getActiveWorksheet();
  const selectedRange = workbook.getSelectedRange().getIntersection(selectedSheet.getUsedRange())
  //  Set the quantity of blank cells
  const cntInsertRow = 76
  //  Selection is blank
  if (!selectedRange) {
    console.log("No cells in worksheet selected.");
    return;
  }
  //  Selection has more than one column
  if (selectedRange.getColumnCount() > 1) {
    console.log("Select name list in a column.");
    return;
  }
  console.log(`Selected range for the worksheet: ${selectedRange.getAddress()}`);
  let cellCount = selectedRange.getRowCount();
  //  Loop through name list in reverse order
  for (let cellIdx = cellCount - 1; cellIdx > -1; cellIdx--) {
    let cellRange = selectedRange.getRow(cellIdx).getOffsetRange(1, 0);
    //  Insert rows if cell is NOT blank
    if (selectedRange.getRow(cellIdx).getText().toString().length > 0) {
      //  Insert rows
      cellRange.getAbsoluteResizedRange(cntInsertRow, 1).getEntireRow().insert(ExcelScript.InsertShiftDirection.down)
    }
  }
}

    enter image description here