Office Script to replace value in column

[joelgw054]

Hi. I'm trying to create an Excel Office Script that will take the current value in a cell, append a letter to the beginning of the cell and then replace the value in the cell with the new concatenated value. I have a column in my sheet holding these values and I'd like to replace all of the values in that column. Importantly, I don't want the script to replace the value if the cell is blank.

Example Column:

ID

123

456

789

(blank cell)

Desired End Result:

ID

A123

A456

A789

(blank cell)

Thanks in advance for any suggestions!

[#2]

@joelgw054 

You can use the following Office Script in Excel to achieve the desired result of appending a letter to the beginning of each non-blank value in a column:

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 range = sheet.getRange("A2:A5");
  
  // 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);
}

This script assumes that the data is in the first column (column “A”) and starts from the second row (row 2). You can adjust the range variable to match the location of your data.

[#3]

@NikolinoDE thanks so much! So the number of rows in my worksheet changes each time the script is run. It could be 20 rows today and 40 (or zero) tomorrow. I defined a table in my script that captures the used range. Is there a way I could use that, or some other means, to determine the range I need to cycle over instead of hard-coding the range values? Thanks again for your assistance!

[#4]

@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 :))

[#5 · Accepted solution]

@NikolinoDE Thanks. I got an error on line 9 that the sheet object does not have a getRangeByColumn method. I was able to modify the code you provided to get it to work, though. Thanks again.

  let columnNumber = 1; 

  // Get the last row in the column
   let lastRowCount = sheet.getTable("ActiveTable").getRowCount()
 if (lastRowCount>0) {

  // Adjust the range to include all rows in the column
  let range = sheet.getRangeByIndexes(1,1, lastRowCount, 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);
 }

[#6]

I am pleased that you have come to a solution to your project.
I wish you continued success with Excel!

[#7]

Thanks - and thanks again for your assistance!

[#8]

@joelgw054 

As a comment, it's not necessary to count the rows

function main(workbook: ExcelScript.Workbook) {

  const columnRange =
     workbook.getActiveWorksheet()
    .getTable("Table1")
    .getColumnByName("ID")
    .getRangeBetweenHeaderAndTotal()
    
  columnRange.setValues(
    columnRange.getValues()
      .map(x => Array.of(x[0] !== "" ? "A" + x : ""))
  )
}