Bridge Question: How do I Add a "^" as the First Character in All Cells in Sheet

NeilKY

I'm trying to use Bridge to add a ^ in front of all text in every cell in a sheet.

If it works, it'll allow me to turn sheet formulas into text that can then be treated as text, which Smartsheet can actually back-up. I would likely schedule the bridge flow to run shortly before the scheduled Sheet backup. Then I would reverse engineer the flow to remove the ^ shortly thereafter.

Does anyone have a similar solution already built that they wouldn't mind sharing with me? Or can you explain to me how this could be done?

If so, please please dumb down the explanation as much as you can tolerate because I'm new to both API's and Bridge :)

THANK YOU!

Paul Newcome

I have been working on something similar for different reasons. Do you need to be concerned with different formulas within a single column such as a metrics sheet with different sections, or are you able to assume that all formulas will be column formulas or at the very least in a specific row?

NeilKY

https://community.smartsheet.com/discussion/comment/478146#Comment_478146

I have to assume that there must be some cell formulas that are not column formulas. The flow could apply to ALL cells in a sheet if that helps.

Paul Newcome

So this is entirely untested, but it is a theory.

Get the sheet then use a JavaScript module that can be passed the array of cell data from the entire sheet. That script would search for any object that has a "formula" property. It will replace the "value" property with a string that consists of "^" followed by the string in the "formula" property. If within that same JS module it converts that array into a csv following the proper format, you can use an API module to then import the csv output by the JS module as a new sheet which would be your back-up.

Doing it this way instead of changing the data within the sheet itself should also help with any cross sheet formulas, reports, etc. that may be referencing the cell data within the sheet.

The proper format for the csv can be found by (temporarily) using an API module to export the sheet as a csv file.

You can go to ChatGPT or similar, feed it a few starter prompts to make sure it understands context and constraints.

"You are an expert in Smartsheet API. You are an expert in Javascript. You are an expert on the Smartsheet Bridge Platform. You understand that a JavaScript module in the Smartsheet Bridge platform strictly forbids the calling of API's within the javascript code, strictly forbids the access of the Bridge workflow runtime data, strictly forbids the user of external libraries, and that parameters will always be passed directly into the javascript module."

The above was given to me by someone who very very frequently uses things like ChatGPT, and the couple of times I have used it for more complex JS codes, it has worked fairly well for me.

After the "learning" prompts, I would pass it something along the lines of…

"Write the JS Code for a module that will be passed the following:

sheetDataArray

It will search the array for any object containing a "formula" property. It will then replace the "value" and "displayValue" properties within those objects with "^" followed by the string in the "formula" property. It will then output the result as a csv in the following format.

Paste the csv output from the previously mentioned API call (grab a small sample sheet that only has a few rows and a few columns to keep it short)"

This JavaScript module should then output the appropriate csv data which can be used in an "Import Sheet" API module.

prime_nathaniel

NeilKY

not sure I understand the value of the use case but yea it's pretty simple.

Get sheet
ColumnFormulas are going to be in the column array
CellFormulas will be in the cell.formula parameter

Augment with a simple concatenation and send back as update request API

I'd personally just use the built in back ups for this use case or if I was going to do this specifically, I'd store the results on a separate sheet so the data model matches my use case of backing up column info. This would also allow you to normalize your results, log changes, store metadata, etc.

Anyways hope that helps.

NeilKY

Thank you both! @prime_nathaniel and @Paul Newcome That gives me something to work on.

You know what would be cool? a sheet that has four columns: Old Value, New Value, Sheet ID, and a checkbox

Where you could type in the values and when checked, Bridge would do a find and replace in that sheet for you. That's probably where I'll go with this eventually if I can figure it out.

prime_nathaniel

NeilKY

1. Webhook on row update (your checkbox)
2. in workflow, get row, junction match, checkbox = true
3. update row, map current value from get row to old, map current value of new.