Populating a drop down from data in another sheet
Hello, I am in need of some assistance please!
I am a developer and have been tasked with importing employee data into Smartsheet through the API. I have successfully built a C# service that pushes data into a sheet in smartsheet, my problem is they want to be able to auto populate a different secondary sheet (destination sheet) with the data in my employee sheet(source sheet test). The request is that the employee ID column (in the destination sheet) is a drop down that populates ID's from my master source sheet, when an ID is selected, specified fields (name, email, jobTitle, etc) are also autopopulated based on the selected ID. Is this even possible? I have read through and watched several guides but have not seen anything close to what I am trying to do, if there is a way please help me!
Best Answer
-
You have to populate it. Theres a long standing enhancement request for Smartsheet to provide a way to populate a dropdown through a reference, search and vote it if you like, but for now the only option is to populate the array of choices.
Answers
-
I have to do something similar, although it may not be the only way I typically use index/match to pull in all of the details. If the above screenshot is mostly accurate the you would only have to build 4 formula's that's pretty quick and easy. When you place the ID in all the data in the row will look into your main sheet pulling in all the mapped details you want. Once the formula's are built you could mass paste the ID's right into that ID column as well, it will take a few minutes sometimes but the sheet will refresh. This wont allow for any of the data to be updated though, it would be view only. If data needs to be updated it might be useful to look into using reports, not sure if that helped but its what I do.
-
You can do this with the Update Column endpoint, if you first read the list of IDs from the first sheet, you can post those into the dropdown column on the second sheet as "options".
https://smartsheet.redoc.ly/tag/columns#operation/column-updateColumn
// Specify column properties Column columnSpecification = new Column { Id = 5005385858869124, Title = "First Column", Index = 0, Type = ColumnType.PICKLIST, Options = new string[] { "One", "Two"} }; // Update column Column updatedColumn = smartsheet.SheetResources.ColumnResources.UpdateColumn( 2252168947361668, // sheetId columnSpecification
-
I actually decided not to use the Smartsheet sdk and wrote the code myself to create, populate and update a sheet in Smartsheet (not a huge fan of relying on somebody else's code). We have roughly 12,000 employees in our employee database table, over 8,000 of which are currently active, so I would likely have to loop over all our employee records and add each ID to an array. Is their anyway to reference the ID's from my source sheet in the dropdown in my destination sheet on Smartsheet's end? Or am I going to have to figure out a way to populate it with code?
-
You have to populate it. Theres a long standing enhancement request for Smartsheet to provide a way to populate a dropdown through a reference, search and vote it if you like, but for now the only option is to populate the array of choices.
-
Sounds good, thanks for the feedback!
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives