At the end of this article, you will learn how to deploy your Google Sheets code written in Typescript on your local machine, in your favorite IDE and deploy the code with a single command: git push origin
Introduction
During every code development process, we always test the changes before applying them to the production environment. This is an inevitable part of every software development lifecycle.
Different frameworks provide different methods of running the tests and automating the deployment process. At the time of this writing and up to my knowledge, the Apps Script does not.
Luckily there is the awesome tool named clasp that allows you to develop your Apps Script projects locally. That means you can check your code into GitHub source control and use your favorite tools (I use VS-Code) to develop Apps Script scripts. Best of all, it allows you to automate the Apps Script code deployment by using Github Actions.
What are the Development and Production Environments and Why Does it Matter?
Note: When talking about development and production environments in this article, I will be actually talking about the development and production version of a spreadsheet document.
When you start working on a project, technically you do not need two environments. You can work on a single spreadsheet. Implement new features, experiment, fix bugs, and repeat. When you are satisfied with the result, share the document with the end-user, and you are done.
Really?
After working on your shiny sheet for a while and updating the sheet data, the user might encounter some bugs or request new features to be implemented. You need to investigate the request, do some additional development, test, and repeat. But, do you want to do it on the live document while the user is using it? Of course not. This is where the development environment comes into place.
The development environment is where you will be making code updates, and trying out new features and updates before pushing them live. The development environment does not affect what the end-user sees.
The simplest approach is probably to open the live (“production”) spreadsheet and make a copy of it. Then we work on the copy and when done, copy the changes back to the production. We’ve all been there and we know that this process is slow, cumbersome, and error-prone.
Let’s see if there is a better way to do it.
What are we Going to Build?
We are going to build an automated system that will be automatically deployed to one of the destination spreadsheets when the code is committed to the GitHub repository. Depending on the branch pushed, the deployment will update different documents and will be aware of running on the dev or prod spreadsheet. We will keep it simple for demo purposes and only the menu title and the dialog popup content will be different, depending on the spreadsheet (production or development) used.
Development
Production
Setup the environment
Start by cloning the repo and installing all required tools and dependencies:
$ git clone https://github.com/gkukurin/automate_apps_script_deployment.git
$ npm install –-also=dev
We will use the Google’s clasp tool to handle the creation of new spreadsheet documents and code deployment. If you didn’t do this before, visit https://script.google.com/home/usersettings and enable the Apps Script API. It is required for clasp to run.
Then login to your Google account with:
$ npx clasp login
This will create the credentials file $HOME/.clasprc.json. Keep this file safe, it contains the credentials that grant access to your Google Drive and different other permissions. We will use this file later in our CI/CD script.
Create the spreadsheets
We are going to create two Google Sheets documents. One will be used during development, and the other as the production-ready document.
$ npx clasp create –-title MySheetPrd –-type sheets -–rootDir src
$ mv .clasp.json .clasp-prd.json
$ npx clasp create –-title MySheetDev –-type sheets -–rootDir src
$ cp .clasp.json .clasp-dev.json
The npx clasp create command, besides creating the spreadsheet on your Google Drive, creates a small configuration file (.clasp.json) that contains important information about the created document. As we will use two documents as possible destinations for our code, we must rename the default file to .clasp-prd.json and .clasp-dev.json respectively.
Secrets
As already mentioned earlier, clasp needs your credentials to deploy the code to the spreadsheets you’ve created. I also warned you about keeping the $HOME/.clasprc.json safe. But as our final goal is to copy all required files and tools to Github, how should we handle this file?
Storing it in plain text to the Git repository is definitely a bad idea.
The recommended approach is to encrypt the credentials file and store the password to GitHub secrets. Run this command and add a strong password when prompted:
$ gpg -o .clasprc.json.gpg –-symmetric -–cipher-algo AES256 $HOME/.clasprc.json
Next, go to your GitHub repository and add a new secret named CLASP_SECRET. CLASP_SECRET is the custom name that we will use in our GitHub Actions configuration config.yml. You can use any name, but make sure you update the configuration files.
Deployment
We are ready to go! There are three different ways how to deploy the code changes.
1. VS Code – run a build task
If you use VS Code you can take advantage of the build tasks that are already configured for you. Hit Ctrl-Shift-B (or select Run build task from the menu) and select Development or Production from the dropdown menu.
Running the VS-code tasks
2. Run the Node script
$ npm run deploy_dev
or
$ npm run deploy_prd
3. Push the code to GitHub
Final and probably the most exciting possibility, and the reason why I wrote this post 🙂 is to run:
$ git checkout develop
$ git push origin
or
$ git checkout main
$ git push origin
The code will be committed to your GitHub repo, and after a short while, the code will be deployed.
You can find the accompanying code for this blog post on the GitHub repository: https://github.com/gkukurin/automate_apps_script_deployment
Originaly published at https://gorankukurin.com
Top comments (0)