I’m working with a Google Sheets document that has a column containing mixed data. Each cell has a person’s name followed by unwanted text that starts with a specific marker.
Example data:
john doe marker× finance×
mary clark marker× marketing×
steve brown marker× support×
lisa green marker× admin×
I need to clean this up so only the names remain. In Excel I used to handle this by selecting the range and using Find & Replace with "marker×" as the search term and empty string as replacement*.
Now I’m trying to do the same thing in Google Sheets using Apps Script. I thought about using replaceText() method but it seems like that’s only for Google Docs, not Spreadsheets. I’ve been looking through the SpreadsheetApp documentation but haven’t found a similar method.
Is there a built-in function in Google Sheets API that can handle this pattern matching and removal? Or do I need to write a custom solution to process each cell individually?
I had the same issue cleaning up employee data exports. Apps Script doesn’t have Excel’s Find & Replace with wildcards, so you’ll have to process each cell separately. I used regular expressions with the replace() method - worked great. Loop through your range, grab each cell value as a string, then use cellValue.replace(/marker×.*$/g, '').trim() to strip everything from “marker×” to the end. The .*$ in the regex matches any characters until the line ends. Clean each value, then write it back with setValue(). More work than Excel but you get way better control over the whole process.
Google Sheets has a built-in Find & Replace that works with wildcards, just like Excel. Go to Edit > Find and replace, check “Search using regular expressions,” and use marker×.* with an empty replacement. But if you want to automate this with Apps Script, you’re right - there’s no direct replaceText() equivalent. I’ve done similar cleanup tasks and found the best approach is using getValues() to grab the whole range as an array, then process it with JavaScript string methods. Something like data[i][j] = data[i][j].split('marker×')[0].trim() for each cell. Way more efficient than hitting the API for every single cell.
Exactly! Getting the whole range is crucial. I do the same thing - grab all values and split on ‘marker×’. Way easier handling batches than going one by one. setValues() is super efficient for this!