Share solution? Find and replace text in a Sheet (including inside formulas)

NeilKY

I’m trying to build a solution that can search for a specific text string in a Smartsheet — and replace it with another value — across all cells in the sheet, including inside formulas.

I'm trying to find a way to "Replace text including in formulas" since this feature is not available within Smartsheet. I've been told can be accomplished using the API? Maybe Bridge?

I would define the “Search for” and “Replace with” values in one sheet, two different cells. Then search for and replace with those values in a target sheet, updating any cell where that text appears.

Has anyone found a way to do this, either using Javascript + Bridge, or something else? (That would be willing to share step by step how they did it? (or the code)

prime_nathaniel

@NeilKY when you say within a formula do you mean inside the formula definition or within the results of the formula? It is possible to do this with the API but the approach is going to be a bit different depending on what you mean here. Let's assume you mean within the formula definition as that is more likely the case,

1. Get sheet (required via bridge, option via straight api)
2. Get all columns (can get direct from api, otherwise it is part of get sheet)
3. Iterate through all columns
4. for each column create a column update json object and put them into an array of json objects for update
5. process all column updates via the column update API, now all your formulas are done

you can use 1-5 to also rename anything in a column header or description if that is text you also want to change.

Now to do cells,
1. get sheet
2. get rows
3. interate through all rows
4. iterate through all columns
5. prepare JSON update object for each row (needs id, column id, and value)
6. process all row updates via the update row API

prime_nathaniel

@NeilKY when you say within a formula do you mean inside the formula definition or within the results of the formula? It is possible to do this with the API but the approach is going to be a bit different depending on what you mean here. Let's assume you mean within the formula definition as that is more likely the case,

1. Get sheet (required via bridge, option via straight api)
2. Get all columns (can get direct from api, otherwise it is part of get sheet)
3. Iterate through all columns
4. for each column create a column update json object and put them into an array of json objects for update
5. process all column updates via the column update API, now all your formulas are done

you can use 1-5 to also rename anything in a column header or description if that is text you also want to change.

Now to do cells,
1. get sheet
2. get rows
3. interate through all rows
4. iterate through all columns
5. prepare JSON update object for each row (needs id, column id, and value)
6. process all row updates via the update row API

NeilKY

https://community.smartsheet.com/discussion/comment/483389#Comment_483389

Yes, change inside the formula definition. =IF(1=1,"Neil is cool","") would become =IF(1=1,"Nathaniel is cool",""), for example.

That is a very great and well thought-out example of how to do it! THANK YOU!

Would it be possible for you to give a few more details? I'm still new to APIs and Bridge so any help you can offer would be much appreciated. If not, I'll mark your post as the answer and go from there! Thank you again!