Bridge examples including Javascript

Brian_Richardson · 09/20/24

Hi fellow Smartsheeters! In preparation for my session at Engage 2024, The future of workflows, I'm posting a variety of live Bridge examples to this thread. Each "block" will have a use case description, workflow layout, and (if needed) the javascript code I used.

Enjoy!

Brian_Richardson · 09/20/24

USE CASE: TRANSLATION

Problem: The Innovation Committee at Iron Mountain receives ideas from around the world, often in local languages that the Innovation Committee does not speak natively. The committee needed ideas translated to English, the working language of the global committee.

Workflow Summary

The workflow below is triggered when someone adds an idea to the sheet via a form. It reads the row that was added, then translates two columns to English and posts the translations back into the same row in two "English" columns.

Workflow Detail

Trigger: when rows are added to the Innovation Ideas tracker sheet, the Translation workflow is triggered
Get Row: because triggers do not provide data content from the row that's triggered, this step reads the row data.
1. Sheet = {{runtime.sheetID}}
2. Row ID = {{runtime.event.id}}
State Translate Description: changing the State name. This is important when you have two steps of the same type in your workflow, you MUST separate them with different states.
Google Translate: this is a prebuilt step in Bridge to translate the Description to English. Before using Google Translate in your workflow, you need to go to the Integrations panel in Bridge and Authenticate to Google Translate.
1. Text to Translate = {{states.startstate.smartsheet.get_row.row.cells.Description.value||No Description}}
2. Note: use the || symbol (two pipe symbols above your Enter key) to return an alternative result if the reference has no data or doesn't exist. If a Description wasn't provided, this would error without the ||. Instead, the Text to Translate becomes "No Description".
3. Language to Translate To = en
State Translate Short Name
Google Translate: translates the Idea Short Name to English
1. Text to Translate = {{states.startstate.smartsheet.get_row.row.cells.Idea Short Name.value||No Short Name}}
2. Language to Translate To = en
Update Row: posts the two translated text values back into the same row
1. Sheet = {{runtime.sheetID}}
2. Row ID = {{runtime.event.id}}
3. Cells - Key #1 = Description Translated to English (this is the name of the column where I want to post the description translation)
4. Cells - Value #1 = {{states.Translate Description.google_translate.translate.data.translations.0.translatedText||}} (note I use the || mark again, this time with nothing after it. this prevents errors from stopping the workflow if there's no translated text to post)
5. Cells - Key #2 = Idea Short Name English
6. Cells - Value #2 = {{states.Translate Short Name.google_translate.translate.data.translations.0.translatedText||}}

Brian_Richardson · 09/20/24

USE CASE: CUSTOM TASK SHEETS UPON REQUEST

Problem: The IRM Marketing department receives thousands of requests for work each year. Each request involves a potentially unique set of tasks and assignees. Marketing needed a way to manage this workload, report on it, and accommodate each requestor's needs.

Workflow Summary: A parent-child workflow set is triggered when a request is submitted through a form. The workflow checks to see if a "personal task sheet" was requested, and if not it then reads the set of categories selected by the requestor, copies a task template sheet, removes the task sections that don't apply, then sets start date and some other metadata on the sheet to activate it. Note that I do a "copy then remove" process to maintain predecessors in the task sheet. If I, instead, tried to add in sections I would have to read and recreate predecessors, which is extremely difficult.

Sheet Setup Items:

The Intake form has Setting = Require login. This results in the Created By system column on the sheet having the full email address of the requestor
The Intake sheet has the following columns used in this workflow:
- Requestor = [Created By]@row or, if flagged on the form, the email of the person the requestor is requesting on behalf of. The key is that this needs to be an email address for sharing.
- Personal Task Sheet? = a checkbox indicating whether the request is for a "Personal Task Sheet" which has no preset tasks on it, just blank space and key formulas.
- Personal Sheet Name = the name that the requestor wants to use for their personal task sheet
- Project Name Trimmed = takes the Project Name field and trims it to 30 characters to be used as a sheet name
- Task Sheet Sections = a multiple-pick list of the task sections that the requestor wants in their task sheet
- Request ID = autonumber
- Request Submission Date = Created Date system column
The Task Template has the following columns and setup:
- Row Type column uses a formula to determine if the row is "Campaign Info" (the top row) amongst other values
- Combined Component column uses the hierarchy to determine if the row is a section header, subsection header, or task. If section/subsection it picks up the name of the row.
- Request ID = a column where only the first row is used to capture the request ID from the intake sheet. This is then used by multiple summary fields to do INDEX/MATCH lookups back to the intake sheet to get other related data about the request
- Start = the start date column. Note this sheet is dependency-enabled so it cannot have a formula in Start. We will solve that in Bridge :-)

Notes on Call API: this workflow uses Call API to achieve some actions in Smartsheet that aren't available in the default Bridge steps. You can use Call API to do Smartsheet advanced actions, or to use any other tool's API for integration.

