Forum Discussion

Copper Contributor

May 18, 2023

Solved

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 concatenat...

excel

Office Scripts

joelgw054

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);
 }

joelgw054

Copper Contributor

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);
 }

NikolinoDE

Gold Contributor

May 24, 2023

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

joelgw054

Copper Contributor

May 24, 2023

Thanks - and thanks again for your assistance!

SergeiBaklan

Diamond Contributor

May 24, 2023

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 : ""))
  )
}