Office Script to replace value in column

joelgw054 

Here's a try with this modified code while keeping the original code:

function main(workbook: ExcelScript.Workbook) {
  // Get the current worksheet
  let sheet = workbook.getActiveWorksheet();
  
  // Set the range to the column of data you want to modify
  let columnNumber = 1; // Column number (A=1, B=2, etc.)
  
  // Get the last row in the column
  let lastRow = sheet.getRangeByColumn(columnNumber).getUsedRange().getLastRow();
  
  // Adjust the range to include all rows in the column
  let range = sheet.getRangeByIndexes(2, columnNumber, lastRow - 1, 1);
  
  // Get the values from the range
  let values = range.getValues();
  
  // Loop through each row in the range
  for (let i = 0; i < values.length; i++) {
    // Check if the cell is not blank
    if (values[i][0] !== "") {
      // Append "A" to the beginning of the value
      values[i][0] = "A" + values[i][0];
    }
  }
  
  // Set the new values back to the range
  range.setValues(values);
}

In this modification, the getRangeByColumn() method is used to get the range for the specified column. Then, the getUsedRange() method is applied to the column range to determine the last row in that column. After that, the range is adjusted based on the number of rows.

With this modification, the existing code is largely preserved, and only the part that determines the range is adjusted. This allows the range to be dynamic based on the actual number of rows in the column.

Code is untested, hope it works and helps :))