Forum Discussion
Office Script to replace value in column
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!
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); }
7 Replies
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.
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!
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 :))
