I have been using Google Sheets for my work
Recently, I came across a find and replace function that I found here. This function works perfectly when the array is generated from a single column of data.
However, there are certain cases where I need to use this function with all my data stored in an array that has been passed from previous processing. The formatted data looks like this when printed on the page:
| Id | Segment | Other Column | etc..
|----|--------------------|--------------|
| 1 | AAA AA|AA|CaaL AA | stuff |
| 2 | AAA-AA|AA|CaaL | stuff |
| 3 | AAA, AA|AA|AA | AA |
| 4 | AA | stuff |
| 5 | AA AA | |
| 6 | AA, AA | AA |
| 7 | | stuff |
| 8 | CaaL | stuff |
| 9 | AA | stuff |
My goal is to only replace data in the Segment column:
| Id | Segment || Other Column | etc..
|----|-------------------|---------------|
| 1 | AAA AA|zz|CaaL AA | stuff |
| 2 | AAA-AA|zz|Bob | stuff |
| 3 | AAA, AA|zz|zz | AA |
| 4 | zz | stuff |
| 5 | AA AA | |
| 6 | AA, AA | AA |
| 7 | | stuff |
| 8 | Bob | stuff |
| 9 | zz | stuff |
The issue I face is that the AA in the Other Column (or any other column) gets replaced inadvertently, which is not desired.
Is there a way to restrict the findReplace function to update values only in a specific column within the values?
I tried to target the desired column using the following script:
function getColumn(matrix, col, startrow){
var column = [];
for(var i=startrow; i<matrix.length; i++){
column.push(matrix[i][col]);
}
return column;
}
and then attempted to isolate the input to this column:
function fr(input) {
const aCOL = getColumn(values, 2, 0)
return input.aCOL.map(c => c.replace(regex, (x) => map[x]));
AND
return input.map(c => c.aCOL.map(y => y.replace(regex, (x) => map[x])));
}
Unfortunately, I encountered an error saying
Cannot read property 'map' of undefinedThank you for any assistance provided.
function findReplace(values) {
var search_for = ["AA", "Caal"];
var replace_with = ["zz", "yy"];
var map = {};
search_for.forEach(function(item, i) {
map[item] = replace_with[i];
});
//map = { AA: 'zz', Caal: 'yy' };
const regex = new RegExp("(?<![^|])(?:" + search_for.join("|") + ")(?![^|])", "g");
range.setValues(values.map(fr));
function fr(input) {
return input.map(c => c.replace(regex, (x) => map[x]));
}
}