Tracking Expenses and Submitting Invoices/Receipts within Resource Management (RM)

amanda.milliken

Hello - we are looking to help a customer with tracking expenses in Resource Management (RM). For expenses, how can employees upload and attach receipts? From doing research, it does not look like this is possible within the Time & Expenses tab. Is there an alternative way receipts can be tracked with expense submission OR what are RM users are supposed to do if they can't? Thank you for your suggestions!

Brian_Richardson

Sorry one other idea. If your customer has Google Drive or Onedrive or some similar cloud based document management, you could instead have the users upload their receipts to a common folder and then post the link in the expense line Notes box.

Brian_Richardson

Hi it's not possible to attach items to the timesheets. Just off the top of my head, if I needed to implement this for our company I'd probably pull the expenses data into Smartsheet and then trigger an automation that sends an update request to the user and requests that they upload their receipts to the row. This would require either Bridge for automated pull of the data from RM to Smartsheet, or if you don't have Bridge you could manually export a report from RM and Data Shuttle it in. If you don't have Bridge or Data Shuttle then you could manually export a report from RM and then copy/paste the cells into a Smartsheet sheet.

If you have Bridge and want to pull in data, see my article on using Javascript in Bridge to make the process much, much faster:

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<\/a> at Smartsheet - Large Enterprise Technical Sales Engineer, who gave me the basis for this script at ENGAGE 2022. Thank you Nathan! <\/p>

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.<\/p>

THE PROBLEM<\/strong><\/p>

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

Issue 1)<\/strong> 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.<\/p>

Issue 2) <\/strong> 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.<\/p>

Issue 3) <\/strong>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.<\/p>

THE SOLUTION<\/strong><\/p>

With the Custom Jscript module, I'm able to load 1000 users in under 1 minute<\/em><\/strong> 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<\/a><\/p>

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.<\/p>

SETUP<\/strong><\/p>

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<\/em> 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.<\/p>

1) Create a Sheet:<\/strong> 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. <\/p>

2) Add a "Clear Sheet" Automation: optional<\/strong>. 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.<\/p>

3) Tweak and test your Javascript code<\/strong>. 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)<\/em> to console.log(chunkArray(rows)) <\/em>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.<\/p>

4) Add the script to Bridge. <\/strong>In Bridge, click the Integrations<\/strong> on the lefthand panel, then scroll down and click Javascript. In the popup box click Scripts tab. Click Scripts<\/strong> and then Add to Scripts<\/strong>. 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)<\/em> Now Save<\/strong> the script and close the Javascript module box.<\/p>

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

Utilities...Utility Functions...HTTP Call<\/em><\/p>

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

  6) Build your parent Workflow. <\/strong>In Workflows, click New Workflow<\/strong>. Add the following modules with the settings shown:<\/p>

  Utilities...Utility Functions...HTTP Call<\/em><\/p>

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

    Integrations...Smartsheet...Get Sheet<\/em><\/p>

    • Sheet: sheet ID of your destination sheet<\/li><\/ul>

      Utilities...Javascript...Run Script<\/em><\/p>

      • Script Name: the name you gave your script in the previous step<\/li>
      • Script Parameters:<\/li>
      • 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}}<\/li>
      • Key 2: targetColumns Value #2: {{states.startstate.smartsheet.get_sheet.sheet.columns}}<\/li><\/ul>

        Run Child Workflow<\/em><\/p>

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

          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!<\/strong><\/p>

          CODE IN NEXT THREAD REPLY<\/strong><\/p>","bodyRaw":"[{\"insert\":\"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 \"},{\"insert\":{\"mention\":{\"name\":\"Nathan Lloyd\",\"userID\":57241}}},{\"insert\":\" at Smartsheet - Large Enterprise Technical Sales Engineer, who gave me the basis for this script at ENGAGE 2022. Thank you Nathan! \\nIn 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.\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"THE PROBLEM\"},{\"insert\":\"\\nI needed to load the user list from Resource Management into a Smartsheet sheet, in an automated way via the RM API. \\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"Issue 1)\"},{\"insert\":\" 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.\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"Issue 2) \"},{\"insert\":\" 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.\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"Issue 3) \"},{\"insert\":\"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.\\n\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"THE SOLUTION\"},{\"insert\":\"\\nWith the Custom Jscript module, I'm able to load 1000 users in \"},{\"attributes\":{\"italic\":true,\"bold\":true},\"insert\":\"under 1 minute\"},{\"insert\":\" into Smartsheet! It used to take 1.5 hours. And I can find any custom field I want, even if I add more later.\"},{\"insert\":{\"mention\":{\"name\":\"Nathan Lloyd\",\"userID\":57241}}},{\"insert\":\"\\nYou 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.\\n\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"SETUP\"},{\"insert\":\"\\nBut 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 \"},{\"attributes\":{\"italic\":true},\"insert\":\"should\"},{\"insert\":\" 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.\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"1) Create a Sheet:\"},{\"insert\":\" 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. \\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"2) Add a \\\"Clear Sheet\\\" Automation: optional\"},{\"insert\":\". 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.\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"3) Tweak and test your Javascript code\"},{\"insert\":\". 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 \"},{\"attributes\":{\"italic\":true},\"insert\":\"chunkArray(rows)\"},{\"insert\":\" to \"},{\"attributes\":{\"italic\":true},\"insert\":\"console.log(chunkArray(rows)) \"},{\"insert\":\"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.\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"4) Add the script to Bridge. \"},{\"insert\":\"In Bridge, click the \"},{\"attributes\":{\"bold\":true},\"insert\":\"Integrations\"},{\"insert\":\" on the lefthand panel, then scroll down and click Javascript. In the popup box click Scripts tab. Click \"},{\"attributes\":{\"bold\":true},\"insert\":\"Scripts\"},{\"insert\":\" and then \"},{\"attributes\":{\"bold\":true},\"insert\":\"Add to Scripts\"},{\"insert\":\". 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 \"},{\"attributes\":{\"italic\":true},\"insert\":\"return chunkArray(rows)\"},{\"insert\":\" Now \"},{\"attributes\":{\"bold\":true},\"insert\":\"Save\"},{\"insert\":\" the script and close the Javascript module box.\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"5) Build your child Workflow. (yes, child first). \"},{\"insert\":\"In Workflows click \"},{\"attributes\":{\"bold\":true},\"insert\":\"New Workflow\"},{\"insert\":\". Add the module to the bottom with the settings shown:\\n\"},{\"attributes\":{\"italic\":true},\"insert\":\"Utilities...Utility Functions...HTTP Call\"},{\"insert\":\"\\nURL: \"},{\"attributes\":{\"link\":\"https:\\\/\\\/api.smartsheet.com\\\/2.0\\\/sheets\\\/5485316887865220\\\/rows?_gl=1*1azn6n0*_ga*MjAzNTEyOTE4MS4xNjQ4NzM5NTk3*_ga_ZYH7XNXMZK*MTY2ODEwMjU0NS4xNjQuMC4xNjY4MTAyNTQ1LjYwLjAuMA..&_ga=2.97976152.677405739.1667837768-2035129181.1648739597\"},\"insert\":\"https:\\\/\\\/api.smartsheet.com\\\/2.0\\\/sheets\\\/yourdestinationsheetidnumber\\\/rows\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"Method: POST\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"Headers\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"Header 1: Authorization Value 1: Bearer yoursmartsheetapitoken\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"Header 2: Content-Type Value 2: application\\\/json\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"Body: {{runtime.data}}\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"Response Handler: checked\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"6) Build your parent Workflow. \"},{\"insert\":\"In Workflows, click \"},{\"attributes\":{\"bold\":true},\"insert\":\"New Workflow\"},{\"insert\":\". Add the following modules with the settings shown:\\n\"},{\"attributes\":{\"italic\":true},\"insert\":\"Utilities...Utility Functions...HTTP Call\"},{\"insert\":\"\\nURL: your API URL. For RM I used \"},{\"attributes\":{\"link\":\"https:\\\/\\\/api.rm.smartsheet.com\\\/api\\\/v1\\\/users?per_page=2000&auth=aEpqdWFGUlJ4MEJ0NmFpaWNqL2dqWmN1VEZYSDdEY2FYRm95RDBFdzR2NEhkZUw2RWZJaGNMeUhOOHl0CndndmsrYTBCQmtZZllUeG04TktidnRZMEFjYjNCK3NEVlJHcUZ6ZlRZOUdpa3Arc1l4SEhOZklRQjlyWgpPSHV0aExKWgo=&fields=custom_field_values,tags&_gl=1*1asdfl6*_ga*MjAzNTEyOTE4MS4xNjQ4NzM5NTk3*_ga_ZYH7XNXMZK*MTY2ODA5NTc0MC4xNjMuMS4xNjY4MDk3MjIzLjE0LjAuMA..&_ga=2.38370276.677405739.1667837768-2035129181.1648739597\"},\"insert\":\"https:\\\/\\\/api.rm.smartsheet.com\\\/api\\\/v1\\\/users?per_page=2000&auth=\"},{\"attributes\":{\"italic\":true,\"link\":\"https:\\\/\\\/api.rm.smartsheet.com\\\/api\\\/v1\\\/users?per_page=2000&auth=aEpqdWFGUlJ4MEJ0NmFpaWNqL2dqWmN1VEZYSDdEY2FYRm95RDBFdzR2NEhkZUw2RWZJaGNMeUhOOHl0CndndmsrYTBCQmtZZllUeG04TktidnRZMEFjYjNCK3NEVlJHcUZ6ZlRZOUdpa3Arc1l4SEhOZklRQjlyWgpPSHV0aExKWgo=&fields=custom_field_values,tags&_gl=1*1asdfl6*_ga*MjAzNTEyOTE4MS4xNjQ4NzM5NTk3*_ga_ZYH7XNXMZK*MTY2ODA5NTc0MC4xNjMuMS4xNjY4MDk3MjIzLjE0LjAuMA..&_ga=2.38370276.677405739.1667837768-2035129181.1648739597\"},\"insert\":\"yourtokenhere\"},{\"attributes\":{\"link\":\"https:\\\/\\\/api.rm.smartsheet.com\\\/api\\\/v1\\\/users?per_page=2000&auth=aEpqdWFGUlJ4MEJ0NmFpaWNqL2dqWmN1VEZYSDdEY2FYRm95RDBFdzR2NEhkZUw2RWZJaGNMeUhOOHl0CndndmsrYTBCQmtZZllUeG04TktidnRZMEFjYjNCK3NEVlJHcUZ6ZlRZOUdpa3Arc1l4SEhOZklRQjlyWgpPSHV0aExKWgo=&fields=custom_field_values,tags&_gl=1*1asdfl6*_ga*MjAzNTEyOTE4MS4xNjQ4NzM5NTk3*_ga_ZYH7XNXMZK*MTY2ODA5NTc0MC4xNjMuMS4xNjY4MDk3MjIzLjE0LjAuMA..&_ga=2.38370276.677405739.1667837768-2035129181.1648739597\"},\"insert\":\"&fields=custom_field_values,tags\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"Method: GET\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"Response Handler: checked\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"attributes\":{\"italic\":true},\"insert\":\"Integrations...Smartsheet...Get Sheet\"},{\"insert\":\"\\nSheet: sheet ID of your destination sheet\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"attributes\":{\"italic\":true},\"insert\":\"Utilities...Javascript...Run Script\"},{\"insert\":\"\\nScript Name: the name you gave your script in the previous step\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"Script Parameters:\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"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}}\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"Key 2: targetColumns Value #2: {{states.startstate.smartsheet.get_sheet.sheet.columns}}\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"attributes\":{\"italic\":true},\"insert\":\"Run Child Workflow\"},{\"insert\":\"\\nName: Push Rows (or whatever you want)\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"Child workflow name: Name of the child workflow from step 5\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"Number of runs: {{states.startstate.javascript.run_script.result}}\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"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!\"},{\"insert\":\"\\n\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"CODE IN NEXT THREAD REPLY\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2022-11-10T19:18:03+00:00","insertUser":{"userID":140451,"name":"Brian_Richardson","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Brian_Richardson","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtdoak7dmkycj4YXWA0KDZnC6qO3ZJMvHnAxo52tsjtiGh4=s96-c","dateLastActive":"2023-08-25T18:13:32+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":true,"showPostLink":true,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/97621\/use-javascript-in-bridge-to-efficiently-import-data-from-an-api","embedType":"quote","name":"Use Javascript in Bridge to efficiently import data from an API"}"> https://community.smartsheet.com/discussion/97621/use-javascript-in-bridge-to-efficiently-import-data-from-an-api

Brian_Richardson

Sorry one other idea. If your customer has Google Drive or Onedrive or some similar cloud based document management, you could instead have the users upload their receipts to a common folder and then post the link in the expense line Notes box.

amanda.milliken

Thank you so much,@Brian_Richardson! Very helpful.