Use Javascript in Bridge to efficiently import data from an API

Brian_Richardson Overachievers
edited 05/08/23 in Best Practice

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.


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.


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.


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

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

Utilities...Utility Functions...HTTP Call

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: {{}} (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!






  • Brian_Richardson
    Brian_Richardson Overachievers

    THE CODE (see next section for tweaks)

    //Build Map of Target Sheet Columns. This JS module parameter is an array of columns from the Get Sheet module, fetching the target sheet. targetColumns is set in Bridge as the array of column data from a Get Sheet step. sourcedata is set in Bridge as an array of users from a RM HTTP call for user endpoint.

    const columnMap = new Map(); => columnMap.set(column.title,

    const rows = []; //Empty array to store cells objects later

    for (row of sourcedata) {


     let tagsText=tags.toString() //gets all the values for tags and combines them into a text string

     const cust = [];

     const rmCustomFieldMap = new Map(); => rmCustomFieldMap.set(customField.custom_field_name, customField.value)) // is the path to an array of custom fields. We are doing this additional map so we can search for the name of a custom field to pick up it's value from an array of custom fields - basically searching the array for the custom field value we want

      const resultObject = {

       cells: [                  

       //Duplicate cells objects as many times as you need to based on # of target columns you want to update. Each cell needs a {} around it.


        "columnId": columnMap.get('User ID'),  




         "columnId": columnMap.get('Role'),

         "value": row.role



         "columnId": columnMap.get('Employee Number'),

         "value": row.employee_number



        "columnId": columnMap.get('Vendor'),

       "value": rmCustomFieldMap.get('Vendor (required)')



        "columnId": columnMap.get('People Tags'),

        "value": tagsText



       "toTop": true //need a location value - toBottom or toTop or other




     // The chunkArray function splits any array into small arrays of 500 chunks.

     // This is to allow all Smartsheet API calls to succeed as there is a limit of 500 rows per request.

     const chunkArray = (array) => {

      const arrays = [];

      let i,



       chunk = 500; // Sets the value to 500 items per array.

      for (i = 0, j = array.length; i < j; i += chunk) {

       temparray = array.slice(i, i + chunk);



      return arrays;  


    return chunkArray(rows)



      "approvee_user_ids": [],

      "approver_user_ids": [],

      "last_login_time": null,

      "billrate": -1,

      "id": 100000,

      "first_name": "Someone",

      "last_name": "ThatWorksHere",

      "account_owner": false,

      "archived": false,

      "billability_target": 100,

      "billable": true,

      "created_at": "2022-03-14T20:57:44Z",

      "deleted": false,

      "deleted_at": null,

      "discipline": "IT",

      "display_name": "Someone ThatWorksHere",

      "email": "[email protected]",

      "employee_number": "",

      "guid": "xxxx-xxx-xxx",

      "hire_date": null,

      "invitation_pending": false,

      "license_type": "managed_resource",

      "location": null,

      "location_id": null,

      "mobile_phone": "",

      "office_phone": "",

      "role": "Network",

      "termination_date": null,

      "type": "User",

      "updated_at": "2022-03-14T22:16:04Z",

      "user_settings": 0,

      "user_type_id": 3,

      "thumbnail": null,

      "has_login": false,

      "login_type": null,

      "archived_at": null,

      "tags": {

       "paging": {

       "self": "/api/v1/users/100000/tags?per_page=2000&page=1",

        "next": null,

        "previous": null,

        "page": 1,

        "per_page": 2000


       "data": [{

         "id": 8494929,

         "value": "Project Dedicated"



         "id": 8494938,

         "value": "Infrastructure"



         "id": 8494939,

         "value": "Network Engineer"




      "custom_field_values": {

       "paging": {

        "self": "/api/v1/users/1532008/custom_field_values?per_page=6&page=1",

        "next": null,

        "previous": null,

        "page": 1,

        "per_page": 6


       "data": [{

         "id": 9636636,

         "custom_field_name": "Vendor (required)",

         "custom_field_id": 20397,

         "value": "Vendor",

         "created_at": "2022-03-14T20:57:44Z",

         "updated_at": "2022-03-14T20:57:44Z" }

       ]  } } ]


    Here's how this works. Don't kill me if I use some wrong terms here, I am by no means a Javascript expert.

    1) targetColumns is an array of column info from Smartsheet Get Sheet step. The array has a list of columns with title and id as two of the values for each column. The step extracts the title and id values for each column.

    2) sourcedata is an array of data from whatever API you are getting data from. This is the array that should have all the values you want to push into Smartsheet. You "hand this" to the Javascript by referring to the array in the Script Parameters setting of the Run Script step of your parent workflow.

    3) for (row of sourcedata) sets up a loop that takes the first object from your sourcedata array and does something with it. It calls this first object a "row"

    4) const>t.value) let tagsText=tags.toString() then takes all the tag values from that first object and mashes them together into a text string that you can put into Smartsheet. If you want to do this with a different array that's within your first object, then change the to (ex. if there's a comments array in your object). Also change the t.value to t.whatevervalueyouwanttoextract (ex. If you don't have any items to group together into one big string, delete these two rows of code.

    5) => rmCustomFieldMap.set(customField.custom_field_name, customField.value)) is the part that maps the custom fields. If you don't have a need to "search" for fields, you can delete this code. You can change the path for to whatever path you need to use to get to the array that you're searching. Also change the customField.custom_field_name and customField.value to be the values that you want to look for and the results you want to use. in this case I'm going to search for a field name and get the value from that field.

    6)  {"columnId": columnMap.get('User ID'),  "value":  } This step looks for the Smartsheet Column name (ie User ID) and gets the Smartsheet Column ID number for that column. Then it picks up the value to put into that cell from the the id field in the sourcedata object that you're looking at. You need to replace the 'User ID' and the parts of this step with the column name and the data that you want to get. This step repeats for each column that you want to push into your Smartsheet.

    7)    { "columnId": columnMap.get('Vendor'),"value": rmCustomFieldMap.get('Vendor (required)') } This step looks for the Smartsheet Column Name (Vendor in this case) and gets the Column ID for it. Then it searches the Custom Fields for the one with the name "Vendor (required)" and gets the value for that field.

    8)   { "columnId": columnMap.get('People Tags'),"value": tagsText  } This step looks for the Smartsheet Column Name "People Tags", then gets that string of lumped-together tags as the value.

    9)  rows.push(resultObject) This step puts all those columnIds and values together into an object and adds it to an array. Now the whole thing repeats for the next object in your source data (the next user), adding each set of results to one big ol' array. This happens very quickly.

    10) The rest of the script "chunks" the resulting array into pieces of 500 objects each, because Smartsheet Add Rows can only add 500 rows at a time.

    11) The result in Bridge is a set of arrays, of 500 objects each, with all your column IDs and the values to put in those columns. This set of arrays is then sent to your child workflow, which processes each array and adds the data to your Smartsheet!

    Whew! I know that's a lot! But it works like gangbusters once you set it up. And you can rinse and repeat use this script for other data by simply tweaking the column names and the path to your data in the script as described above.




  • works great, had to do quite a few tweaks to make it work within the context of smartsheet to smartsheet but concept works very well for all bulkified "appends". Now they just need a better way to clear sheets out.

  • Brian_Richardson
    Brian_Richardson Overachievers

    @nathaniel.kam Glad to hear it worked for you. Do you want to post your tweaked Jscript for others? Also, completely agree...I'm searching for ways to clear sheets but have yet to find a solid, simple option.




  • Brian_Richardson
    Brian_Richardson Overachievers

    Update: this process adds rows to a sheet. Often you need to clear the sheet first so that the new rows become the current information. In Step 2: Clear Sheet Automation above I lay out an option to do this, however I found it to be finicky in practice.

    A better Clear Sheet solution is to use Bridge and embed the sheet clear right at the top of the workflow you're using to read in data. You can see the details of how to set that up, in my new post here:




  • Brian_Richardson
    Brian_Richardson Overachievers

    @nathaniel.kam I worked out a way to clear sheets at the top of the bulk load workflow. Instructions here: