Edit
Entered different characters for string to void confusion with row numbers
I have a information written in some cells:
Column A | Column B
Scrambled Egg with Toast x2 (Butter) | Strawberry Smoothie x3 (Cream)
Banana Cake with Icing and Sprinkles x3 (Oven 180c) | Scrambled Egg with Toast x1 (Butter)
Strawberry Smoothie x3 (Cream) | Ice Cream and Waffles x3 (Freezer)
Ice Cream and Waffles x3 (Freezer) | Banana Cake with Icing and Sprinkles x3 (oven 180c)
I would like to some help to write a script that searches for (part) string in the columns above and replace the whole cell.
Finding: "Scrambled Egg" | replacing entire cell with: "SE+T"
Finding: "Banana Cake" | replacing entire cell with: "BC+IS"
Finding: "Strawberry Smoothie" | replacing entire cell with: "SS"
Finding: "Ice Cream" | replacing entire cell with: "IC+W"
Resulting in:
Before Search and Replace
Column A | Column B
Scrambled Egg with Toast x2 (Butter) | Strawberry Smoothie x3 (Cream)
Banana Cake with Icing and Sprinkles x3 (Oven 180c) | Scrambled Egg with Toast x1 (Butter)
Strawberry Smoothie x3 (Cream) | Ice Cream and Waffles x3 (Freezer)
Ice Cream and Waffles x3 (Freezer) | Banana Cake with Icing and Sprinkles x3 (oven 180c)
After Search and Replace
Column A | Column B
SE+T | SS
BC+IS | SE+T
SS | IC+W
IC+W | BC+IS
.
.
I've looked around for various 'Find and Replace' solutions and ways to simplify the search and the answers found from this question seemed to make sense however one returned only the first search:
function findReplace() {
const ui=SpreadsheetApp.getUi();
const ss=SpreadsheetApp.getActiveSpreadsheet();
const srchA=['Scrambled Egg','Banana Cake','Strawberry Smoothie'];
const vA=['SE+T','BC+IS','SS'];
const sh=ss.getActiveSheet();
const rg=sh.getRange(2,2,sh.getLastRow()-1,sh.getLastColumn()-1);
const v=rg.getValues();
v.forEach(function(r,i){
r.forEach(function(c,j){
srchA.forEach(function(s,k){
if(v[i][j].toString().includes(s,k)) {
sh.getRange(i+2,j+2).setValue(vA[k]);
}
});
});
});
}
and the other didn't return any result.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var searchRange = sheet.getDataRange();
var lastColumn = searchRange.getLastColumn();
var lastRow = searchRange.getLastRow();
function findReplace() {
var rangeValues = searchRange.getValues();
for ( i = 0; i < lastColumn; i++){
for ( j = 0 ; j < lastRow; j++){
var value = rangeValues[j][i];
if(typeof value != "string") { continue; }
if(value.indexOf("Scrambled Egg") > -1){
var newValue = value.replace("Scrambled Egg", "SE+T")
sheet.getRange(j+1,i+1).setValue(newValue);
}
else if (value.indexOf("Banana Cake") > -1){
var newValue = value.replace("Banana Cake", "BC+IS")
sheet.getRange(j+1,i+1).setValue(newValue);
}
else if (value.indexOf("Strawberry Smoothie") > -1){
var newValue = value.replace("Strawberry Smoothie", "SS")
sheet.getRange(j+1,i+1).setValue(newValue);
};
};
};
};
"Four"is what would change the cell to "Yellow". Will try to use different characters to make it more readable. Thanks