Best answer

Efficient method for find/replace

  • 4 November 2019
  • 5 replies
  • 420 views

Userlevel 7
Badge +10

So, let's say you've got an input message:

"Zapier is the best company in the world. They'll probably be bigger than Apple one day"

Now say you've got a Google Sheet table as such:

Find | Replace

====================

1 | Apple | @Apple

2 | Bentley | @Bentley

3 | We Work | @WeWork

4 | Zapier | @Zapier

=====================

We want to compare the input message with this find/replace table and make swaps where appropriate.

In case of the example, the output would be:

"@Zapier is the best company in the world. They'll probably be bigger than @Apple one day".

The problem - how do I do this without massive task usage?

Breaking the input message into single words and comparing with the table is task-intensive and won't handle "Find" values of more than a single word.

Looping every row on the Google Sheet and comparing to the input message is also task-intensive (in real life, the sheet has 130+ rows and growing daily).

Anyone got any smart ideas here?

Thanks!


icon

Best answer by ikbelkirasan 5 November 2019, 02:46

Alright, I could do it with a single task (Seriously :D).

Basically I'm using two sheets, one for the words to find and their replacements. The other one is where you add inputs and get the desired output.

I offloaded the computation to Google Sheets by using a custom script I wrote that you can find below.

This works by adding a new row to the second sheet, the input is the original string and the output is a formula that executes the script.


image.png


image.png

image.pngimage.png

image.png


View original

5 replies

Userlevel 7
Badge +11

Alright, I could do it with a single task (Seriously :D).

Basically I'm using two sheets, one for the words to find and their replacements. The other one is where you add inputs and get the desired output.

I offloaded the computation to Google Sheets by using a custom script I wrote that you can find below.

This works by adding a new row to the second sheet, the input is the original string and the output is a formula that executes the script.


image.png


image.png

image.pngimage.png

image.png


Userlevel 7
Badge +10

@ikbelkirasan - this is perfect! Thanks so much for this.

Could you possibly paste the code in text form here?

Thanks!


Userlevel 7
Badge +11

You're welcome! Here's the code.


function arrayToMap(array) {

return array.reduce(function(target, current) {

const key = current[0]

const value = current[1]

target[key] = value

return target

}, {})

}

function myFunction(input) {

var rows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getDataRange().getValues();

rows = rows.filter(function(row, index){

return index > 0

});

const map = arrayToMap(rows)

const words = String(input).split(' ')

const results = words.map(function(word) {

const replacement = map[word]

if (replacement) {

return replacement

}

return word

})

const output = results.join(' ')

return output

}



Userlevel 7
Badge +11

And this is the formula:

=myFunction(INDIRECT(ADDRESS(ROW(),COLUMN()-1)))


Userlevel 7
Badge +10

Thanks again @ikbelkirasan - this is a great help!


Reply