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}}

Brian_Richardson · 10/14/24

USE CASE: AUTOMATICALLY SHARE CONTENT TO NEW EMPLOYEES THROUGH GROUPS

Problem: We have an AI training program that employees can sign up to. Because I use a Member Dashboard with reports and widgets set to Current User filters, I cannot publish those dashboards/reports for use, I need to share the content to the members to use natively.

Solution: New members are added to a sheet as a contact when they sign up for the program via a form. The form requires login, which captures their email address in the Created column. A separate Contact type column simply reads the Created column value and it converts to a contact. Bridge triggers when a new row is added and adds the contact to the group. The group has been already given View permissions to the workspace. Bridge also checks a "Member Added to Group" checkbox on the sheet, which triggers a sheet automation to notify the new member of their new access..

Workflow Details:

Trigger: When Rows are Added to the signup sheet.
State: startstate: Default state, can leave as is
Smartsheet: Get Row: this step reads the data from the new row that was just added. The Trigger only starts the workflow with basic information and doesn't contain the data (like the new member's name), so you need to go get it.
1. Sheet = {{runtime.sheetID}}
2. Row ID = {{runtime.event.id}}
State: Add Member to Group
Call API: Make API Call: this step uses the Smartsheet API to push the email into the group that has View rights to the workspace. Note that Call API must be pre-setup for the Smartsheet API calls with the base URL (https://api.smartsheet.com/2.0) and the Authentication: Bearer xxxx header
1. API Profile = Smartsheet
2. Endpoint = /groups/mygroupid/members (note - I got the group id by going to Postman and doing a call to /groups, which lists all the groups with their group ids)
3. Method = POST
4. HTTP Request Body = {"email":"{{states.startstate.smartsheet.get_row.row.cells.Member Contact.value}}"}
Smartsheet Update Row: this step checks a checkbox on the signup sheet so Smartsheet knows this workflow ran. Then, an automation on the sheet will fire (triggered by Rows Added/Changed and checkbox gets checked) and send a note to the new member.
1. Sheet = {{runtime.sheetID}}
2. Row ID = {{runtime.event.id}}
3. Cells Key #1 = Member Added to Group
4. Cells Value #1 = true

Brian_Richardson · 10/14/24

USE CASE: BUILD AN ORGANIZATIONAL STRUCTURE WITH WORKDAY DATA AND CLEANUP VENDOR NAMES USING JAVASCRIPT

Problem: The Procurement team ingests monthly travel card data and needs to show that data on a dashboard, filtered for the current user's organization. Also need to change vendor names like "7-11 #34234" to just "7-11" so that vendor totals can be pivoted.

Solution: First, 3 Data Shuttles are used to ingest the card data and split it using DS filters into three sheets. A fourth sheet has a row for each destination sheet and a formula =MAX({Created Date}) that references the Created Date column on each sheet. A fourth row contains a "Ready for Update?" checkbox column that uses a formula on one of the rows to see if all three max dates are the same. If they are all the same, I know that all three Data Shuttles have finished their jobs and therefore the checkbox gets checked to trigger Bridge. Bridge then runs, reads Workday data and card data in, and then runs Javascript to create the org structure and cleanup vendor names. Finally, Bridge pushes the results in chunks of 500 rows to a Child workflow that posts the data back into the sheets.

A Note on Javascript: the Javascript step is the "workhorse" of this process. The code must first be entered, though, to be used. Open the Javascript module on the Bridge Integrations page and paste the code in, then give it a name for reference.

The Javascript in this workflow does multiple steps:

Reads the Workday data in three parts
Reads the card data from the report. Note that in the code the column references are by number, not name, as the report data has the data broken up by column ID rather than column name.
For each row of card data, it finds the employee ID of the cardholder (contained in the card data) and matches it to the employee ID in the Workday data
From the matching employee, it gets the manager's email address
Finds the manager by email in the Workday data, and gets their manager's email address
Repeats this process 8 times to produce a chain of managers from employee to CEO
Reverses the results to place the last found entry (the CEO) into a "Top Level" field, the second to last entry (the CEO's direct report) into a 1st Level field, and so on.
Reads the Merchant name from each row of card data and uses regex to trim off any trailing ID numbers
Formats the results of that work into Smartsheet row objects - a very specific JSON format that the Smartsheet API can read and understand and use to post updated data back into the card data sheet
Chunks up the results into groups of 500 rows, the maximum number of rows that Smartsheet can update at once.
Returns the set of "chunks" to Bridge

Details:

Trigger: when rows are changed (on the "trigger sheet" - I'm looking for that checkbox to check when all three Datashuttle workflows finish. See the Solution description above)
State: Ready:
Smartsheet: Get Row: The trigger doesn't tell me if the "Ready for Update?" checkbox was checked, or unchecked, so I get the row so I can evaluate the state of the checkbox
Junction: Ready to Update: this is a Contains junction that checks the "Ready for Update?" checkbox that was read by the Get Row step and sees if it is true (checked) or not.
1. Name = Ready to Update
2. Value = {{states.Ready.smartsheet.get_row.row.cells.Ready for Update?.value}}
3. Contents = true
State: Workday1 - this is the "Success" side of the Junction. You need to give it a state name. The other side of the junction for "Failure" is given the State name "Not Ready" and left hanging. This stops the workflow if the checkbox is not checked
Smartsheet: Get Report: my Workday data is spread across many sheets, but a large report pulls it all back together again. This step reads the first 10,000 rows of the report, which is the maximum rows you can read.
1. Report = report id
2. Filters: Page Number = 1
3. Filters: Results Per Page = 10000 (if you don't specify this, it defaults to 100 rows. 10,000 is the max)
State: Workday 2 (note it's important to use different states if you are making the same call (Get Report) multiple times. Otherwise, Bridge will not run duplicate steps correctly)
Smartsheet: Get Report
1. Report = report id
2. Filters: Page Number = 2
3. Filters: Results per page = 10000
State: Workday 3
Smartsheet: Get Report
1. Report = report id
2. Filters: Page Number=3
3. Filters: Results per page = 10000
State: PCard
Smartsheet: Get Sheet (this step reads the first sheet of card data that Data Shuttle imported. In this case it's the "PCard" data.)
1. Sheet = sheet id
2. Filters: Columns = here I typed out all the columns that I wanted. You don't have to limit the columns that are returned, but it's less data if you do.
3. Results per Page = 10000 (if you don't specify this, it defaults to 100 rows. 10,000 is the max).
Javascript: Run Script
1. Script Name = Procurement Card Data
2. Script Parameters Key #1 = rows
3. Script Parameters Value #1 = {{states.PCard.smartsheet.get_sheet.sheet.rows||}}
4. Script Parameters Key #2 = workday1
5. Script Parameters Value #2 = {{states.Workday1.smartsheet.get_report.report.rows||}}
6. Script Parameters Key #3 = workday2
7. Script Parameters Value #3 = {{states.Workday2.smartsheet.get_report.report.rows||}}
8. Script Parameters Key #4 = workday3
9. Script Parameters Value #4 = {{states.Workday3.smartsheet.get_report.report.rows||}}
Child Workflow: Push PCard
1. Child Workflow Name = Update Batched Rows in Smartsheet (this is a workflow that I created that can take arrays of 500 rows from any other workflow and update those rows in a sheet. I pass along to this child workflow the results from the javascript, which is 1 or more arrays with 500 row objects in each array. The Child Workflow then takes each group of 500 rows in turn and updates them.
2. Number of Runs = {{states.PCard.javascript.run_script.result}}
3. Child entity values Key #1 = sheetID
4. Child entity values Value #1 = the actual sheet ID that I want to update

The "PCard" workflow then repeats for the other two sheets, with the same settings, just different sheet IDs.

CHILD WORKFLOW: UPDATE BATCHED ROWS IN SMARTSHEET

Trigger: the parent workflow calling this workflow. The trigger contains one of the groups of 500 rows to update that came from the parent workflow. The trigger also has the sheetID "entity" passed along by the parent workflow so I know what sheet to update.
State: startstate (default)
Call API: Make API Call this is the step that uses the Smartsheet API to push the group of 500 row updates that are coming from the parent. Because the parent calls this child with "Number of Runs" = the array of 500 row "chunks", this child workflow runs once for each chunk of 500 rows.
1. API Profile to use: Smartsheet (note you need to pre-setup the Call API module under Integrations to have a Smartsheet profile that contains the root URL for Smartsheet API (https://api.smartsheet.com/2.0) and the Authentication: Bearer token id header, as well as the Content-Type: application/json header.
2. Endpoint = /sheets/{{runtime.entities.sheetID}}/rows?allowPartialSuccess=true
3. Method = PUT
4. HTTP Request Body: {{runtime.data}} (Javascript in the parent workflow put together the body in a format that's recognized by Smartsheet API)

JAVASCRIPT CODE

Note I've removed specific column IDs and email addresses and inserted placeholders like "workday report column ID for manager's email here". Those should be replaced with your own column IDs.

Also note this is an example - your specific columns and data layout is going to be different.

The result of this code is an set of arrays, where each array contains up to 500 Smartsheet row objects. Each object contains a number of columns and values which you can see in the code. This code creates a top-to-bottom org structure of contacts (emails) across 8 contact columns, and also trims merchant names of trailing number identifiers.

// Define the function to trim merchant name

function trimMerchantName(merchantName) {

    let segments = merchantName.trim().split(' ');

    if (segments.length > 1) {

        let lastSegment = segments.pop();

        if (/^\d/.test(lastSegment) || lastSegment.startsWith('#') || !/^[A-Za-z\/]*$/.test(lastSegment)) {

            segments.push('');

        } else {

            segments.push(lastSegment);

        }

    }

    return segments.join(' ');

}


// Define the function to find an entry in a workday array

function findEntryInWorkday(workday, employeeID) {

    return workday.find(entry => entry.cells["report column id for employee ID here"].displayValue === employeeID);

}


// Define the function to find managers

function findManagers(cells, workday1, workday2, workday3) {

        if (cells["Employee ID"]?.displayValue === undefined) {

        return; // This will exit the function prematurely if no Employee ID

    };

if (rows === undefined || rows === null) {

    // Exit the function or perform any necessary action

    return;

};


    let employeeID = cells["Employee ID"]?.displayValue || null;

    let email = null, location = null, manager = null, manager0 = null, manager1 = null, manager2 = null, manager3 = null, manager4 = null, manager5 = null, manager6 = null, manager7 = null, manager8 = null, jobProfile = null


    let step1Entry = findEntryInWorkday(workday1, employeeID) || findEntryInWorkday(workday2, employeeID) || findEntryInWorkday(workday3, employeeID);


    // Check if step1Entry is null, and if so, exit the function

if (step1Entry === null) {

    return;

}


    if (step1Entry) {

        manager0 = step1Entry.cells["workday report column ID for manager's email here"]?.displayValue ?? null;

        location = step1Entry.cells["workday report column ID for location here"]?.displayValue ?? null;

        email = step1Entry.cells["workday report column ID for email address here"]?.displayValue ?? null;

        jobProfile = step1Entry.cells["workday report column ID for Job Profile here"]?.displayValue ?? null;




        // Start the loop from manager1

        let i = 1;

        for (; i <= 8; i++) {

            const currentManagerKey = "manager" + (i - 1);

            const nextManagerKey = "manager" + i;

            const currentManagerValue = eval(currentManagerKey);

            if (!currentManagerValue) break; // Break the loop if the current manager is null

            const stepEntry = workday1.find(entry => entry.cells["workday report column ID for email address here"]?.displayValue === currentManagerValue) || workday2.find(entry => entry.cells["workday report column ID for email address here"]?.displayValue === currentManagerValue) || workday3.find(entry => entry.cells["workday report column ID for email address here"]?.displayValue === currentManagerValue);

            if (stepEntry) {

                eval(`${nextManagerKey} = stepEntry.cells['workday report column ID for manager's email here']?.displayValue ?? null;`);

            } else {

                break;

            }

        }

    }



// Initialize all topMinus variables to null

for (let m = 1; m <= 8; m++) {

    eval(`var topMinus${m} = null;`);

}


// Find the first manager variable with "ceo's email"

let topLevel = null;

let emailFound = false;

let i = 0;

for (let i = 0; i < 8; i++) {

    const managerKey = "manager" + i;

    if (eval(managerKey) === "ceo's email here") {

        emailFound = true;

        topLevel = eval(managerKey); // Assigning the value of managerKey to topLevel

        for (let j = 1; j <= i; j++) {

            eval(`var topMinus${j} = manager${i-j};`);

        }

        break;

    }

}



    return { manager0, location, email, manager1, manager2, manager3, manager4, manager5, manager6, manager7, manager8, jobProfile,  topLevel, topMinus1, topMinus2, topMinus3, topMinus4, topMinus5, topMinus6, topMinus7, topMinus8 };

}






// Define an empty array called cardData to contain all the lookup info and trimmed name etc

const cardData = [];


// Iterate over each row and perform the lookup

rows.forEach(row => {

const trimmedName = row.cells["Merchant Name"]?.displayValue ? trimMerchantName(row.cells["Merchant Name"].displayValue) : null;


    const trimmedNameCol = row.cells["Merchant Name Trimmed"].columnId

    const emailCol = row.cells["Email"].columnId

    const locationCol = row.cells["Cost Center"].columnId

    const topMinus1Col = row.cells["1st Level Leader"].columnId

    const topMinus2Col = row.cells["2nd Level Leader"].columnId

    const topMinus3Col = row.cells["3rd Level Leader"].columnId

    const topMinus4Col = row.cells["4th Level Leader"].columnId

    const topMinus5Col = row.cells["5th Level Leader"].columnId

    const topMinus6Col = row.cells["6th Level Leader"].columnId

    const topMinus7Col = row.cells["7th Level Leader"].columnId

    const jobCol = row.cells["Job Profile"].columnId

    const rowId = row.id


    const managers = findManagers(row.cells, workday1, workday2, workday3);

    if (!managers || !trimmedNameCol || !emailCol || !locationCol ||  !topMinus1Col || !topMinus2Col || !topMinus3Col || !topMinus4Col || !topMinus5Col || !topMinus6Col || !topMinus7Col || !jobCol || !rowId) {

        return;

    }


    const { manager0, location, email, manager1, manager2, manager3, manager4, manager5, manager6, manager7, manager8, jobProfile,  topLevel, topMinus1, topMinus2, topMinus3, topMinus4, topMinus5, topMinus6, topMinus7, topMinus8  } = managers;


    let lookups = {

        "id": rowId,

        "cells": [{

                "columnId": trimmedNameCol,

                "value": trimmedName  || null

            },

            {

                "columnId": emailCol,

                "value": email  || null

            },

            {

                "columnId": locationCol,

                "value": location  || null

            },

            {

                "columnId": topMinus1Col,

                "value": topMinus1  || null

            },

            {

                "columnId": topMinus2Col,

                "value": topMinus2  || null

            },

            {

                "columnId": topMinus3Col,

                "value": topMinus3  || null

            },

            {

                "columnId": topMinus4Col,

                "value": topMinus4  || null

            },

            {

                "columnId": topMinus5Col,

                "value": topMinus5  || null

            },

            {

                "columnId": topMinus6Col,

                "value": topMinus6  || null

            },

            {

                "columnId": topMinus7Col,

                "value": topMinus7  || null

            },

            {

                "columnId": jobCol,

                "value": jobProfile  || null

            }   

        ]

    }


    cardData.push(lookups)

});


// Define the function to chunk the array into groups of 500 row objects which Smartsheet API can push

const chunkArray = (array) => {

    const arrays = [];

    let chunk = 500;


    for (let i = 0; i < array.length; i += chunk) {

        arrays.push(array.slice(i, i + chunk));

    }


    return arrays;

}



return chunkArray(cardData)

Bridge examples including Javascript

Comments

Categories