Call API Pre-Setup: before setting up this workflow, you need to setup the Call API module to be able to "talk" to Smartsheet. This is not the same as signing into Smartsheet or Bridge.

In Bridge, open the Integrations panel using the lefthand navigation pane upon first login
Click Call API
Click Profiles…Add to Profiles
1. Name = Smartsheet
2. Base URL = https://api.smartsheet.com/2.0
3. Secret Headers
  1. Key #1 = Authorization
  2. Value #1 = Bearer your token here ← get your token in Smartsheet by clicking your Personal Settings, API Access. So this should look something like Bearer klo234nndfoiun234lnllll
4. Headers
  1. Key #1 = Content-Type
  2. Value #1 = application/json this is used for POST calls to API. Putting this here prevents issues later.
  3. Key #2 = Accept
  4. Value #2 = application/json
Click Save to save this profile. Now you can use the Smartsheet API in your Bridge workflows!

PARENT WORKFLOW DETAILS

The Parent workflow reads the request, copies the task template sheet, posts the start date and request Id to the new copy, searches the new copy to get a list of row ids corresponding to task sections, then passes those to a Child workflow to remove all the task sections that are unwanted by the requestor.

Note when setting up parent-child workflows, you must setup at least a placeholder child workflow before adding the child workflow call to your parent.

Trigger: when row is added to the intake sheet
Smartsheet: Get Row: gets the data from the row that we'll use in the next steps
1. Sheet = {{runtime.sheetID}}
2. Row ID = {{runtime.event.id}}
3. Advanced Options = Include Row Permanent Link (for later use), Exclude Empty Cells (to speed things up), Include Additional Object Value (important - to get the desired "Components" as an array to use later)
Match Junction: checks if two values match. Specifically I'm checking here to see if the Personal Task Sheet? checkbox in the intake sheet was checked, indicating that the requestor just wants a blank task sheet that isn't prefilled, and isn't shared to anyone. Important Note - this is a "Match" type junction, not the "Junction Match" type junction which is completely different. Confusing!
1. Name = Personal Sheet
2. First Value = {{states.startstate.smartsheet.get_row.row.cells.Personal Task Sheet?.value}}
3. Second Value = true
State: Copy Personal Sheet: this is the "success" side of the junction, you need to give it a name by dragging a state onto the open "success" box that appears when setting up this junction
1. Call API: Make API Call: this step copies the task template sheet which is pre-setup with all the tasks and dependencies needed, to a "Personal Task Sheets" workspace.
  1. API Profile to use = Smartsheet
  2. Endpoint = /sheets/my template sheet ID/copy?include=attachments,cellLinks,data,filters,forms,ruleRecipients,rules
  3. Method = POST
  4. HTTP Request Body = {"destinationType":"workspace","destinationId":my workspace id,"newName":"{{states.startstate.smartsheet.get_row.row.cells.Personal Sheet Name.value}}"}
  5. Additional Settings = Stop the workflow if API returns an error Note the default is to keep going but I want this to fail if the copy fails, as every step after this works with the copy
2. State Share Personal Sheet need to add a new state here because we're going to do another API Call, you must have separate states for duplicate step types
3. Call API: Make API Call: this step shares the newly copied sheet to the requestor
  1. API Profile to use = Smartsheet
  2. Endpoint = /sheets/{{states.Copy Personal Sheet.call_api.make_api_call.response.result.id}}/shares
  3. Method = POST
  4. HTTP Request Body = {"email":"{{states.startstate.smartsheet.get_row.row.cells.Requestor.value}}","accessLevel":"ADMIN"} uses the requestor's email address from the Requestor column in the row that triggered this workflow
State: Copy Template: this is the "failure" side of the Match Junction.
1. Call API: Make API Call: copies the task template to a "Projects" workspace
  1. API Profile to use = Smartsheet
  2. Endpoint = /sheets/my template sheet ID/copy?include=attachments,cellLinks,data,filters,forms,ruleRecipients,rules
  3. Method = POST
  4. HTTP Request Body = {"destinationType":"folder","destinationId":my workspace id,"newName":"{{states.startstate.smartsheet.get_row.row.cells.Project Name Trimmed.value}}"}
  5. Additional Settings = Stop the workflow if API returns an error
State: Update Copy with Start Date: both sides of the Junction feed into this same state. You can accomplish this by holding Alt and clicking the end of one side of the junction and then this state.
Utility Functions: Delay sets a 30s delay in the workflow to allow time for the sheet copy to complete before proceeding…helps tamp down errors
Smartsheet: Search Sheet: looks for the "Campaign Info" row (top row) to get the row ID
1. Sheet = {{states.Copy Template.call_api.make_api_call.response.result.id||states.Copy Personal Sheet.call_api.make_api_call.response.result.id}} this is the sheet ID from the copy step, the Call API step returns this information. Because there's two possible paths, I use || to pick up the value regardless of which path was used.
2. Row Filters 1
  1. Column = Row Type
  2. Condition = Is Equal To
  3. Value = Campaign Info
