Populating a drop down from data in another sheet

Jake Riley

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!

Brian_Richardson

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.

Cory Page

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.

Brian_Richardson

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

Jake Riley

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?

Brian_Richardson

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.

Jake Riley

Sounds good, thanks for the feedback!