Export Data Store to Excel - how to preserve original document's data validations?

Certified Senior Developer

Hi, my use case is I want to provide an excel template for users to download and fill out. Some of the information the user is filling out is dependent on data in a ref table that updates occasionally, so before the user downloads the document, I run Export Data Store Entity to Excel smart service in a process to update the document, so when the user downloads the document, it'll have the most updated ref data.

Here's the issue I'm running into - the excel template has two sheets:

- Sheet 1 is where the user fills out info. This sheet has data validation rules to make it more user friendly, most notably vlookup functions and data validation dropdowns that pull from Sheet 2

- Sheet 2 is where the ref data lives, and what the Export Data Store Entity smart service updates

However, after the smart services runs, the excel template that gets generate has no data validations - no dropdowns or excel functions. I'm imagining even though I'm only having the smart service update Sheet 2, the entire excel document gets impacted and erases all data validations. Below is the code that is being run in the smart service (note: I'm using the smart service's process model version, but for sake of clarity here's the SAIL code version):

a!exportDataStoreEntityToExcel(
          documentToUpdate: cons!DOC_CREATE_EXCEL_TEMPLATE,
          entity: cons!REF_TABLE,
          sheetNumber: 2
        )

Is this expected behavior or is there another workaround I can consider?

Thanks in advance

  Discussion posts and replies are publicly visible