SFDownloadFiles User Guide
SFDownloadFiles is an Excel Utility to download multiple files of all types from salesforce into a structured directory.
What if you could create a report on Salesforce, Export that report, then use it to download all the files related to the objects in that report?
This utility was created to use a report of objects (Usually Cases) to download all files related to those objects into a structured directory with a folder for each object's files.
It works just as well if you just have a list of Content Document Links, or if you want, to download all files into the same directory.
Originally developed to meet Legal Discovery Requests, it is also been used for Freedom of Information Act (-ish, Sunshine, . . .) requests.
Basically, if you give it a list of objects that you want all files related to those objects extracted, and a directory for each object you would like the files for each object to be loaded into, it will create (or use) the specified sub-directory and use a Salesforce Connected App to download all related files into those sub-directories.
You can run the utility several times and it will accrue addition downloads and intermediate work tables each time. To start from scratch, you need to delete the created tables and remove the downloaded files and directories.
- Microsoft Excel (Business professional preferred)
- Enable Excel Tools References for HTTP Requests, JSON Parsing, and File/Directory Handling
- Microsoft workstation (it may work on crApples, but don't bet on it)
- A SF REST Connected App)REST API Developer Guide - Quick Start
- A Salesforce account capable of OATH2 and Password and Token for that account (View Profile-->Settings-->My Personal Information-->Reset My Security Token
- A Salesforce User with Permissions necessary to access the desired CDLs and ContentVersion records
- Apex REST Service
- API Enabled
- View All Data (Careful!)
- Assigned Connected Apps-->[Connected App]
- Access Libraries
SFDownloadFiles has a multi-tiered directory structure which is by default, 3 tiered:
[root]-->[working]-->[file Directories, optional]
The root directory contains critical connection information that you almost NEVER want to go out - for example, the connection authentication configuration
This is the base directory for the Excel Macro file, and the top level directory for all other relative sub-directories specified in the linked Objects List
File Directories are often sub-directories of the working directory. If used like this, the entire working directory can be zipped (after cleaning of course) for distribution to the requesting party.
../AllDownloadRoot/ThisRequest/[file directories]
The Connection Configuration file would be resident in the [root] folder ("AllDownloadRoot"), The SFDownloadFiles.xlsm file (or whatever you choose to rename it to) would be in the [working] directory ("ThisRequest"), and when the workbook is run, a directory for each entry as found in the Directory Column is created and all files relating to that object ID are loaded into that directory.
So for the following (which is 3 Case Ids putting all documents related to each in the Directory defined by the CaseNumber
| Directory | LinkedEntityId | Associated Docs |
|---|---|---|
| 000001 | 500xxxxxxxxxxx1AAY | |
| 000002 | 500xxxxxxxxxxx2AAY | |
| 000003 | 500xxxxxxxxxxx3AAY |
would result in a directory structure of:
Six things need to be setup - The first two already discussed.
- A SF REST Connected App)REST API Developer Guide - Quick Start
- User with Appropriate permissions to the content document links, the content documents, and access to the Connected App
- Local Directory Structure with Excel Utility in "working" directory
- Configure SFDownloadFiles Authentication Configuration File
- Configure SFDownloadFiles Configuration Tables
- Configure List of Object Links and Download Directories
Download and copy the Excel Macro Workbook into the working directory of the above Structure (.e.g, ../[root]/[working})
The authentication file contains information necessary to make and maintain an API REST Connection using OAUTH2. The information necessary to complete this file is a combination of User Authentication and Connection Authentication.
There is a Connection Authentication information template (JSON format) which should be used to create the Connection configuration file (Default: ../[root]/SFDownloadConfig.json). This file is found in the [root] folder to help prevent it from being accidently sent to the discovery recipient when the information is packaged.
There are two kinds of Connection Authentication information in this configuration file; User and App authentication.
The connection information is found in the OATH2(API (Enable OAuth Settings)-->Enable OAuth Settings) Connected App you set up previously. I find it easiest to go to the App Manager in setup, select the connection, then click "View" in the drop-down.
The User Authentication information can be found in the User Settings and the "My Security Token" which is sent to that user when requested from within View Profile-->My Personal Information-->Reset My Security Token.
These Configuration tables can be found on the "SFDownload Parameters" worksheet in the SFDOWNLOAD_GLOBALPARAMS and the SFDOWNLOAD_SOURCESETTINGS Tables. Do not change the name of this worksheets, the names of the tables, or the names of the parameters. This sheet is protected so (by default) you can only change the parameter values themselves.
These parameters will work for the basic, un-modified download. For advanced usage, see Advanced Features
You can create a basic table of Object links by clicking the "Download Files" to create a template table on a template worksheet, (Linked Entity Table) if one does not already exist.
Once you load the list of Object Ids into that table (in the "LinkedEndityId" column), you can get fancy by adding directory information for each Related Object.
Once you've complete all configuration, go to the "SFDownload Parameters" sheet and click on the "Download Files" button. By default, this will create supplementary sheets to further identify which files should be downloaded, create any specified directory (if files are loaded there), and fill out the "Associated Files" column to indicate how many files were loaded for each and a link to the directory they were loaded into. If the directories exist, files will be loaded into them. If there are already files in those directories (or files with the same name), new versions will be created and the existing files left intact. If configured to load multiple versions, all files except the IsLatest will add the version to the file name when downloaded.
File names may be adjusted automatically to allow storage on a Microsoft station or server - invalid characters are modified to "-".
You will generally not want to distribute some of the auxiliary information created for your use in inventorying the downloaded information, so it is best to clean out once you're sure you have what you were looking for. This includes the following which can be done with the "Clean Up for Transfer" Button:
- Remove inventory sheets
- Remove macros
- Convert to .xlsx
- Delete old .xlsm (Optional)
You may also want to remove any remaining columns unnecessary to the recipient manually.
- (optional) create a new zip file from everything in the [working] directory
- Make sure you do NOT include the Connection Configuration JSON file (.json)
The coolest part of this utility is the ability to use reports generated on Salesforce, Exported as Excel file, then modify the configuration to use the appropriate columns. To us an example, an executive wants to see all the issues (cases) submitted about a certain product line along with any documentation submitted with those issues. To do this, you would need to add add the ID of the Case to the report and probably the case number, along with any other fields (such as Description, submitted date, . . .). You would copy the exported spreadsheet into the Excel Utility (SFDownloadFiles.xlsm) and then configure the parameters below. You end up with a complete report with a column at the end that allows you to see how many files were related to that case, and when you click on it, takes you to the directory where they are held.
These are settings that determine globally how the document downloads will be performed
This specifies the name of the file located in the root directory - the one above the working directory where SFDownloadFiles.xlsm is located. There is a template for this JSON file here with embedded instructions.
The API version you wish to use to download from the target org in the format vXX.0 (example, v52.0)
SNOTE files (comments, etal) can also be downloaded. Set to false to disable this capability. Note: When you download SNOTE files, they will be converted and stored as .html files.
SFDownloadFiles will download all versions of the files using the naming convention of filename.[extension] unless this value is set to true. Then it will only load the latest version and not modify the filename.
This is more for debugging and should normally not be used. It will create a detailed log file of the interactions with the Salesforce org - which can get long and messy if not needed.
Set to True by default, if you set this value to false, it will generate the metadata required to access the files, but stop short of creating the directory structure and downloading the files themselves.
By defining what you want to use to guide the downloads, you can use existing reports or even a report you've created externally (even if it's just two columns of "Directory" and "LinkedObjectId". To do the download of the report shown above, 3 parameters were modified:
Changed Parameters for Exported Salesforce Report
You can use any table you want to define which files related to specified objects should be downloaded. Simply modify this parameter in the table.
An intermediate sheet and table are created to sustain the relationship between the original object and the Content Version eventually downloaded. This is the list of Content Data Links (CDLs) which are indicated in the Linked Table
The actual table of Content Version information (intermediate product) and sheet which is used to create the relationship and download the file.
This is the name of the column in which the RecordId of the related objects is stored. It must contain only record ids and those record ids must be a valid type for relating documents. If this Id is a Content Document Link ID type, only the versions related to that CDL will be downloaded. Otherwise, it will select and download all files related to that object.
This allows you to create a unique directory for each object, and thereby logically segregate the downloaded files for viewing. A normal use of this might be the CaseNumber, so that all files related to a particular case are loaded into the same directory. The utility will build this directory relative to the working directory of the SFDownloadFiles.xlsm
If you specify this, that column will be used to contain the count of files downloaded for the ObjectId in the LinkedID column and create a hyperlink which can be clicked on to go directly to that directory specified in the Local Relative Directory Column
This is used to create a template worksheet with a template linked object ID table in it if one doesn't already exist.
If you run the download multiple times, it will NOT overwrite existing information. Instead it creates a versioned sheet for each intermediate run.
If you run the download multiple times, it will NOT overwrite existing information. Instead it creates a versioned sheet for each intermediate run.
This Macro can be found and executed in the Macros. After you have downloaded all of the files/notes you need, you can use this macro to convert these snote files back into something usable in a text field such as a comment, description, . . .
It uses the information in the Content Version Table created by SFDownloadFiles (before cleaning out for transfer), and creates a "Comment" field with the text converted from the HTML downloaded as an snote. You can then use this field to update or create a new comment or field with DataLoader or your other favorite tool.
Note: There is a 32,767 character limit for SF text fields, and any excess will be truncated.
To use it, make sure the Content Version Table table has been created and is referenced appropriately in the setup parameters, then just run the macro. If a "Comments" column does not exist in the table, it will automatically create same.
Out of the Box, your version of Excel may need to have add-ins enabled to run this utility. The fastest way I've found is to go into Developer-->Visual Basic(Microsoft Visual Basics for Applications) and enable those you see in the list below:
- Visual Basic for Applications
- Microsoft Excel 16.0 Library
- OLE Automation
- Microsoft Office 16.0 Library
- Microsoft Scripting Runtime
- Microsoft ActiveX Data Objects 2.8 Library
- Microsoft ActiveX Data Objects Recordset 2.8 Library
- System
- ScriptingObjectModel
- Microsoft HTML Object Library
Note: If the **Developer **tab doesn't show in the ribbon, you can click File > Option > Customize Ribbon, then check Developer in the right pane, and it will display in the ribbon.