Hi all - I'm excited to share a custom Javascript solution with my fellow Bridge users. I am not a Jscript coder - all credit for this goes to the awesome @Nathan Lloyd at Smartsheet - Large Enterprise Technical Sales Engineer, who gave me the basis for this script at ENGAGE 2022. Thank you Nathan!

In this thread I'll explain how you can leverage this script for any API. My example is with the Resource Management API, but it will work (with tweaks) for just about anything.

THE PROBLEM

I needed to load the user list from Resource Management into a Smartsheet sheet, in an automated way via the RM API.

Issue 1) It took forever. I had a parent-child workflow setup, where the parent got the users from the RM API, and then a child workflow pushed each user object into a Sheet. It took about 1.5 hours to go through 1000 users.

Issue 2) Custom fields. Custom fields via the RM API come back as an array of custom field objects, with each object having a field name and a value. That meant I couldn't just reference the "Cost Center" custom field, I had to "find" the Cost Center object and get it's value. I did that by extracting a field from the array and then sorting it, for every user, which is maybe why things took so long.

Issue 3) Tags. I needed to group a bunch of tags together, each tag was a separate object with a id and value. I could have used the Bridge "Extract Field" to get this tag list, but this Jscript is faster.

THE SOLUTION

With the Custom Jscript module, I'm able to load 1000 users in under 1 minute into Smartsheet! It used to take 1.5 hours. And I can find any custom field I want, even if I add more later.@Nathan Lloyd

You can implement this solution for any API, not just Resource Management. I'll explain below how to setup the workflows and the script to accomodate any need.

SETUP

But first, a caveat. This method uses Add Row to bulk add data into a sheet. It will not Update existing rows, that's a whole other ball of wax that I don't have a solution for (yet). Also there's no guarantee of add order. It should load in the same order as you get the data from your API, but it's not guaranteed by Smartsheet. So if that's critical for you, you may need another solution.

1) Create a Sheet: create a sheet to hold your data and add all the columns you plan to populate. Click File..Properties and record the sheet ID. Note that you can have more columns than just the API populated ones - you can run formulas and do everything as normal in this sheet.

2) Add a "Clear Sheet" Automation: optional. This solution works by Adding Rows each time it runs. So - if you want to replace information in your sheet, you'll need to clear out the old info. To do that automatically you can create a Move Row automation on a schedule or trigger, that moves the row to another sheet to clear it out. Or you can use Data Shuttle to import a blank file to the sheet on a schedule, selecting "Delete rows that don't match filter criteria" as the option.

3) Tweak and test your Javascript code. This is the trickiest (for me at least-not knowing much Jscript). You'll need to adjust the code to work for you. You may need to read up on some Jscript basics to do this. More on tweaking the code further down this thread. You can test the code to make sure it works prior to loading it into Bridge. I used JSFiddle for this. Copy/paste the code from this thread into JSFiddle's JavaScript box. You'll also need to add two arrays with sample data, you can get sample data using Postman or Bridge. Add the source data to the top of the code as const sourcedata = [ your sample array]. Add the column data to the top of the code as const targetColumns = [ your sample columns array]. Also change the very last step from return chunkArray(rows) to console.log(chunkArray(rows)) and enable the console in Settings, so you can see the results or errors. As you tweak, look for red dot errors next to the code lines.

4) Add the script to Bridge. In Bridge, click the Integrations on the lefthand panel, then scroll down and click Javascript. In the popup box click Scripts tab. Click Scripts and then Add to Scripts. Give your script a name that you can remember (write it down as you need to refer to it in the workflow). In the Script Body section copy/paste the code from this post or the code that you tweaked in JSFiddle. Note: do not copy in any testing data arrays for sourcedata or targetColumns. If copying from JSFiddle, change the very last step back from console.log to return chunkArray(rows) Now Save the script and close the Javascript module box.

5) Build your child Workflow. (yes, child first). In Workflows click New Workflow. Add the module to the bottom with the settings shown:

Utilities...Utility Functions...HTTP Call

• URL: https://api.smartsheet.com/2.0/sheets/yourdestinationsheetidnumber/rows
• Method: POST
• Headers
• Header 1: Authorization Value 1: Bearer yoursmartsheetapitoken
• Header 2: Content-Type Value 2: application/json
• Body: {{runtime.data}}
• Response Handler: checked

6) Build your parent Workflow. In Workflows, click New Workflow. Add the following modules with the settings shown:

Utilities...Utility Functions...HTTP Call

• URL: your API URL. For RM I used https://api.rm.smartsheet.com/api/v1/users?per_page=2000&auth=yourtokenhere&fields=custom_field_values,tags
• Method: GET
• Response Handler: checked

Integrations...Smartsheet...Get Sheet

• Sheet: sheet ID of your destination sheet

Utilities...Javascript...Run Script

• Script Name: the name you gave your script in the previous step
• Script Parameters:
• Key 1: sourcedata Value #1: {{states.startstate.utilities.httpcall.data}} (note that your exact data reference might be slightly different. Run your workflow and get the data reference for the array you want to import. It very well may just be {{states.startstate.utilities.httpcall}}
• Key 2: targetColumns Value #2: {{states.startstate.smartsheet.get_sheet.sheet.columns}}

Run Child Workflow

• Name: Push Rows (or whatever you want)
• Child workflow name: Name of the child workflow from step 5
• Number of runs: {{states.startstate.javascript.run_script.result}}

7) Now run your parent workflow to test it. If all is well, you should see the data appear in your sheet within a minute or two!

CODE IN NEXT THREAD REPLY