I'm using this script but it's replacing every instance of a T, A, etc. How do I get it to only replace an exact match? Only if it's the letter T and nothing else.
function runReplaceInSheet(){
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Underlevel");
// get the current data range values as an array
// Fewer calls to access the sheet -> lower overhead
var values = sheet.getDataRange().getValues();
// Replace
replaceInSheet(values, "/^T$/", '=image("https://i.imgur.com/Dxl893F.png")');
replaceInSheet(values, 'A', '=image("https://i.imgur.com/omc7F9l.png")');
replaceInSheet(values, 'R', '=image("https://i.imgur.com/12ZmSp3.png")');
replaceInSheet(values, 'M', '=image("https://i.imgur.com/kh7RqBD.png")');
replaceInSheet(values, 'H', '=image("https://i.imgur.com/u0O7fsS.png")');
replaceInSheet(values, 'F', '=image("https://i.imgur.com/Hbs3TuP.png")');
// Write all updated values to the sheet, at once
sheet.getDataRange().setValues(values);
}
function replaceInSheet(values, to_replace, replace_with) {
//loop over the rows in the array
for(var row in values){
//use Array.map to execute a replace call on each of the cells in the row.
var replaced_values = values[row].map(function(original_value) {
return original_value.toString().replace(to_replace,replace_with);
});
//replace the original row values with the replaced values
values[row] = replaced_values;
}
}
Thank you :D
String#replace()
and expecting a regex type execution. "/^T$/"
will be interpreted as a string literal that starts with /
, contains ^
,T
, and $
and ends with /
."
./^T$/ //or new RegExp('^T$')
You can also just use .replace()
with a replacer function directly.
var range = sheet.getDataRange();
var replaceObj = {
//to_replace: imgur id
T: 'Dxl893F',
A: 'omc7F9l',
};
var regex = new RegExp('^(' + Object.keys(replaceObj).join('|') + ')$', 'g');// /^(T|A)$/
function replacer(match) {
return '=image("https://i.imgur.com/' + replaceObj[match] + '.png")';
}
range.setValues(
range.getValues().map(function(row) {
return row.map(function(original_value) {
return original_value.toString().replace(regex, replacer);
});
})
);
Thank you :D Both solutions work, but the problem I'm running into now is that when more than one string is entered it deletes the previous. So I type in a T and an R, for instance, it'll find and replace the T then delete it and replace the R. Is this a Google Sheets thing or the code?
@Ryan
T
andR
in a single cell? LikeTR
and it outputsR
?No, sorry, different cells. It will replace the T, delete it, and then replace the different cell with the R in it. I've been toying with it, putting random T's, A's, R's all over the sheet and in groups. One at a time as well as inputting several. All previously replaced ones disappear and reappear as new strings are replaced. But then they won't reappear. Even waiting between doing a new cell, it'll still delete the previous ones. Hopefully this is making some sense! @TheMaster
I suppose I should note, the intent was to use this script more than once on the same sheet. Not sure if that changes anything or not. Probably worth mentioning, though, some of the scripts I saw for Find and Replace were meant to be used once it seemed like. @TheMaster
Previously replaced formulas
=image()
are deleted because when you getValue(), the value is empty in a cell with image formula. Your value array should be a mix of formulas and values See here on how to do that. If you have issues, ask a new question with enough details.