These articles are abridged from my book on Office to Apps migration. Going GAS, from VBA to Google Apps Script. Now available directly from O’Reilly or Amazon.
As with most VBA authentication processes on this site, authentication is handled through the VBA cOauth2 class and the VBA wrapper function getGoogled.
The project that contains the Apps Scripts to be executed remotely is the one that provides the scopes and the credentials, and can be accessed through the developers console entry for the project.
Enabling the execution API
The Execution API needs to be enabled through the Developers console in every project that will be accessed remotely.
Credentials
Publishing
Just like a webapp, a version of the project needs to be saved and published, but this time using the ‘Deploy as API executable’ publishing option. The API ID matches the project key in the project properties (and is the same key as used when accessing as a library).
Scopes
The API itself needs no particular scope. Instead, the authentication scopes are those required by the Apps Script project to be executed. In other words, the scopes specified when setting up getGoogled should match the scopes in the Apps Script project properties.
For the example in this demo, the only scope needed is spreadsheets.
Getting googled
Now you can set up the PC to be able to access this project, using cOAuth2 in VBA. This writes some information to the PC registry that is used behind the scenes for token management.
Firstly you need to add the project key to the known projects and define which scope(s) it’s going to need.
- locate the addGoogleScope function in the cOauth2 class.
- add this line with your project key. There are other standard entries there you can use as a reference.
Case "...project key"
.add "scope", URLEncode("https://www.googleapis.com/auth/spreadsheets")
First time use
For first time use, you need to provide the credentials. Since these credentials and scope are specific to the project being executed, the project key is used to store the credentials against, as in the example below.
This should be run once, then deleted (it’s neither a good idea nor necessary to keep credentials in code).
Private Function onceOff()
getGoogled "...project key", , _
"......client id", _
"......secret"
End Function
That’s it – now the project can be accessed by VBA
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.
Other items in this section
- Incremental migration – the steps involved in moving workload from Office to Apps in manageable chunks.
- VBA and Apps Script inventory – what’s needed, and examples of orchestrating the Execution API from VBA.
- Authentication and access – how to authenticate to and access the Execution API from VBA.
- JSON and VBA – how to deal with JSON and navigate JavaScript like objects in VBA.
- Reading and writing Sheets from VBA – manipulate sheet data directly from VBA.
- Migrating logic – delegating logic tasks to Apps Script from VBA.
- Migrating orchestration – the migration is almost complete. Move the orchestration tasks to Apps Script.
- Final migration steps – In most cases the Office workflow can be retired. However there are cases when legacy dependencies still need to be maintained for some period.
- Testing Javascript on the PC – you can even use the Execution API to retrieve Apps code to execute locally on the PC. A great tool for early testing of migrated code.
For more like this, see Execution API and Office to Apps migration
Why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available.