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
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.