3. Advanced Options = Include Sheet Data I usually check this box - needed here to get the row ID
Smartsheet: Update Row: takes the Row ID from the previous step and updates that row with the Request ID and Start Date from the intake sheet. Because Start cannot have a formula in a dependency-enabled sheet, this is the solution to post a Start date into the sheet of our choosing.
1. Sheet = {{states.Copy Template.call_api.make_api_call.response.result.id||states.Copy Personal Sheet.call_api.make_api_call.response.result.id}}
2. Row ID = {{states.Update Copy with Start Date.smartsheet.search_sheet.sheet.rows.0.id}}
3. Cells
  1. Key #1 = Request ID
  2. Value #1 = {{states.startstate.smartsheet.get_row.row.cells.Request ID.value}}
  3. Key #2 = Start
  4. Value #2 = {{states.startstate.smartsheet.get_row.row.cells.Request Submission Date.value||2023-01-01T08:00:00}}
State: Get Section Row IDs from Copy
Smartsheet: Search Sheet: reads the newly copied sheet to get a list of row IDs that correspond to the section headers
1. Sheet = {{states.Copy Template.call_api.make_api_call.response.result.id||states.Copy Personal Sheet.call_api.make_api_call.response.result.id}}
2. Row Filters 1
  1. Column = Combined Component
  2. Condition = Is Not Empty
3. Advanced Options = Include Sheet Data
Child Workflow: Remove Sections: this step takes each row from the search above and sends that row data to another workflow to be processed. This is the same thing as a for-each loop in coding.
1. Child Workflow Name = whatever you called the child workflow (need to setup at least a placeholder before adding this step)
2. Number of Runs = {{states.Get Section Row IDs from Copy.smartsheet.search_sheet.sheet.rows}}
3. Child Entity Values these are additional variables you can send along to the child workflow in addition to the data in the "Number of runs"
  1. components = {{states.startstate.smartsheet.get_row.row.cells.Task Sheet Sections.displayValue}} this is the list of selected sections from the request
  2. sheetID = {{states.Copy Template.call_api.make_api_call.response.result.id||states.Copy Personal Sheet.call_api.make_api_call.response.result.id}} sheet ID of the newly copied template that we are working with

Whew! There's a few other steps in this workflow but nothing much more than making a couple of update rows to post the sheet permalink back into the intake sheet and set some status info.

CHILD WORKFLOW DETAILS

The Child workflow takes each row ID sent to it by the parent in turn, checks to see whether that row is "wanted" or not based on selections in the request, then deletes the section if it's not wanted. Note that deleting a parent row also deletes child rows under it in one step.

Trigger: Parent workflow (automatically set when calling the child). This workflow runs once for each row sent by the parent.
Contains Junction: this is where we check the "Combined Component" column of the row, the row name, to see if it's listed in the request as a "component" to retain . If it was included in the request, workflow passes to the "success" side and ends. If it was not included in the request, workflow passes to the "failure" side and we remove it from the sheet.
1. Name = Component Selected
2. Value = {{runtime.entities.components}} this is the list of selected sections from the intake sheet that was passed along from the Parent workflow as an "entity" called "components"
3. Contents = {{runtime.data.cells.Combined Component.value}} this is the name of the row which may correspond to one of the selected sections in "components".
State: Selected: this is the "success" side of the Junction, give it a name and then that's the end of that side. Meaning - the row name was found in the requested list of desired sections to keep.
State: Not Selected: this is the "failure" side of the Junction, drag a State here to give it a name
Smartsheet: Search Sheet: this step is just a double-check that the row still exists in the sheet to be deleted. this is error correcting in the event that someone is manually "messing" with the sheet prior to completion of the Bridge workflow
1. Sheet = {{runtime.entities.sheetID}}
2. Row Filters 1
  1. Column = Combined Component
  2. Condition = Is Equal To
  3. Value = {{runtime.data.cells.Combined Component.value}}
3. Advanced Options = Include Sheet Data
Is Empty Junction: this validates that the row still exists. if it didn't exist in the Search results above, the results will be empty
1. Name = Missing Section
2. Value = {{states.Not Selected.smartsheet.search_sheet.sheet.rows.0.id}}
State: Yes: this is the "success" side of the Junction. Meaning - the search results were empty and the row was already deleted. no steps after this state, ends the workflow.
State: No: this is the "failure" side of the Junction meaning the search results were not empty, the row still exists and needs deletion
Smartsheet: Delete Row: here's the final step of the child workflow to delete the row. Since this is a parent row, this step will also delete all the tasks under it as children.
1. Sheet = {{runtime.entities.sheetID}}
2. Row ID = {{states.Not Selected.smartsheet.search_sheet.sheet.rows.0.id}}

Bridge examples including Javascript

Comments

Categories