Forum Discussion
Office Script to replace value in column
- May 23, 2023
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); }
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 :))
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);
}
- NikolinoDEMay 24, 2023Gold ContributorI am pleased that you have come to a solution to your project.
I wish you continued success with Excel!- joelgw054May 24, 2023Copper ContributorThanks - and thanks again for your assistance!
- SergeiBaklanMay 24, 2023Diamond Contributor
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 : "")) ) }