In this blog post I teach you how to convert GoogleDoc records into Salesforce Files via Google Drive external data source.

Google Docs, Notes, & Attachments

Prior to April 2015, Salesforce customers who used Google Apps (later renamed to G Suite) were able to create and link documents, spreadsheets, and presentations to records as an alternative to uploading a physical attachment. This feature required enabling Google Apps in your org, and once enabled also renamed “Notes & Attachment” related list to “Google Docs, Notes, & Attachments”.

salesforce-google-docs-pre-2015.jpg

After April 2015, according to this help article, Salesforce changed the integration citing changes to Google’s API. You can now only create bookmarks to the URLs of Google Drive files this way.

salesforce-google-docs-post-2015.png

To help compensate for this loss in functionality, in the Summer ’15 release Salesforce introduced capabilities for connecting Google Drive with Files Connect.

Converting to Files

I’m a big fan of Salesforce Files and have a dedicated page with Dreamforce sessions and multiple open source projects I’ve developed all around adopting Files & Enhanced Notes.

Jeff Issenberg, after successfully using my open source apps to migrate his org’s Notes & Attachments then asked about migrating GoogleDoc records to Files too. Specifically, Jeff wanted to migrate the GoogleDoc records into external file references via Google Drive using the Files Connect feature.

jeff_issenberg_googledoc.png

The GoogleDoc object is very simple with few fields, the important ones are:

  • Name – name of the file/bookmark the user gave it
  • URL – link to the Google Drive document
  • ParentId – Salesforce record the document is related to

The ContentVersion object, which represents Salesforce Files, can be used to either store actual files uploaded to Salesforce or it can be used as a bookmark to a file that lives in an external repository like Google Drive or SharePoint. In this blog post we want to do just that, migrate the GoogleDoc bookmarks into ContentVersion external file references and take advantage of all that Salesforce Files and Files Connect provides us. The important fields for this are:

  • ContentLocation – use value “E” to indicate external file
  • Origin – use value “H” to indicate this is a Salesforce File
  • Title – name of the file (GoogleDoc.Name)
  • PathOnClient – name of the file (GoogleDoc.Name)
  • ExternalDataSourceId – ID of an External Data Source for Google Drive
  • ExternalDocumentInfo1 – link to the Google Drive document (GoogleDoc.URL)
  • ExternalDocumentInfo2 – ID of the Google Drive document (parsed from URL)
  • FirstPublishLocationId – Salesforce record ID to relate file (GoogleDoc.ParentId)

To do the migration we don’t need any Apex code, this can be accomplished entirely with Data Loader and spreadsheet functions.

Instructions

  1. Enable Files Connect and create External Data Source for Google Drive
  2. Export GoogleDoc records, the Name, URL, and ParentId fields, to .csv file
    googledoc-export.png
  3. Add the ContentVersion columns to the .csv file, mapping the GoogleDoc columns.
    googledoc-import.png
  4. Import the .csv file to ContentVersion object
  5. Verify that files linking to Google Drive now appear in the “Files” related list
    googledoc-as-files.png

The process is very straight forward data loader exercise, the only challenge was extracting the Google document ID from the URL. To save you the headache, you can refer to this Google spreadsheet template that includes the formulas and sample data.

If you did want to use Apex code for some reason, check out this gist.

public class GoogleDocToFilesBatchable implements Database.Batchable<SObject>, Database.Stateful {
private ID externalDataSourceId { get; set; }
public GoogleDocToFilesBatchable( ID externalDataSourceId ) {
this.externalDataSourceId = externalDataSourceId;
}
public Database.QueryLocator start( Database.BatchableContext context ) {
return Database.getQueryLocator([
SELECT
id, name, url, ownerId, parentId,
createdById, createdDate, lastModifiedById, lastModifiedDate
FROM
GoogleDoc
ORDER BY
parentId
]);
}
public void execute( Database.BatchableContext context, List<GoogleDoc> googleDocs ) {
List<ContentVersion> filesToInsert = new List<ContentVersion>();
for ( GoogleDoc gd : googleDocs ) {
String gdocId = gd.url.substringAfter( '/d/' ).substringBefore( '/' );
filesToInsert.add( new ContentVersion(
contentLocation = 'E',
origin = 'H',
ownerId = gd.ownerId,
title = gd.name,
pathOnClient = gd.name,
externalDataSourceId = this.externalDataSourceId,
externalDocumentInfo1 = gd.url,
externalDocumentInfo2 = gdocId,
firstPublishLocationId = gd.parentId,
// Enable "Create Audit Fields" in Setup then you can
// preserve the original create/update values and owner if original owner is inactive
// https://help.salesforce.com/articleView?id=Enable-Create-Audit-Fields&language=en_US&type=1
createdById = gd.createdById,
createdDate = gd.createdDate,
lastModifiedById = gd.lastModifiedById,
lastModifiedDate = gd.lastModifiedDate
));
}
insert filesToInsert;
// optionally, can delete the GoogleDoc bookmarks since now redundant of Files
// delete googleDocs;
}
public void finish( Database.BatchableContext context ) {
}
}

view raw
GoogleDocToFilesBatchable.cls
hosted with ❤ by GitHub