Update Single Column from an Array to another Array

[Walter Wong]

Hi 

Below is a section of app script code.

I'm unable to share access to the file due to confidentiality.

The objective of the code is to update ID Numbers in "Shipment" column of Buy sheet.

All values in "Shipment" column are formatted as richtextvalues with hyperlinks.

The function of the code is simply to replace the display richtext value of the cells in the column with new_ID when the current value matches old_ID.

/*** Update Shipment ID in Buy sheet ***/

let shBuy = e.source.getSheetByName(sheet_Buy);

/*** Get data from Buy Sheet ***/

let rDataBY = shBuy.getDataRange();

let vDataBY = rDataBY.getValues();

let rtDataBY = rDataBY.getRichTextValues();

Logger.log("rtDataBY is " + rtDataBY);

/*** Get Target Column from Buy Sheet header ***/

let colBY_ID = vDataBY[0].indexOf("Shipment");

/*** Iterate through rows and modify only the target column ***/

rtDataBY.forEach(row => {

let rtValue = row[colBY_ID];

let rtText = rtValue.getText();

if (rtText.includes(oId_ID)) {

// Create a builder from the existing rich text to preserve formatting

let updatedText = rtText.replace(oId_ID, new_ID);

Logger.log("updatedText is " + updatedText);

row[colBY_ID] = rtValue.copy().setText(updatedText).build();

}

});

// Code works as it should up to this point

// Write the updated 2D array back to the sheet

// The issue here is the following line overwrites the entire rDataBY range and corrupts other values in the range

// I want to update the rich text values in the target column of rDataBY, not the entire range

rDataBY.setRichTextValues(rtDataBY);