Use Javascript in Bridge to efficiently import data from an API
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
Comments
-
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();
targetColumns.map(column => columnMap.set(column.title, column.id))
const rows = []; //Empty array to store cells objects later
for (row of sourcedata) {
const tags=row.tags.data.map(t=>t.value)
let tagsText=tags.toString() //gets all the values for tags and combines them into a text string
const cust = [];
const rmCustomFieldMap = new Map();
row.custom_field_values.data.map(customField => rmCustomFieldMap.set(customField.custom_field_name, customField.value)) //row.custom_field_values.data 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'),
"value": row.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
}
rows.push(resultObject)
}
// 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,
j,
temparray,
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);
arrays.push(temparray);
}
return arrays;
}
return chunkArray(rows)
EXAMPLE SOURCE DATA ARRAY
[{
"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": "someone@ourcompany.com",
"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" }
] } } ]
LAYMAN'S EXPLANATION AND TWEAKING
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 targetColumns.map 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 tags=row.tags.data.map(t=>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 row.tags.data.map to row.pathtothearray.map (ex. row.comments.map if there's a comments array in your object). Also change the t.value to t.whatevervalueyouwanttoextract (ex. t.date). If you don't have any items to group together into one big string, delete these two rows of code.
5) row.custom_field_values.data.map(customField => 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 row.custom_field_values.data 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": row.id } 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 row.id 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.
-
@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.
-
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:
https://community.smartsheet.com/discussion/104848/clear-a-sheet-using-bridge#latest
-
@nathaniel.kam I worked out a way to clear sheets at the top of the bulk load workflow. Instructions here:
https://community.smartsheet.com/discussion/104848/clear-a-sheet-using-bridge#latest
-
-
I'm not seeing the option for Http call. Has this changed since this post?
-
Yes it’s been replaced with Call API
-
Hi Brian, thanks for the quick response. I figured that was the case. I'm getting this error when I'm running the workflow. Does this have something to do with my code?
-
No that error means that the array of column information from your Get Sheet step isn't getting passed along to the Javascript script. The Javascript step should have a Script Parameter called targetColumns and the value for it should be a reference to the columns from your Get Sheet step. You might have changed the state name?
My screenshot below shows the settings. Instead of {{states.Get Users....}} the reference for your flow should be {{states.Get Columns and Report.smartsheet.get_sheet.sheet.columns}}
Also make sure there's columns in place in your sheet for it to get and put the data into!
-
Hi Brian, really appreciate your responses and time on this. A new error populated once I changed the values.
-
Your reference for sourcedata isn't right, so Javascript got no source data and is telling you "I cannot do something with no data".
You have {{states.startstate.call_api.....}} but two things - you named your state "Get Columns and Reports" and you aren't using Call API, you're using RM Get Report Rows as the step to get the report data. So, you need to replace that reference with the reference to the data that you got from the RM Get Report Rows step. You can do that by opening the run log, find the data object in the results from the Get Report Rows step, click the menu next to it and choose Copy Reference.
You're also going to need to tweak the Javascript, because the results you get back from Get Report Rows will probably be differently formatted than if using Call API. Take a look at my instructions above for tweaking the Javascript. For example, I'm pretty sure the Get Report Rows step does not return custom fields. So if you have no custom fields in the data, the script is going to fail when it tries to find them. So you'll need to remove the code that maps custom fields. Same with tags. Again - take a look at the instructions above and hopefully they'll help.
-
Hi Brian, thanks for the advice. I spent some time yesterday tweaking the script with no luck. It seems to not be reading the data properly for all the tweaks i tried. I attempted this script below for the source data.
Sourcedata = {{states.Get Columns and Report.ten_k_ft.get_report_rows.data.rows}}
But there's an error that it cannot read undefined (reading 'data')
What is the proper input value?
-
If it's complaining about "data" then it's likely that it's looking for the custom field data, the tag data, or both. The paths for that are custom_field_values.data and tags.data in each report row. I don't think the Get Report Rows returns either of those, so to fix the error you either need to replace Get Report Rows with Call API, or remove the following sections of the script:
Tag Data
remove this code
for (row of sourcedata) { const tags=row.tags.data.map(t=>t.value) let tagsText=tags.toString() //gets all the values for tags and combines them into a text stringand also remove any object in the return results that references tagsText for the value
{ "columnId": columnMap.get('People Tags'), "value": tagsText }Custom Field Data
remove this code
const cust = []; const rmCustomFieldMap = new Map(); row.custom_field_values.data.map(customField => rmCustomFieldMap.set(customField.custom_field_name, customField.value))
And remove any object in the return results that refers to the rmCustomFieldMap like this:
{ "columnId": columnMap.get('Vendor'), "value": rmCustomFieldMap.get('Vendor (required)') } -
Thanks very much for posting this code. I'm getting an error that sasys sourcedata is not iterable.
I'm trying to get the "date" and "port" fields from the Itinerary section of the JSON (attached, so as to keep length reasonable), and put them in columns called Date_ and FantPort in my Smartsheet.
I took out the 2 lines in your javascript source about tags=row.tags.data.map(t=>t.value) and let tagsText= , and modified the resultObject cells (I think putting the right column names in the right place), leaving this
//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(); targetColumns.map(column => columnMap.set(column.title, column.id)) const rows = []; //Empty array to store cells objects later for (row of sourcedata) { const cust = []; const rmCustomFieldMap = new Map(); row.custom_field_values.data.map(customField => rmCustomFieldMap.set(customField.custom_field_name, customField.value)) //row.custom_field_values.data 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('Date_'), "value": row.date }, { "columnId": columnMap.get('FantPort'), "value": row.port } ], "toTop": true //need a location value - toBottom or toTop or other } rows.push(resultObject) } // 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, j, temparray, 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); arrays.push(temparray); } return arrays; } return chunkArray(rows)
Categories
- All Categories
- 14 Welcome to the Community
- 10.6K Get Help
- 63 Global Discussions
- 68 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 465 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
