Skip to main content
katiel0001
Known Participant
May 13, 2026 8:36 PM UTC
Question

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

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

5 replies

harshas2775
harshas2775
Brainy
May 14, 2026 2:21 AM UTC

We had similar requirements and the way I implemented is

1. Have an excel template with all validations.

2. The occasional data change in ref table was driven by admin actions so whenever data was modified by admin the same process would update the excel template’s ref data sheet in the template
3. Users always download the latest excel template’s ref data as step 2 updates the version of old template document instead of creating a new one.

Even with data changes the validations were untouched. Ensure that the smart service in process model updates the exact cell column/row which is used in excel data validation column. Also pass the old template as existing template to the smart service so it doesnt overwrite anything on sheet 1. 
There can be more smaller delicate configurations that might be impacting the outcome but this is a perfectly solvable problem. Let me know if you need more help on this

Happy to help. Even happier to optimize.
    arjiv.jivithkumar
    Employee
    Nov 11, 2026 9:19 AM UTC

    Hi harshas2775, I'm dealing with a very similar requirement and would love your advice on how you handled the implementation:

    Current Setup: We have an Excel template with built-in data validations that reference a lookup/reference table on another sheet. Users have the ability to update this reference data in the system - after this update happens, we look to “refresh” the reference data in the relevant sheet within the Excel file.

    The Problem: When we update the reference data in the sheet via the Export Data Store Entity to Excel smart service in our process model, either the existing Excel validations break/disappear, or it doesn't cleanly overwrite the range without affecting the rest of the template.

     

    Which specific smart service plugin did you use to update the ref data sheet while creating a new version of the existing template document? Any extra details on your smart service node inputs or configuration would be greatly appreciated!

     


     

    shubhama926776
    shubhama926776
    Brainy
    May 14, 2026 9:45 AM UTC

    This is expected behavior. exportDataStoreEntityToExcel regenerates the sheet from scratch, which overwrites the targeted sheet entirely, breaking any cross-sheet formulas or data validations that depend on it.
    Use a two-document approach:
    Keep your master template (with Sheet 1 validations/vlookups intact) - never touch this file.
    At download time, run the process in this order:
    Use Copy Document smart service to copy the master template -> creates a fresh copy.
    Run exportDataStoreEntityToExcel  smart service on the copy, targeting Sheet 2.
    Serve the copy to the user for download.

    शुभम्
      mikes0011
      mikes0011
      Brainy
      May 14, 2026 5:06 PM UTC
      Run exportDataStoreEntityToExcel  smart service on the copy, targeting Sheet 2.
      Serve the copy to the user for download

      though the generated copy still won't have the customized content they're after, since the copy still has everything stripped out after the smart service node is done with it.

      The Expression Guru
      mikes0011
      mikes0011
      Brainy
      May 14, 2026 4:00 PM UTC

      The export DSE to excel smart service truly is terrible for anything other than dumping raw data into an otherwise completely bare Excel file.  I've been complaining from day 1 that they removed the ability (as seen in the older Excel Tools plug-in smart services) to accept a base template, but have never gotten any traction on this.

      The Expression Guru
        Terms & ConditionsAccessibility statement