All code samples can be found on Github. Note that that VBA samples have been developed for Office for Windows and may need some tweaking for Office for Mac.
What is incremental migration ?
The process of migrating assets and workflow from Office to Apps is fairly straightforward.
The ‘magic happens’ step is not so hard for as long as you don’t have VBA automation and documents which are dependent on each other.
Sets of documents with dependencies that can be isolated can of course be migrated. But this is more complicated as it usually involves coordination between processes and groups of people, but nevertheless, if a cutover period is tolerable and can be scheduled and the dependencies manageable, it can be done.
When there are documents, automated processes and data that span multiple subject areas and migration timetables that cannot be coordinated, a different process is needed. One in which assets can be partially moved, and automation which can be incrementally delegated.
VBA Migration process
This is an example of some VBA automation that accesses or maintains various Excel workbooks. The data and logic modules represent the pieces of VBA code that access the data or contain the business process logic for manipulating the data.
There may be various reasons that this entire process cannot be scheduled to happen all at once. Some examples could be;
- The workbooks(s) may be shared with a different subject group or process which is not ready to migrate.
- The automation may access databases or other resources which are not scheduled to move to Apps.
- There is no resource available to migrate the processing from VBA to Apps Script, but a need to move the workbooks to Sheets to support a subject group or process that has already moved.
The Execution API
This API allows assets and automation to be migrated a chunk at a time.
Workbooks can be migrated to Sheets but still accessed from VBA by simply substituting the data access code in VBA to delegate data access to Apps Script.
Some or all of the logic functions can be migrated to be executed by Apps Script, returning the result to VBA for orchestration
- Adding abstracts to documents
- Bulk search and replace images in Google Docs
- Converting Google Docs table to JSON and back
- Execution API and Office to Apps migration
- Hosting files on Google Cloud Storage
- Using Object.create in Apps Script